mysql中rpl_MySQL管理工具MySQL Utilities — mysqlrplcheck(44)

mysqlrplcheck  工具是用来检查复制的先决条件的。这些检查的设计或者说是测试，是用来确保复制的健康。测试的内容有：

在主上是否启用了二进制？

是否有排除某些二进制(如有*_do_db 或 *_ignore_db的设置)？如果有，显示它们。

在主上是否有复制用户以及权限是否正确？

SERVER_ID是否冲突？

从是否连接到主？如果没有显示主的主机和端口。

从上的master.info文件与主上的SHOW SLAVE STATUS显示的值是否冲突？

InnoDB配置是否兼容(插件还是内嵌的)？

存储引擎是否兼容(主从一样)？

lower_case_tables_names设置是否兼容？如果有设置大小写表名可能会导致问题产生警告。

从是否落后主？

该工具进行每个测试，如果发现任何一个发生错误将退出。连接服务器失败也会退出的。

每个测试的状态有：pass(满足先决条件)、fail(满足先决条件但是发生了一个或多个错误，或者例外)、warn(需要进一步研究配置，但是不是错误的。)

使用--verbose选项来获取额外的信息，如server_id、lower_case_table_name设置和从上面的主信息文件内容。

为了查看到SHOW SLAVE STATUS语句的值，可以使用 --show-slave-status 选项。

选项

MySQL Utilities mysqlrplcheck version 1.5.3

License type: GPLv2

Usage: mysqlrplcheck --master=root@localhost:3306 --slave=root@localhost:3310

mysqlrplcheck - check replication

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>[].

--slave=SLAVE connection information for slave server in the form:

[:]@[:][:] or

[:][:] or

path>[].

--master-info-file=MASTER_INFO

the name of the master information file on the slave.

Default = 'master.info' read from the data directory.

Note: this option requires that the utility run on the

slave with appropriate file read access to the data

directory.从上的master.info文件。要求在从上执行并有访问该文件的权限。

-s, --show-slave-status

show slave status。在主上显示SHOW SLAVE STATUS值。

--width=WIDTH display width。改变测试报告的显示宽度。默认是75个字符。

--suppress suppress warning messages。取消警告消息。

--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.

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

MySQLUtilitiesmysqlrplcheckversion1.5.3

Licensetype:GPLv2

Usage:mysqlrplcheck--master=root@localhost:3306--slave=root@localhost:3310

mysqlrplcheck-checkreplication

Options:

--versionshowprogram's version number and exit

--help                display a help message and exit

--license             display program'slicenseandexit

--master=MASTERconnectioninformationformasterserverintheform:

[:]@[:][:]or

[:][:]or

path>[].

--slave=SLAVEconnectioninformationforslaveserverintheform:

[:]@[:][:]or

[:][:]or

path>[].

--master-info-file=MASTER_INFO

thenameofthemasterinformationfileontheslave.

Default='master.info'readfromthedatadirectory.

Note:thisoptionrequiresthattheutilityrunonthe

slavewithappropriatefilereadaccesstothedata

directory.从上的master.info文件。要求在从上执行并有访问该文件的权限。

-s,--show-slave-status

showslavestatus。在主上显示SHOWSLAVESTATUS值。

--width=WIDTHdisplaywidth。改变测试报告的显示宽度。默认是75个字符。

--suppresssuppresswarningmessages。取消警告消息。

--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.

注意

用户必须要有SHOW SLAVE STATUS, SHOW MASTER STATUS, SHOW VARIABLES的执行权限。

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

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

实例

在设置主从复制之前，检测主从的先决条件，命令如下：

shell> mysqlrplcheck --master=root@host1:3310 --slave=root@host2:3311

# master on host1: ... connected.

# slave on host2: ... 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]

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]

# ...done.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

shell>mysqlrplcheck--master=root@host1:3310--slave=root@host2:3311

# master on host1: ... connected.

# slave on host2: ... connected.

TestDescriptionStatus

------------------------------------------------------------------------

Checkingforbinaryloggingonmaster[pass]

Aretherebinlogexceptions?[pass]

Replicationuserexists?[pass]

Checkingserver_idvalues[pass]

Isslaveconnectedtomaster?[pass]

Checkmasterinformationfile[pass]

CheckingInnoDBcompatibility[pass]

Checkingstorageenginescompatibility[pass]

Checkinglower_case_table_namessettings[pass]

Checkingslavedelay(secondsbehindmaster)[pass]

# ...done.

在这个例子中，必须要有登录主从的有效的登录信息。

执行相同的命令，并显示从上的主信息文件的内容和 SHOW SLAVE STATUS 值以及额外的详细信息，如下：

shell> mysqlrplcheck --master=root@host1:3310 --slave=root@host2:3311 \

--show-slave-status -vv

# master on host1: ... connected.

# slave on host2: ... 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]

master id = 10

slave id = 11

Is slave connected to master? [pass]

Check master information file [pass]

#

# Master information file:

#

Master_Log_File : clone-bin.000001

Read_Master_Log_Pos : 482

Master_Host : host1

Master_User : rpl

Master_Password : XXXX

Master_Port : 3310

Connect_Retry : 60

Master_SSL_Allowed : 0

Master_SSL_CA_File :

Master_SSL_CA_Path :

Master_SSL_Cert :

Master_SSL_Cipher :

Master_SSL_Key :

Master_SSL_Verify_Server_Cert : 0

Checking InnoDB compatibility [pass]

Checking storage engines compatibility [pass]

Checking lower_case_table_names settings [pass]

Master lower_case_table_names: 2

Slave lower_case_table_names: 2

Checking slave delay (seconds behind master) [pass]

#

# Slave status:

#

Slave_IO_State : Waiting for master to send event

Master_Host : host1

Master_User : rpl

Master_Port : 3310

Connect_Retry : 60

Master_Log_File : clone-bin.000001

Read_Master_Log_Pos : 482

Relay_Log_File : clone-relay-bin.000006

Relay_Log_Pos : 251

Relay_Master_Log_File : clone-bin.000001

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 : 482

Relay_Log_Space : 551

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 :

# ...done.

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

shell>mysqlrplcheck--master=root@host1:3310--slave=root@host2:3311\

--show-slave-status-vv

# master on host1: ... connected.

# slave on host2: ... connected.

TestDescriptionStatus

------------------------------------------------------------------------

Checkingforbinaryloggingonmaster[pass]

Aretherebinlogexceptions?[pass]

Replicationuserexists?[pass]

Checkingserver_idvalues[pass]

masterid=10

slaveid=11

Isslaveconnectedtomaster?[pass]

Checkmasterinformationfile[pass]

#

# Master information file:

#

Master_Log_File:clone-bin.000001

Read_Master_Log_Pos:482

Master_Host:host1

Master_User:rpl

Master_Password:XXXX

Master_Port:3310

Connect_Retry:60

Master_SSL_Allowed:0

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Master_SSL_Verify_Server_Cert:0

CheckingInnoDBcompatibility[pass]

Checkingstorageenginescompatibility[pass]

Checkinglower_case_table_namessettings[pass]

Masterlower_case_table_names:2

Slavelower_case_table_names:2

Checkingslavedelay(secondsbehindmaster)[pass]

#

# Slave status:

#

Slave_IO_State:Waitingformastertosendevent

Master_Host:host1

Master_User:rpl

Master_Port:3310

Connect_Retry:60

Master_Log_File:clone-bin.000001

Read_Master_Log_Pos:482

Relay_Log_File:clone-relay-bin.000006

Relay_Log_Pos:251

Relay_Master_Log_File:clone-bin.000001

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:482

Relay_Log_Space:551

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:

# ...done.

权限

在主上需要对mysql数据库具有SELECT 和 INSERT权限，同时还要有REPLICATION SLAVE, REPLICATION CLIENT 和 GRANT OPTION权限。

在从上需要有SUPER 权限。

此外，当使用GTIDs时，从用户还必需要有对mysql数据库的SELECT 权限。