MySql 复制失败

Question

我有一个 mysql(版本 14.14 Distrib 5.7.29，适用于 Linux (x86_64))主 -> 辅助复制设置。最初，复制工作正常，直到我们必须重新启动主数据库。从那时起，复制就失败了。我们曾多次尝试重新启用复制，但几分钟后复制再次失败。

my.cnf 文件 -> 主要

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
log-bin=mysql-bin
server-id=1
datadir=/mysqldb/datadir/mysql
socket=/mysqldb/datadir/mysql/mysql.sock
log-error=/var/log/mysql/mysql_error.log
log-syslog=ON
max_connections=1000

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/mysqldb/datadir/mysql/mysql.sock

my.cnf 文件 -> 辅助

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
server-id=2
datadir=/mysqldb/datadir/mysql
socket=/mysqldb/datadir/mysql/mysql.sock
log-error=/var/log/mysql/mysql_error.log
log-syslog=ON
max_connections=1000

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/mysqldb/datadir/mysql/mysql.sock

显示状态从属\G -> 次要

Slave_IO_State: Waiting for master to send event
                  Master_Host: <ip-address>
                  Master_User: <username>
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 336473455
               Relay_Log_File: avt-mysql-sc-prod-2-relay-bin.000002
                Relay_Log_Pos: 187164
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1032
                   Last_Error: Could not execute Delete_rows event on table store_metadata.access_token; Can't find record in 'access_token', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 336136737
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 336136333
              Relay_Log_Space: 524507
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Delete_rows event on table store_metadata.access_token; Can't find record in 'access_token', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 336136737
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 7c367a1b-de48-11e9-a49d-42010a164351
             Master_Info_File: /mysqldb/datadir/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 200122 17:37:02
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

然后我们尝试通过以下方式再次重新启用复制:

stop slave;

CHANGE MASTER TO
       MASTER_HOST='<ip-address>',
       MASTER_USER='<username>',
       MASTER_PASSWORD='<password>',
       MASTER_LOG_FILE='mysql-bin.000003',
       MASTER_LOG_POS=336562088;

start slave;

哪里

MASTER_LOG_POS=;

来自

show master status\G
File: mysql-bin.000003
         Position: 336562088

几分钟后复制失败error_log -> 辅助

2020-01-22T17:32:45.562685Z 1524 [Note] Slave I/O thread for channel '': connected to master 'replica1@<ip-address>:3306',replication started in log 'mysql-bin.000003' at position 335949489
2020-01-22T17:37:02.294105Z 1525 [ERROR] Slave SQL for channel '': Could not execute Delete_rows event on table store_metadata.access_token; Can't find record in 'access_token', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 336136737, Error_code: 1032
2020-01-22T17:37:02.294180Z 1525 [Warning] Slave: Can't find record in 'access_token' Error_code: 1032
2020-01-22T17:37:02.294193Z 1525 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000003' position 336136333.

我还注意到主数据库上出现以下连接错误。然而，一旦复制失败，就会发生这种情况。或者可能是连接终止而导致复制失败。

mysqld.log -> 主要

2020-01-22T21:10:13.065441Z 697 [Note] Start binlog_dump to master_thread_id(697) slave_server(2), pos(mysql-bin.000003, 347432570)
2020-01-22T21:53:31.113366Z 678 [Note] Aborted connection 678 to db: 'store_metadata' user: 'avtadmin' host: '<hostname>-f6297368e00118f33f8e74e464.interna' (Got an error reading communication packets)

其他测试信息

在从属上

stop slave;
drop database;
create database;

关于大师

flush tables with read lock;
show master status\G
mysqldump -h <ip-address> --single-transaction --max_allowed_packet=1024m --databases store_metadata> /tmp/mysqlsnapshot-replication.sql
unlock tables;

在从属上

mysql -h<ip-address> -u <username> -p  store_metadata < "/tmp/mysqlsnapshot-replication.sql"
show databases;
use store_metadata;
show tables;

启用复制

CHANGE MASTER TO
       MASTER_HOST=‘<ip-address>’,
       MASTER_USER=‘<username>’,
       MASTER_PASSWORD=‘<password>’,
       MASTER_LOG_FILE='mysql-bin.000003',
       MASTER_LOG_POS=364656552;

start slave;
show slave status\G
tail -f /var/log/mysqld.log

关于大师

show master status\G
tail -f /var/log/mysqld.log

最初一切正常。然后，我添加了一个新数据库来主控，但该数据库从未被复制到辅助数据库。然后，我删除了主数据库上的新数据库，这再次破坏了复制，并出现新数据库在辅助数据库上不存在的错误。对此有什么想法吗？我应该重新启动主数据库以便创建新的二进制日志吗？

谢谢。

Answer 1

基本上，这意味着您的从属设备现在与主设备已经过时，并且在从属设备完全同步之前，它将一直缺少数据。您可能必须从头开始启动一个新的从属设备，并使其与主设备的当前状态同步。

这个问题之前已经在这里回答过:

https://forums.mysql.com/read.php?26,367180,367785#msg-367785