MySQL failure (master-slave replication SQL thread does not run)

Failure phenomena:

Enter the slave server and run:

MySQL>  show Slave status\g ...        .            Relay_log_file:localhost-Relay-bin. 000535      21795072 relay_master_log_file:localhost-bin.               000094Slave_IO_Running:YesSlave_SQL_Running:No          replicate_do_db:       replicate_ignore_db:       ......

Workaround I.

Slave_sql_running:no
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 being reset.

This is typically caused by a transaction rollback:
Workaround:

MySQL>  slave stop;mysql>set GLOBAL sql_slave_skip_counter=1  ; MySQL> slave start;

Solution II,

First stop the slave service: Slave stop
To view host status on the primary server:
Record the value corresponding to file and position
Enter Master

Mysql>show master status;+----------------------+----------+--------------+------------------+| File                |Position|binlog_do_db|binlog_ignore_db|+----------------------+----------+--------------+------------------+|localhost-Bin.000094 | 33622483 |              |                  | +----------------------+----------+--------------+------------------+1Rowinch Set(0.00Sec

Then perform a manual synchronization on the slave server:

Mysql>Change Master to >Master_host='master_ip',>Master_user='User', >Master_password='pwd', >Master_port=3306, >Master_log_file=localhost-Bin.000094', > master_log_pos=33622483; 1 row in Set (0.00 sec) mysql> slave start;1 Row in Set (0.00 sec) mysql> Show SLA ve status\g*************************** 1.          Row *************************** ..... master_log_file:localhost-bin.000094 read_master_log_pos:33768775 relay_log_file:localhost-relay-bin.00        0537 relay_log_pos:1094034 relay_master_log_file:localhost-bin.000094 Slave_io_running:yes Slave_sql_running:yes replicate_do_db:

Manual sync needs to stop Master's write operation!
View MySQL master-slave configuration status and fix slave not start problem

1. View the status of master

Show master status;  // position should not be 0show processlist;   //  All  to  for  to be updated

2. View slave status

show slave status; // slave_io_running and slave_sql_running state should be yesshow processlist; //  Readalllog for the slave I/toupdate   for to send event

3. Error log

/usr/local//usr/local/mysql/data/   shaped like, hostname.err

4. Change Master to

If the Slave from the library is not started, slave_io_running is no.
May be the Master library is the information of master is changed,
View Main Library show master status;
Record the File,position field, assuming ' mysql-bin.000004 ', 98;
In the From Library execution:

MySQL>stop slave;mysql> to Master_log_file='  mysql-bin.000004', master_log_pos=98; MySQL>start slave;

5, SET global sql_slave_skip_counter=n;

If the slave_sql_running from the library is no.
The Err file records:

' 1 '  for Key 1  on the query .....

It is possible that master did not synchronize successfully to slave, but there is already a record in slave. The resulting conflict can be executed from the vault.

set global sql_slave_skip_counter=n;

Skip a few steps. Then restart slave.

6. Synchronous error Handling

found that MySQL slave server often because of some special characters or symbols generated by the UPDATE statement error, the entire synchronization will be stuck there, the original method is only manually go to the wrong machine to execute the following three SQL statements, skip the error.

MySQL>slave stop;mysql>set GLOBAL sql_slave_skip_counter=1  ; MySQL>slave start;

Description
Slave_io_running: Connect to the main library and read logs from the main library to local, generate local log files
Slave_sql_running: Reads the local log file and executes the SQL command in the log.

MySQL failure (master-slave replication SQL thread does not run)