我在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 |
1条答案
按热度按时间bttbmeg01#
在多主场景中使用自动增量时,会出现这种情况。
检查
https://mysqlhighavailability.com/mysql-group-replication-auto-increment-configuration-handling/