mysqldump / mysql tips

February 14, 2013 by Michael

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

One comment

  1. Martin Jansen wrote:

    Percona’s XtraBackup ist worth a look, too:

    http://www.percona.com/software/percona-xtrabackup

    Posted on February 16, 2013 at 8:40 AM | Permalink
One Trackback/Pingback
  1. […] mysqldump / mysql tips […]

Post a Comment

Your email is never published. We need your name and email address only for verifying a legitimate comment. For more information, a copy of your saved data or a request to delete any data under this address, please send a short notice to michael@simons.ac from the address you used to comment on this entry.
By entering and submitting a comment, wether with or without name or email address, you'll agree that all data you have entered including your IP address will be checked and stored for a limited time by Automattic Inc., 60 29th Street #343, San Francisco, CA 94110-4929, USA. only for the purpose of avoiding spam. You can deny further storage of your data by sending an email to support@wordpress.com, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *