MySQL timer Events

MySQL supports the event function from 5.1. With this function, we can enable MySQL to automatically execute data aggregation.
And other functions. For example, linux crontab function 1. Background: The data volume of MySQL table A has reached 0.16 billion. For some historical reasons, you need to transfer the data of Table A to A new one.
Table B. However, because this is an online product, the downtime should be as short as possible,
I want to migrate the data slowly in the form of a job (timer Events. Www.2cto.com 2. Use Process (1) to check whether the event scheduler is currently enabled. There are three methods: 1) show variables like 'event _ scheduler '; 2) SELECT @ event_schedist; 3) show processlist; (2) there are four ways to enable the event Scheduler: 1) set global event_scheduler = 1; 2) SET @ global. event_scheduler = 1; 3) set global event_scheduler = ON; 4) SET @ global. event_scheduler = ON; key value 1 or ON indicates enabled; 0 or OFF indicates disabled; www.2cto.com (3) permission ON event Planning: When you use event alone to call an SQL statement, to view and create an SQL statement, you must have the event permission.
You have the permission to execute this SQL statement. The Event permission settings are stored in the Event_priv of the mysql. user table and mysql. db table.
Field. (Flush privileges;) When event and procedure are used together, you must have the create routine permission to view and create a stored procedure,
When calling a stored procedure for execution, you must use the excute permission. When calling a specific SQL statement, you must have
Permission for the SQL statement. Select host, USER, Event_priv FROM mysql. user;
Obtain the currently logged-on user and Database: SELECT CURRENT_USER (), SCHEMA (); from Figure1, we can know that bfsql @ % has no Event_priv permission. When an event is created for this user
The following Error occurs: Error Code: 1044 Access denied for user 'bfsql' @ '%' to database' blog '. If the preceding Error occurs, run the following SQL statement to grant the Event creation permission to bfsql @ %: UPDATE
Mysql. user SET Event_priv = 'y' where host = '%' and user = 'bfsql'; if you try again at this time
The preceding error still occurs when you execute the SQL statement for Event creation, because you need to execute: FLUSH PRIVILEGES;
Finally, you can use show grants for 'bfsql' @ '%'; to view all permissions. (4) Create an event: 1) the syntax FOR creating an event is as follows: create event [if not exists] event_nameON SCHEDULE schedule [on completion [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'comment'] DO SQL _statement2) Example of EVENT creation: DELIMITER $ create event if not exists e_blogON schedule every 30 secondon completion preservedo begincall MoveBlogData (); END $ DELIMITER; www.2cto.com DO SQL _statement field indicates the event needs SQL statement or stored procedure. The SQL statement here can be
Is a composite statement that uses the BEGIN and END identifiers to place the composite SQL statement in the execution order.
Java code -- execute the create event EVENT1 on schedule every 9 day starts now () on completion preserve enable do begin call total () EVERY nine days from now on (); END -- execute create event EVENT2 on schedule every 1 month starts DATE_ADD (DATE_SUB
(CURDATE (), interval day (CURDATE ()-1 DAY), INTERVAL 1 MONTH), INTERVAL 1 HOUR) on completion preserve enable do begin call stat (); END --- execute create event TOTAL_SEASON_EVENT on schedule every 1 quarter starts DATE_ADD (DATE (CONCAT (YEAR
(CURDATE (), '-', ELT (QUARTER (CURDATE (),), '-', 1), INTERVAL 1 QUARTER ),
INTERVAL 2 HOUR) on completion preserve enable do begin call SEASON_STAT (); END -- execute create event TOTAL_YEAR_EVENT on schedule every 1 year starts DATE_ADD (DATE (CONCAT (YEAR (CURDATE () at ON January 1, January 1 every year ())
+ 1, '-', 1, '-', 1), INTERVAL 4 HOUR) on completion preserve enable do begin call YEAR_STAT (); END (5) EVENT enabling and disabling: ENABLE an EVENT: alter event e_test on completion preserve enable; DISABLE an EVENT: alter event e_test on completion preserve disable; 3. Other knowledge points for our online environment, when using event, note that the timer is enabled on the master database, and the timer is disabled from the database.
All operations will record binlog for master-slave synchronization. Enabling the timer on the slave database may cause the card library. After switching the master database, remember
Open the timer on the new master database.