Dump a complete MySQL database as SQL
|Debian (Etch, Lenny, Squeeze)|
|Ubuntu (Hardy, Intrepid, Jaunty, Karmic, Lucid, Maverick, Natty, Trusty)|
To dump the complete content of a MySQL database as a series of SQL statements
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’.
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
Dump the database
Once the required permissions have been established, the backup can be performed using the
mysqldump -u backup -p finance > finance.sql
mysqldump sends its output to
stdout, hence the redirection to record it in the file
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:
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 -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.
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.
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
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.