Nazmul
Middleware and Databases Expert

Automate backup Mysql via Cronjob

Hi guys ! In this write up, we will see how to automate the mysql backup using the cronjob.

Create Directory & File

At first, we need to create a directory to make that our working directory. In this case I am doing that on /var directory

Let’s do this like this

sudo mkdir -p /var/db-db-backup

Make a Script

Next we need to write a bash script to make things done. We will name that say “backup_script.sh”

Also, give it the proper execution permission.

cd /var/db/db-backup
touch backup_script.sh
chmod u+x backup_script.sh

Now the main script.

After writhing the script, it will look like this

### SECTION 1 set up all the mysqldump variables
DATE=`date +"%d_%b_%Y_%H%M"`
SQLFILE=/var/db//db-backup/db-backup_$(date +%Y%m%d-%H-%M-%S).sql
DATABASE=<database_name>
USER=<db_user>
PASSWORD=<db_user_password>

### Section 2 if you need to run this more than once a day,
# remove the previous version of the file
unalias rm     2> /dev/null
rm ${SQLFILE}     2> /dev/null
rm ${SQLFILE}.gz  2> /dev/null

### Section 3 mysql database backup (dump)
sudo mysqldump -u ${USER} -p${PASSWORD} ${DATABASE}|gzip > ${SQLFILE}.gz

There are 3 different sections of the contents of this file, let’s have a look:

Section 1

The first section allows you to set specific values, which suit your environment into variables that are later used in the other two sections of the same file.

  • DATE

To append the current date & time to the name of the backup file in order to facilitate you in identifying any required file later by just having a look at its name.

  • SQLFILE

To let you set the path and file name of the backup file, appending the current date & time (calculated by previous variable) for your ease.

  • DATABASE

To specify which database is to be backed up. This is used in the MySQL script for taking database dump/backup.

  • USER

To let you set the database username that has access to take a dump of the previous mentioned database.

  • PASSWORD

To mention the password of the database user that you chose to provide in the previous variable.

Section 2:

The second section helps you remove any database with the same name. You won’t really need this section when you are appending the date and time parameters to your file’s name, but if you are using a general name for the file and you wish your script to override the previous file, then you may include this section to your script.

Section 3:

The third section uses the variables of the first section and executes the mysqldump command to create an exported gzip file. Please note that you need to place the database password in this file that you might not want in some cases, however there exists an alternate method to dump the database without providing the password in the script. To do so, you need to edit the my.cnf file of your mysql configuration. The file might be in 5 or more locations, all are valid because they all load cascading.

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • $MYSQL_HOME/my.cnf
  • [datadir]/my.cnf
  • ~/.my.cnf

Edit the file and locate the [mysqldump] section in it.

Now, place the password in this file section just below the user, like the one displayed below:

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
user=root
password=backup$unixcop!

Save this file and restart the mysql service using the following command:

sudo systemctl restart mysql

Test & Verify

Traverse to the script’s folder & run the script

cd /var/db/db-backup
./backup_script.sh

If your script is working correctly, you should have a backup file created in your /var/db/db-backup folder.

If there is no such file, it is likely that you have missed something in the aforementioned commands or you have some user permission restrictions that might require you to use sudo with each command.

Schedule with Cron

Edit the server’s cron with the undermentioned command:

sudo crontab -e

Enter the following line at the bottom of this file:

0 1 * * * /var/db/db-backup/backup_script.sh

The above line in cron will run at 1:00 AM server time every day. If you want to schedule it differently, you may modify it as per your need.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest articles

x