What is 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.

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.

MySqlDump Examples

Here is a simple backup procedure that could be used for your site's database backup:

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

The next example could be used for securely database transfering between servers located on two different machines. The example demonstrates transfering from a remote server to the local machine:

ssh servername "mysqldump -u username --password=password --default-character-set=latin1 -N database | gzip -c -" | \
 
gunzip -c | mysql -u username --password=password --default-character-set=latin1 database

Commands above are just examples and you will probabli omit your password  because it will be remembered in the command line history.