MySQL Backup and Restore Specific Table

“Backups are a very critical operation in modern applications. For example, when working as a database administrator, you must ensure the latest backup copies of your databases. This will allows you to perform restorations in case of failure or reverting to a specific timeline in the database changes.

Although backups are essential, they can take up large amounts of space, limiting the frequency of backups. The best way to overcome this is by backing up specific features of the database instead of the complete database.”

That’s what we will look into using this tutorial. You will learn how to back up and restore specific tables from a given database.

Let’s jump in.

How to Backup a Single Table Using MySQLDump

To backup a specific table from a database, we can use the command syntax as shown below:

mysqldump database_name table_name > filename.sql

The command above should export the specific table from the target database to the filename.sql file.

The command will default save the file in the current working directory. However, you can specify a path to the file, provided you have to write permissions to the target path.

You can also specify database credentials using the -u and -p parameters for username and password, respectively.

The command syntax is as shown:

mysqldump -u -p database_name table > /path/to/file.sql

Example

The following example illustrates how to back up a specific table from a given database.

NOTE: If you already have an existing database, feel free to skip the database and table creation statements.

create database users;
use users;
drop table if exists employees;
create table employees(
id int auto_increment primary key,
name varchar(100),
salary int,
active bool
) character set utf8mb4 collate utf8mb4_general_ci;
insert
into
employees(name,
salary,
active)
values ('Beatriz',
67000,
true),
('Ali',
87000,
true),
('Gabriel',
76800,
false),
('Hanna',
87000,
true),
('Eric',
100000,
true);

To back up the “employees” table, we can run the command:

mysqldump -u root -p users employees > employees.sql

The command above will prompt you for the password of the root user. Once authenticated, the command will export the employees’ table into the employees.sql file under the current working directory.

We can also export the database file as a compressed gzip format. The command syntax is as shown:

mysqldump database_name table_name | gzip > filename.sql.gz

How to Restore a Single Table From .SQL File

Once you have backed up your table into a specific file, you can restore it using the mysqldump utility and the less than the operator.

The command syntax is as shown below:

mysqldump database_name < path/to/filename.sql

The command will restore the table in the .sql file to the target database specified in the command.

If you need to specify the username and password, run the command:

mysqldump -u username -p < /path/to/filename.sql

If you are using a compressed gzip file, you can restore the table with the command:

gunzip < filename.sql.gz | mysql -u username -p database_name

Conclusion

In this post, you learned how to use the MySQLDump utility to back up and restore a specific table from a given database.

Thanks for reading!!

MySQL Backup and Restore Specific Table

How to Backup a Single Table Using MySQLDump

Example

How to Restore a Single Table From .SQL File

Conclusion

About the author

John Otieno