MySQL case 08:mysql The risks associated with events

Timing tasks are often used by our development, operations and maintenance personnel, such as Cron,job,schedule,events scheduler, etc. are designed to facilitate our repeated implementation of a task without human involvement, here I would like to say is the MySQL database itself scheduled tasks, This is the risk case of events scheduler.

I. Description of the phenomenon

Here is a data from the library out of sync phenomenon, the specific error is as follows:

slave_io_running:yes slave_sql_running:no Last_sql_errno:1032last_sql_error:could not ExecuteDelete_rows Event on TableBs.dg_sale; Can't find record in'Dg_sale', error_code:1032; handler Error Ha_err_key_not_found; the event'S MasterLogMysql-Bin.000079, End_log_pos159513315

This behavior occurs because of a primary key problem that causes data deletion to fail, which in turn raises data synchronization errors.

Second, reason analysis

The above error is more common is to do some removal from the library, and then the data synchronization when the primary key to find the condition is deleted when the deletion can not be performed, resulting in a master-slave error.

By comparing the main library data with the data from the library to find the table data records are 0, and then from the value of the different, from the library always have no external account access, here is a bit crazy? Yes, there is another situation that can result from being manipulated from the library, which is the scheduled task. Through the discovery, the main library has a few events, there is a scheduled task to design to this table multiple query, delete, insert and other operations.

Third, the processing process

1. View the status and error code information from the library.

2. Check the main library, from the library table data information, table structure information.

Show slave status \g

Show CREATE TABLE Bs.dg_sale \g

Select COUNT (1) from Bs.dg_sale;

3. Analyze Binlog information that produces errors.

Main Library:

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. View Main Library/from library Events Scheduler information

Show variables like ' Event_scheduler ';

Show events;

Select Event_schema,event_name,status, execute_at,interval_value from events;

See Events Scheduler here

5. Disable the events scheduler from the Library

Set global event_scheduler=0;

Adding set global event_scheduler=0 from the library my.cnf configuration file

6. Re-complete data synchronization

Iv. Expansion of knowledge

1. Create MySQL Events Scheduler

2. Delete MySQL Events Scheduler

3. Change MySQL Events Scheduler

MySQL case 08:mysql The risks associated with events