How to Backup a Single Table Using MySQL Dump

Database backups are a very fundamental task for any database administrator. However, you may encounter a scenario where you do not need to backup an entire database but instead, you would to backup a single or subset of tables.

In this tutorial, we will show you how you can use the MySQL Dump utility to backup a single database table in very few steps.

What is MySQL Dump?

If you are not familiar, the MySQLdump is a command-line utility provided by MySQL that simplifies the process of backing up and restoring databases from the terminal.

It has a wide array of options allowing you to customize the backup and restoration features. Similarly, it allows you to selectively include or exclude specific items during a backup or restore operation. This makes it a very useful tool when you need to backup specific database objects.

Requirements

Ensure you have the following

• MySQL Server installed and running on your system.
• Access to the MySQL server either as a root user or any user with necessary permissions to the database and the table that you wish to back up.
• mysqldump tool installed.

Identify the database and the table you want to backup

The first step is to identify the table you wish to backup. If you already know the name and the database on which the table resides, you can skip this step.

Otherwise, start by logging to the MySQL Server CLI:

mysql -u root -p

Replace the root username with your database username. Enter the password for the specified username which allows you to login to the server.

Finally, show the databases available in the server with the command:

SHOW DATABASES;

This should return a list of all the databases available in the server.

Select the database containing your target table.

Next, use the use command to switch to that database:

USE database_name;

Replace the database_name with your target database. For example, to switch to the sakila database, run the command:

USE sakila;

Next, list all the tables in the currently selected databases using the command:

SHOW TABLES;

This should return a list of all the tables in that database. Note down the name of the table you wish to backup.

Backup a Single Table Using mysqldump

The final step is to log out of the MySQL interface and use the mysqldump utility to backup. Use the exit command to exit the MySQL Shell.

To backup a single table using MySQL Dump, run the command as shown in the syntax below:

mysqldump -u username -p database_name table_name > backup.sql

Remember to replace the username, database_name, and table_name options as desired. The file backup.sql is the file that will be created with the backup data.

The command above will prompt you for the specified username. Once authenticated, the command will create a SQL dump file in your current working directory with the specified name.

The resulting SQL file contains a series of SQL statements that allows you to re-create the table and populate it with the original data. Hence, you can use it as a standalone unit and even re-create it in a different database.

Conclusion

Using this tutorial, you learned how we can use the MySQL Dump utility to quickly and efficiently backup a single table from an existing database. Whether its performance concerns, disk space usage, or simply the need to backup one table, this tool will come in handy.