MySQL Utilities

5.26 mysqlslavetrx — Slave transaction skip utility

This utility allows users to skip multiple transactions on slaves in a single step for gtid-enabled servers. In particular, it injects empty transactions on all specified slaves for each GTID in the specified GTID set.

Note

Skipping transactions can be useful to recover from erroneous situations that can occur during the replication process. However, this technique must be applied with extreme caution and full knowledge of its consequences because it might lead to data inconsistencies between the replication servers.

For example, let's consider that a transaction that inserts some data 'row1' into table 't1' fails on 'slave1'. If that transaction is simply skipped to quickly resume replication on 'slave1' without any additional intervention, then 'row1' is missing from that slave. Moreover, 'row1' is no longer replicated from the master since the GTID for the skipped transaction is associated to an empty transaction. As a consequence, the data for table 't1' on 'slave1' is inconsistent with the one on the master and other slaves because 'row1' is missing. For this reason, we should make sure that the technique to skip transactions is applied in the right situations and that all additional operations to keep the data consistent are also taken.

Skipping transactions is also useful to ignore errant transactions on slaves in order to avoid those transactions from being replicated if a failover occurs. For example, consider that some transactions with custom data changes were accidentally committed on a slave without turning off binary logging, and that those changes are specific to that slave and should not be replicated (e.g., additional data for reporting purposes, data mining, or local administrative commands). If that slave becomes the new master as a result of a failover or switchover, then those errant transactions start being replicated across the topology. In order to avoid this situation, errant transactions should be skipped on all slaves.

Note

An errant transaction is a transaction that exists on a slave but not on all of the slaves connected to the master. An errant transaction has a GTID associated with the UUID of the slave to which it was committed. These type of transactions can result from write operations performed on the slave while binary logging is enabled. By nature, these transactions should not be replicated.

It is considered poor practice to execute write operation on slave with binary logging enabled because it creates errant transactions that can lead to unstable topologies in failover scenarios. The best way to deal with errant transactions is to avoid writing or applying query statements to the slave directly without turning off binary logging first.

There are other situations like provisioning and scale out where injecting empty transaction can be a useful technique. See Using GTIDs for Failover and Scaleout, for more information about this scenario.

Users must specify the set of GTIDs for the transactions to skip with the --gtid-set option, and the server connection parameters for the list of target slaves using the --slaves option.

The utility displays the GTID set that is effectively skipped for each slave. If any of the specified GTIDs correspond to an already committed transaction on a slave, then those GTIDs are ignored for that slave (not skipped) because no other transaction (empty or not) can be applied with the same GTID. Users can execute the utility in dry run mode using the --dryrun option to confirm which transactions would be skipped with the provided input values without effectively skipping them.

The utility does not require replication to be stopped. However, in some situations it is recommended. For example, in order to skip a transaction from the master on a slave, that slave should be stopped otherwise the target transaction might be replicated before the execution of the skip operation and therefore not skipped as expected.

Users can also use the --verbose option to see additional information when the utility executes. This includes a list of slaves not supporting GTIDs and the GTIDs of the injected transactions.

OPTIONS

mysqlslavetrx accepts the following command-line options:

NOTES

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

LIMITATIONS

The utility requires all target slaves to support global transaction identifiers (GTIDs) and have gtid_mode=ON.

EXAMPLES

Skip multiple GTIDs on the specified slaves:

shell> mysqlslavetrx --gtid-set=af6b22ee-7b0b-11e4-aa8d-606720440b68:7-9 \
          --slaves=user:pass@localhost:3311,user:pass@localhost:3312
WARNING: Using a password on the command line interface can be insecure.
#
# GTID set to be skipped for each server:
# - localhost@3311: af6b22ee-7b0b-11e4-aa8d-606720440b68:7-9
# - localhost@3312: af6b22ee-7b0b-11e4-aa8d-606720440b68:7-9
#
# Injecting empty transactions for 'localhost:3311'...
# Injecting empty transactions for 'localhost:3312'...
#
#...done.
#

Execute the utility in dryrun mode to verify which GTIDs would have been skipped on all specified slaves:

shell> mysqlslavetrx --gtid-set=af6b22ee-7b0b-11e4-aa8d-606720440b68:6-12 \
          --slaves=user:pass@localhost:3311,user:pass@localhost:3312
          --dryrun
WARNING: Using a password on the command line interface can be insecure.
#
# WARNING: Executing utility in dry run mode (read only).
#
# GTID set to be skipped for each server:
# - localhost@3311: af6b22ee-7b0b-11e4-aa8d-606720440b68:6:10-12
# - localhost@3312: af6b22ee-7b0b-11e4-aa8d-606720440b68:6:10-12
#
# (dry run) Injecting empty transactions for 'localhost:3311'...
# (dry run) Injecting empty transactions for 'localhost:3312'...
#
#...done.
#

Skip multiple GTIDs on the specified slaves using the verbose mode:

shell> mysqlslavetrx --gtid-set=af6b22ee-7b0b-11e4-aa8d-606720440b68:6-12 \
          --slaves=user:pass@localhost:3311,user:pass@localhost:3312
          --verbose
WARNING: Using a password on the command line interface can be insecure.
#
# GTID set to be skipped for each server:
# - localhost@3311: af6b22ee-7b0b-11e4-aa8d-606720440b68:6:10-12
# - localhost@3312: af6b22ee-7b0b-11e4-aa8d-606720440b68:6:10-12
#
# Injecting empty transactions for 'localhost:3311'...
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:6
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:10
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:11
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:12
# Injecting empty transactions for 'localhost:3312'...
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:6
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:10
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:11
# - af6b22ee-7b0b-11e4-aa8d-606720440b68:12
#
#...done.
#

PERMISSIONS REQUIRED

The user used to connect to each slave must have the required permissions to inject empty transactions, more precisely the SUPER privilege is required to set the gtid_next variable.