Troubleshooting of abnormal shutdown of MySQLSlave (pt-slave-restart) _ MySQL

You can also use the pt-slave-restart command of percona-toolkit to handle abnormal shutdown of MySQLSlave (pt-slave-restart.

The percona-toolkit-2.2.8-1.noarch.rpm needs to install some dependency packages.

1. Yum install perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Time-HiRes

Handle abnormal shutdown simulation 1. JAVA programs are constantly writing to the Master

1. Import java. SQL. Connection

   ;

2. Import java. SQL. DriverManager;
3. Import java. SQL. PreparedStatement;
4. Import java. SQL. SQLException;
5. PublicclassTest {
6. Publicstatic void main (String [] args) throws ClassNotFoundException,
7. SQLException {
8. Long start = System. currentTimeMillis ();
9. Class. forName ("com. mysql. jdbc. Driver ");
10. Connection connection = DriverManager. getConnection (
11. "Jdbc: mysql: // 192.168.1.70: 3306/xx", "xx", "xx ");
12. Connection. setAutoCommit (true );
13. PreparedStatement cmd = connection
14. . PrepareStatement ("insert into test (name) values (?) ");
15. For (inti = 0; I <100000000; I ++ ){
16. Cmd. setString (1, "test ");
17. Cmd.exe cuteUpdate ();
18. }
19. Connection. commit ();
20. Cmd. close ();
21. Connection. close ();
22. Longend = System. currentTimeMillis ();
23. System. out. println (end-start );
24. }
25. }

2. disable the Slave VM.

In the JAVA program running, the server load balancer virtual machine is forcibly disabled. Then restart Slave.

After startup, an exception occurs during replication. (Show slave status/G)

In addition to the auto-increment ID, this table is the name field. the default value is test.

Now, on slave, change the name field to 'mysql'

Update test set name = 'mysql ';

3. use

Pt-slave-restart

Command to fix the error.

1. [Root @ mysql2 ~] # Pt-slave-restart -- user = root -- password = xxxxxxxxx -- error-numbers = 1062
2. 2014-06-16T01: 17: 47 p =..., u = root mysql2-relay-bin.000003 312116 1062
3. 2014-06-16T01: 17: 47 p =..., u = root mysql2-relay-bin.000003 312373 1062
4. 2014-06-16T01: 17: 47 p =..., u = root mysql2-relay-bin.000003 312630 1062
5. 2014-06-16T01: 17: 47 p =..., u = root mysql2-relay-bin.000003 312887 1062
6. 2014-06-16T01: 17: 48 p =..., u = root mysql2-relay-bin.000003 313144 1062
7. 2014-06-16T01: 17: 48 p =..., u = root mysql2-relay-bin.000003 313401 1062
8. 2014-06-16T01: 17: 48 p =..., u = root mysql2-relay-bin.000003 313658 1062
9. 2014-06-16T01: 17: 48 p =..., u = root mysql2-relay-bin.000003 313915 1062
10. 2014-06-16T01: 17: 48 p =..., u = root mysql2-relay-bin.000003 314172 1062
11. 2014-06-16T01: 17: 48 p =..., u = root mysql2-relay-bin.000003 314429 1062
12. 2014-06-16T01: 17: 48 p =..., u = root mysql2-relay-bin.000003 314686 1062
13. 2014-06-16T01: 17: 48 p =..., u = root mysql2-relay-bin.000003 314943 1062
14. 2014-06-16T01: 17: 48 p =..., u = root mysql2-relay-bin.000003 315200 1594
15. Error 1594 is not in -- error-numbers.

1594 errors found

Reconnect to the Master

Reference: http://www.linuxidc.com/Linux/2014-07/104235.htm

Finally, check the replication status, and it is running properly.

The names entered on the Master node are all test, and the names are changed to mysql after the Slave fault.

Generally, there are two methods (I can think of) to process primary key duplication)

1. ignore repeated binlog events sent from the Master

2. use the binlog event of the Master node to update the slave data.

For the first method, the slave name should be mysql

For the second method, the first part is mysql, and the last part is test.

The test results are as follows. it can be preliminarily indicated that the second method is used.

The following 302 records with the name 'test' are all part of the data that has been executed but has not been written to the file when the system shuts down abnormally.

Pt-slave-restart re-executes the binlog of this part of data, so their name is 'test '.