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.
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.
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
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:
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:
database_name with your target database. For example, to switch to the
sakila database, run the command:
Next, list all the tables in the currently selected databases using the command:
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
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.
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.