主页 / server / 问题 / 834232
Accepted
Jim Rubenstein
Asked: 2017-02-23 08:17:30 +0800 CST

MySQL Slave 卡在单个 bin 日志 + bin 日志位置 17 小时以上

  • 772

tl;dr：复制在特定的二进制日志和位置上停止，我不知道为什么

我有一个 MySQL 5.5 的 MySQL 复制设置。

这种复制设置没有落后的历史，并且一直很稳定。

今天早上，我注意到奴隶比主人晚了 17 个小时。

做更多的研究，它看起来是 SQL_Thread 的一个问题。

当前的主日志文件，根据从属（通过SLAVE STATUS），是mysql-bin.001306@position 20520499。这与MASTER STATUSmaster 的输出一致。

但是，SLAVE STATUS表明Relay_Master_Log_File是当前mysql-bin.001302具有Exec_Master_Log_Pos的36573336。今天早上我一直在监视它们时，Relay_Master_Log_Filenor根本没有进步。Exec_Master_Log_Pos

查看 master 上的 binlogs，这是位于以下位置的语句mysql-bin.001302@3657336：

# at 36573053
#170221 14:33:48 server id 1  end_log_pos 36573130      Query   thread_id=96205677      exec_time=0     error_code=0
SET TIMESTAMP=1487716428/*!*/;
BEGIN
/*!*/;
# at 36573130
# at 36573213
#170221 14:33:48 server id 1  end_log_pos 36573213      Table_map: `database-name`.`table-name` mapped to number 5873
#170221 14:33:48 server id 1  end_log_pos 36573309      Write_rows: table id 5873 flags: STMT_END_F
### INSERT INTO `database-name`.`table-name`
### SET
###   @1='xxxxxxxx'
###   @2=6920826
###   @3='xxxxxxxx'
###   @4='GET'
###   @5='address'
###   @6=2017-02-21 14:40:24
###   @7=2017-02-21 14:40:24
# at 36573309
#170221 14:33:48 server id 1  end_log_pos 36573336      Xid = 1668637037
COMMIT/*!*/;
# at 36573336

大约在昨天的这个时候，我确实执行了一些大型查询以将数据迁移到新表。这个过程看起来有点像这样；

mysql> insert into tmp_table ( select <rows> from origin table ); -- 44 million rows
mysql> insert into dest_table ( select * from tmp_table ); -- 44 million rows

有问题的两个表上没有主键或唯一键，我读过这可能是个问题。然而，虽然上面 binlog 条目中显示的数据库 + 表是此处的目标表——显示的插入记录不是在迁移期间生成的。

如果你已经走到这一步，你应该得到互联网积分。

在这一点上，我不确定还需要考虑什么，或者在哪里寻找日志停止的原因。任何见解都值得赞赏。

谢谢。

作为参考，这是截至本文发布时的MASTER STATUS和输出：SLAVE STATUS

主状态

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.001306 | 20520499 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

从属状态

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-host
                  Master_User: replication-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001306
          Read_Master_Log_Pos: 20520499
               Relay_Log_File: relay-bin.002601
                Relay_Log_Pos: 36573482
        Relay_Master_Log_File: mysql-bin.001302
             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: 36573336
              Relay_Log_Space: 3565987462
              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: 63435
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: 1
1 row in set (0.00 sec)
mysql mysql-replication mysql5.5

1 个回答

  1. Best Answer

    从昨天开始的大量查询交易中，我走在了正确的轨道上。

    迁移数据后，我在原始表上执行了一条 DELETE 语句，以删除我已迁移走的行。

    这些表只是充满了跟踪数据，因此它们上没有任何主键或唯一键。

    由于基于 ROW 的复制是如何工作的，slave 不会执行在 master 上执行的相同 DELETE 语句，而是为每一行执行一个 DELETE 语句，最终看起来像这样：

    DELETE FROM table WHERE colA=foo AND colB=bar AND colC=baz....etc

    而且，由于没有与该查询匹配的索引，因此单线程复制 SQL 线程执行了 4000 万+条删除语句（或...试图执行），由于必须进行所有扫描，这需要很长时间才能运行完成以识别每一行（当时该表的大小约为 8000 万行）。

    最后，我通过停止从属线程 ( STOP SLAVE) 跳过单个从属事务 ( SET GLOBAL sql_slave_skip_counter = 1;) 并重新启动从属线程 ( START SLAVE) 来处理这个问题。

    这导致我的 Master 和 Slave 在这里有问题的表上不同步 - 但我能够利用基于行的复制的性质通过在 Master 上执行以下操作使其恢复同步：

    mysql> CREATE TABLE table_tmp; -- with the same schema as 'table' (SHOW CREATE TABLE table;)
mysql> RENAME TABLE table TO table_bak, table_tmp TO table;
mysql> INSERT INTO table ( SELECT * FROM table_bak );
mysql> DROP TABLE table_bak;

    由于DELETE是在Master上执行的，所以这里的INSERT只插入了我想保留的记录（删除的已经没有了）。而且，由于基于行的复制单独插入每一行而不是执行相同的 INSERT INTO...SELECT 语句，所以从表只填充了所需的数据。然后，随后的 DROP TABLE 语句删除从属上的表，而不必单独处理每一行。

    这里需要注意的是，因为表的主版本仍然是 30-40 百万行......插入和后续复制最终会锁定你的从属一段时间（重复上面的问题），但它的停顿时间要短得多（最终大约是 20 分钟）由于 mysql 不必扫描数据库以删除要删除的行。

    我希望这对将来的某人有所帮助。对不起，它很啰嗦，希望它提供信息和帮助。

    • 8

相关问题