MySQL Event Scheduler

Introduction

In this lab, you will learn how to use the MySQL Event Scheduler to automate tasks. The lab guides you through enabling the Event Scheduler, creating an event to periodically update data in a table, checking the event's execution status, and finally, dropping the event.

The first step involves connecting to the MySQL server and enabling the Event Scheduler using SQL commands. You'll verify the status of the scheduler before and after enabling it. Subsequently, you will create an event to update a table periodically. The lab will then guide you through checking the execution status of the event and finally, how to drop the event when it's no longer needed.

Skills Graph

Enable the Event Scheduler

In this step, we will enable the Event Scheduler in MySQL. The Event Scheduler is a background thread that executes scheduled events. By default, it is disabled. We need to enable it to create and run events.

First, let's connect to the MySQL server using the mysql client. Open a terminal and execute the following command:

mysql -u root -p

You will be prompted for the root password. Enter the password you set during the MySQL installation. If you haven't set a password, just press Enter.

Now that you are connected to the MySQL server, you can check the current status of the Event Scheduler. Execute the following SQL query:

SHOW GLOBAL VARIABLES LIKE 'event_scheduler';

You should see output similar to this:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| event_scheduler  | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

This confirms that the Event Scheduler is currently disabled.

To enable the Event Scheduler, execute the following SQL query:

SET GLOBAL event_scheduler = ON;

This command enables the Event Scheduler globally.

Now, let's verify that the Event Scheduler is enabled. Execute the same SHOW GLOBAL VARIABLES query again:

SHOW GLOBAL VARIABLES LIKE 'event_scheduler';

You should now see output similar to this:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| event_scheduler  | ON    |
+------------------+-------+
1 row in set (0.00 sec)

This confirms that the Event Scheduler is now enabled.

Finally, exit the MySQL client by typing exit and pressing Enter.

exit

You have successfully enabled the Event Scheduler in MySQL. In the next step, we will create an event to update data periodically.

Create an Event to Update Data Periodically

In this step, we will create a MySQL event that updates a table periodically. First, we need to create a database and a table. Then, we will create the event to update the table every minute.

First, let's connect to the MySQL server using the mysql client. Open a terminal and execute the following command:

mysql -u root -p

Enter the root password when prompted.

Now, let's create a database named test_db:

CREATE DATABASE IF NOT EXISTS test_db;

Next, switch to the test_db database:

USE test_db;

Now, let's create a table named event_table with two columns: id (INT, primary key, auto-increment) and value (INT):

CREATE TABLE IF NOT EXISTS event_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value INT
);

Let's insert an initial value into the table:

INSERT INTO event_table (value) VALUES (0);

Now, let's create the event. The event will update the value column in the event_table table every minute. Execute the following SQL statement:

CREATE EVENT IF NOT EXISTS update_event
ON SCHEDULE EVERY 1 MINUTE
DO
  UPDATE event_table SET value = value + 1;

This statement creates an event named update_event. The ON SCHEDULE EVERY 1 MINUTE clause specifies that the event should run every minute. The DO clause specifies the SQL statement to execute when the event runs, which increments the value column by 1.

To check if the event is created, execute the following SQL query:

SHOW EVENTS FROM test_db;

You should see output similar to this:

+----------+---------------+-------------+-----------+----------+---------------------+---------------------+-----------+------------+-------------------+----------------------+--------------------+--------------------+
| Db       | Name          | Definer     | Time zone | Type     | Execute at          | Interval_value      | Interval_field | Starts              | Ends               | Status             | Originator          | character_set_client | collation_connection | db_collation         |
+----------+---------------+-------------+-----------+----------+---------------------+---------------------+-----------+------------+-------------------+----------------------+--------------------+--------------------+
| test_db  | update_event  | root@%      | SYSTEM    | RECURRING | NULL                | 1                   | MINUTE       | 2024-10-27 14:30:00 | NULL              | ENABLED            | 1                    | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci   |
+----------+---------------+-------------+-----------+----------+---------------------+---------------------+-----------+------------+-------------------+----------------------+--------------------+--------------------+
1 row in set (0.00 sec)

This confirms that the event has been created and is enabled.

Now, exit the MySQL client:

exit

The event will now run every minute, updating the value column in the event_table table. In the next step, we will check the execution status of the event.

Check Event Execution Status

In this step, we will check the execution status of the event we created in the previous step. We will connect to the MySQL server and query the event_table to see if the value column has been updated. Since the event runs every minute, we should see the value incrementing over time.

First, let's connect to the MySQL server using the mysql client:

mysql -u root -p

Enter the root password when prompted.

Next, switch to the test_db database:

USE test_db;

Now, let's query the event_table to see the current value of the value column:

SELECT * FROM event_table;

You should see output similar to this:

+----+-------+
| id | value |
+----+-------+
|  1 |     N |
+----+-------+
1 row in set (0.00 sec)

Where N is an integer value. Since the event runs every minute, the value of N should be greater than the initial value of 0 that we inserted in the previous step. If you just created the event, wait for a minute or two and run the query again. You should see the value incrementing.

To further verify the event execution, you can check the MySQL error log for any errors related to the event. However, for this lab, we will assume that the event is running correctly if the value column is being updated.

You can run the SELECT query multiple times, waiting a minute between each execution, to observe the value column incrementing. This confirms that the event is running as expected.

Finally, exit the MySQL client:

exit

You have successfully checked the execution status of the event. In the next step, we will drop the event.

Drop the Event

In this step, we will drop the event we created in the previous steps. Dropping an event removes it from the MySQL server, preventing it from running again.

First, let's connect to the MySQL server using the mysql client:

mysql -u root -p

Enter the root password when prompted.

Next, switch to the test_db database:

USE test_db;

Now, let's drop the event named update_event. Execute the following SQL statement:

DROP EVENT IF EXISTS update_event;

This statement drops the event if it exists. The IF EXISTS clause prevents an error from occurring if the event does not exist.

To verify that the event has been dropped, execute the following SQL query:

SHOW EVENTS FROM test_db;

You should see an empty result set, indicating that there are no events in the test_db database:

Empty set (0.00 sec)

Alternatively, if you see the update_event in the list, it means the event was not dropped successfully. Please check your SQL syntax and try again.

Finally, exit the MySQL client:

exit

You have successfully dropped the event. This concludes the lab. You have learned how to enable the Event Scheduler, create an event to update data periodically, check the event execution status, and drop the event.

Summary

In this lab, the initial step focuses on enabling the MySQL Event Scheduler, a background thread responsible for executing scheduled events. This involves connecting to the MySQL server using the mysql client, verifying the current status of the Event Scheduler (which is initially disabled), and then enabling it using the SET GLOBAL event_scheduler = ON; command. The successful activation is confirmed by re-checking the event_scheduler global variable.

The subsequent step introduces the creation of a MySQL event designed to periodically update a table. This involves defining the event's schedule and the SQL statements it will execute.