How to Schedule a Daily MySQL Backup on Your Server

MySQL, widely revered for its stability and performance, is the most popular database management system around. Be it a small media player or a movie-collection manager, MySQL is widely used across both proprietary and open-source platforms. With its humble beginnings in 1995, MySQL was named after co-founder Michael Widenius' daughter named My, and as for the SQL part, the phrase stands for Structured Query Language.

If you're handling a MySQL database, you must surely know the importance of a regular backup. Apart from taking the first step, that is securing the server, backing it up is one of the most important skills required whilst handling a database. On a real server environment, a simple crash can take down the whole database, corrupt it, or the database might even get deleted by accident. In order to avoid such catastrophes, it is of prime importance to backup the databases regularly. If the database is accessed almost daily then it becomes even more important to back it up every day. Of course, you being a lazy kid won't go and back up the server daily would you? Well, that's where system administrators outsmart everyone else by automatic simple tasks so that they don't have to do much work. If you're one of those lazy sysadmins then this simple guide will help you schedule a daily backup of your MySQL database.

Step 1: Taking Backups

The command that helps you take backups is mysqldump. Mysqldump is actually a program written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server. So, if you're curious to know how to use the command, here's a simple example: 

mysqldump -u root -pPASSWORD --all-databases

Type that command on your server and replace PASSWORD with your root password. This will automatically backup all your databases. 

Step 2: Scheduling

Then, to schedule that command as a daily backup, you need to add it as a CRON job. Here's a custom command that lets you backup your databases with their timestamp. Now, open a text file paste the following code (using your password and your own directory ) and save it as mysqlback.sh

mysqldump -u root -pPASSWORD  --all-databases > /home/techsource/MyBACKUP_`date '+%d-%m-%Y'`.sql

Now enter the following command in your terminal:

chmod +x mysqlback.sh

Then, to add it as a CRON job enter the following command: 

crontab -e

Next, you'll find that the Vi text editor has opened up. If you don't know how to use it, press I, then paste the following command (using the location to the script on your computer):

@daily /home/techsource/mysqlback.sh

Now, type in :wq to save and exit.

So, every day, the mysqlback.sh script will be executed thus giving you a timestamped backup of all your databases.


Written by: Abhishek, a regular TechSource contributor and a long-time FOSS advocate.