mysql data synchronization occurs Slave_IO_Running: solution summary of No problem
- 2020-05-10 23:00:45
- OfStack
So let me write down 1, how do I recover these two if I have no.
If is slave_io_running no, so as far as I am to see have three kinds of case, one is the network has a problem, can't connect, like one time I replication builds with the virtual machine, use the network structure of nat, not even is death, the second is likely my cnf has a problem, not how to write configuration files that the web too much, the last one is authorized problem, replication slave and file permissions is a must. If you're not afraid to die, then all.
Once io is no, first look at the err log to see what is wrong. It may be the network, or it may be the packet is too big to accept. At this time, the parameter max_allowed_packet will be changed.
If it is slave_sql_running no, so there are two possible, one kind is slave machine this table appeared other writes, is to write the program, this is going to have a problem, today I want to return, but sometimes there is a problem, sometimes there is no problem, it is not too clear, later update, still one kind of most likely slave process restart, transaction rollback, that is one kind of mysql self-protection measures, only 1 sample like the right time.
If you want to restore it at this point, just stop slave, set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; Just turn on slave. The global variable assigned to N means:
This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.
This statement is valid only when the slave thread is not running. Otherwise, it produces an error.
Well, it's clearer than me.
Recovery of MYSQL mirror server stopped due to error
In the afternoon, the master server crashed due to some reasons. After restarting, I found that the data from the slave server did not keep up.
It was only a few days ago, so I didn't have much experience. I was a little anxious when I met this problem for the first time. Nevertheless, oneself tried, still calculate solved :)
From the server
Master_Log_File: mysqlhxmaster.000007
Read_Master_Log_Pos: 84285377
Take a look at the primary server: mysqlhxmaster.000007 | 84450528 |
It's been a long time. I really didn't catch up.
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
There is a problem. Slave_SQL_Running should be Yes.
Further down, there is an error:
Last_Errno: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is chance that your master inconsistent at this replication run this on the slave and then with SQL SQL SLAVE_SKIP_COUNTER =1; START SLAVE; Query: 'INSERT INTO hx_stat_record... (1 sentence SQL)'
There are instructions on how to do this :)
stop slave, SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
OK is done, and the slave server is done with the stacked log in a few minutes, and the two sides are in sync again :)
Slave_IO_Running: No solution 2 from MYSQL server Slave_IO_Running: No solution 2
In the morning, there was an unexpected power failure in the machine room, which led to the discovery of abnormal synchronization of mysql from the server. The solution using Slave_SQL_Running as No encountered before is invalid and still not synchronized.
See 1 for the status 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
Master 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 in 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 in log 'mysqlmaster.000079' at position 183913228
100512 9:13:17 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
100512 9:13:17 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
100512 9:13:17 [Note] Slave I/O thread exiting, read up to log 'mysqlmaster.000079', position 183913228
This time, Slave_IO_Running is No. According to the log, when the server reads mysqlmaster.000079, the location of Log is 183913228, this location does not exist, so it cannot be synchronized.
Take a look at 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 98 Start: binlog v 4, server v 5.0.27-standard-log created 100511 9:35:15
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
Try to start from the position before the damage
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE='mysqlcncnmaster.000079', MASTER_LOG_POS=183913220;
SLAVE START;
Invalid!
You have 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 is restored to Yes and synchronized! Observation for a while, no sign of error, problem solved.
In addition, Slave_IO_Running:NO is also present because there is no permission on slave to read the data on master.
If is slave_io_running no, so as far as I am to see have three kinds of case, one is the network has a problem, can't connect, like one time I replication builds with the virtual machine, use the network structure of nat, not even is death, the second is likely my cnf has a problem, not how to write configuration files that the web too much, the last one is authorized problem, replication slave and file permissions is a must. If you're not afraid to die, then all.
Once io is no, first look at the err log to see what is wrong. It may be the network, or it may be the packet is too big to accept. At this time, the parameter max_allowed_packet will be changed.
If it is slave_sql_running no, so there are two possible, one kind is slave machine this table appeared other writes, is to write the program, this is going to have a problem, today I want to return, but sometimes there is a problem, sometimes there is no problem, it is not too clear, later update, still one kind of most likely slave process restart, transaction rollback, that is one kind of mysql self-protection measures, only 1 sample like the right time.
If you want to restore it at this point, just stop slave, set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; Just turn on slave. The global variable assigned to N means:
This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.
This statement is valid only when the slave thread is not running. Otherwise, it produces an error.
Well, it's clearer than me.
Recovery of MYSQL mirror server stopped due to error
In the afternoon, the master server crashed due to some reasons. After restarting, I found that the data from the slave server did not keep up.
It was only a few days ago, so I didn't have much experience. I was a little anxious when I met this problem for the first time. Nevertheless, oneself tried, still calculate solved :)
From the server
Master_Log_File: mysqlhxmaster.000007
Read_Master_Log_Pos: 84285377
Take a look at the primary server: mysqlhxmaster.000007 | 84450528 |
It's been a long time. I really didn't catch up.
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
There is a problem. Slave_SQL_Running should be Yes.
Further down, there is an error:
Last_Errno: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is chance that your master inconsistent at this replication run this on the slave and then with SQL SQL SLAVE_SKIP_COUNTER =1; START SLAVE; Query: 'INSERT INTO hx_stat_record... (1 sentence SQL)'
There are instructions on how to do this :)
stop slave, SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
OK is done, and the slave server is done with the stacked log in a few minutes, and the two sides are in sync again :)
Slave_IO_Running: No solution 2 from MYSQL server Slave_IO_Running: No solution 2
In the morning, there was an unexpected power failure in the machine room, which led to the discovery of abnormal synchronization of mysql from the server. The solution using Slave_SQL_Running as No encountered before is invalid and still not synchronized.
See 1 for the status 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
Master 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 in 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 in log 'mysqlmaster.000079' at position 183913228
100512 9:13:17 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
100512 9:13:17 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
100512 9:13:17 [Note] Slave I/O thread exiting, read up to log 'mysqlmaster.000079', position 183913228
This time, Slave_IO_Running is No. According to the log, when the server reads mysqlmaster.000079, the location of Log is 183913228, this location does not exist, so it cannot be synchronized.
Take a look at 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 98 Start: binlog v 4, server v 5.0.27-standard-log created 100511 9:35:15
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
Try to start from the position before the damage
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE='mysqlcncnmaster.000079', MASTER_LOG_POS=183913220;
SLAVE START;
Invalid!
You have 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 is restored to Yes and synchronized! Observation for a while, no sign of error, problem solved.
In addition, Slave_IO_Running:NO is also present because there is no permission on slave to read the data on master.