info.michael-simons.eu

Two tips to make your life with mysqldump easier and your backups better:

The following command creates a full backup of your mysql server including all databases and structures (including views and(!) stored procedures) along with the data:

mysqldump -uroot -proot --opt --routines --add-drop-database --default-character-set=utf8 --create-options --all-databases | \ 
bzip2 > backup.sql.bz2

You can also choose which databases to export with the –databases option:

mysqldump -uroot -proot --opt --routines --add-drop-database --default-character-set=utf8 --create-options --databases db1 db2 | \ 
bzip2 > backup.sql.bz2

But i prefer the complete version that can be used to restore the whole server including the mysql schema.

What if you want to restore only one database or transfer a database from this backup? Easy. mysql has a -D or –database parameter that is “default database to use” and -o respectively –one-database, the later one means “skip all sql commands that are not used while the default database is in use.

So to restore only db1 from the first backup just use

bzcat backup.sql.bz2 | mysql -uroot -proot -D db1 -o

and you’re done. Be aware that the db1 must exist when using this command, so you may want to create it as an empty database first.

To restore your whole server, just pipe the whole dump to mysql:

bzcat backup.sql.bz2 | mysql -uroot -proot