At some point in time, when performing some maintenance/administration operation or other task which verify your replication topology, you may discover the existence of errant transactions. Some utilities like mysqlfailover and mysqlrpladmin detects errant transactions and issues a warning or error before executing. This is done because errant transactions can lead to an unstable replication topology or introduce errors after a failover or switchover.
What are errant transactions? Errant transactions are transactions directly applied by a client on a slave that do not exist on the other slaves connected to the master. By nature, these transactions should not be replicated and can lead to replication errors if the slave that possesses them is promoted to the master. In practice, this can happen for example if the errant transaction corresponds to a data insert or delete on a table that only exists on that slave. These kind of transactions usually result from a mistake or poor practice with data being changed directly on the slave without turning off the binary log.
The best way to deal with errant transaction is to avoid them, making sure that every transaction on a slave, even if needed for example to add data for reporting or execute local administrative commands, must be applied with binary logging disabled. See SET sql_log_bin Syntax, for more information about how to control logging to the binary log. However, in case errant transaction are found we still need to be able to deal with them in a easy and quick way, skipping those transactions and avoiding them from being replicated if the slave becomes the new master.
Always turn off the binary log when executing queries that
change data on a slave. Use
= 0 before the queries to turn off the binary log
sql_log_bin = 1 after the
query to turn it back on.
The goal is to execute the mysqlslavetrx utility to skip errant transactions on slaves making sure that those transaction are replicated if the slave that originated them becomes the new master.
Skipping errant transactions is done by injecting an empty transaction for each corresponding GTID on every slave. This can be a very tedious task when performed manually, especially if many transactions need to be skipped.
Thankfully, the mysqlslavetrx utility allows us to skip multiple transactions on multiple slaves in a single step.
Let's assume that we have three slaves (slave1:3311, slave2:3312, and slave3:3313) and that one of the slaves (slave1:3311) has five errant transactions that need to be skipped on the other slaves. The GTID set of those transactions is ce969d18-7b10-11e4-aaae-606720440b68:1-5.
Execute the mysqlslavetrx utility, specifying the GTID set of the transaction to skip and the target slaves.
shell> mysqlslavetrx --gtid-set=ce969d18-7b10-11e4-aaae-606720440b68:1-5 \ --slaves=dba:pass@slave2:3312,dba:pass@slave3:3313 WARNING: Using a password on the command line interface can be insecure. # # GTID set to be skipped for each server: # - slave2@3312: ce969d18-7b10-11e4-aaae-606720440b68:1-5 # - slave3@3313: ce969d18-7b10-11e4-aaae-606720440b68:1-5 # # Injecting empty transactions for 'slave2:3312'... # Injecting empty transactions for 'slave3:3313'... # #...done. #
The above example illustrates how to execute the mysqlslavetrx utility to skip the transactions for the specified GTID set on all given slaves.
To achieve this task, we only need to specify the GTID set for
the transactions to be skipped with the
--gtid-set option, and
the list of connection parameters for the target slaves with
In the above example, all of the specific GTIDs were skipped on all target slaves injecting an empty transaction for each one of them. However, it might happen that some of the GTIDs cannot be skipped on some slaves. This can happen if a transaction with the same GTID was previously applied on the target slave. The reason is due to the purpose of GTIDs, which is to uniquely identify a transaction, therefore two distinct transactions cannot be applied with the same GTID, otherwise an error is issued. The mysqlslavetrx utility checks the transactions that can be effectively skipped on each slave at the beginning, excluding already executed GTIDs.
The user for the slaves must have the required permissions to
inject an empty transaction for a specific GTID, i.e. to set
gtid_next variable. More
specifically, the login user to connect to slaves requires the
The mysqlslavetrx provides a dry run mode
that allows users to verify the GTID that would be skipped in
each slave without actually injecting empty transactions. The
--dryrun option must be
specified to use this read-only mode.