I stumbled on amifamousnow.com the other day, reading a post on MySQL Automated Backup : Cron Job

It is a fair tutorial on how to backup your MySql data, but there were some comments from people that led me to believe a more detailed tutorial may be helpful.

If you follow the below steps, you will end up with a script that can backup ALL your databases, not just your wordpress blog database. As a bonus, you wont have to alter the script every time you add a new database. If you only have one database, or 100, thats fine, this script should handle it with no future adjustments.

I take no responsibility for your system, that being said, this script has worked for me since I wrote it many years ago.

I will go through the code a few lines at a time, explaining what each line does. In the end, you want to put all this code into a file, or mysqlbksh.zip.

# set date and time
time=`date +%m-%d-%y_%I-%M%p`

The above simply sets a variable called “time” to the current date and time, otherwise known as a timestamp.

# set path to final destination
location="/Volumes/drive_name/sql_dumps/"

“location” is the path in your filesystem where you want your backups to be saved. In this case, it is a Mac OS X file path, you should change yours to something similar to the above. Take caution to make sure the path it NOT accessible from the web, and is in a secure location.

# set db_list to the list of databases
db_list=`echo "show databases" | /usr/local/mysql/bin/mysql -N
-uUSERNAME -pPASSWORD`

“db_list” fetches a list of all your databases. The code should be all on one line. You will need to put in your username and password, and this will need to be a privileged username and password that has been granted access to ALL your databases. In my experience, you can set this up in phpMyAdmin, or ask your hosting provider to do so. You should only need SELECT privileges on ALL databases.

You may have to alter the path of /usr/local/mysql/bin/mysql to where your hosting provider has stored mysql. You should be able to get this by running whereis mysql from your command prompt.

for db in $db_list;
do
echo "dumping " $db "to " $location$db.sql
/usr/local/mysql/bin/mysqldump -uUSERNAME -pPASSWORD --opt $db > $location$db.sql
done

Again, line 3 in the above should be on one line, and you also may have to edit the path to mysqldump. The code takes the “db_list” and loops through it, while telling mysql to dump each database. On each iteration of the loop, a new database name is passed to the loop, so you get a single dump of each database. Each database will then be named the date stamp + database name + .sql.

The reason to loop, rather than using the “all-databses” option, is I wanted each database separate. Some of my databases are large, and to restore one, I wouldn’t want to have to deal with data that was not related to that restore.

The –opt is shorthand for adding in the following options to the dump.
–add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset

echo "changing to directory " $location
cd $location
echo "Now in:"
pwd

This is just some simple notification code, to print to the screen that we have changed to the backup directory.

echo "begin gzipping and tarballing"
tar -zcf $location$time.tar.gz *.sql

Above, we are going to archive ALL the databases into one tar/gzip file.

echo "removing:"
ls -la $location*.sql
rm $location*.sql

Finally, we print out a listing of the all the files in the backup directory, and remove the original .sql files since we have one large archive of them all.

echo "All your MySql Database are Belong to Us";
echo $location$time.tar.gz

And last, we just output a little message to let us know that the job is done.

You will want to put all that code in a file, and chmod+x the file, to give it execute permissions. Please be aware, there is a user and pass in the file, it will be up to you to make sure your system is secure enough that other people can not read that file. There are more secure ways to do this with hidden passwords and secure keys, which are beyond the scope of this simple tutorial.

Next up, we want to automate this, via cron. Cron is simply a scheduler, and we are going to tell this to run once day.

You will want to put this line into a text file, you can call the file whatever you like:

1 6 * * * sh /path/to_above_script/MySql_backup.sh

This tells cron to run the backup script once a day, every day, at 6:01AM. This means, every day, a backup will be waiting for you. This is really nice as you can incrementally roll back your data to any arbitrary day you like. However, it comes at the cost of drive space. You could easily add a line to the script to find all backup files older than 10 days and delete them. I will leave that as a exercise for someone to put into the comments.

Once you have your cron code in a file, you load it with:
crontab the_name_of_the_cron_file

Now, once a day, you should get an email telling you your databases were backed up. There will be enough data in the email to explain to you what happened and how it was done. If you do not want the email, simply tell cron to send it nowhere, by adding this to the end of the cron line:

> /dev/null 2>&1

Hope this helps out, if anything is not clear, feel free to post a comment, and I will do my best to help you out.