Backing up a MySQL or MariaDB table or database

You can backup a table in a MySQL or MariaDB database by using the mysqldump backup tool included with both database management systems. To backup a specific table you can use the command mysqldump database_name table_name where database_name is the name of the database that contains the table and table_name is the name of the particular table you wish to backup. E.g.:

mysqldump -u ptolemy -p Planets Mars > Mars.sql

In the above case, if I have a database named Planets with a table within it named Mars, I could backup just that one table from the database with the command above. The -u option to the command allows you to specify a MySQL/MariaDB username that has access to the database and the -p option will prompt you for the password associated with that username. The Mars.sql file will then contain all of the Structured Query Language (SQL) commands needed to recreate the table structure and the data in the table. E.g., it will contain insert SQL commands that will insert entries in the table.

If you wish to backup the entire database, you can omit the table name after the database name, e.g., if I wanted to backup the entire Planets database in the example above, I could use the command mysqldump Planets > Planets.sql. If the account from which I was running the command wasn't one with access to the database, I could again specify a username with the -u option and ask to be prompted for the password with the -p option. To backup multiple databases at once, I could use mysqldump --databases db1 db2 db3 > outputfile.sql. You can also use --databases for just one database or you can omit it, if there is only one database to backup. To backup all databases on the system, you can use mysqldump --alldatabases, which might require specification of the root MySQL/MariaDB account, e.g. mysqldump -u root -p --alldatabases .

To restore a table to a database from a dump file of SQL commands, you can issue the command mysql -u user_name -p database_name < backup_filename. E.g. to restore the Mars table in the Planets database from the first example, I could use mysql -u ptolemy -p Planets < Mars.sql. You need to specify the database, e.g., Planets in this case, to which the table should be placed. Any existing table of that name will be dropped first. If you backed up one database, you can specify the databasename on the command line, or you can edit the backup file to put a use database_name; command at the top of the file. Alternatively, you can obtain the MySQL/MariaDB prompt by just entering mysql at the command line and then typing commands as shown below:

mysql -u username -p
mysql> use database_name;
mysql> source backup_filename;

If the database doesn't already exist, you will need to create it first with the create database database_name command. E.g., if I backed up the Mars table as in the example above, but then needed to put it in a new database called Planets:

mysql -u username -p
mysql> create database Planets;
mysql> use Planets;
mysql> source Mars.sql;


  1. Backing Up a MySQL Database
    Posted: Sunday, October 16, 2011
    MoonPoint Support