Slave power outage, MySQL master-slave crash recovery from service to normal

Tagged with: mysql thread sequence position mysql master-slave. mysql master-slave collapse

I want to upload the photos together, so more intuitive, I am sorry that the photos can not be uploaded, but also can not prevent my post!!!

Operation on Slave:

[Email protected] mysql]# tail Slave.err

160121 21:44:43 [Note] Event scheduler:purging the queue. 0 Events

160121 21:44:43 [Note] Error reading relay log event:slave SQL thread was killed

160121 21:44:43 [Note] Slave I/O thread killed while reading event

160121 21:44:43 [Note] Slave I/O thread exiting, read up to log ' test.000003 ', position 1048964

160121 21:44:43 innodb:starting shutdown ...

160121 21:44:44 Innodb:shutdown completed; Log sequence number 0 44243

160121 21:44:44 [Note]/usr/local/mysql/bin/mysqld:shutdown complete

160121 21:44:44 mysqld_safe mysqld from PID File/data/mysql/slave.pid ended

There is a position value of 1048964, this is the time to synchronize the Binlog position error, in order to avoid errors, first to see if there is this position value;

Operation on Master:

Mysqlbinlog test.000003 >test000003.txt

Vim test000003.txt#查找1048964 to determine the position;

Operation on Slave:

Mysqlbinlog slave-relay-bin.000008 > 0008.txt

Vim 0008.txt#查找1048964, found 1048964 of the location at the very bottom of the file record

The two TXT generated above can also be compared to the content, the two 1,048,964 positions near the content is consistent, so determine 1048964 this position is slave before the collapse of the main position of synchronization

Continue from the top action:

Mysql

Slave stop; #显示OK

Change Master to master_host= ' 192.168.20.125 ', master_port=3306, master_user= ' repl ', master_password= ' 123456 ', master _log_file= ' test.000003 ', master_log_pos=1049044;

#重新change Master to, Error!!

ERROR 1201 (HY000): Could not initialize master info structure; More error messages can is found in the MySQL error log

You can only clear it first.

Reset slave; #显示OK, and then re-operate the change master in the above to be able to execute; show OK

Slave start; #显示OK

show slave status\g; #IO和SQL线程Yes, master and slave recovery. Query the database table, found that the data are synchronized over

Note: Mysqlbinlog After those steps, if in the larger log file directly vim after the search takes a long time and particularly laborious;

You can first grep-n 1048964 0008.txt, draw his line number, and then use tail and head to intercept their section.

Finally, MySQL master and slave configuration is not difficult, but very fragile, need to write a script monitoring, not elaborated here;

How to recover after the crash, this has to clarify the MySQL master-slave mechanism; MySQL master and slave operation mechanism is probably:

1. Slave the above IO line thread attached the Master and requests the log content from the specified location (or from the beginning of the log) to the designated log file;

2. When Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the Slave side. In addition to the information contained in the log, the return information includes the name of the binary log file on the Master side of the returned information and its location in binary log;

3. After the Slave IO thread receives the information, it writes the received log content to the end of the relay log file (mysql-relay-bin.xxxxxx) on the Slave side, And the read to the master side of the Bin-log's file name and location to the Master-info file, so that the next time you read the high-speed master "I need to start from somewhere in the Bin-log log content, please send me"

4. When the Slave SQL thread detects a new addition to the Relay log, it immediately parses the contents of the log file into those executable query statements that are executed at the Master end, and executes the query itself. In this way, the same Query is actually executed on the Master and Slave ends, so the data on both ends is exactly the same.

From the 3rd above you can understand how to operate the recovery, that is, to specify the location of the binlog to achieve the purpose of recovery, only that.

This article is from the "Kw_lee" blog, make sure to keep this source http://11398377.blog.51cto.com/11388377/1758396

Slave power outage, MySQL master-slave crash recovery from service to normal