Posted November 24, 2009 by Spyros in DBMS / Databases

How to Backup MySQL Databases Automatically Using Cron


Could you ever imagine a very big company losing important data because of incomplete backups or no backups at all ? If you think that large companies, that earn millions of dollars, backup their data daily or even hourly, then you are wrong. The reality is sad. A quite large portion of those high caliber corporations don’t even create backups and some of them that do, do not do it the right way.

The scenario can be a living hell. Millions of data getting lost because of a simple mistake in mySQL privileges or trust. Even a small human mistake can send information about the government flushed down the toilet. Would you care to try whether that would happen to you ? If you think it never will, rest assured that it will and when it happens, it will be at the worst time.

Don’t let that happen to you. Be sensible and smart beforehand. Back up once. If the data is very important, back up twice, this time on another server. In this tutorial i will be showing you how to create a small and simple bash script that backs up your database and runs automatically everyday. What will actually happen is that you will execute this script on another server than the one actually running your mySQL database (you can do it on the same server as well though, the same way).

It would be quite important that you are at least a bit familiar with how cron works and how to use it to schedule your jobs. Let’s now see the actual bash script code first (file named backup.sh) :


date=`date -I`

mysqldump -h your_domain -u userDB -pPASSWORD DBname | gzip > /home/hthought/backup-$date.sql.gz

This script is pretty trivial. It first gets the current date and stores it to a variable. The idea is that we will use this to form the name of the gzip file depending on the date it was created. You can see that on “backup-$date.sql.gz”. Therefore, if you create a backup on 23th of November 2009, you get a filename like “backup-2009-11-23.sql.gz”.

The command that is used in order to create the actual dump is mysqldump. Using the -h switch, we can specify the hostname of the server where the mySQL server is located. Thus, if mySQL runs on another server, let’s say whatever.com, you will be using “-h whatever.com”. If the server runs on the actual machine executing the script, you do not need to specify this switch, because it defaults to localhost. The idea of using -h is that we would want to create a backup on a remote server so that if a problem comes up on the server where mysql is located, we will be sure that the backups are intact, since they are located in another server.

Important Notice

In order to be able to access a mysql server remotely, you need to make sure that the user that is bound to the database can use its privileges outside localhost. This is actually specified when first creating the user of the database. The usual command is like :

CREATE USER 'userDB'@'localhost' IDENTIFIED BY 'some_pass';

However, since in our case we need to have the user invoke commands from a remote host, we specify the command as :

CREATE USER 'userDB'@'%' IDENTIFIED BY 'some_pass';

Now, we use the standard credentials input process. We use -u to specify the database user, -p to specify the password (make sure there is no space between -p and your database password). Finally we also input the database name. This mysql command gets piped to gzip, which in turns creates a gzipped version of our database and then writes this to a file on our desirable location.

Running the Script Daily

As you see the script is pretty simple. The only thing that we need to do now is have it run automatically every day (or whenever you like to do that). As i mentioned before, we will be using cron to do that. We open up cron using “crontab -e” as the user we currently are (not root). Then, we add a line like :

0 0 * * * /bin/sh /home/hthought/backupPlace/backup.sh > /dev/null 2>&1

Simply, this script runs when the minute is 0 and the hour is 0 on whatever weekday of whatever month. Simply put, on each day there is only one time when the hour is 0 and the minute is 0 and that is the midnight. Therefore, this script runs once per day, every midnight. Moreover, since i do not really care about cron’s logs (and don’t really want to get useless mails), i redirect output to /dev/null/ , shamelessly trashing it.

And now, we can be safe knowing that our data will never be lost (again?).