Posts Tagged ‘mysql’

Backing up MySQL Databases with mysqldump

Posted in Uncategorized on 06/20/2009 11:42 am by ashlux

We all know that backing up critical data is very important. Unfortunately, it’s not always very clear how to go about doing that.

Luckily, making back ups of MySQL databases is easy by using mysqldump.

Backing up all databases

Backing up all databases is as easy as as this:

mysqldump -u username -p password –all-databases -c > mysqldump.sql

Did you expect it to be much harder? Sorry to disappoint.

The –all-databases option backs up all databases (genius naming!). The –complete-insert or -c uses complete insert statements in the backup script.

With the above command, mysqldump outputs all the SQL needed to regenerate all databases from scratch with all the data from the time of the dump. Users and other MySQL tables are also included in the dump.

Backing up a specific database or table

Sometimes it’s useful to only backup a small portion of the MySQL instance, either an entire database or a specific set of tables.

Not surprisingly, you can backup a specific database with mysqldump:

mysqldump -u username -p password YourDatabase > yourdatabase.sql

It’s just as easy to backup one or more tables in a database:

mysqldump -u username -p password YourDatabase table1 table2 > yourdatabase.sql

Compressing the backup

If you have a lot of data, compression with gzip will be helpful to eliminate disk space waste:

mysqldump -u username –password=password –all-databases -c | gzip -9 > mysqldump.sql.gz

Automating backups

If you’re dealing with a database of any significant importance, it’s critical to automate the backup process. In the Unix world there is cron. I won’t go into specifics on how to use cron since it is easily worthy of its own posting.

If you ever need your backup, it’ll suck. With backups, it’ll suck less. With automated backups, it’ll suck much much less.

mysqldump isn’t the right tool for all your MySQL backup needs, but its ease of use gives you no excuse. More complicated backup techniques, including incremental backups, can be found in the MySQL reference guide.