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:

• SELECT
• 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.

Next: Restore MySQL database backups