Dump a complete MySQL database as SQL

Objective

To dump the complete content of a MySQL database as a series of SQL statements

Scenario

You have a database called finance for which you wish to create a backup copy. The database is hosted on the local machine and accepts UNIX domain connections. The backup is to be performed by a MySQL account called ‘backup’ with the password ‘xyzzy’.

Method

Grant the necessary privileges

If this has not already been done, first ensure that the backup account is known to the database system, has the expected password, and has permission to read the relevant database:

CREATE USER 'backup'@'localhost';
SET PASSWORD FOR 'backup'@'localhost' = PASSWORD('xyzzy');
GRANT SELECT, LOCK TABLES ON finance.* TO 'backup'@'localhost';

Note the application of the principle of least privilege:

• The backup account been given permission to read from the database, but not to write to it.
• The account is usable only when connecting from localhost.

Dump the database

Once the required permissions have been established, the backup can be performed using the mysqldump command:

mysqldump -u backup -p finance > finance.sql

By default mysqldump sends its output to stdout, hence the redirection to record it in the file finance.sql.

You will be prompted to enter the password. This is the simplest and safest way to provide it if you are performing the backup interactively, but is not well suited to non-interactive use. In the latter case the safest alternative is to put the password in a file called .my.cnf. This should be placed in the home directory of the system account that will be invoking mysqldump (which is not necessarily backup: system accounts and database accounts are distinct). It should have the following content:

[client]
password=xyzzy

The file mode should be set to 600 (readable and writable by its owner, but not by anyone else):

chmod 600 ~/.my.cnf

When using this method to supply the password you should omit the -p option from mysqldump:

mysqldump -u backup finance > finance.sql

Do not specify the password on the command line or using the MYSQL_PWD environment variable: both of these methods are highly insecure.

By default mysqldump produces SQL that is optimised for re-importing into MySQL. If this is not the intended use then the --compatible option can be used to request output that is more suitable for other purposes, however you should still expect to perform some post-processing if you want to import the database into a different DBMS.

Variations

Dump all databases

In order to create a backup of all databases the backup account naturally requires correspondingly greater privileges:

GRANT SELECT, LOCK TABLES ON *.* TO 'backup'@'localhost';

Rather than specifying a particular database as an argument to mysqldump you should instead use the option --all-databases:

mysqldump -u backup -p --all-databases > backup.sql

Note that if the backup account does not have permission to read all databases then those it lacks permission to read will be silently ignored.

• Conventions
• Privacy
• Legal