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-backupMake 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.shNow 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}.gzThere 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 mysqlTest & Verify
Traverse to the script’s folder & run the script
cd /var/db/db-backup
./backup_script.shIf 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 -eEnter the following line at the bottom of this file:
0 1 * * * /var/db/db-backup/backup_script.shThe 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.
