MySQL管理工具MySQL Utilities — mysqlrplsync(46)

默北
MySQLMySQL管理工具MySQL Utilities — mysqlrplsync(46)已关闭评论11,3191字数 14189阅读47分17秒阅读模式

mysqlrplsync 对复制同步进行检查，检查主从或从从之间的数据是否一致，并报告丢失的对象以及数据。

可以对活动的复制拓扑进行操作，应用同步进程进行数据检查。对不活动的复制也可以检查但是同步进程会被跳过。在这种情况下，需要手动进行同步。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

必需提供链接服务器的参数。主需要使用--master选项，从需要使用--slaves选项，仅仅比较从，就只需--slaves选项。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

该工具还提供了一个自动发现从的功能，需要使用--discover-slaves-login 和 --master 选项。为了使用这一功能，在启动从时，必需使用 --report-host 和 --report-port 选项来指定正确的主机名和端口号。如果没有使用这两个参数或报告不正确的信息，从可能不被发现，也就不会同步校验了。不可连接从发现从功能将忽略。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

默认情况下，所有的数据都要比较的。要检查特定的数据库或表，使用全名称列出每个元素。还可以使用--exclude选项排除某些数据库或表。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

该工具也提供了一些重要的功能，允许用户针对自己的系统调整一致性检查的执行。如用户希望减少执行的同步进程。使用--rpl-timeout 选项定义每个从同步的最大时间。更具体的说，为了比较数据让从尽可能的赶上主。在这个等待中，从状态根据预定的时间间隔周期性的轮询。可以使用--interval选项调整此轮询间隔以验证是否从是否同步。校验查询用来比较服务器之间每个表的数据。如果校验查询执行超过了预定的时间，该步骤将被跳过，避免给系统带来不良的影响如果执行耗时长。可以使用 --checksum-timeout  选项来指定校验超时。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

如果要查看更多的其他信息可以使用--verbose选项。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

该工具是专门为支持全局事务标识符（GTIDs）而设计的， gtid_mode=ON。服务器GTID被禁用将会跳过。更多信息可以查看全局事务标识符复制。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

该工具考虑到复制过滤规则来跳过过滤的数据库和表的检查。然而，使用复制过滤仍然可能导致数据一致性的问题，取决于语句的评估。更多信息可以查看下如何评估服务器的复制过滤规则。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

选项

MySQL Utilities mysqlrplsync version 1.5.3 
License type: GPLv2
Usage: mysqlrplsync --master=user:pass@host:port --slaves=user:pass@host:port \
                    [<db_name>[.<tbl_name>]]

mysqlrplsync - replication synchronization checker utility

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --discover-slaves-login=DISCOVER
                        at startup, query master for all registered slaves and
                        use the user name and password specified to connect.
                        Supply the user and password in the form
                        <user>[:<password>] or <login-path>. For example,
                        --discover-slaves-login=joe:secret will use 'joe' as
                        the user and 'secret' as the password for each
                        discovered slave.
  --master=MASTER       connection information for master server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  --slaves=SLAVES       connection information for slave servers in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].List multiple slaves in comma-
                        separated list.
  --ssl-ca=SSL_CA       The path to a file that contains a list of trusted SSL
                        CAs.
  --ssl-cert=SSL_CERT   The name of the SSL certificate file to use for
                        establishing a secure connection.
  --ssl-key=SSL_KEY     The name of the SSL key file to use for establishing a
                        secure connection.
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  --rpl-timeout=RPL_TIMEOUT
                        maximum timeout in seconds to wait for synchronization
                        (slave waiting to catch up to master). Default = 300.
  --checksum-timeout=CHECKSUM_TIMEOUT
                        maximum timeout in seconds to wait for CHECKSUM query
                        to complete. Default = 5.
  -i INTERVAL, --interval=INTERVAL
                        interval in seconds for polling slaves for sync
                        status. Default = 3.
  --exclude=EXCLUDE     databases or tables to exclude. Example:
                        <db_name>[.<tbl_name>]. List multiple names in a
                        comma-separated list.

Introduction
------------
The mysqlrplsync utility is designed to check if replication servers with
GTIDs enabled are synchronized. In other words, it checks the data consistency
between a master and a slave or between two slaves.

The utility permits you to run the check while replication is active. The
synchronization algorithm is applied using GTID information to identify those
transactions that differ (missing, not read, etc.) between the servers. During
the process, the utility waits for the slave to catch up to the master to
ensure all GTIDs have been read prior to performing the data consistency
check.

Note: if replication is not running (e.g., all slaves are stopped), the
utility can still perform the check, but the step to wait for the slave to
catch up to the master will be skipped. If you want to run the utility on a
stopped replication topology, you should ensure the slaves are up to date
first.

By default, all data is included in the comparison. To check specific
databases or tables, list each element as a separated argument for the
utility using full qualified names as shown in the following examples.

  # Check the data consistency of a replication topology, explicitly
  # specifying the master and slaves.

  $ mysqlrplsync --master=root:pass@host1:3306 \
                 --slaves=rpl:pass@host2:3306,rpl:pass@host3:3306

  # Check the data consistency of a replication topology, specifying the
  # master and using the slaves discovery feature.

  $ mysqlrplsync --master=root:pass@host1:3306 \
                 --discover-slaves-login=rpl:pass

  # Check the data consistency only between specific slaves (no check
  # performed on the master).

  $ mysqlrplsync --slaves=rpl:pass@host2:3306,rpl:pass@host3:3306

  # Check the data consistency of a specific database (db1) and table
  # (db2.t1), explicitly specifying master and slaves.

  $ mysqlrplsync --master=root:pass@host1:3306 \
                 --slaves=rpl:pass@host2:3306,rpl:pass@host3:3306 \
                 db1 db2.t1

  # Check the data consistency of all data excluding a specific database
  # (db2) and table (db1.t2), specifying the master and using slave
  # discovery.

  $ mysqlrplsync --master=root:pass@host1:3306 \
                 --discover-slaves-login=rpl:pass --exclude=db2,db1.t2


Helpful Hints
-------------
  - The default timeout for performing the table checksum is 5 seconds.
    This value can be changed with the --checksum-timeout option.

  - The default timeout for waiting for slaves to catch up is 300 seconds.
    This value can be changed with the --rpl-timeout option.

  - The default interval to periodically verify if a slave has read all of
    the GTIDs from the master is 3 seconds. This value can be changed
    with the --interval option.

注意

使用校验表对每个表进行数据的一致性检查。如果计算出的校验和不同，则表示该表不同步。然而，由于校验操作并不是完全不冲突的，两个不同的表可以产生一个相同的校验和，不过概率是非常小的。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

IP地址和主机名混合使用不推荐。涉及到反向解析的问题。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

同样也要避免混合使用 '127.0.0.1' 和'localhost',所有的'127.0.0.1'将被内部转换成localhost。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

MySQL客户端工具的路径需要包含在PATH环境变量中，以便使用login-paths验证机制。允许使用my_print_defaults 来从登陆配置文件（.mylogin.cnf）读取login-path值。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

限制

该工具是专为支持全局事务标识符（GTIDs并gtid_mode= ON设计的。由于一些在同步过程中发生的已知问题，只有MySQL Server 5.6.14或更高的版本支持。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

由于服务器端的已知问题，replicate_do_db, replicate_ignore_db, replicate_wild_do_table 复制过滤项不支持。如果检测到不支持这些复制项，该工具将发出错误信息并退出。在工具启动前将执行这些检测的。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/

实例

检测明确指定的活动的主从数据一致性，如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/ 

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is 15 transactions behind Master.
# - Slave 'localhost@3312' is 12 transactions behind Master.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_rplsync_db' database...
# - Checking 't0' table data...
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# Checking 'test_db' database...
# - Checking 't0' table data...
#   [OK] `test_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t1` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

在活动的复制系统使用从发现检测数据一致性，如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/ 

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --discover-slaves-login=rpl:pass
# Discovering slaves for master at localhost:3310
# Discovering slave at localhost:3311
# Found slave: localhost:3311
# Discovering slave at localhost:3312
# Found slave: localhost:3312
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is 15 transactions behind Master.
# - Slave 'localhost@3312' is 15 transactions behind Master.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_rplsync_db' database...
# - Checking 't0' table data...
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# Checking 'test_db' database...
# - Checking 't0' table data...
#   [OK] `test_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t1` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

在从从之间检测数据的一致性，如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/ 

shell> mysqlrplsync --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312
#
# Checking data consistency.
#
# Using Slave 'localhost@3311' as base server for comparison.
# Checking 'test_rplsync_db' database...
# - Checking 't0' table data...
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# Checking 'test_db' database...
# - Checking 't0' table data...
#   [OK] `test_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_db`.`t1` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

对特定的数据库和表进行数据一致性检查，如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/ 

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312 \
          test_rplsync_db test_db.t1
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is 15 transactions behind Master.
# - Slave 'localhost@3312' is 12 transactions behind Master.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_rplsync_db' database...
# - Checking 't0' table data...
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# Checking 'test_db' database...
# - Checking 't1' table data...
#   [OK] `test_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t1` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

排除某些特定的数据库和表，进行数据一致性检查，如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/ 

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312 \
          --exclude=test_rplsync_db,test_db.t1
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is 15 transactions behind Master.
# - Slave 'localhost@3312' is 12 transactions behind Master.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_db' database...
# - Checking 't0' table data...
#   [OK] `test_db`.`t0` checksum for server 'localhost@3311'.
#   [OK] `test_db`.`t0` checksum for server 'localhost@3312'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

下面是数据不一致的复制检查，如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/ 

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is up-to-date.
# - Slave 'localhost@3312' is up-to-date.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# [DIFF] Database NOT on base server but found on 'localhost@3311': only_on_slave_db
# Checking 'test_rplsync_db' database...
#   [DIFF] Table NOT on base server but found on 'localhost@3311': t3
#   [DIFF] Table NOT on base server but found on 'localhost@3312': t3
#   [DIFF] Table 'test_rplsync_db.t0' NOT on server 'localhost@3311'.
# - Checking 't0' table data...
#   [DIFF] `test_rplsync_db`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   WARNING: Slave not active 'localhost@3311' - Sync skipped.
#   [DIFF] `test_rplsync_db`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t1` checksum for server 'localhost@3312'.
# - Checking 't2' table data...
#   WARNING: Slave not active 'localhost@3311' - Sync skipped.
#   [OK] `test_rplsync_db`.`t2` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db`.`t2` checksum for server 'localhost@3312'.
# Checking 'only_on_master_db' database...
#   [DIFF] Database 'only_on_master_db' NOT on server 'localhost@3311'.
#   [DIFF] Database 'only_on_master_db' NOT on server 'localhost@3312'.
#
#...done.
#
# SUMMARY: 8 data consistency issues found.
#

对有过滤的复制进行检查，如下所示： 

shell> mysqlrplsync --master=user:pass@localhost:3310 \
          --slaves=rpl:pass@localhost:3311,rpl:pass@localhost:3312 \
          --verbose
# Checking users permission to perform consistency check.
#
# WARNING: Replication filters found on checked servers. This can lead data consistency issues depending on how statements are evaluated.
# More information: http://dev.mysql.com/doc/en/replication-rules.html
# Master 'localhost@3310':
#   - binlog_do_db: test_rplsync_db1
# Slave 'localhost@3311':
#   - replicate_do_table: test_rplsync_db1.t1
# Slave 'localhost@3312':
#   - replicate_ignore_table: test_rplsync_db1.t2
#   - replicate_wild_ignore_table: test\_rplsync\_db1.%3
#
# GTID differences between Master and Slaves:
# - Slave 'localhost@3311' is up-to-date.
# - Slave 'localhost@3312' is up-to-date.
#
# Checking data consistency.
#
# Using Master 'localhost@3310' as base server for comparison.
# Checking 'test_rplsync_db1' database...
# [SKIP] Table 't0' check for 'localhost@3311' - filtered by replication rule.
# - Checking 't0' table data...
#   Setting data synchronization point for slaves.
#   Compute checksum on slaves (wait to catch up and resume replication).
#   [OK] `test_rplsync_db1`.`t0` checksum for server 'localhost@3312'.
# - Checking 't1' table data...
#   Setting data synchronization point for slaves.
#   Compute checksum on slaves (wait to catch up and resume replication).
#   [OK] `test_rplsync_db1`.`t1` checksum for server 'localhost@3311'.
#   [OK] `test_rplsync_db1`.`t1` checksum for server 'localhost@3312'.
# [SKIP] Table 't2' check for 'localhost@3311' - filtered by replication rule.
# [SKIP] Table 't2' check for 'localhost@3312' - filtered by replication rule.
# [SKIP] Table 't3' check for 'localhost@3311' - filtered by replication rule.
# [SKIP] Table 't3' check for 'localhost@3312' - filtered by replication rule.
# [SKIP] Database 'test_rplsync_db0' check - filtered by replication rule.
# [SKIP] Database 'test_rplsync_db2' check - filtered by replication rule.
# [SKIP] Database 'test_rplsync_db3' check - filtered by replication rule.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

权限

对于主需要有锁定表、进行校验和获取主状态信息的权限。具体来说，需要有SUPER 或 REPLICATION CLIENT, LOCK TABLES 和 SELECT 权限。

对于从需要启动/停止从、进行校验和获取从状态信息的权限。具体来说，需要有SUPER 和 SELECT 权限。

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
默北
  • 本文由 发表于 17/04/2015 01:00:52
  • 转载请务必保留本文链接：https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplsync/
  • MySQL Utilities
  • mysqlrplsync