How to Create Cron Job in MySQL

1. MySQL Cron Job
2. Make a Backup of the Table
3. Create the Cron Job

The main aim of this article is to demonstrate how to schedule a query written in MySQL and execute it after a fixed amount of time using Cron Jobs.

MySQL Cron Job

When handling large amounts of data, a certain task may be repeated.

It can be anything and depends on the task’s context; examples include removing entries from a table after a certain amount or making timed backups by saving the database’s copy on the local machine - the possibilities are limitless.

Consider the following table:

+------+---------------+-------------+----------------------+------------+
| ID   | Name          | CountryCode | District             | Population |
+------+---------------+-------------+----------------------+------------+
|    1 | Kabul         | AFG         | Kabol                |    1780000 |
|    2 | Qandahar      | AFG         | Qandahar             |     237500 |
|    3 | Herat         | AFG         | Herat                |     186800 |
|    4 | Mazar-e-Sharif| AFG         | Balkh                |     127800 |
|    5 | Amsterdam     | NLD         | Noord-Holland        |     731200 |
|    . | . . . . . . . | . . . . . . | . . . . . . . . . .  |  . . . . . |
|    . | . . . . . . . | . . .  < OUTPUT REDACTED >  . . .  |  . . . . . |
|    . | . . . . . . . | . . . . . . | . . . . . . . . . .  |  . . . . . |
| 4075 | Khan Yunis    | PSE         | Khan Yunis           |     123175 |
| 4076 | Hebron        | PSE         | Hebron               |     119401 |
| 4077 | Jabaliya      | PSE         | North Gaza           |     113901 |
| 4078 | Nablus        | PSE         | Nablus               |     100231 |
| 4079 | Rafah         | PSE         | Rafah                |      92020 |
+------+---------------+-------------+----------------------+------------+

For the sake of simplicity, let’s assume that the following table, which stores data about various cities throughout the world, is updated regularly; the situation demands that a table backup be present on the local machine before the table is updated regularly.

To achieve that, we can use cron jobs to run a query, which makes the backup regularly of the table. Let’s see how we can achieve that using cron jobs.

Make a Backup of the Table

Before jumping to the schedule part, let us create the query that will allow us to back up this specific table.

Consider the following query:

mysql --user=[user] --password=[pass] --database=world --execute="SELECT * from world.city INTO OUTFILE 'D:\\a.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'"

Here’s a rundown of what is happening in the query:

1. mysql - MySQL binary to execute to access the database
2. --user=[user - Username
3. --password=[pass] - Password
4. --database=world - Database name to access and process, world in this case
5. --execute="..." - The query to execute
   5.1. Select * from world.city* - Select all entries from the table world present in the database name world.
   5.2. INTO OUTFILE 'PATH\.csv' - Write the query’s result to the file mentioned.
   5.3. FIELDS TERMINATED BY ',' - Terminate each field with a comma (for CSV-like structure)
   5.4. ENCLOSED BY '' - Enclose each field with ''
   5.5. LINES TERMINATED BY '\n' - Terminate each line with a newline character

Save this query somewhere in your preferred location, with the format preferably being .sh.

Now that we have created the query, we can move on to implementing the query as a scheduled task.

Create the Cron Job

Before creating the scheduled query, let’s look at the general syntax of a cron job.

The general syntax is below.

a b c d e /path/script result

1. a, b, c, d, and e specify the job’s time, date and recurrence.
2. /path/script specifies the script which needs to be executed
3. result specifies how the user is notified about the completion of the job (Optional)

Time Format of Cron Jobs

Let’s have a look at the time format cron jobs follow.

1. [a] - Stands for a minute, can range between 0 and 59
2. [b] - Stands for an hour, can range between 0 and 23
3. [c] - Stands for the day, can range between 0 and 31
4. [d] - Stands for a month, can range between 0 (none) and 12 (December)
5. [e] - Stands for the day of the week, can range between 0 (None) and 7 (Sunday)

Set Up the Cron Job

To create a cron job, open the crontab configuration file with your preferred text editor.

To open the configuration file, write the following in your terminal:

crontab -e

And add your job there; in our case, the syntax would be as follows:

0 7 * * * /tmp/back.sh

The above cron job will run every day at 7 AM, creating a backup of the city table and saving it at the desired location.

After you add the cron job, save the file and you are done!