About mysqldump

mysqldump is a command line utility for exporting database table definitions and data from MySQL. Usualy, it is distributed together with the standard MySQL software.

Here is a simple backup procedure that could be run from the command line:

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

Character sets and mysqldump

A very interesting issue with mysqldump is its corruption of character sets other than latin1. UTF-8 is one of the most often used character sets in the world, but still mysqldump will not work properly if we need to backup data encoded in UTF-8.

Paradoxically, the solution it to tell mysql to use the latin1 character set because setting UTF-8 in dumps and imports causes double conversion.

The output must be set to latin1, with no SET NAMES in the file. This will tell mysqldump command to not re-encode the file (i.e. output strings just as it sees them, even if in another character 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 should always be used with every database dump unless only the latin1 set is used. MySQL conf files can also be appropriately to avoid having to always include this in the command line.