A brief summary of the solution to the slave_io_running:no problem in MySQL data synchronization _mysql

Write below, if the two have no, how to recover.

If it is slave_io_running no, then I personally see that there are three kinds of situations, one is the network has problems, not connected, like once I use a virtual machine to build replication, using the network structure of NAT, is not even dead, The second is that there may be my.cnf problems, the configuration file how to write not to say, the internet too much, the last one is the issue of authorization, replication slave and file permissions are required. If you're not afraid to die, all of you.

Once IO is no the first to see the Err log, to see what is wrong, it is likely the network, but also may be too big to receive the package, this time the main preparation on the change max_allowed_packet this parameter.

If it's slave_sql_running no, then there are two possibilities, one is the slave machine on this table there are other write operations, that is, the program is written, this is going to be a problem, today I want to reproduce, but sometimes there are problems, sometimes there is no problem, now is not too clear, Later update, there is a majority of possible is the slave process restart, transaction rollback caused, this is a self-protection of MySQL measures, like the key time read-only.

If you want to recover this time, just stop Slave,set global sql_slave_skip_counter=1, and then open the SLAVE, and this global variable assignment to n means:

This is statement skips the next N events from the master. This is useful to recovering from replication stops caused by a statement.

This statement is valid the slave thread is not running. Otherwise, it produces an error.

Oh, speak more than I know.

MySQL mirror server recovery from error stopped

PM main server, due to a number of reasons, resulting in a panic, restart, found data from the server did not keep up.
With good MySQL master and subordinate also just a few days ago, not much experience, the first encounter this problem, a little anxious. However, I have tried, but also solved:

From the server
master_log_file:mysqlhxmaster.000007
read_master_log_pos:84285377

Look at the main server: mysqlhxmaster.000007 | 84450528 |
It's been a lot, and it's really not following.

Show Slave Status\g
Slave_io_running:yes
Slave_sql_running:no

If there is a problem, slave_sql_running should be yes.


Looking down again, there is the wrong hint:

last_errno:1053
Last_error:query partially completed on the master (Error on master:1053) and is aborted. There is a chance the your master is inconsistent in this point. If you are are sure that your master are OK, run this query manually on the slave and then restart the slave with SET GLOBAL SQ L_slave_skip_counter=1; START SLAVE;. Query: ' INSERT into Hx_stat_record ... (a SQL statement) '

Here is a description of how to operate:

Stop slave First, then execute the prompt statement, then set GLOBAL sql_slave_skip_counter=1; START SLAVE;

Show Slave Status\g

Slave_io_running:yes
Slave_sql_running:yes

OK, from the server also within a few minutes of the accumulated log processing, both sides are synchronized:

solution from MySQL server Slave_io_running:no 2

The Morning machine room unexpectedly power off, resulting in the discovery of MySQL from the server synchronization exception. The workaround for no with the previously encountered slave_sql_running is not valid and still cannot be synchronized.

Take a look at state show slave status
master_log_file:mysqlmaster.000079
read_master_log_pos:183913228
relay_log_file:hx-relay-bin.002934
relay_log_pos:183913371
relay_master_log_file:mysqlmaster.000079
Slave_io_running:no
Slave_sql_running:yes

Primary Server Show Master Status\g
file:mysqlmaster.000080
position:13818288
binlog_do_db:
Binlog_ignore_db:mysql,test

MySQL error log:
100512 9:13:17 [note] Slave SQL thread initialized, starting replication into log ' mysqlmaster.000079 ' at position 183913228 , relay log './hx-relay-bin.002934 ' position:183913371
100512 9:13:17 [note] Slave I/O thread:connected to master ' replicuser@192.168.1.21:3306 ', replication started into log ' my sqlmaster.000079 ' at position 183913228
100512 9:13:17 [ERROR] Error reading packet from Server:client requested master to start replication from impossible tion (server_errno=1236)
100512 9:13:17 [ERROR] Got fatal ERROR 1236: ' Client requested Master to start replication from impossible ' Master when reading the data from binary log
100512 9:13:17 [note] Slave I/O thread exiting, read up to log ' mysqlmaster.000079 ', position 183913228

This is slave_io_running for no, from the log point of view, the server read mysqlmaster.000079 This log 183913228 error occurred, this location does not exist, so can not sync.

Check out the last few lines of this log:
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE =@ @COMPLETION_TYPE, completion_type=0*/;
# at 4
#100511 9:35:15 Server ID 1 end_log_pos start:binlog v 4, Server v 5.0.27-standard-log created 100511 9:35:15
# Warning:this Binlog is not closed properly. Most probably mysqld crashed writing it.

Try to start at the location before the damage
SLAVE STOP;
Change MASTER to master_log_file= ' mysqlcncnmaster.000079 ', master_log_pos=183913220;
SLAVE START;
Invalid!
I had to start with a new log.
SLAVE STOP;
Change MASTER to master_log_file= ' mysqlcncnmaster.000080 ', master_log_pos=0;
SLAVE START;
At this point slave_io_running restore to Yes, synchronized! Observed for a while, without any sign of error, problem solved.

In addition, there is also a reason for the presence of slave_io_running:no that there is no permission to read data on master on slave.