Mysqldump

mysqldump is a command line utility which exports database table definitions and data from MySQL servers. It is typically distributed along with the standard MySQL client software.

The convenience of mysqldump is its ability to export entire databases, or any selected tables, while the server is still running, into a text file containing just SQL. If necessary this file can be modified using standard text tools. This dump file can then be read by any MySQL server while it is also running. mysqldump is ideal for hot backups of small databases.

One drawback of mysqldump is the necessity for the MySQL server to read and translate all data back into SQL. This can be very time and process intensive for large databases. To move a database it's much simpler and quicker to move the raw data files, although the server must be shut down to safely do this.

Common Uses

A very simple backup routine might include the following, run from the command line:

mysqldump -u backup -p mydb | gzip -c - > /mnt/backups/sql/db_$(date +%Y%m%d_%H%M).mysql.gz

Another useful example is to easily and securely transfer databases between servers running on two computers with one command, in this case from a remote server to the local one:

# This is just an example. For security reasons you may not want to include the password this way 
# as it will be stored in your command line history. This example also assumes both databases are
# set to store text in the latin1 character set.
ssh servername "mysqldump -u username --password=xyz --default-character-set=latin1 -N database | gzip -c -" | \
gunzip -c | mysql -u username --password=xyz --default-character-set=latin1 database

Character Sets

One peculiarity with mysqldump is its potential corruption of character sets other than latin1. A very common use case is the need to preserve UTF8 characters in mysql dumps. If Unicode is stored in the database, but table character sets are set to latin1, the Unicode characters will be corrupted during a dump and import. The solution is to tell mysql to use the latin1 character set in both the dump and import connections to avoid any conversions.

In cases where UTF8 or any other character set is used throughout, and all database connections and table definitions are set to this characters set, there is no potential for corruption. When setting up a database it's best to choose the most appropriate character set and apply the appropriate settings to all table definitions and connections.

To avoid corruption of non-latin1 strings when the database is configured for latin1:

The output must be set to latin1, with no SET NAMES in the file. This actually tells the mysqldump command to not re-encode the file (i.e. output strings just as it sees them, even if in another char set). Use the mysqldump parameters --default-character-set=latin1 and -N.

The input must also be set to latin1. This, plus the missing SET NAMES sql command, prevents the mysql command line interpreter from re-encoding the file. Use the mysql parameter --default-character-set=latin1.

So, for example:

mysqldump -u username -p --default-character-set=latin1 -N database > backup.sql
mysql -u username -p --default-character-set=latin1 database < backup.sql

This method, theoretically, will work for any character set, so these parameters can always be used with every database dump. MySQL conf files can also be set appropriately to avoid having to always include this in the command line.

See Also

• MySQL
• Unicode