Introduction
In this lab, you will learn how to use the MySQL Event Scheduler to automate database tasks. The Event Scheduler is a feature in MySQL that executes SQL statements at scheduled times or intervals. This is useful for automating routine tasks like database maintenance, data cleanup, or periodic reporting.
You will be guided through enabling the Event Scheduler, creating a database and table, scheduling a recurring event to modify data, verifying the event's execution, and finally, removing the event. By the end of this lab, you will understand the fundamentals of using the MySQL Event Scheduler.
Enable the Event Scheduler
The MySQL Event Scheduler runs as a background thread that executes scheduled events. By default, this feature is disabled. Your first step is to enable it.
First, open the terminal from your desktop.
Connect to the MySQL server as the root user. In this lab environment, you can use sudo to connect without a password.
sudo mysql -u root
Once connected, you will see the MySQL prompt (mysql>).
Check the current status of the Event Scheduler with the following command:
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
The output will show that the event_scheduler is OFF by default.
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| event_scheduler | OFF |
+------------------+-------+
1 row in set (0.00 sec)
To enable the Event Scheduler, run the following command. This setting is global and will persist as long as the server is running.
SET GLOBAL event_scheduler = ON;
Verify that the change was successful by checking the status again:
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
The output should now show the value as ON, confirming that the Event Scheduler is active.
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| event_scheduler | ON |
+------------------+-------+
1 row in set (0.00 sec)
Now that the Event Scheduler is enabled, you can proceed to the next step.
Create a Database and Table
Before creating a scheduled event, you need a database and a table for the event to interact with. In this step, you will create a database named test_db and a table named event_logs.
While still in the MySQL shell, create the test_db database. The IF NOT EXISTS clause prevents an error if the database already exists.
CREATE DATABASE IF NOT EXISTS test_db;
Switch to the newly created database to make it the current database for subsequent commands.
USE test_db;
You will see a confirmation message: Database changed.
Next, create the event_logs table. This table will have an auto-incrementing id and a counter column that our event will update.
CREATE TABLE event_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
counter INT NOT NULL
);
Finally, insert an initial row into the event_logs table with a starting value of 0. This gives our event a record to update.
INSERT INTO event_logs (id, counter) VALUES (1, 0);
You can verify that the table was created and the data was inserted correctly by running the following commands:
SHOW TABLES;
SELECT * FROM event_logs;
The output should show the event_logs table and a single row with id = 1 and counter = 0.
+-----------------+
| Tables_in_test_db |
+-----------------+
| event_logs |
+-----------------+
1 row in set (0.00 sec)
+----+---------+
| id | counter |
+----+---------+
| 1 | 0 |
+----+---------+
1 row in set (0.00 sec)
With the database and table ready, you can now create the scheduled event.
Create a Recurring Event
Now you will create an event that runs periodically. This event will increment the counter value in the event_logs table every 10 seconds.
Ensure you are still using the test_db database.
USE test_db;
Create an event named update_counter_event.
CREATE EVENT update_counter_event
ON SCHEDULE EVERY 10 SECOND
DO
UPDATE event_logs SET counter = counter + 1 WHERE id = 1;
Let's break down this statement:
CREATE EVENT update_counter_event: Defines a new event with the nameupdate_counter_event.ON SCHEDULE EVERY 10 SECOND: Specifies that the event will run every 10 seconds.DO UPDATE ...: This is the SQL statement that the event will execute. It increments thecounterfor the row whereidis1.
To verify that the event was created successfully, use the SHOW EVENTS command.
SHOW EVENTS;
The output will list your new event and its details. Check that its Status is ENABLED.
+---------+----------------------+---------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+---------+----------------------+---------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| test_db | update_counter_event | root@localhost| SYSTEM | RECURRING | NULL | 10 | SECOND | 2024-05-27 12:00:00 | NULL | ENABLED | 1 | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+---------+----------------------+---------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
Note: The Definer and Starts time will vary.
The event is now active and scheduled to run. In the next step, you will check if it is updating the data as expected.
Check Event Execution
Now that the event is scheduled, you need to confirm that it is executing correctly. Since the event runs every 10 seconds, you should see the counter value increase over time.
Query the event_logs table to see the current value.
SELECT * FROM event_logs WHERE id = 1;
Now, the counter might be 2 or a small number.
+----+---------+
| id | counter |
+----+---------+
| 1 | 2 |
+----+---------+
1 row in set (0.00 sec)
Wait for about 15-20 seconds to allow the event to run at least once. Then, execute the SELECT query again.
SELECT * FROM event_logs WHERE id = 1;
You should see that the counter value has increased.
+----+---------+
| id | counter |
+----+---------+
| 1 | 3 |
+----+---------+
1 row in set (0.00 sec)
If you wait longer and run the query again, the value will continue to increase, confirming that the update_counter_event is executing successfully on its schedule. This demonstrates the power of automated, time-based tasks in MySQL.
Drop the Event
When a scheduled event is no longer needed, it is good practice to remove it to prevent unnecessary resource consumption. In this final step, you will drop the update_counter_event.
To drop the event, use the DROP EVENT statement. The IF EXISTS clause is recommended to avoid an error if the event has already been removed.
DROP EVENT IF EXISTS update_counter_event;
To verify that the event has been dropped, run the SHOW EVENTS command again.
SHOW EVENTS;
The output should now be an empty set, confirming that there are no more scheduled events in the test_db database.
Empty set (0.00 sec)
The update_counter_event will no longer execute. You can confirm this by waiting and querying the event_logs table again; the counter value will no longer increase.
You have successfully managed the lifecycle of a MySQL event. You can now exit the MySQL shell.
exit;
Summary
In this lab, you have learned the fundamentals of using the MySQL Event Scheduler for task automation. You successfully enabled the scheduler, set up a database and table, and created a recurring event to update data automatically. You also practiced verifying an event's status and execution, and learned how to remove it once it was no longer needed.
These skills are valuable for automating a wide range of database administration and maintenance tasks, helping to ensure data integrity and reduce manual workload.
