Slave_sql_running:no MySQL Sync fault resolution
Check the database today to find that a MySQL slave is not synchronized with the host to see the slave status:
Mysql> Show Slave Status\g
Slave_io_running:yes
Slave_sql_running:no
last_errno:1062
....
Seconds_behind_master:null
Reason:
1. The program may have been written on the slave
2. It is also possible that the transaction rollback is caused by the slave machine. Workaround I:
1. First stop slave service: Slave stop2. To view host status on the primary server:
Records the value corresponding to file and position.
Mysql> Show master status;
+------------------+-----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+-----------+--------------+------------------+
| mysql-bin.000020 | 135617781 | | |
+------------------+-----------+--------------+------------------+
1 row in Set (0.00 sec) 3. Perform a manual synchronization to 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 slave status Discovery again:
Slave_io_running:yes
Slave_sql_running:yes
...
seconds_behind_master:0 Solution II:
mysql> slave stop;
Mysql> set GLOBAL sql_slave_skip_counter=1;
mysql> slave start; Own use Experience: Method one is mandatory from a point to start synchronization, there will be some non-synchronous data loss, subsequent primary server delete record synchronization will also have some error messages, will not affect the use. Method two does not necessarily have an effect. =======================================================================================]1, master and slave can not sync: show slave Status error: Error XXX dosn ' t exist
and show slave status\g:
Slave_sql_running:no
Seconds_behind_master:null Workaround:
Stop slave;
Set global sql_slave_skip_counter = 1;
start slave; then slave will sync with master. Main view: Slave_io_running:yes
Slave_sql_running:yes
Seconds_behind_master whether or not 0,0 is already synchronized 2, also need to do some optimization and monitoring:
Show full processlist; View MySQL current sync thread number
Skip-name-resolve//Skip DNS name queries to help speed connection and synchronization
max_connections=1000//Increase the number of MySQL connections (default 100)
MAX_CONNECT_ERRORS=100//Increase the number of incorrect connections for MySQL (default 10)
View logs some commands
1, show Master status\g;
The main thing here is to see if the Log-bin files are the same.
show slave status\g;
Here the main is to see:
Slave_io_running=yes
Slave_sql_running=yes
If all is yes, the configuration is successful. 2, enter show Processlist\g on 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, the configuration is successful. Stop slave #停止同步
Start slave #开始同步 and updates from the location where the log is terminated.
SET sql_log_bin=0|1 #主机端运行, need super permission, used to open and stop the log, arbitrary open and stop, will cause the host from the machine data inconsistency, resulting in errors
The SET GLOBAL sql_slave_skip_counter=n # Client runs to skip several events and can only be executed if the synchronization process has an error.
RESET Master #主机端运行, clear all logs, this command is the original flush MASTER
Reset SLAVE #从机运行, clear the Log Sync location flag, and regenerate Master.info
Although Master.info is regenerated, it is not a good idea to restart the MySQL process from the machine,
LOAD table tblname from Master #从机运行, reread the data of the specified table from the host side, read only one at a time, subject to timeout time limit, need to adjust timeout time. Executing this command requires a sync account with reload and super privileges. And a SELECT permission on the corresponding library. If the table is larger, increase the value of net_read_timeout and Net_write_timeout
The load data from master #从机执行 to re-read all data from the host. Executing this command requires a sync account with reload and super privileges. And a SELECT permission on the corresponding library. If the table is larger, increase the value of net_read_timeout and Net_write_timeout
Change MASTER to Master_def_list #在线改变一些主机设置, with multiple comma intervals, such as
Change MASTER to
Master_host= ' master2.mycompany.com ',
Master_user= ' Replication ',
Master_password= ' Bigs3cret '
Master_pos_wait () #从机运行
Show MASTER STATUS #主机运行, see log export information
Show SLAVE HOSTS #主机运行, see the situation of the connected 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 '
Slave_sql_running:no MySQL Synchronous fault resolution method
