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

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

必需指定--discover-slaves-login选项来提供用户名和密码以发现拓扑结构中的任何从。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

使用 --recurse 选项，递归搜索从。这将导致工具连接到发现的每个从并尝试确定是否还有任何其他的从。如果发现从，这个过程将一直持续到从的主（圆形拓扑）。以连续缩进的图显示拓扑结构。 <--> 符号表示圆形拓扑。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

如果使用--recurse选项，该工具将使用主提供的用户名和密码尝试连接从。默认情况下，如果连接尝试失败，抛出一个错误并停止。为了改变这种行为，可以是用 --prompt 选项，来提示连接失败的用户名和密码。也可以是用 --num-retries=n 选项来指定重新尝试的次数。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

下面是一个典型的从中继拓扑：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

# Replication Topology Graph::
localhost:3311 (MASTER)
   |
   +--- localhost:3310 - (SLAVE)
   |
   +--- localhost:3312 - (SLAVE + MASTER)
       |
       +--- localhost:3313 - (SLAVE)

MASTER, SLAVE, SLAVE+MASTER 分别表明仅仅是主，仅仅是从，既是从也是主。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

下面是一个圆形的复制拓扑。其中<--> 符合表示圆：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

# Replication Topology Graph
localhost:3311 (MASTER)
   |
   +--- localhost:3312 - (SLAVE + MASTER)
       |
       +--- localhost:3313 - (SLAVE + MASTER)
           |
           +--- localhost:3311 <--> (SLAVE)

使用--show-list选项除了图还会产生一个列列表。在这种情况下，可以指定如何显示列表，使用--format 选项指定，值可以是：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

• grid (default)
• csv
• tab
• vertical

该工具使用SHOW SLAVE HOSTS语句，来确定主有哪些从。如果要想使用--recurse 选项，从在启动前需要指定 --report-host 和 --report-port 选项来设置它们的实际主机名和端口号或者该工具可能无法连接到从来确定它们的从。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

选项

MySQL Utilities mysqlrplshow version 1.5.3 
License type: GPLv2
Usage: mysqlrplshow --master=root@localhost: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的权限来执行该命令。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

对于--format选项，值不区分大小写。也可以指定一个有效的唯一的前缀，否则会报错。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

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

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

实例

显示本地3311主的从，如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

shell> mysqlrplshow  --master=root@localhost:3311 --discover-slaves-login=root
# master on localhost: ... connected.
# Finding slaves for master: localhost:3311

# Replication Topology Graph
localhost:3311 (MASTER)
   |
   +--- localhost:3310 - (SLAVE)
   |
   +--- localhost:3312 - (SLAVE)

在上面的例子中，需要指定主的有效的登录信息。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

为了显示额外的信息，如IO线程的状态，确认从是否真正连接到主，使用--verbose选项。如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

shell> mysqlrplshow  --master=root@localhost:3311 --discover-slaves-login=root --verbose
# master on localhost: ... connected.
# Finding slaves for master: localhost:3311

# Replication Topology Graph
localhost:3311 (MASTER)
   |
   +--- localhost:3310 [IO: Yes, SQL: Yes] - (SLAVE)
   |
   +--- localhost:3312 [IO: Yes, SQL: Yes] - (SLAVE)

显示一个完整的主复制拓扑，如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

shell> mysqlrplshow  --master=root@localhost:3311 --recurse --discover-slaves-login=root
# master on localhost: ... connected.
# Finding slaves for master: localhost:3311

# Replication Topology Graph
localhost:3311 (MASTER)
   |
   +--- localhost:3310 - (SLAVE)
   |
   +--- localhost:3312 - (SLAVE + MASTER)
       |
       +--- localhost:3313 - (SLAVE)

显示一个完整的主复制拓扑，提示从的用户名和密码与主的用户名和密码凭证不一样。如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

shell> mysqlrplshow --recurse --prompt --num-retries=1 \
          --master=root@localhost:3331 --discover-slaves-login=root

Server localhost:3331 is running on localhost.
# master on localhost: ... connected.
# Finding slaves for master: localhost:3331
Server localhost:3332 is running on localhost.
# master on localhost: ... FAILED.
Connection to localhost:3332 has failed.
Please enter the following information to connect to this server.
User name: root
Password:
# master on localhost: ... connected.
# Finding slaves for master: localhost:3332
Server localhost:3333 is running on localhost.
# master on localhost: ... FAILED.
Connection to localhost:3333 has failed.
Please enter the following information to connect to this server.
User name: root
Password:
# master on localhost: ... connected.
# Finding slaves for master: localhost:3333
Server localhost:3334 is running on localhost.
# master on localhost: ... FAILED.
Connection to localhost:3334 has failed.
Please enter the following information to connect to this server.
User name: root
Password:
# master on localhost: ... connected.
# Finding slaves for master: localhost:3334

# Replication Topology Graph
localhost:3331 (MASTER)
   |
   +--- localhost:3332 - (SLAVE)
   |
   +--- localhost:3333 - (SLAVE + MASTER)
       |
       +--- localhost:3334 - (SLAVE)

权限

连接到主需要有REPLICATION SLAVE 权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/

用户指定了 --discover-slaves-login选项，用户登录到每个从需要有 REPLICATION CLIENT 权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqlrplshow/