ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs
Errant transactions on the slave are those GTIDs listed that do not start with the UUID from the master.
From this list, we see there is one GTID, 130a0dec-9968-11e5-9766-aa87a4a44672:1, which is errant (not on
the master and not originating from the master). To skip this, we need to trick the master into ignoring the
transaction. Let’s look at the statements and then discuss their use. Listing 8-3 shows the process to create an
empty transaction to ignore (skip) an errant transaction.
Listing 8-3. Skipping Errant Transactions with GTIDs
mysql> SET GTID_NEXT='dd365ccc-9898-11e5-872d-172ea4aa6911:1';
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
mysql> SET GTID_NEXT='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.08 sec)
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1091 |
| mysql-bin.000002 | 314 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> PURGE BINARY LOGS TO 'mysql-bin.000002';
Query OK, 0 rows affected (0.04 sec)
Wow! That’s a complex process. We begin with setting the next GTID on the master and then starting
and committing a transaction (an empty one). We then return the GTID numbering to automatic and flush
the logs. We do this so that we can purge the old logs. You can see in the output of the SHOW BINARY LOGS
that we can purge the old binary log file. Once this process is complete, we can restart the slave.
For more information about this process, see the online MySQL reference manual at
http://dev.mysql.com/doc/refman/5.7/en/replication-gtids-failover.html#replication-
gtids-failover-empty.
Starting Replication with Existing Data
The discussion of replication in Chapter 7 assumed you were starting replication from a clean slate.
However, if you already have data on the master, you will need to make a backup of the data on the master
and restore it on the slave before starting the binary log on the master.
However, what if you already have a master and slave running and have been for some time but you
want to add another slave? You could use the backup you made originally when you set up the slave (if you
did so), but the new slave will have to read all the changes that have occurred since the backup.