Scheduling Individual Backups of all MySQL Databases on Linux

Expanding on from an older post covering a simple usage of the the mysqldump program I finally found some time to write a bash script to control this process and allow all MySQL databases to be backed up separately with one command.

This script uses the MySQL client to pull in a list of all the databases on the server and then loop round and back each one up into its own compressed file (using the gzip program). Once a backup file for all the databases has been created the script then bundles them all into a single tar archive, removing the original individual files. This then allows you to easily keep track of all your backups, by just having one file containing all the data you need to restore all DBs (or just one) to a point in time. A script such as my one could be used to limit the number of backups stored at any one time (to save on disk space).

Continue reading

Scheduling MySQL Backups on Linux

There are a number of ways to automatically backup MySQL databases, the simplest one is just to use the mysqldump program and schedule a cron job.

The mysqldump program typically produces an SQL file which can used to restore any where between one to all databases on a server.

Running the mysqldump program is done using the following command:

mysqldump [OPTIONS] (Database name or list of databases)
NOTE: You can use --all-databases in the place of a single or list of database
names to backup all the databases in one file!

Whilst there are a large number of options to give the program the most important ones are probably the “username” and “password” ones.

Continue reading