Backup a single table – MySQL

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.

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>


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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s