MySQL案例08:MySQL Scheduler Events帶來的風險

Rangle發表於2018-07-27
MySql

定時任務是我們開發、運維人員經常用到的，比如cron,job,schedule,events scheduler等都是為了方便我們重複執行某項工作而無需人工參與而設計，這裡我要說的是MySQL資料庫本身的定時任務，即events scheduler的風險案例。

一、現象描述

這裡有一個從庫出現資料不同步現象，具體報錯如下：

             Slave_IO_Running: Yes
            Slave_SQL_Running: No
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Delete_rows event on table bs.dg_sale; Can't find record in 'dg_sale', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000079, end_log_pos 159513315

這個現象出現是由於主鍵問題導致資料刪除失敗，進而引發資料同步錯誤。

二、原因分析

出現上述錯誤比較常見的是從庫做了一些刪除操作，然後資料同步的時候通過主鍵尋找條件刪除的時候無法執行刪除操作，進而導致主從錯誤。

通過對比主庫資料和從庫資料發現表資料記錄數都是0，然後自增值不同，從庫始終沒有外部賬戶訪問，這裡就有點懵逼了吧？沒錯，還有一種情況可能導致從庫被操作，那就是定時任務。通過排查發現，果然主庫設有幾個events事件，其中有個定時任務就設計到這個表的多次查詢、刪除、插入等操作。

正常情況下主庫建立event schedule，從庫自動的將event disable掉，如果切換需要手動enable event scheduler，如果搭建主從實現建立好的定時任務複製到從庫，從庫的scheduler可能會被啟用，導致主從的scheduler都被執行。

三、處理過程

1.檢視從庫狀態和錯誤程式碼資訊。

2.檢查主庫、從庫表資料資訊、表結構資訊。

show slave status \G

show create table  bs.dg_sale \G

select count(1) from bs.dg_sale;

3.分析產生錯誤的binlog資訊。

主庫：

show binlog events  in 'mysql-bin.000079' from 159512534 limit 10;

mysqlbinlog  --base64-output='decode-rows' --start-position=159512534 --stop-position=159512838 -vv  mysql-bin.000079 >binlog.txt

4.檢視主庫/從庫events scheduler資訊

show variables like 'event_scheduler';

show events;

select EVENT_SCHEMA,EVENT_NAME,STATUS ,EXECUTE_AT,INTERVAL_VALUE from events;

 

這裡看到events scheduler

5.禁用從庫的events scheduler

set global event_scheduler=0;或者在主建立的時候加入DISABLE ON SLAVE

在從庫my.cnf配置檔案中加入set global event_scheduler=0

6.重新完成資料同步

四、總結和知識擴充套件

含有scheduler事件的風險項：

1）主從切換的時候，新主庫需要enable scheduler events

2）含有scheduler 的資料庫搭建從庫，需要特別注意從庫的scheduler events需要被disable

1.建立mysql events scheduler

語法：

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

例項：

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

2.刪除mysql events scheduler

語法：

DROP EVENT [IF EXISTS] event_name

3.更改mysql events scheduler

語法：

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    [DO event_body]

例項：

ALTER EVENT no_such_event 
      ON SCHEDULE 
      EVERY '2:3' DAY_HOUR;

五、案例回放測試

名稱 主庫 備庫
IP地址 192.168.1.1 192.168.1.2
OS RHEL6.6 RHEL6.6
MySQL 5.7.21-20 5.7.21-20
     

 1.部署主從（略）

 2.檢查主從scheduer是否開啟（mysqladmin var |grep event_scheduler）

主：

從：

3.主庫建立schedure相關資訊

(root:localhost:Fri Jul 27 14:32:52 2018)[dbtest]>create table t(id int primary  key,name varchar(30));

CREATE EVENT ev_test 
ON SCHEDULE EVERY 1 MINUTE STARTS '2018-07-27 15:58:00' ON COMPLETION PRESERVE ENABLE DO 
BEGIN
    insert into   t  values(1,'N1'),(2,'N2'),(3,'N3');
END

4.主從資料檢查

show slave status \G

select * from t;

 

主從狀態正常，資料正常。

這裡發現並無異常，原因主從狀態本身存在的情況下，在主庫新建scheduler,從庫的scheduler event會被預設設定為disable

主庫：

(root:localhost:Fri Jul 27 16:29:12 2018)[dbtest]>show events;

從庫：

(root:localhost:Fri Jul 27 16:29:49 2018)[dbtest]>show events;

5.調整從庫的schedule為enable狀態

(root:localhost:Fri Jul 27 16:31:37 2018)[dbtest]>alter event ev_test enable;
Query OK, 0 rows affected (0.00 sec)
此時從庫的scheduer也會被執行，如果因為時間等原因的關係，從庫先執行了scheduler events，主庫再執行然後傳輸binlog到從庫再次執行會導致主從資料不一致，進而導致複製失敗，這也就是為什麼含有scheduer event的主從架構需要特別注意的原因了。

 

相關文章