Backing Up All MySQL Databases

[patrick]

To back up all databases on a particular server, use the mysqldump command:

$ mysqldump --all-databases -p | bzip2 -c > databasebackup.sql.bz2
Enter password:
$


This will put all of the databases in a compressed bzip2 file. Transfer the file to your other server and decompress:

$ bzip2 -d databasebackup.sql.bz2
$ ls data*.sql
databasebackup.sql
$ head -n 7 databasebackup.sql
-- MySQL dump 9.11
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 4.0.22-standard
--
$


Restore your backup using the mysql command:

$ mysql -p < databasebackup.sql
Enter password:
$


Do be doubly sure that you aren't restoring over something that exists already. This command is ideal for rebuilding a server, but is risky if there are already MySQL databases and tables on the server.