mysql组复制主id不连续?

hec6srdp  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(376)

我在3台服务器上安装了mysql 5.7.23并启动了组复制。我将记录插入其中一个,但id不是连续的。
需要配置吗?这是node1的当前conf文件:

[mysqld]
basedir=/usr/local/services/mysql-5.7.23
datadir=/data/mysql-5.7.23
user=user_00
explicit_defaults_for_timestamp=true
character-set-server=utf8

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
relay-log=zhiyun_notifer_svr123-relay-bin
binlog_format= ROW

transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = "node1:13306"
loose-group_replication_group_seeds ="node1:13306,node2:13306,node3:13306"
loose-group_replication_ip_whitelist="zhiyun_notifer_svr123,zhiyun_notifer_svr221,zhiyun_notifer_svr212"
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=true

log_error_verbosity=2
log_error=/data/log/mysql-5.7.23/error.log

slow_query_log=ON
slow_query_log_file=/data/log/mysql-5.7.23/slow.log
long_query_time=1
log_queries_not_using_indexes=ON
log_throttle_queries_not_using_indexes=1
log_slow_admin_statements=ON   

[client]
default-character-set=utf8

使用此sql创建测试表,并在其中插入不带设置id的记录:

CREATE TABLE IF NOT EXISTS `test_ids` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `task_id` VARCHAR(64) NOT NULL DEFAULT '',
  `caller_id` INT NOT NULL DEFAULT 0,
  `notify_method` VARCHAR(64) NOT NULL DEFAULT '',
  `send_to` VARCHAR(255) NOT NULL DEFAULT '',
  `title` VARCHAR(255) NOT NULL DEFAULT '',
  `content` VARCHAR(5120) NOT NULL DEFAULT '',
  `send_status` TINYINT NOT NULL DEFAULT 0,
  `result` VARCHAR(5120) NOT NULL DEFAULT '',
  `is_deleted` TINYINT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT NOW(),
  `updated_at` DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uidx_task_id` (`task_id`),
  INDEX `idx_caller_id` (`caller_id`),
  INDEX `idx_notify_method` (`notify_method`),
  INDEX `idx_send_status` (`send_status`),
  INDEX `idx_created_at` (`created_at`)
)

插入记录:

insert into test_ids (task_id) values ('1');
insert into test_ids (task_id) values ('2');
insert into test_ids (task_id) values ('3');

结果:

mysql> select id, task_id from test_ids;
+----+---------+
| id | task_id |
+----+---------+
|  9 | 1       |
| 16 | 2       |
| 30 | 3       |
bttbmeg0

bttbmeg01#

在多主场景中使用自动增量时,会出现这种情况。
检查
https://mysqlhighavailability.com/mysql-group-replication-auto-increment-configuration-handling/

相关问题