Slave_ SQL _Running: No mysql synchronization fault solution

Slave_ SQL _Running: No mysql synchronization fault solution today I checked the database and found that a MySQL Slave was not synchronized with the host. Check the Slave status: mysql> show slave status \ GSlave_IO_Running: yesSlave_ SQL _Running: NoLast_Errno: 1062 .... seconds_Behind_Master: NULL reason: 1. the program may perform write operations on slave. it may also be caused by transaction rollback after the Server Load balancer instance restarts. solution I: 1. first stop the Slave service: slave stop2. view the host status on the master server: record the values of File and Position. Mysql> show master status; + bytes + ----------- + -------------- + bytes + | File | Position | Binlog_Do_DB | bytes | + bytes + ----------- + -------------- + bytes + | mysql-bin.000020 | 135617781 | + bytes + ------------- + -------------- + ------------------ + 1 row in set (0.00 sec) 3. run manual synchronization on the slave server: mysql> change master to> master_host = 'master _ Ip ',> master_user = 'user',> master_password = 'pwd',> master_port = 3307,> master_log_file = 'mysql-bin.000020',> master_log_pos = 135617781; 1 row in set (0.00 sec) mysql> slave start; 1 row in set (0.00 sec) view the slave status again and find: Slave_IO_Running: YesSlave_ SQL _Running: Yes... seconds_Behind_Master: 0 solution II: mysql> slave stop; mysql> set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1; mysql> slave start; your own experience: method 1: it is mandatory to start synchronization from a certain point, there will be Some data that has not been synchronized is lost, and the deletion record synchronization on the master server will also have some error messages, which will not affect the usage. method 2 may not be effective. ========================================================== ========================================================== =======] 1, master/slave synchronization fails: show slave status; Error: Error xxx dosn't exist and show slave status \ G: Slave_ SQL _Running: NOSeconds_Behind_Master: NULL solution: stop slave; set global SQL _slave_skip_counter = 1; start slave; after that, Slave will be synchronized with the Master. For details, refer to Slave_IO_Running: YesSlave_ SQL _Running: YesSecon. Whether the ds_Behind_Master is 0 or 0 means that 2 has been synchronized. Some optimizations and monitoring are required: show full processlist; // view the current mysql synchronization thread number skip-name-resolve // skip the dns name query to speed up connection and synchronization. max_connections = 1000 // increase the number of Mysql connections, (default: 100) max_connect_errors = 100 // increase the number of wrong connections of Mysql. (default: 10) view logs. Command 1: show master status \ G; here, we mainly check whether the log-bin files are the same. Show slave status \ G; here we mainly look at: Slave_IO_Running = Yes Slave_ SQL _Running = Yes. If Yes, the configuration is successful. 2. Enter show processlist \ G on the master; mysql> show processlist \ G **************************** 1. row ************************** Id: 2 User: root Host: localhost: 32931 db: NULL Command: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL if Command: Binlog Dump appears, it is configured. Stop slave # stop synchronization start slave # start synchronization, and update from the location where the log is terminated. SET SQL _LOG_BIN = 0 | 1 # run on the host. The super permission is required to enable or stop the log. This will cause data inconsistency between the host and slave, cause error set global SQL _SLAVE_SKIP_COUNTER = n # Run the client to skip several events. It can be executed only when the synchronization process stops due to an error. Reset master # run on the host, clear all logs. This command is the original flush master reset slave # Run from the SLAVE, clear the log synchronization location mark, and re-generate master.info although the re-generate master.info, but not available, it is best to restart the mysql process FROM the machine, load table tblname from master # Run FROM the machine, repeat the data in the specified table from the host. Only one table can be read at a time. Due to the timeout time limit, you need to adjust the timeout time. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, you need to add the values of net_read_timeout and net_write_timeout to load data from master # Run FROM the slave machine and read all the data from the host. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, you need TO increase the values of net_read_timeout and net_write_timeout to change master to master_def_list # online CHANGE some host settings. Separate multiple settings with commas (,), for example, change master to MASTER_HOST = 'master2 .mycompany.com ', MASTER_USER = 'replicase', MASTER_PASSWORD = 'bigs3cret 'MASTER_POS_WAIT () # Run show master status on the SLAVE machine # Run the host, view the log export information show slave hosts # Run the host, check the connection status of the slave. Show slave status (slave) show master logs (master) show binlog events [IN 'logname'] [FROM pos] [LIMIT [offset,] rows] PURGE [MASTER] logs to 'logname'; PURGE [MASTER] logs before 'date'