We all know that backing up critical data is very important. Unfortunately, it’s not always very clear how to go about doing that.
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
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.