It is occasionally necessary to take a table backup before applying data corrections or structural modifications to a table. In the event of a problem, the table backup assists in restoring normalcy.
There are numerous ways to accomplish this in MySQL, as discussed in this post.
Using MySQLdump
mysqldump -u <username> -p<password> -h host db_name table_name > yourpath\table_name.sql
The above command will help in backup the specified table to specified location.
Example:
mysqldump -u <username> -ppassword -h host TestDB TestTable1 > C:\DBBackups\TestTable1.sql
If you want to backup multiple tables then use the below command.
mysqldump -u <username> -p<password> -h host db_name table_name1 table_name2 table_name3 > yourpath\backupname.sql
If you want the table to be compressed into gzip format
mysqldump -u <username> -p<password> -h host db_name table_name | gzip > yourpath\table_name.sql.gz
Table Copy
If the backup is just temporary, there is no time for backup and restore, and you need a quick solution to restore deleted data, the following is a better option.
CREATE TABLE <tableBackupName> AS SELECT * FROM <YourTable>
DUMP AS A FILE
First check what is the default file location path that was configured.
SHOW GLOBAL VARIABLES ‘%secure_file_priv%’;
This command displays the directory that has been specified. Your output file can only be saved in this directory. If you want, you can configure/change the file location. Once you change it use that location in the below command.
SELECT * FROM <tableName>
INTO OUTFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/backupfile.sql’;
Hope you find this article helpful.
Big Data and SQL 