MySQL管理工具MySQL Utilities — mysqlrplshow(45)

mysqlrplshow 用来显示主从复制关系，并绘制主的图形结构，标注每个主机名和端口。

必需指定–discover-slaves-login选项来提供用户名和密码以发现拓扑结构中的任何从。

使用 –recurse 选项，递归搜索从。这将导致工具连接到发现的每个从并尝试确定是否还有任何其他的从。如果发现从，这个过程将一直持续到从的主（圆形拓扑）。以连续缩进的图显示拓扑结构。 <--> 符号表示圆形拓扑。

如果使用–recurse选项，该工具将使用主提供的用户名和密码尝试连接从。默认情况下，如果连接尝试失败，抛出一个错误并停止。为了改变这种行为，可以是用 –prompt 选项，来提示连接失败的用户名和密码。也可以是用 –num-retries=n 选项来指定重新尝试的次数。

下面是一个典型的从中继拓扑：

1 2 3 4 5 6 7 8

# Replication Topology Graph:: trustauth.cn : 3311 ( MASTER )    |    + — – trustauth.cn : 3310 – ( SLAVE )    |    + — – trustauth.cn : 3312 – ( SLAVE + MASTER )        |        + — – trustauth.cn : 3313 – ( SLAVE )

MASTER, SLAVE, SLAVE+MASTER 分别表明仅仅是主，仅仅是从，既是从也是主。

下面是一个圆形的复制拓扑。其中<–> 符合表示圆：

1 2 3 4 5 6 7 8

# Replication Topology Graph trustauth.cn : 3311 ( MASTER )    |    + — – trustauth.cn : 3312 – ( SLAVE + MASTER )        |        + — – trustauth.cn : 3313 – ( SLAVE + MASTER )            |            + — – trustauth.cn : 3311 < — > ( SLAVE )

使用–show-list选项除了图还会产生一个列列表。在这种情况下，可以指定如何显示列表，使用–format 选项指定，值可以是：

• grid (default)
• csv
• tab
• vertical

该工具使用SHOW SLAVE HOSTS语句，来确定主有哪些从。如果要想使用–recurse 选项，从在启动前需要指定 –report-host 和 –report-port 选项来设置它们的实际主机名和端口号或者该工具可能无法连接到从来确定它们的从。

选项

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

MySQL Utilities mysqlrplshow version 1.5.3 License type : GPLv2 Usage : mysqlrplshow — master = root @ trustauth.cn : 3306 mysqlrplshow – show slaves attached to a master Options :    — version             show program ‘s version number and exit   –help                display a help message and exit   –license             display program’ s license and exit    — master = MASTER       connection information for master server in the form :                          < user > [ : < password > ] @ < host > [ : < port > ] [ : < socket > ] or                          < login – path > [ : < port > ] [ : < socket > ] or < config –                          path > [ < [ group ] > ] .    – l , — show – list       print a list of the topology .    – f FORMAT , — format = FORMAT                          display the list in either grid ( default ) , tab , csv ,                          or vertical format    – r , — recurse         traverse the list of slaves to find additional                          master / slave connections . User this option to map a                          replication topology .    — max – depth = MAX_DEPTH                          limit the traversal to this depth . Valid only with the                          — recurse option . Valid values are non – negative                          integers .    – p , — prompt           prompt for slave user and password if different from                          master login .    – n NUM_RETRIES , — num – retries = NUM_RETRIES                          number of retries allowed for failed slave login                          attempt . Valid with — prompt only .    — 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    – q , — quiet           turn off all messages for quiet execution .    — 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 .

注意事项

登录用户需要有REPLICATE SLAVE 和 REPLICATE CLIENT 权限来确保可以成功执行该工具。同时，还需要有SHOW SLAVE STATUS, SHOW MASTER STATUS, SHOW SLAVE HOSTS的权限来执行该命令。

对于–format选项，值不区分大小写。也可以指定一个有效的唯一的前缀，否则会报错。

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

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

实例

显示本地3311主的从，如下所示：

1 2 3 4 5 6 7 8 9 10

shell > mysqlrplshow    — master = root @ trustauth.cn : 3311 — discover – slaves – login = root # master on trustauth.cn: … connected. # Finding slaves for master: trustauth.cn:3311 # Replication Topology Graph trustauth.cn : 3311 ( MASTER )    |    + — – trustauth.cn : 3310 – ( SLAVE )    |    + — – trustauth.cn : 3312 – ( SLAVE )

在上面的例子中，需要指定主的有效的登录信息。

为了显示额外的信息，如IO线程的状态，确认从是否真正连接到主，使用–verbose选项。如下所示：

1 2 3 4 5 6 7 8 9 10

shell > mysqlrplshow    — master = root @ trustauth.cn : 3311 — discover – slaves – login = root — verbose # master on trustauth.cn: … connected. # Finding slaves for master: trustauth.cn:3311 # Replication Topology Graph trustauth.cn : 3311 ( MASTER )    |    + — – trustauth.cn : 3310 [ IO : Yes , SQL : Yes ] – ( SLAVE )    |    + — – trustauth.cn : 3312 [ IO : Yes , SQL : Yes ] – ( SLAVE )

显示一个完整的主复制拓扑，如下所示：

1 2 3 4 5 6 7 8 9 10 11 12

shell > mysqlrplshow    — master = root @ trustauth.cn : 3311 — recurse — discover – slaves – login = root # master on trustauth.cn: … connected. # Finding slaves for master: trustauth.cn:3311 # Replication Topology Graph trustauth.cn : 3311 ( MASTER )    |    + — – trustauth.cn : 3310 – ( SLAVE )    |    + — – trustauth.cn : 3312 – ( SLAVE + MASTER )        |        + — – trustauth.cn : 3313 – ( 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 33 34 35 36 37 38 39

shell > mysqlrplshow — recurse — prompt — num – retries = 1 \            — master = root @ trustauth.cn : 3331 — discover – slaves – login = root Server trustauth.cn : 3331 is running on trustauth.cn . # master on trustauth.cn: … connected. # Finding slaves for master: trustauth.cn:3331 Server trustauth.cn : 3332 is running on trustauth.cn . # master on trustauth.cn: … FAILED. Connection to trustauth.cn : 3332 has failed . Please enter the following information to connect to this server . User name : root Password : # master on trustauth.cn: … connected. # Finding slaves for master: trustauth.cn:3332 Server trustauth.cn : 3333 is running on trustauth.cn . # master on trustauth.cn: … FAILED. Connection to trustauth.cn : 3333 has failed . Please enter the following information to connect to this server . User name : root Password : # master on trustauth.cn: … connected. # Finding slaves for master: trustauth.cn:3333 Server trustauth.cn : 3334 is running on trustauth.cn . # master on trustauth.cn: … FAILED. Connection to trustauth.cn : 3334 has failed . Please enter the following information to connect to this server . User name : root Password : # master on trustauth.cn: … connected. # Finding slaves for master: trustauth.cn:3334 # Replication Topology Graph trustauth.cn : 3331 ( MASTER )    |    + — – trustauth.cn : 3332 – ( SLAVE )    |    + — – trustauth.cn : 3333 – ( SLAVE + MASTER )        |        + — – trustauth.cn : 3334 – ( SLAVE )

权限

连接到主需要有REPLICATION SLAVE 权限。

用户指定了 –discover-slaves-login选项，用户登录到每个从需要有 REPLICATION CLIENT 权限。

文章转载来自：trustauth.cn