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.
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.
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.
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
mysqlslavetrx accepts the following command-line options:
Execute the utility in dry-run mode, show the transactions (GTID) that would have been skipped for each slave but without effectively skipping them. This option is useful to verify if the correct transactions are skipped.
Set of Global Transaction Identifiers (GTID) to skip.
Display a help message and exit.
Display license information and exit.
Connection information for slave servers. List multiple slaves in comma-separated list.
To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.
Use login-paths from your
.mylogin.cnffile (encrypted, not visible). Example :
Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example :
Specify the data on the command-line (unencrypted, visible). Example :
The path to a file that contains a list of trusted SSL CAs.
The name of the SSL certificate file to use for establishing a secure connection.
The name of the SSL key file to use for establishing a secure connection.
Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).
Specify how much information to display. Use this option multiple times to increase the amount of information. For example,
-vv= more verbose,
Display version information and exit.
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).
The utility requires all target slaves to support global
transaction identifiers (GTIDs) and have
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. #
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