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' will be missing from that slave. Moreover, 'row1' will no longer be replicated from the master since the GTID for the skipped transaction will be associated to an empty transaction. As a consequence, the data for table 't1' on 'slave1' will be inconsistent with the one on the master and other slaves because 'row1' will be 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 will 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 will create 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 m[blue]Using GTIDs for Failover and Scaleoutm[]

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 will be 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.PP mysqlslavetrx accepts the following command-line options:

* --dryrun

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 will be skipped.

* --gtid-set=<gtid-set>

Set of Global Transaction Identifiers (GTID) to skip.

* --help

Display a help message and exit.

* --license

Display license information and exit.

* --slaves=<slaves_connections>

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.cnf file (encrypted, not visible). Example : <login-path>[:<port>][:<socket>]

* Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : <configuration-file-path>[:<section>]

* Specify the data on the command-line (unencrypted, visible). Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]

* --ssl-ca

The path to a file that contains a list of trusted SSL CAs.

* --ssl-cert

The name of the SSL certificate file to use for establishing a secure connection.

* --ssl-key

The name of the SSL key file to use for establishing a secure connection.

* --ssl

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).

* --verbose, -v

Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

* --version

Display version information and exit.

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.PP 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.

COPYRIGHT

Copyright © 2006, 2016, Oracle and/or its affiliates. All rights reserved.

This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see www.gnu.org/licenses

NOTES

1.

Using GTIDs for Failover and Scaleout

dev.mysql.com/doc/refman/5.7/en/replication-gtids-failover.html

SEE ALSO

AUTHOR