Create Daily Backup MySQL Database using Cron in Linux

This is an article which is made to create backup file of a database in MySQL Database. Furthermore, it is not just creating backup file of a certain database in MySQL but also generate it automatically. But not only just randomly generating it automatically as an addition, it is generated specifically as it is already being scheduled to be performed in a specific time. Below is the step done to achieve the goal or the scenario which is described earlier :

1. The existance of MySQL Database. The process of the installation can be read in the following article which in this article is the installation process of MySQL Database Server in Ubuntu titled ‘Installing MySQL Server in Ubuntu 16.04‘

2. After making sure that MySQL Database Server which is going to be accessed exist, make sure that the service is active. To be able to check whether the service is active or not, this article titled ‘Check MySQL Service Status‘ could be used for further reference.

3. An active service of MySQL Database Server means that it is ready to accept incoming connection for further operation. To access the service, it is important to have a valid account to perform it. This is an article titled ‘Create User for dumping database MySQL‘ to create the account to do it. To be more specific as the titled said, it is the account or user solely to dump database MySQL.

4. Successfully connected to the service of MySQL Database Server means the process can be done manually. To execute a process correlates with MySQL Database Server’s service automatically, it needs an adjustment to do it. The adjustment can be read in the article titled ‘Access MySQL without Password‘. By doing the adjustment described in the article, the backup process can be done automatically without having to supply a password manually.

5. Doing step 1 through 4 is just generating a simple plain database backup file non-interactively without having to supply a password. To be able to generate the file automatically in full term, it needs a certain utility or tool to initiate the process without having any user involve on the execution process of the database backup command. In Linux operating system, cron utility is used. To be able to insert or to configured the MySQL Database Server’s backup process which is going to be included in the cron scheduler, below is the command which is needed to be performed :

crontab -e

This is the command executed in a real situation :

root@hostname:~# crontab -e
root@hostname:~#

By executing the above command, it will directly in instance to a cron scheduler editor as shown below :

# Edit this file to introduce tasks to be run by cron.
# 
# Each task to run has to be defined through a single line
# indicating with different fields when the task will be run
# and what command to run for the task
# 
# To define the time you can provide concrete values for
# minute (m), hour (h), day of month (dom), month (mon),
# and day of week (dow) or use '*' in these fields (for 'any').# 
# Notice that tasks will be started based on the cron's system
# daemon's notion of time and timezones.
# 
# Output of the crontab jobs (including errors) is sent through
# email to the user the crontab file belongs to (unless redirected).
# 
# For example, you can run a backup of all your user accounts
# at 5 a.m every week with:
# 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/
# 
# For more information see the manual pages of crontab(5) and cron(8)
# 
# m h  dom mon dow   command

6. In this context, the backup database file generated is specifically following the one described in the article titled ‘Add date to filename in cron output‘. The full backup process command which is needed to be written in the crontab file is shown as follows :

30 23 * * * mysqldump -uuser dbname > /root/dbname_$(date +%Y-%m-%d).sql

The above command is a crontab scheduler which is going to initiate and execute command specified in the line above everyday at 23:30 or 11:30 PM. The backup process is specified on the database named dbname. It is executed so that it generates MySQL backup database file named ‘dbname-backup-file-YYYY-MM-DD. Where the ‘YYYY-MM-DD’ is the current date where the file is generated or to be more specific, the command executed.

Since there is no need to interactively input password for the above command in order to generate MySQL database server’s backup file, the command is automatically execute by cron scheduler utility.