Mysql dual-host exceptions and Solutions

Mysql dual-host exception and solution today, Mysql dual-host encountered two problems. Sort out the solution from the Internet and record it: run the following command to view the exception information: SQL code mysql> show slave status \ G; for details, view the Mysql Log. Problem 1: the command line reports the following Slave_IO_Running: No Slave_ SQL _Running: Unknown: 1236Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'client requested master to start replication from impossiposition; the first event 'mysql-bin.000073 'at 662720845, the last event read from '. /mysql-bin.000073 'At 4, the last byte read from '. /mysql-bin.000073 'At 4. 'literally, the slave server requests the master service An incorrect Copy location. For example, BLIN_LOG on the master server does not have this POSITION. solution: First, check whether this row exists in the log file of the master server, use the command Java code mysqlbinlog/LogPath/mysql-bin.000073> log in the log file to find the row corresponding to 662720845 or the line that is close to this number (try it from an early start, cannot be restored and try again from a large one), the log file content is similar: java code # at 662720839 #130614 8:36:01 server id 1 end_log_pos 662720860 Query thread_id = 207498 exec_time = 0 error_code = 0 set timestamp = 1371170161 /*! */; COMMIT /*! */; DELIMITER; then re-set the replication location, as shown in the following Java code: mysql> stop slave; mysql> change master to-> master_log_file = 'mysql-bin.000073 ',-> master_log_pos = 662720860; mysql> start slave; if not found, you can use the following command to view the maximum location of the mysql-bin.000073 Java code ls-la mysql-bin.000073 if the size is smaller than 662720845, you can skip this log, copy from the next log. Java code: mysql> stop slave; mysql> change master to-> master_log_file = 'mysql-bin.000074 ',-> master_log_pos = 4; mysql> start slave; Question 2: the error code is as follows: mysql slave Error_code: 1062 1054 if such code appears in the log, it may be an incorrect select or update operation. The master skips these operations, but when it is recorded in the binary log, slave will perform the same action based on the statements in the binary log, and an error will be reported. It is easy to understand the principle and perform the operation. Solution: Java code stop slave; set gloable SQL _slave_skip_counter = n; // n you want to skip the number of error statements start slave; if you do not want to encounter this error, the format can be written to the slave configuration file in the format of warning = 1062 warning = all // skip all types of error codes problem 3: Error code: Slave_IO_Running: Yes Slave_ SQL _Running: No Last_ SQL _Errno: 1594 Last_ SQL _Error: relay log read failure: cocould not parse relay log event entry. the possible reasons are: the master's binary log is corrup Ted (you can check this by running 'mysqlbinlog' on the binary log ), the slave's relay log is already upted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. if you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'show SLAVE status' on this slave. the above exceptions may be caused by memory overflow or hard disk Insufficient space. Solution: copy the Java code show slave status \ G; Master_Log_File: mysql_master-bin.000164 Read_Master_Log_Pos: 980240603 Relay_Log_File: slave2-relay-bin.000001 Relay_Log_Pos: 251111 Relay_Master_Log_File: mysql_master-bin.000164 Exec_Master_Log_Pos: 980240603Java code stop slave; change master to MASTER_LOG_FILE = 'mysql _ master-bin.000164 ', MASTER_LOG_POS = 980240603; start slave; Remarks: mysql replicati In on, there are two key processes on the server Load balancer instance. One is slave_ SQL _running, the other is Slave_IO_Running, the other is responsible for its own Server Load balancer mysql process, and the other is responsible for io communication with the host. If slave_io_running is no, there may be three situations: one is the network is faulty, and the other is the possibility of my. there is a problem with cnf. The last issue is authorization. replication slave and file permissions are required. If I/O is no, check the err log first to see what error is reported. It may be a network, or the package is too large 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, another possibility is the restart of the slave process and the result of transaction rollback. This is also a self-protection measure of mysql, just like read-only in key cases. If you want to recover the Server Load balancer instance at this time, stop the Server Load balancer instance and set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1; (refer to solution 2). Just enable the Server Load balancer instance.