2015.12.9 MySQL master-slave exception handling process

Reference documents

Http://www.111cn.net/database/mysql/58979.htm

MySQL version

Mysql-v

MySQL Ver 14.14 distrib 5.6.25, for Linux (x86_64) using Editline Wrapper

The company MySQL master server broken

Time of failure: 2015-12-08-19:51:35

Recovery time: 2015-12-09 12:22:38

1. Received colleague Feedback MySQL master/slave disconnected

2. Login slave See the results below.

3. The relevant information obtained is

master_host:192.168.66.6

Master_user:repl

master_port:10010

master_log_file:master-bin.000015

Slave_io_running:yes

Slave_sql_running:no

last_error:2015-12-08 19:51:35 30836 [Error] Slave sql:worker 3 failed executing transaction ' at Master Log master_bin. 000015, End_log_pos 686772256; Error ' Operation CREATE

USER failed for ' Zabbix ' @ ' localhost ' on query. Default database: '. Query: ' CREATE USER ' zabbix ' @ ' localhost ', error_code:1396

Executed_gtid_set:30f53a54************************:1-23

Master_auto_position:1

4. Restore Operations

Login slave

Stop slave;
Reset slave All;
Set global gtid_purged=30f53a54************************:1-23;

Change Master to master_host= ' 192.168.66.6 ', master_user= ' repl ', master_password= ' MySQL ', master_port= ' 10010 ', master _log_file= ' master_bin.000015 ', master_auto_position=1;

Start slave;
show slave status\g;

Master-Slave State recovery

5. Cause of failure

Http://blog.sina.com.cn/s/blog_439f80c40100yoln.html

When the user is built to report this error, because there is already a user in MySQL, can be deleted from the Mysql.user, and then refresh the permissions, then build the user will not have this problem. If it is the drop user first, then MySQL internal should be automatically refreshed, then the building will not have this problem.

A foreigner encountered this problem and gave an example. He just didn't have the refresh permission to cause the problem.

2015-12-08 19:51:35 We have already created a query MySQL master-slave status query account Zabbix No password this point to the account assigned other permissions to query the status of MySQL but no refresh permissions cause problems

6. Late avoidance

For MySQL regardless of the master slave operation must be cautious

If you modify the data from, then it is very likely from the execution of the main Bin-log error and stop synchronization, this is a very dangerous operation. So in general, be very careful to modify the data from above.

2015.12.9 MySQL master-slave exception handling process