display show mysql_MySQL管理工具MySQL Utilities — mysqlrplshow(45)

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

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

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

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

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

# Replication Topology Graph::

localhost:3311 (MASTER)

|

+--- localhost:3310 - (SLAVE)

|

+--- localhost:3312 - (SLAVE + MASTER)

|

+--- localhost:3313 - (SLAVE)

1

2

3

4

5

6

7

8

# Replication Topology Graph::

localhost:3311(MASTER)

|

+---localhost:3310-(SLAVE)

|

+---localhost:3312-(SLAVE+MASTER)

|

+---localhost:3313-(SLAVE)

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

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

# Replication Topology Graph

localhost:3311 (MASTER)

|

+--- localhost:3312 - (SLAVE + MASTER)

|

+--- localhost:3313 - (SLAVE + MASTER)

|

+--- localhost:3311 (SLAVE)

1

2

3

4

5

6

7

8

# Replication Topology Graph

localhost:3311(MASTER)

|

+---localhost:3312-(SLAVE+MASTER)

|

+---localhost:3313-(SLAVE+MASTER)

|

+---localhost:3311(SLAVE)

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

grid (default)

csv

tab

vertical

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

选项

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:

[:]@[:][:] or

[:][:] or

path>[].

-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

[:] or . For example,

--discover-slaves-login=joe:secret will use 'joe' as

the user and 'secret' as the password for each

discovered 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

40

41

42

43

44

45

46

47

MySQLUtilitiesmysqlrplshowversion1.5.3

Licensetype:GPLv2

Usage:mysqlrplshow--master=root@localhost:3306

mysqlrplshow-showslavesattachedtoamaster

Options:

--versionshowprogram's version number and exit

--help                display a help message and exit

--license             display program'slicenseandexit

--master=MASTERconnectioninformationformasterserverintheform:

[:]@[:][:]or

[:][:]or

path>[].

-l,--show-listprintalistofthetopology.

-fFORMAT,--format=FORMAT

displaythelistineithergrid(default),tab,csv,

orverticalformat

-r,--recursetraversethelistofslavestofindadditional

master/slaveconnections.Userthisoptiontomapa

replicationtopology.

--max-depth=MAX_DEPTH

limitthetraversaltothisdepth.Validonlywiththe

--recurseoption.Validvaluesarenon-negative

integers.

-p,--promptpromptforslaveuserandpasswordifdifferentfrom

masterlogin.

-nNUM_RETRIES,--num-retries=NUM_RETRIES

numberofretriesallowedforfailedslavelogin

attempt.Validwith--promptonly.

--ssl-ca=SSL_CAThepathtoafilethatcontainsalistoftrustedSSL

CAs.

--ssl-cert=SSL_CERTThenameoftheSSLcertificatefiletousefor

establishingasecureconnection.

--ssl-key=SSL_KEYThenameoftheSSLkeyfiletouseforestablishinga

secureconnection.

-v,--verbosecontrolhowmuchinformationisdisplayed.e.g.,-v=

verbose,-vv=moreverbose,-vvv=debug

-q,--quietturnoffallmessagesforquietexecution.

--discover-slaves-login=DISCOVER

atstartup,querymasterforallregisteredslavesand

usetheusernameandpasswordspecifiedtoconnect.

Supplytheuserandpasswordintheform

[:]or.Forexample,

--discover-slaves-login=joe:secretwilluse'joe'as

theuserand'secret'asthepasswordforeach

discoveredslave.

注意事项

登录用户需要有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主的从，如下所示：

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)

1

2

3

4

5

6

7

8

9

10

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)

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

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

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)

1

2

3

4

5

6

7

8

9

10

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)

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

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)

1

2

3

4

5

6

7

8

9

10

11

12

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)

显示一个完整的主复制拓扑，提示从的用户名和密码与主的用户名和密码凭证不一样。如下所示：

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)

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@localhost:3331--discover-slaves-login=root

Serverlocalhost:3331isrunningonlocalhost.

# master on localhost: ... connected.

# Finding slaves for master: localhost:3331

Serverlocalhost:3332isrunningonlocalhost.

# master on localhost: ... FAILED.

Connectiontolocalhost:3332hasfailed.

Pleaseenterthefollowinginformationtoconnecttothisserver.

Username:root

Password:

# master on localhost: ... connected.

# Finding slaves for master: localhost:3332

Serverlocalhost:3333isrunningonlocalhost.

# master on localhost: ... FAILED.

Connectiontolocalhost:3333hasfailed.

Pleaseenterthefollowinginformationtoconnecttothisserver.

Username:root

Password:

# master on localhost: ... connected.

# Finding slaves for master: localhost:3333

Serverlocalhost:3334isrunningonlocalhost.

# master on localhost: ... FAILED.

Connectiontolocalhost:3334hasfailed.

Pleaseenterthefollowinginformationtoconnecttothisserver.

Username: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 权限。

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