Mysqldump
From DocForge
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.
[edit] 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:
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
(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.)
[edit] Character Sets
One peculiarity with mysqldump is its corruption of character sets other than latin1. A very common use case is the need to preserve UTF8 characters in mysql dumps. Ironically the solution is to tell mysql to use the latin1 character set. Setting UTF8 in dumps and imports causes double conversion.
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 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.
