MySQL for the Internet of Things

ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs

For binary log file and position-based replication, you can instruct the slave to skip a number of events
if they do not apply to the slave. But first, you should perform the following steps for diagnosing the problem.
At this point, the slave has stopped with an error.

1. Determine the error and look up the error in the reference manual. Sometimes
   the error is well known or has a predictable solution.


2. Determine the event or events causing a problem. A look at the output of SHOW
   SLAVE STATUS will tell you the binary log file and position of the event. You can
   use that on the master to see the event with the SHOW BINLOG EVENTS statement.
   If the event cannot be run (wrong tables, and so on), you must skip the event. If
   you are not sure, start the slave again and check for errors.


3. If you must skip the events, issue the following statements to skip and start the
   slave. The following statement skips the next two events:

mysql> SET GLOBAL sql_slave_skip_counter = 2

mysql> START SLAVE;

4. If this does not solve the problem, you may need to skip more events. However, if
   you cannot find a restarting point, you may need to do more intensive diagnosis
   or restore the slave from the latest backup and restart replication.^2

For GTID-enabled replication, things are not so simple. Here, we need to identify the errant
transactions on the slaves. You can see these in the SHOW BINLOG EVENTS output on the slave. Once you
have the GTID, you can use the following statements to create an empty transaction in the binary log on
the master. This will effectively tell the master that the event has already occurred even though it was not
executed on the master or any slave to which the event is transmitted.
Errant transactions are more common in GTID replication because the slaves are all logging changes
in their own binary logs. Plus, when the slave connects to a master, it exchanges its lists of GTIDs with the
master. Should the lists disagree, there will be errant transactions. You can determine whether there are
errant transactions by issuing the following statements. We begin by identifying the UUID on the master.

mysql> SHOW VARIABLES LIKE "%uuid%";
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 0f5ae7a2-9968-11e5-8990-08fdde5e3c13 |
+---------------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

Then, on the slave, run SHOW SLAVE STATUS and examine the result for executed_gtid_set, as
shown here:

Executed_Gtid_Set: 0f5ae7a2-9968-11e5-8990-08fdde5e3c13:1,
130a0dec-9968-11e5-9766-aa87a4a44672:1

(^2) Sometimes this is the easiest and fastest way to bring a slave back online after encountering fatal errors or data
corruption. Thus, always take regular backups.