mysql-utilities 复制工具
chenzuoqing Lv3
  2018-02-27 16:05:11 2018-02-27 16:05   2023-10-29 15:04:57      

MySQL复制工具

了解 mysql-utilities 中关于复制的几个工具

  • mysql-utilities：
    • mysqlreplicate # 快速部署主从
    • mysqlrplcheck # 检测从服务器状态
    • mysqlrplsync # 检测主从复制数据是否一致
    • mysqlrplshow # 获取主从拓扑

贴一下工具包中其他脚本的用途

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
Binary Log Operations(二进制日志操作) 
        mysqlbinlogmove    二进制日志移动
        mysqlbinlogpurge   二进制日志清理
        mysqlbinlogrotate  二进制日志轮转工具    

Database Operations(数据库操作)
        mysqldbexport      数据导出
        mysqldbimport      数据导入
        mysqldbcopy        库级别数据库复制
        mysqldiff          数据库对象级别比较工具
        mysqldbcompare     数据库库级别比较工具

General Operations(通用用的操作)     
        mysqldiskusage     磁盘空间查看
        mysqlfrm           恢复故障表.frm文件
        mysqluserclone     用户克隆工具
        mysqluc            Utilities帮助工具 
        mysqlindexcheck    索引检测工具
        mysqlmetagrep      元数据过滤器
        mysqlprocgrep      进程搜索及清理工具

High Availability Operations(高可用)
        mysqlreplicate     主从复制工具
        mysqlrpladmin      主从复制管理工具
        mysqlrplcheck      主从复制检测工具
        mysqlrplms         主从多源复制工具
        mysqlrplshow       主从复制拓扑图工具
        mysqlrplsync       主从复制同步工具
        mysqlfailover      主从failover工具
        mysqlslavetrx      从库事务跳过工具 

Server Operations(服务器操作)
        mysqlserverinfo    服务器信息查看工具
        mysqlserverclone   服务器克隆工具    

Specialized Operations(特殊操作)
        mysqlauditadmin    审计管理工具 
        mysqlauditgrep     审计日志过滤工具

mysqlreplicate

该实用程序允许管理员设置并启动从一台服务器（主设备）到另一台服务器（从设备）的复制。用户提供从站的登录信息和连接到主站的连接信息。还可以指定要用于测试复制的数据库。

  • 参数解析 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    --master=master                 通常命令行： user[:passwd]@host[:port][:socket]
    --slave=slave                   同上，指定的用户需要有with grant option权限
    --rpl-user=user:passwd          指定用户名密码，用户可以不存在
    -b --start-from-beginning       从开头复制
    --master-log-file               从特定主二进制日志文件中的第一个事件开始复制
    --master-log-pos=master_log_pos 从master日志文件中的这个位置开始复制。除非--master-log-file给出，否则此选项无效
    -vv                             详细信息
    -vvv                            debug信息
    -p --pedantic       如果两台服务器没有相同的存储引擎集合，相同的默认存储引擎和相同的InnoDB存储引擎，则会失败。

  1. 准备工作

    • master：192.168.122.66
    • slave：192.168.122.70
    • 分别开启二进制日志，配置不同的 server_id
    • 配置文件添加 report-host=[本机ip]（非必要）

  2. master 和 slave 为 mysqlreplicate 创建一个带授权权限的用户

    1
    grant all on *.* to 'root'@'%' identified by '111111' with grant option;

  3. 在安装了 utilities 工具包并且可以连接到master和slave的任意机器上执行

    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
    shell> mysqlreplicate --master=root:[email protected]:3306 \
                          --slave=root:[email protected]:3306 \
                          --rpl-user=rpl:rpl -vv -b
    WARNING: Using a password on the command line interface can be insecure.
    # master on 192.168.122.66: ... connected.
    # slave on 192.168.122.70: ... connected.
    # master id = 11
    #  slave id = 12
    # master uuid = d09f71e0-0caf-11e8-b13e-525400eac085
    #  slave uuid = f733905a-1d1a-11e8-bab4-5254004dae68
    # Checking InnoDB statistics for type and version conflicts.
    # Checking storage engines...
    # Checking for binary logging on master...
    # Setting up replication...
    # Connecting slave to master...
    # CHANGE MASTER TO MASTER_HOST = '192.168.122.66', MASTER_USER = 'rpl', MASTER_PASSWORD = 'rpl', MASTER_PORT = 3306, MASTER_AUTO_POSITION=1
    # Starting slave from the beginning...
    # IO status: Queueing master event to the relay log
    # IO thread running: Yes
    # IO error: None
    # SQL thread running: Yes
    # SQL error: None
    # Waiting for slave to synchronize with master
    # IO status: Queueing master event to the relay log
    # IO thread running: Yes
    # IO error: None
    # SQL thread running: Yes
    # SQL error: None

    # Waiting for slave to synchronize with master  -- 这里因为我是-b参数从头复制，所以要很长时间才追到master的进度，程序不等完成，会报错。其实slave还在追
    ERROR: failed to sync slave with master.
    ERROR: Cannot setup replication.

mysqlrplcheck

  1. 当主从启动后，可以通过另外的 mysqlrplcheck 工具来查看主从复制状态，指定 master 和 slave，常用的参数就是 -s, --show-slave-status 可以看到 slave 复制状态。

  2. mysqlrplcheck 工具检测

    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
    mysql> mysqlrplcheck --master=root:[email protected]:3306 --slave=root:[email protected]:3306 -s
    WARNING: Using a password on the command line interface can be insecure.
    # master on 192.168.122.66: ... connected.
    # slave on 192.168.122.70: ... connected.
    Test Description                                                     Status
    ---------------------------------------------------------------------------
    Checking for binary logging on master                                [pass]
    Are there binlog exceptions?                                         [pass]
    Replication user exists?                                             [pass]
    Checking server_id values                                            [pass]
    Checking server_uuid values                                          [pass]
    Is slave connected to master?                                        [pass]
    Check master information file                                        [pass]
    Checking InnoDB compatibility                                        [pass]
    Checking storage engines compatibility                               [pass]
    Checking lower_case_table_names settings                             [pass]
    Checking slave delay (seconds behind master)                         [pass]

    #
    # Slave status: 
    #
                    Slave_IO_State : Waiting for master to send event
                       Master_Host : 192.168.122.66
                       Master_User : rpl
                       Master_Port : 3306
                     Connect_Retry : 60
                   Master_Log_File : bin.000010
               Read_Master_Log_Pos : 17301
                    Relay_Log_File : relay.000009
                     Relay_Log_Pos : 17502
             Relay_Master_Log_File : bin.000010
                  Slave_IO_Running : Yes
                 Slave_SQL_Running : Yes
                   Replicate_Do_DB : 
               Replicate_Ignore_DB : 
                Replicate_Do_Table : 
            Replicate_Ignore_Table : 
           Replicate_Wild_Do_Table : 
       Replicate_Wild_Ignore_Table : 
                        Last_Errno : 0
                        Last_Error : 
                      Skip_Counter : 0
               Exec_Master_Log_Pos : 17301
                   Relay_Log_Space : 223036003
                   Until_Condition : None
                    Until_Log_File : 
                     Until_Log_Pos : 0
                Master_SSL_Allowed : No
                Master_SSL_CA_File : 
                Master_SSL_CA_Path : 
                   Master_SSL_Cert : 
                 Master_SSL_Cipher : 
                    Master_SSL_Key : 
             Seconds_Behind_Master : 0
     Master_SSL_Verify_Server_Cert : No
                     Last_IO_Errno : 0
                     Last_IO_Error : 
                    Last_SQL_Errno : 0
                    Last_SQL_Error : 
       Replicate_Ignore_Server_Ids : 
                  Master_Server_Id : 11
                       Master_UUID : d09f71e0-0caf-11e8-b13e-525400eac085
                  Master_Info_File : mysql.slave_master_info
                         SQL_Delay : 0
               SQL_Remaining_Delay : None
           Slave_SQL_Running_State : Slave has read all relay log; waiting for more updates
                Master_Retry_Count : 86400
                       Master_Bind : 
           Last_IO_Error_Timestamp : 
          Last_SQL_Error_Timestamp : 
                    Master_SSL_Crl : 
                Master_SSL_Crlpath : 
                Retrieved_Gtid_Set : d09f71e0-0caf-11e8-b13e-525400eac085:42-384
                 Executed_Gtid_Set : d09f71e0-0caf-11e8-b13e-525400eac085:1-384,
    f733905a-1d1a-11e8-bab4-5254004dae68:1-6
                     Auto_Position : 1
              Replicate_Rewrite_DB : 
                      Channel_Name : 
                Master_TLS_Version : 
    # ...done.

mysqlrplsync

mysqlrplsync 用于检测主从数据是否一致，不指定 test_rplsync_db 为检测全部

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
# 测试test库是否一致
# 不同步案例
shell> mysqlrplsync --master=root:[email protected]:3306 \
                    --slave=root:[email protected]:3306 test_rplsync_db test
#
# GTID differences between Master and Slaves:
# - Slave '192.168.122.70@3306' is up-to-date.
#
# Checking data consistency.
#
# Using Master '192.168.122.66@3306' as base server for comparison.
# WARNING: specified database to check does not exist on base server and will be skipped: test_rplsync_db.
# Checking 'test' database...
#   [DIFF] Table 'test.a' NOT on server '192.168.122.70@3306'.     -- DIFF位置为OK表示一致
#   [DIFF] Table 'test.b' NOT on server '192.168.122.70@3306'.
#   [DIFF] Table 'test.customer' NOT on server '192.168.122.70@3306'.
#   [DIFF] Table 'test.district' NOT on server '192.168.122.70@3306'.
#   [DIFF] Table 'test.history' NOT on server '192.168.122.70@3306'.
#   [DIFF] Table 'test.item' NOT on server '192.168.122.70@3306'.
#   [DIFF] Table 'test.new_orders' NOT on server '192.168.122.70@3306'.
#   [DIFF] Table 'test.orders' NOT on server '192.168.122.70@3306'.
#   [DIFF] Table 'test.order_line' NOT on server '192.168.122.70@3306'.
#   [DIFF] Table 'test.stock' NOT on server '192.168.122.70@3306'.
#   [DIFF] Table 'test.warehouse' NOT on server '192.168.122.70@3306'.
#
#...done.
#
# SUMMARY: 11 data consistency issues found.   -- 上面11个表不一致，此时slave还在追日志
#

# 正常案例
shell> mysqlrplsync --master=root:[email protected]:3307 --slave=root:[email protected]:3307
WARNING: Using a password on the command line interface can be insecure.
#
# GTID differences between Master and Slaves:
# - Slave '192.168.122.80@3307' is up-to-date.
#
# Checking data consistency.
#
# Using Master '192.168.122.66@3307' as base server for comparison.
# Checking 'tpcc10' database...
# - Checking 'customer' table data...
#   [OK] `tpcc10`.`customer` checksum for server '192.168.122.80@3307'.
# - Checking 'district' table data...
#   [OK] `tpcc10`.`district` checksum for server '192.168.122.80@3307'.
# - Checking 'history' table data...
#   [OK] `tpcc10`.`history` checksum for server '192.168.122.80@3307'.
# - Checking 'item' table data...
#   [OK] `tpcc10`.`item` checksum for server '192.168.122.80@3307'.
# - Checking 'new_orders' table data...
#   [OK] `tpcc10`.`new_orders` checksum for server '192.168.122.80@3307'.
# - Checking 'orders' table data...
#   [OK] `tpcc10`.`orders` checksum for server '192.168.122.80@3307'.
# - Checking 'order_line' table data...
#   [OK] `tpcc10`.`order_line` checksum for server '192.168.122.80@3307'.
# - Checking 'stock' table data...
#   [OK] `tpcc10`.`stock` checksum for server '192.168.122.80@3307'.
# - Checking 'warehouse' table data...
#   [OK] `tpcc10`.`warehouse` checksum for server '192.168.122.80@3307'.
# Checking 'an' database...
# - Checking 'a' table data...
#   [OK] `an`.`a` checksum for server '192.168.122.80@3307'.
#
#...done.
#
# SUMMARY: No data consistency issue found.
#

mysqlrplshow

  1. 列出 master 下所有的 slave，文字图形的方式展示，有层级时可以递归

  2. 前提条件：需要在 slave 的 my.cnf 中添加 report-host = [本机ip]，report-port默认 3306

  3. 用法和参数

    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
    mysqlrplshow   --master=user:pass@host:port
                   --discovery-slave-login=user:pass    # 自动发现从机并通过这个用户查询信息，需要replicate client和slave权限
                   --recurse, -r      # 递归查找拓扑，在级联复制的时候很方便
                   --show-list, -l    # 列出表格描述slave对应的master
                   -vvv               # debug信息，-v可以显示slave线程状态

    shell> mysqlrplshow --master=root:111111@"192.168.122.66" --discover-slaves-login=root:111111 -vvv
    WARNING: Using a password on the command line interface can be insecure.
    # master on 192.168.122.66: ... connected.
    # Finding slaves for master: 192.168.122.66:3306

    # Replication Topology Graph
    192.168.122.66:3306 (MASTER)
       |
       +--- 192.168.122.70:3306 [IO: Yes, SQL: Yes] - (SLAVE)
       |
       +--- 192.168.122.80:3306 [IO: Yes, SQL: Yes] - (SLAVE)
       
    # 搭建级联后的示例
    shell> mysqlrplshow --master=root:111111@"192.168.122.66:3307" --discover-slaves-login=root:111111 -r -v
    WARNING: Using a password on the command line interface can be insecure.
    # master on 192.168.122.66: ... connected.
    # Finding slaves for master: 192.168.122.66:3307
    # master on 192.168.122.70: ... connected.
    # Finding slaves for master: 192.168.122.70:3307
    # master on 192.168.122.70: ... connected.
    # Finding slaves for master: 192.168.122.70:3306
    # master on 192.168.122.80: ... connected.
    # Finding slaves for master: 192.168.122.80:3307

    # Replication Topology Graph
    192.168.122.66:3307 (MASTER)
       |
       +--- 192.168.122.70:3307 [IO: Yes, SQL: Yes] - (SLAVE + MASTER)
       |   |
       |   +--- 192.168.122.70:3306 [IO: Yes, SQL: Yes] - (SLAVE)
       |
       +--- 192.168.122.80:3307 [IO: Yes, SQL: Yes] - (SLAVE)
mysql-utilities 复制工具
2018/02/27/mysql-utilities-复制工具/
Author
chenzuoqing
Published
2018-02-27 16:05
License
 Comments
  1. MySQL复制工具
    1. mysqlreplicate
    2. mysqlrplcheck
    3. mysqlrplsync
    4. mysqlrplshow