Ubuntu Manpage: mysqlslavetrx - Slave transaction skip utility

Provided by: mysql-utilities_1.6.4-1build1_all

NAME

       mysqlslavetrx - Slave transaction skip utility

SYNOPSIS

       mysqlslavetrx --gtid-set=GTID_SET --slaves=SLAVES

DESCRIPTION

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' 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 Using GTIDs for Failover and Scaleout[1], 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 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.
NOTES.PP 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 will allow
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.PP The utility
requires all target slaves to support global transaction identifiers (GTIDs) and have
gtid_mode=ON. EXAMPLES.PP 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.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 http://www.gnu.org/licenses/.

NOTES

        1. Using GTIDs for Failover and Scaleout
           http://dev.mysql.com/doc/refman/5.7/en/replication-gtids-failover.html

AUTHOR