mysqlrplcheck 工具是用来检查复制是否正常,是用来确保复制的健康。
1.检查从库的状态及同步状态。
[root@mysql1 test]# mysqlrplcheck --master=root:rootroot@192.168.1.51:3306 --slave=root:rootroot@192.168.1.58:3306 -s
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.1.51: ... connected.
# slave on 192.168.1.58: ... 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]
Checking server_uuid values [pass]
Is slave connected to master? [pass]
Check master information file [WARN]
Cannot read master information file from a remote machine.
Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]
Checking slave delay (seconds behind master) [pass]
#
# Slave status:
#
Slave_IO_State : Waiting for master to send event
Master_Host : 192.168.1.51
Master_User : repl
Master_Port : 3306
Connect_Retry : 60
Master_Log_File : mysql-binlog.000088
Read_Master_Log_Pos : 1007
Relay_Log_File : mysql2-relay-bin.000003
Relay_Log_Pos : 567
Relay_Master_Log_File : mysql-binlog.000088
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 : 1007
Relay_Log_Space : 1247
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 :
Replicate_Ignore_Server_Ids :
Master_Server_Id : 1311
Master_UUID : daf4393d-22e8-11ed-a240-000c296a9b44
Master_Info_File : /mysql/mysql3306/master.info
SQL_Delay : 0
SQL_Remaining_Delay : None
Slave_SQL_Running_State : Slave has read all relay log; waiting for more updates
Master_Retry_Count : 86400
Master_Bind :
Last_IO_Error_Timestamp :
Last_SQL_Error_Timestamp :
Master_SSL_Crl :
Master_SSL_Crlpath :
Retrieved_Gtid_Set : daf4393d-22e8-11ed-a240-000c296a9b44:261-262
Executed_Gtid_Set : 617c3539-ae6a-11ed-af03-000c291fca1c:1-7,
daf4393d-22e8-11ed-a240-000c296a9b44:198-223:261-262
Auto_Position : 0
Replicate_Rewrite_DB :
Channel_Name :
Master_TLS_Version :
# ...done.
2.检查SERVER_ID,UUID,lower_case_table_names及从库信息。
[root@mysql1 mysql]# mysqlrplcheck --master=root:rootroot@192.168.1.51:3306 --slave=root:rootroot@192.168.1.58:3306 --show-slave-status -vv
WARNING: Using a password on the command line interface can be insecure.
# master on 192.168.1.51: ... connected.
# slave on 192.168.1.58: ... 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 = 1311
slave id = 13158
Checking server_uuid values [pass]
master uuid = daf4393d-22e8-11ed-a240-000c296a9b44
slave uuid = 617c3539-ae6a-11ed-af03-000c291fca1c
Is slave connected to master? [pass]
Check master information file [WARN]
Cannot read master information file from a remote machine.
Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]
Master lower_case_table_names: 0
Slave lower_case_table_names: 0
Checking slave delay (seconds behind master) [pass]
#
# Slave status:
#
Slave_IO_State : Waiting for master to send event
Master_Host : 192.168.1.51
Master_User : repl
Master_Port : 3306
Connect_Retry : 60
Master_Log_File : mysql-binlog.000088
Read_Master_Log_Pos : 1007
Relay_Log_File : mysql2-relay-bin.000003
Relay_Log_Pos : 567
Relay_Master_Log_File : mysql-binlog.000088
Slave_IO_Running : Yes
Slave_SQL_Running : Yes
Last_Errno : 0
Last_Error :
Skip_Counter : 0
Exec_Master_Log_Pos : 1007
Relay_Log_Space : 1247
Until_Condition : None
Until_Log_File :
Until_Log_Pos : 0
Master_SSL_Allowed : No
Seconds_Behind_Master : 0
Master_SSL_Verify_Server_Cert : No
Last_IO_Errno : 0
Last_IO_Error :
Last_SQL_Errno : 0
Last_SQL_Error :
Replicate_Ignore_Server_Ids :
Master_Server_Id : 1311
Master_UUID : daf4393d-22e8-11ed-a240-000c296a9b44
Master_Info_File : /mysql/mysql3306/master.info
SQL_Delay : 0
SQL_Remaining_Delay : None
Slave_SQL_Running_State : Slave has read all relay log; waiting for more updates
Master_Retry_Count : 86400
Retrieved_Gtid_Set : daf4393d-22e8-11ed-a240-000c296a9b44:261-262
Executed_Gtid_Set : 617c3539-ae6a-11ed-af03-000c291fca1c:1-7,
daf4393d-22e8-11ed-a240-000c296a9b44:198-223:261-262
Auto_Position : 0
Replicate_Rewrite_DB :
Channel_Name :
Master_TLS_Version :
# ...done.
3.总结。
mysqlrplcheck检查工具,可以查看主从的server_id,uuid,lower_case_table_names的值及主从同步情况。相对来说非常实用。