MySQL is an open-source and extensively used database management system. If you are using MySQL for storing your application data, then it is essential to take regular backups to have a good DRP (Disaster Recovery Plan) for your system. MySQL comes with an inbuilt utility/program called mysqldump for simplifying the backup process of your MySQL databases. This article will help you understand more about how to take the backup of your MySQL database using mysqldump and how to restore it to create the exact replica of your database.
mysqldump is a simple command-line program that creates a logical backup of your database by generating a simple set of SQL statements in a text file that can be executed to reproduce the same database with both schema and data. This command can be also used to generate the backup file in the format of CSV or XML format. This command can only be executed while mysqld process is running and accessible for mysqldump command. You can also use the generated SQL file to transfer your database to some other MySQL server. Keep in mind that, mysqldump command doesn’t copy the actual data (physical backup), instead it generates the SQL statements which can be executed later to restore the database (Logical backup).
Let’s go through some examples for performing different operations on your MySQL database to take backup using mysqldump command and we will also see how to restore the database using the generated backup.
How to Take a Backup Using mysqldump
Following is the list of all the most commonly used commands which can be used to take the backup of your database using mysqldump.
General Backup Command
$ mysqldump [options] [db_name [tbl_name ...]]
This is the general syntax for mysqldump command. You can provide multiple options with this command as explained below. Generally, all the commands include the username (-u) and password (-p) for authentication. If you are logged in as the same user that is running the mysqldump command, then it’s not required to provide the username option.
Backup of All Databases
--all-databases options will take the backup of all the databases and dump it in the all_databases.sql file.
$ mysqldump -u <USERNAME> -p <PASSWORD> --all-databases > all_db_backup.sql
Backup of Single Database
$ mysqldump -u <USERNAME> -p <PASSWORD> mydb > mydb_backup.sql
Backup of Multiple Databases
If you want to take the backup of multiple databases, then you can use --databases option along with specific database names.
$ mysqldump -u <USERNAME> -p <PASSWORD> --databases mydb1 mydb2 > mydb1_mydb2_backup.sql
Backup of Database Schema
You can use –no-data option if you just want to take a backup of the database schema.
$ mysqldump -u <USERNAME> -p <PASSWORD> –no-data mydb > mydb_schema_backup.sql
Backup of Only Data
If you want to take back up of only data and not schema then use -no-create-info option.
$ mysqldump -u <USERNAME> -p <PASSWORD> mydb –no-create-info > mydb_data_backup.sql
Backup of a Specific Table
$ mysqldump -u <USERNAME> -p <PASSWORD> mydb user_tbl order_tbl > user_order_backup.sql
mysqldump Ignore Tables
$ mysqldump -u <USERNAME> -p <PASSWORD> mydb --ignore-table=mydb.order_tbl > mydb_backup.sql
How to Backup Remote MySQL Database
You can also take the backup of databases which are hosted on the remote servers by providing the host info along with the other options.
$ mysqldump -h <REMOTE_HOST> -u <USERNAME> -p <PASSWORD> my_remote_db > my_remote_db_backup.sql
Where <REMOTE_HOST> can be the database server IP Address or Hostname.
Backup of All Databases in Different Files
This command doesn’t provide the functionality for generating separate backup files for different databases. In this case, we can use this simple bash for loop:
$ for DB_NAME in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB_NAME > "$DB_NAME.sql"; done
Sample Backup file
If you open any SQL file which is generated by mysqldump command, you will see SQL commands to create tables and insert data into the newly created table. Here is the subset of one such file:
-- -- Current Database: `mydb` -- DROP DATABASE IF EXISTS `mydb`; CREATE DATABASE IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1; USE `mydb`; -- -- Table structure for table `order` -- DROP TABLE IF EXISTS `order`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `order` ( `orderId` int(7) NOT NULL AUTO_INCREMENT, `orderAmount` int(6) NOT NULL, `orderItem` varchar(50) NOT NULL, .... ....
I hope this covers almost all the useful variants of mysqldump command which can be used in various scenarios.
Now, once we have taken the backup of databases, we will see how we can restore the databases using the SQL file created by the mysqldump command.
How to Restore the Backup Using mysqldump
Similar to the backup process using mysqldump, restoring mysql databases is also very simple. To restore any database, it is recommended first dropping any existing database to avoid conflicts:
mysql > drop database mydb; mysql > create database mydb; // Create fresh new database
Now, we will restore our data into this database:
$ mysql -u <USERNAME> -p <PASSWORD> mydb < /tmp/mydb_backup.sql
This will restore all the tables of mydb database into this new database.
If you don’t want to create the backup first in a SQL file and then restore it on a different server, you can use this simple one-line command:
$ mysqldump -u <USERNAME> -p <PASSWORD> mydb | mysql -h <REMOTE_HOST> -u <USERNAME> -p <PASSWORD> remote_mydb
This command will generate the output SQL file and the pipe command will directly restore this backup in the remote server into the remote_mydb database. Make sure that the remote_mydb database exists on the remote server before running this command.
It is very important that you know all the options provided by the mysqldump utility before you plan to take the backup of your production database. This will ensure that you are using the best approach to take the backup. You can use multiple mysqldump options together for efficiently taking backups. This will also ensure that you are taking backup of only required information instead of all the databases. You can also check the mysqldump man page to know the rest of the options not mentioned in this guide.
Database backup should be a regular process and sometimes you can forget to run the backup command if you are taking backups manually. To handle this, you can add this command in the crontab that will ensure that mysqldump command runs at every specified time interval and you never miss to take the backup of your data.