MySQL comes with a utility called mysqldump that allows you to take a backup of your MySQL database. It generates a text file containing SQL statements that you can use to recreate your database on any MySQL server.
Using mysqldump is pretty simple:
mysqldump -p -u username database_name > database_dump.sql
-p is used to specify the password. If you are using mysqldump interactively you should omit the password and wait for mysqldump to prompt you for it. If you are running mysqldump from a script you will need to embed the password. I discuss how to do this later on in the article.
-u is the MySQL username.
And finally you add the database name and the dump file name.
Dumping multiple databases
mysqldump can also be used to backup multiple databases:
mysqldump -B -p -u username database_1 database_2 database_3 > combined_db_dump.sql
The -B switch is what tells mysqldump to look for multiple database names in the command. If you omit the -B switch it will treat the first one as the database name and subsequent ones as table names.
Backing up all MySQL databases
If you want to backup lots of databases it’s easier to use the all databases option. When you do that mysqldump will backup all the databases to which a user has access rights:
mysqldump -A -p -u username > all_db_dump.sql
If you want more compact dumps you would do well to use the extended insert option:
mysqldump -e -A -p -u username > all_db_dump.sql
The -e switch tells mysqldump to use extra long insert statements that insert multiple rows into a table in one go. This reduces the size of the sql file and also makes restoring backups faster.
Compressed MySQL backups
If you want to further reduce the size of the sql file you can pipe it to gzip which will compress the file:
mysqldump -e -A -p -u username | gzip > all_db_dump.sql.gz
MySQL user privileges for backups
MySQL has an extensive user privileges system and in order to backup a MySQL database you need a MySQL user with a minimum of two privileges:
- LOCK TABLES
Most likely you already have a MySQL user with access rights to the database. This is the user your web application or content management system is using to read and write to the database. But if you are backing up multiple MySQL databases you may want to create a user exclusively for that purpose. The advantages of this approach are:
- You can restrict the user’s privileges to read only access. This is better from a security point of view.
- You can add the user to multiple databases and back them all up in one go using the all databases option.
The first step is to log into your MySQL server as root:
mysql -p -u root
Once you enter the root password you’ll get the MySQL command prompt. Enter the below command to create a new user specifically for the purpose of taking backups:
CREATE USER 'username'@localhost IDENTIFIED BY 'password';
And then to grant rights to databases:
GRANT SELECT,LOCK TABLES ON database1.* TO 'username'@localhost; GRANT SELECT,LOCK TABLES ON database2.* TO 'username'@localhost; ... GRANT SELECT,LOCK TABLES ON databaseN.* TO 'username'@localhost;
Scripting MySQL backups.
It’s always a good idea to automate backups. When running mysqldump from scripts you have to include the password as part of the command:
mysqldump -eAp'password' -u backupuser | gzip > all_db.sql.gz #backup all databases;
It’s important to note that there is no space between the -p switch and the password. If you include a space the command will fail and, as can be seen below, your password will be exposed in the system’s list of running processes. So if someone runs the ps or top command he can see your mysql password but only if you mistype the command:
1000 5912 10276 0 07:36 pts/2 00:00:00 mysql -p dumbpass -u dumbuser 1000 5919 10276 0 07:36 pts/2 00:00:00 mysql -px xxxxxx -u smartuser
Another alternative is to create a .my.cnf (note the leading period) in your home directory and place your MySQL username and password in it:
[mysqldump] user=username password=password
Now you can run mysqldump without providing a username or password:
mysqldump -Ae |gzip > all_databases.sql.gz ## backup all databases;
The downside of using a .my.cnf file is that anyone who gains access to your user account also gets access to your mysql database account. You definitely don’t want to use a .my.cnf file in a web accessible folder.