Backup and restore a MySQL database

Backup or export the database

Use mysqldump to do this.

The following command will prompt you for a password with -p, and dump the contents of the database named proddb to the file prodb.20060414.sql.

mysqldump --host=localhost --user=produser -p proddb > prodb.20060414.sql

Restore or import the database

prodb.20060414.sql is a SQL script that can be imported using:

mysql --host=standby --user=standbyuser -p backupdb < prodb.20060414.sql

This will insert all the tables and rows necessary into the backup database backupdb. You will have to create the backupdb database first.