"Innovation comes only from readily and seamlessly sharing information rather than hoarding it."- Tom Peters
Development Blog

Automatic Backup of MySQL Databases

This is a trick that works on a Linux system, as most servers are hosted using Linux. So, you would have to figure out how to change it if you are using Windows (sorry).

What are we going to do?

We will just create a simple bash script that will backup all databases, and we will create a cron job to do it for us on automatic intervals.

The Bash Script

You may first ask me, why do this in a bash script? Why not put it straight into a cronjob? Well, I'm doing it just to make it easier if you want to run other commands, or do one file per database. So, here's the script:


mysqldump -h localhost -u mikesir87 -pPASSWORD --all-databases > database.sql
gzip database.sql
mv database.sql.gz BACKUPS/`date +mysql-BACKUP.sql-%y-%m-%d.gz`

The first command, mysqldump, does a simple dump on the database. Since we give it the --all-databases flag, it grabs all databases that user has access to. The -h flag is for the host, which will probably be localhost. The -u flag is the username for the connection and the -p flag is for the user's password. Note that there is space between the flag and the password. If you're not familiar with Unix command lines, the > is what is called an IO Redirect. It basically tells the mysqldump to put the output into the database.sql file, rather than outputting it on the terminal. Try it without that, and you'll see what I'm talking about.

The second command runs a gzip compression on the file, which renames it to database.sql.gz.

The third command moves the file, and puts it into the BACKUPS folder, which you can easily change. You'll notice the date command that is in there. It creates a file name that looks like mysql-BACKUP.sql-{this year}-{this month}-{this day}.gz. That way, you can have a history of backups, without each one overwriting the other.

The Cron job

If you aren't familiar with cron jobs, it basically is just a scheduled task. You can create a cron job that will run on every other hour, for every day of the year. Or, you could create one to run at noon on the second Wednesday, twice a year. It's pretty flexible. In this example, we're going to setup our cron job to run every night at midnight, as we probably won't have much traffic then.

To do this, you're going to need access to the shell for your server (or your own computer if your mysql server allows remote connections).

To edit your cron job, run this command.

crontab -e

The -e flag is for editing. You can also try out the -l option to show what is currently in the list.

Once the crontab is opened, enter this at the bottom of your cron file.

0 0 * * * /path/to/your/mysqlBackups.sh

First of all, you'll want to change the path to your backup script to match yours. If you need help knowing your path, use the pwd command. That will give you your path.

The first part of the cron listing looks a little cryptic. It stands for the following:

##Minutes Hours Day Week Month [cmd]

The * in a position means it will run on all values of that field. So, if you used * * * * *, it would run every minute of the year.

You can do all sorts of crazy things for the dates. For example, if you want it to run every fifteen minutes, you could use either 0,15,30,45 or */15 in the minutes category.

So, there you go! Let me know if you run into any issues, or have any ways to improve it!

How to do it with only one database

In your bash script, you could do something like this:

mysqldump DATABASE1 -h localhost -u mikesir87 -pPASSWORD > database1.sql
mysqldump DATABASE2 -h localhost -u mikesir87 -pPASSWORD > database2.sql
mysqldump DATABASE3 -h localhost -u mikesir87 -pPASSWORD > database3.sql
mysqldump DATABASE4 -h localhost -u mikesir87 -pPASSWORD > database4.sql
gzip *.sql
mv database1.sql.gz BACKUPS/`date +mysql-BACKUP-database1.sql-%y-%m-%d.gz`
mv database2.sql.gz BACKUPS/`date +mysql-BACKUP-database2.sql-%y-%m-%d.gz`
mv database3.sql.gz BACKUPS/`date +mysql-BACKUP-database3.sql-%y-%m-%d.gz`
mv database4.sql.gz BACKUPS/`date +mysql-BACKUP-database4.sql-%y-%m-%d.gz`

This dumps only databases named DATABASE1, DATABASE2, DATABASE3, DATABASE4. I'm sure there's an easier way to moves the files, but that's all I've got for now. If you now another way, feel free to post it!!