Provided by: mysql-utilities_1.6.4-1build1_all bug

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

SEE ALSO

       For more information, please refer to the MySQL Utilities and Fabric documentation, which
       is available online at http://dev.mysql.com/doc/index-utils-fabric.html

AUTHOR

       Oracle Corporation (http://dev.mysql.com/).