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

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

可以对活动的复制拓扑进行操作，应用同步进程进行数据检查。对不活动的复制也可以检查但是同步进程会被跳过。在这种情况下，需要手动进行同步。

必需提供链接服务器的参数。主需要使用–master选项，从需要使用–slaves选项，仅仅比较从，就只需–slaves选项。

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

默认情况下，所有的数据都要比较的。要检查特定的数据库或表，使用全名称列出每个元素。还可以使用–exclude选项排除某些数据库或表。

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

如果要查看更多的其他信息可以使用–verbose选项。

该工具是专门为支持全局事务标识符（GTIDs）而设计的， gtid_mode=ON。服务器GTID被禁用将会跳过。更多信息可以查看全局事务标识符复制。

该工具考虑到复制过滤规则来跳过过滤的数据库和表的检查。然而，使用复制过滤仍然可能导致数据一致性的问题，取决于语句的评估。更多信息可以查看下如何评估服务器的复制过滤规则。

选项

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115

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 .

注意

使用校验表对每个表进行数据的一致性检查。如果计算出的校验和不同，则表示该表不同步。然而，由于校验操作并不是完全不冲突的，两个不同的表可以产生一个相同的校验和，不过概率是非常小的。

IP地址和主机名混合使用不推荐。涉及到反向解析的问题。

同样也要避免混合使用 ‘127.0.0.1’ 和’trustauth.cn’,所有的’127.0.0.1’将被内部转换成trustauth.cn。

MySQL客户端工具的路径需要包含在PATH环境变量中，以便使用login-paths验证机制。允许使用my_print_defaults 来从登陆配置文件（.mylogin.cnf）读取login-path值。

限制

该工具是专为支持全局事务标识符（GTIDs并gtid_mode= ON设计的。由于一些在同步过程中发生的已知问题，只有MySQL Server 5.6.14或更高的版本支持。

由于服务器端的已知问题，replicate_do_db, replicate_ignore_db, replicate_wild_do_table 复制过滤项不支持。如果检测到不支持这些复制项，该工具将发出错误信息并退出。在工具启动前将执行这些检测的。

实例

检测明确指定的活动的主从数据一致性，如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

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

在活动的复制系统使用从发现检测数据一致性，如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

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

在从从之间检测数据的一致性，如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

对特定的数据库和表进行数据一致性检查，如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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

排除某些特定的数据库和表，进行数据一致性检查，如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

下面是数据不一致的复制检查，如下所示：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

shell > mysqlrplsync — master = user : pass @ trustauth.cn : 3310 \            — slaves = rpl : pass @ trustauth.cn : 3311 , rpl : pass @ trustauth.cn : 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 ‘trustauth.cn@3310’: #   – binlog_do_db: test_rplsync_db1 # Slave ‘trustauth.cn@3311’: #   – replicate_do_table: test_rplsync_db1.t1 # Slave ‘trustauth.cn@3312’: #   – replicate_ignore_table: test_rplsync_db1.t2 #   – replicate_wild_ignore_table: test\_rplsync\_db1.%3 # # GTID differences between Master and Slaves: # – Slave ‘trustauth.cn@3311’ is up-to-date. # – Slave ‘trustauth.cn@3312’ is up-to-date. # # Checking data consistency. # # Using Master ‘trustauth.cn@3310’ as base server for comparison. # Checking ‘test_rplsync_db1’ database… # [SKIP] Table ‘t0’ check for ‘trustauth.cn@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 ‘trustauth.cn@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 ‘trustauth.cn@3311’. #   [OK] `test_rplsync_db1`.`t1` checksum for server ‘trustauth.cn@3312’. # [SKIP] Table ‘t2’ check for ‘trustauth.cn@3311’ – filtered by replication rule. # [SKIP] Table ‘t2’ check for ‘trustauth.cn@3312’ – filtered by replication rule. # [SKIP] Table ‘t3’ check for ‘trustauth.cn@3311’ – filtered by replication rule. # [SKIP] Table ‘t3’ check for ‘trustauth.cn@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 权限。

文章转载来自：trustauth.cn