There are two hosts with replication (master-slave), which at some point was broken. Are there ways to detect the difference in the states of the two databases (I’ll say right away - I understand that replication lag will also play its role in this difference), except for the line-by-line comparison of two dumps?

    1 answer 1

    Yes, there is such a possibility, for this, the following command should be executed on the slave server

    SHOW SLAVE STATUS

    First of all, you need to pay attention to the Slave_IO_Running and Slave_SQL_Running , which reflect the status of the delivery and execution of binlog on the replica, respectively. If replication is done normally, both of them are in Yes status. If one or both of these columns has the No status, replication is broken.

    In order to understand how much the replica is lagging behind the master, you should compare the current positions in the binary logs on the master and on the slave server. On the wizard, you can use the command

     SHOW MASTER STATUS +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 73 | test | manual,mysql | +------------------+----------+--------------+------------------+

    The File field returns the current binary log file, the Position field returns the current position. You can determine the current position on the slave server using the above command

     SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 73 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 73 Relay_Log_Space: 552 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: 8

    The Master_Log_File and Read_Master_Log_Pos contain the name of the binary log and the position in it on the master server, which the binlog delivery stream managed to deliver to the replica. Binary logs are rotated, as a rule, by adding an extension with a log number, for example

     mysql-bin.000001 mysql-bin.000002 ... mysql-bin.000134

    Thus, by comparing the file name in the File and Master_Log_File , as well as the position in the Position and Read_Master_Log_Pos you can understand how far the binlog of the slave is behind the master. It should be borne in mind that these fields describe only the delivery of binary logs, they still need to be performed. The field Exec_Master_Log_Pos contains the position in the binary logs that the thread of execution of binlog managed to perform. If the difference between Read_Master_Log_Pos and Exec_Master_Log_Pos is large, then the logs from the master have already been delivered to the replica, but the slave has not yet had time to apply them to the data.