Shulou(Shulou.com)06/01 Report--

Skip replication error

Mysql sometimes fails to replay sql execution from the library because of binlog mechanism problems, especially in the old STATEMENT mode (because of functions and stored procedures, etc.), which is why it emphasizes the use of mixed and row mode. In addition, there are some external XA transactions, because the XA transaction log is not in mysql, sometimes rollback failures occur, resulting in master-slave errors.

What should I do when these conditions occur? If it is redone, it is possible, but if the library is very large, it will not take an hour or two. If it is done to separate production from reading and writing, it will be difficult to explain. At this point, we can choose to modify the data manually, then skip the replication error, and then the master-slave replication will run again.

Let's go on to talk about two modes, starting with GTID mode, which he can skip in two ways. The first is more frequently used, injecting empty transactions to skip:

# find the conflicting GTID number. MySQL > show slave status\ G. . . Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-83648451. . . # stop copying mysql > stop slave;# and then set a transaction GTID to skip, because we are stuck here, so skip the meaning of transaction 83648451: mysql > SET gtid_next = '09cb91bf-2669-11e7-8b70-00163e0835ffRover 83648451transaction # injection empty transaction mysql > BEGIN;COMMIT;# set GTID back to automatic mode mysql > SET gtid_next =' AUTOMATIC';# restart replication mysql > START SLAVE # this allows you to skip a transaction, the principle is to replace the conflicting transaction passed by master by executing an empty transaction. # of course, skipped, it does not mean that the data will not be modified, or you have to manually modify it, so that everything will return to normal mysql > update tables set.

Second, just skip the GTID and change it to the later one.

# find the conflicting GTID number. MySQL > show slave status\ G. . . Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-83648451. . . # stop copying mysql > stop slave;# and directly set the above GTID value + 1mysql > SET @ @ GLOBAL.GTID_PURGED='09cb91bf-2669-11e7-8b70-00163e0835ff:1-83648452GTID value # restart replication mysql > START SLAVE;# skipped, of course, does not mean that this data will not be modified, or you have to manually modify it, so that everything will return to normal mysql > update tables set.

Then let's talk about the traditional model, which is relatively simple.

# stop replication mysql > slave stop;# setting skip a transaction mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = restart replication mysql > slave start# this is normal, but of course, you still have to modify the data to mysql > update tables set.

In fact, there is also a taboo setting way, you can skip some copy errors, because it is not recommended, just take it out to say that you can do it.

# modify configuration file vim / etc/my.cnf [mysqld] # skip errors of specified error no type # slave-skip-errors=1062,1053,1146# skip all errors # slave-skip-errors=all

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.