In MySQL scheduled events are tasks that run according to a given schedule. After creating a scheduled event, we can modify it using the ALTER EVENT statement. This statement allows us to change the event’s definition, change its schedule, enable/disable it, rename it, and more. It also allows us to move the event to another database.

To move an event to another database, we use the ALTER EVENT statement with the RENAME clause, prefixing the event name with the database name (using dot notation).

Example

Here’s an example to demonstrate:

ALTER EVENT PetHotel.Event1
    RENAME TO KrankyKranes.Event1;

Output:

Query OK, 0 rows affected (0.01 sec)

In this example, I moved an event called Event1 from the PetHotel database to the KrankyKranes database. This assumes that the event exists in the PetHotel database.

After running that code, the event no longer exists in the PetHotel database. If I run SHOW EVENTS in that database, there’s no event called Event1:

SHOW EVENTS FROM PetHotel LIKE 'Event%';

Result:

Empty set (0.00 sec)

But if run the same code in the other database, the event is returned:

SHOW EVENTS FROM KrankyKranes LIKE 'Event%';

Result:

+--------------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db           | Name   | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+--------------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| krankykranes | Event1 | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | MONTH          | 2024-04-16 14:33:12 | NULL | ENABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+--------------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

Rename the Event

We can also change the name of the event when we move it. Let’s move it back to the original database and give it a different name:

ALTER EVENT KrankyKranes.Event1
    RENAME TO PetHotel.MonthlyCheckUp;

Output:

Query OK, 0 rows affected (0.00 sec)

And let’s use SHOW EVENTS to check the result:

SHOW EVENTS FROM PetHotel LIKE 'MonthlyCheck%';

Result:

+----------+----------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db       | Name           | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+----------+----------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| PetHotel | MonthlyCheckUp | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | MONTH          | 2024-04-16 14:33:12 | NULL | ENABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+----------+----------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

We can see that it is back in its original database with a different name.

The user executing such statements must have the EVENT privilege on both databases in order to move an event from one database to the other.