nwlinux

Configuring MySQL backups with cron and mysqldump


published April 17th, 2010 | categories: bash, Database, File Formats, Informational, Installing, MySQL, Networking | all categories

There are quite a few tools available for backing up your MySQL databases. I will not go into those here because they are another talk all together. However, you can view the slides from the talk below.

My database backup solution

My choice of backup takes the simple approach using a cronjob and mysqldump. I have quite a few databases, which makes this approach somewhat tedious, but it works without using another script or utility that is not already built in. As a reference, here is the MySQL dump man page (http://dev.mysql.com/doc/mysql/en/mysqldump.html).

There are many syntax options for mysqldump, all of which are found on the reference listed above. The basic syntax for dumping MySQL databases is shown below.

mysqldump –alldatabases DB_name.sql

This works fine if you want to place all your databases into one large file. But, it is just one more step to take for extracting the DB that you want when you need to backup. My preferred method is to backup each database into a separate .sql file. The setup time on crontab takes a bit longer, but this method gives you more control over dump functions per database.

Crontab

Cron is Unix job scheduler that can be used to execute commands like wget, shell scripts, and others. To access your crontab, or job list, open Terminal and type in crontab -e. Cron jobs are fairly simple to set up if you configure your syntax properly. There are 6 columns or commands that you can provide Cron. The first five identify the time at which jobs should be run.

sudo crontab

What is sudo crontab? These are jobs that need to be run as sudo. For instance, if you have system files that you backup, you will likely need to run the backups as root. Access you root crontab with sudo crontab -e and follow the same protocol as listed above. I always run database backups as root. If you have read this far, good jog. Let’s just get right to it. Below is the syntax for a single database backup in the sudo crontab.

@daily mysqldump -u root -p[DB_Password] [DB_Name] > /backup/location/DB_Name.sql

What is the @daily in the front of the crontab entry? Well, I cheat with some jobs. The prefix @daily tells cron that I want to execute the following command one daily at midnight.

Warnings

bad minutes: this error indicates that you have whitespace or empty placeholders in your crontab. This error can be avoided by going to the end of each crontab entry and pressing delete until the line below comes up. Then, simple press entry so that you have one line per entry.

@daily: if you organize too many cron jobs with the @daily prefix, remember that you will have all of those jobs working at the same time. If you database is large, you may want to specify separate times staggered in say 10 minute increments so that you do not overload your systems and stall processes.


Related Posts

Previous Post: «
Next Post: »
Do you have something to say? Send me a message on Twitter @nwlinux.

Recent Posts

Adding a LCD monitor to the air monitoring data logger cube
categories include: Hardware

Office Prank: too many sticky notes
categories include: Images

Zero and Span using Advantech Device Manager
categories include: Hardware

Stuff

About >> I'm a Network Administrator out of Olympia, WA.

Social >> You can find me on Twitter @nwlinux or LinkedIn.

RSS Feed >> You can hit up FeedBurner.