MySQL 主从 AUTO_INCREMENT 不一致问题分析

CREATE TABLE `test_autoincrement` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQL [test2023]> insert into test_autoincrement(name,uid) select '张三',1001;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
MySQL [test2023]> insert into test_autoincrement(name,uid) select '李四',1002;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
MySQL [test2023]>
MySQL [test2023]> insert into test_autoincrement(name,uid) select '王五',1003;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL [test2023]> REPLACE INTO test_autoincrement (name,uid) values('张三丰',1001);
Query OK, 2 rows affected (0.01 sec)

MySQL [test2023]> stop slave;
Query OK, 0 rows affected (0.08 sec)

MySQL [test2023]> show master status\G
*************************** 1. row ***************************
File: binlog.000002
Position: 4317
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 9cc90407-ff89-11ed-8b7a-fa163e2d11e1:1-82,
a0c1d6ff-5764-11ee-94ea-fa163e2d11e1:1-11
1 row in set (0.01 sec)

MySQL [test2023]> CHANGE MASTER TO MASTER_HOST = '原slave节点的IP地址', MASTER_USER = '复制账户', MASTER_PASSWORD = '密码', MASTER_PORT = 端口, MASTER_AUTO_POSITION = 1 ;
Query OK, 0 rows affected, 2 warnings (0.21 sec)
MySQL [test2023]> start slave;
Query OK, 0 rows affected (0.05 sec)
MySQL [test2023]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XXX
Master_User: XXX
Master_Port: XXX
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 4317
Relay_Log_File: relay.000004
Relay_Log_Pos: 445
Relay_Master_Log_File: binlog.000002
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: 4317
Relay_Log_Space: 726
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: 461470011
Master_UUID: a0c1d6ff-5764-11ee-94ea-fa163e2d11e1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
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: a0c1d6ff-5764-11ee-94ea-fa163e2d11e1:11
Executed_Gtid_Set: 9cc90407-ff89-11ed-8b7a-fa163e2d11e1:1-82,
a0c1d6ff-5764-11ee-94ea-fa163e2d11e1:1-11
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

at 10790
#230927 16:23:45 server id 46147000  end_log_pos 10863 CRC32 0x85c60fb7         Update_rows: table id 122 flags: STMT_END_F
BINLOG '
keYTZRO4JcACRQAAACYqAAAAAHoAAAAAAAEACHRlc3QyMDIzABJ0ZXN0X2F1dG9pbmNyZW1lbnQA
AwMPAwKQAQCCO6qB
keYTZR+4JcACSQAAAG8qAAAAAHoAAAAAAAEAAgAD///4AQAAAAYA5byg5LiJ6QMAAPgEAAAACQDl
vKDkuInkuLDpAwAAtw/GhQ==
'/!/;
UPDATE test2023.test_autoincrement
WHERE
@1=1 /* INT meta=0 nullable=0 is_null=0 */
@2='张三' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
@3=1001 /* INT meta=0 nullable=0 is_null=0 */
SET
@1=4 /* INT meta=0 nullable=0 is_null=0 */
@2='张三丰' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
@3=1001 /* INT meta=0 nullable=0 is_null=0 */
at 10863
#230927 16:23:45 server id 46147000  end_log_pos 10894 CRC32 0xe204d99b         Xid = 331
COMMIT/!/;

sql_insert.cc:
...
/* Check if there is more uniq keys after field */
static int last_uniq_key(TABLE table,uint keynr)
{
/
The information about the last key conflict will be used to
do a replace of the new row on the conflicting row, rather
than doing a delete (of old row) + insert (of new row).

Hence check for this flag and disable replacing the last row
by returning 0 always. Returning 0 will result in doing
a delete + insert always.*/
if (table->file->ha_table_flags() & HA_DUPLICATE_KEY_NOT_IN_ORDER){
return 0;
}
while (++keynr < table->s->keys){
if (table->key_info[keynr].flags & HA_NOSAME){
return 0;
}
}
return 1;
}
...
/*
  The manual defines the REPLACE semantics that it is either
  an INSERT or DELETE(s) + INSERT; FOREIGN KEY checks in
  InnoDB do not function in the defined way if we allow MySQL
  to convert the latter operation internally to an UPDATE.
      We also should not perform this conversion if we have
      timestamp field with ON UPDATE which is different from DEFAULT.
      Another case when conversion should not be performed is when
      we have ON DELETE trigger on table so user may notice that
      we cheat here. Note that it is ok to do such conversion for
      tables which have ON UPDATE but have no ON DELETE triggers,
      we just should not expose this fact to users by invoking
      ON UPDATE triggers.
*/
if (last_uniq_key(table,key_nr) &&
    !table->file->referenced_by_foreign_key() &&
        (!table->triggers || !table->triggers->has_delete_triggers()))
    {
      if ((error=table->file->ha_update_row(table->record[1],
                        table->record[0])) &&
          error != HA_ERR_RECORD_IS_THE_SAME)
        goto err;
      if (error != HA_ERR_RECORD_IS_THE_SAME)
        info->stats.deleted++;
      else
        error= 0;
      thd->record_first_successful_insert_id_in_cur_stmt(table->file->insert_id_for_cur_row);
      /*
        Since we pretend that we have done insert we should call
        its after triggers.
      */
      goto after_trg_n_copied_inc;
    }
    else
    {...
}
...

The manual defines the REPLACE semantics that it is either an INSERT or DELETE(s) + INSERT;

CREATE TABLE `test_autoincrement_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

insert into test_autoincrement_2(name) select '孙七';
insert into test_autoincrement_2(name) select '周八';
insert into test_autoincrement_2(name) select '吴九';
#此时主从表结构是一致的，如下：
CREATE TABLE test_autoincrement_2 (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
name varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

MySQL [test2023]> REPLACE INTO test_autoincrement_2 (id,name) values(3,'郑十');
Query OK, 2 rows affected (0.08 sec)

CREATE TABLE `test_autoincrement_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

at 8089
#230928 15:52:08 server id 461470011  end_log_pos 8151 CRC32 0xc2ff85bb         Update_rows: table id 481 flags: STMT_END_F
BINLOG '
qDAVZRM7eYEbRgAAAJkfAAAAAOEBAAAAAAEACHRlc3QyMDIzABR0ZXN0X2F1dG9pbmNyZW1lbnRf
MgACAw8CkAEAFSqQxg==
qDAVZR87eYEbPgAAANcfAAAAAOEBAAAAAAEAAgAC///8AwAAAAYA5ZC05Lmd/AMAAAAGAOmDkeWN
gbuF/8I=
'/!/;
UPDATE test2023.test_autoincrement_2
WHERE
@1=3 /* INT meta=0 nullable=0 is_null=0 */
@2='吴九' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
SET
@1=3 /* INT meta=0 nullable=0 is_null=0 */
@2='郑十' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
at 8151
#230928 15:52:08 server id 461470011  end_log_pos 8182 CRC32 0xaa39d2a4         Xid = 699
COMMIT/!/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog / /!*/;
DELIMITER ;
End of log file
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;

select TABLE_SCHEMA,TABLE_NAME,AUTO_INCREMENT from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys') AUTO_INCREMENTis not null \G

lock tables table_name write;

MAXID=select max(id) from table_name;
AUTO_INCREMENT=select AUTO_INCREMENT from information_schema.tables where TABLE_NAME='t1' ;