MySQL实现keep函数 mysqlrplcheck

转载

mob64ca13fd163c 2023-12-28 11:24:15

文章标签 MySQL实现keep函数 mysql rpl SSL mysql SQL 文章分类 MySQL 数据库

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.

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.

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.

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。