In mysqlreplication, there are two key processes on the slave machine. None of them work. One is slave_ SQL _running, the other is Slave_IO_Running, the other is responsible for io communication with the host, and the other is responsible for its own slavemysql process.
In mysql replication, there are two key processes on the server Load balancer instance. None of them work. One is slave_ SQL _running, the other is Slave_IO_Running, and the other is responsible for io communication with the host, A slave mysql process is in charge of itself.
Next, let's take a look at how to restore the two ..
If slave_io_running is no, I personally think there are three situations. One is that there is a network problem and the connection fails. For example, I once used a virtual machine to build replication and used the nat network structure, that is, it cannot be connected. The second is probably my. if there is a problem with cnf, I won't talk about how to write the configuration file. There are too many websites. The last one is the authorization problem. replication slave and file permissions are required. If you are not afraid of death, you will be all ..
Once I/O is no, check the err log first to see what went wrong. It may be a network, or it may be because the package is too big to receive. In this case, modify the max_allowed_packet parameter on the master and slave nodes.
If slave_ SQL _running is no, there are two possible reasons. One is that the table on the server Load balancer has other write operations, that is, program writing, which may cause problems, today I want to reproduce it, but sometimes there will be problems and sometimes there will be no problem. Now it's not too clear, and I will update it later. Another possibility is that the slave process restarts, transaction rollback is also caused by mysql's self-protection measures, like read-only in critical cases.
If you want to restore the Server Load balancer instance at this time, stop the Server Load balancer instance and set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1. You can enable the Server Load balancer instance again. The GLOBAL variable assigned N indicates:
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, I am better at it.
Restoration of the MYSQL backup storage due to an error
In the afternoon, the master server crashed due to some reasons. After restarting, it was found that the data from the slave server did not keep up.
It was only a few days ago when I configured MYSQL master and slave nodes. I had little experience. I was a little anxious when I encountered this problem for the first time. However, I tried it myself and solved it :)
Slave Server
Master_Log_File: mysqlhxmaster.000007
Read_Master_Log_Pos: 84285377
Check the master server: mysqlhxmaster.000007 | 84450528 |
It's already a lot later, but it's really not keeping up with you.
Show slave status \ G
Slave_IO_Running: Yes
Slave_ SQL _Running: No
If there is a problem, Slave_ SQL _Running should be Yes.
Next, an error message is displayed:
Last_errno.: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. there is a chance that your master is inconsistent at this point. if you are sure that your master is OK, run this query manually on the slave and then restart the slave with set global SQL _SLAVE_SKIP_COUNTER = 1; START SLAVE ;. query: 'insert INTO hx_stat_record ...... (An SQL statement )'
Here is a description of how to operate :)
Stop slave first, execute the prompt statement, and then set global SQL _SLAVE_SKIP_COUNTER = 1; START SLAVE;
Show slave status \ G
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Okay. The slave server also finishes processing the accumulated logs in a few minutes, and the two sides are synchronized again :)
From MYSQL Server Slave_IO_Running: No solution 2
In the morning, an unexpected power failure occurs in the data center, causing mysql synchronization exceptions from the server. The solution that previously encountered Slave_ SQL _Running as No is invalid and cannot be synchronized.
Check the status of show slave status.
Master_Log_File: mysqlmaster.42679
Read_Master_Log_Pos: 183913228
Relay_Log_File: hx-relay-bin.002934
Relay_Log_Pos: 183913371
Relay_Master_Log_File: mysqlmaster.42679
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. 100' at position 000079, relay log'./hx-relay-bin.002934 'position: 183913228
100512 9:13:17 [Note] Slave I/O thread: connected to master 'replicuser @ 192.168.1.21: 100', replication started in log 'mysqlmaster. 100' at position 3306
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. 123456', position 000079
This time, Slave_IO_Running is No. From the Log, an error occurs when the server reads the location 183913228 of the mysqlmaster.000079 Log. This location does not exist and cannot be synchronized.
Check the last lines of the 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 starting from the position before the damage
Slave stop;
Change master to MASTER_LOG_FILE = 'mysqlcncnmaster. 100', MASTER_LOG_POS = 000079;
Slave start;
Invalid!
You have to start with the new log.
Slave stop;
Change master to MASTER_LOG_FILE = 'mysqlcncnmaster. 8080', MASTER_LOG_POS = 0;
Slave start;
At this time, Slave_IO_Running is restored to Yes, and synchronization is in progress! After a while, there were no signs of errors and the problem was solved.
In addition, the reason why Slave_IO_Running: NO occurs is that slave has NO permission to read data on the master.
