MySQL 双主模式解决主键冲突

WechatIMG636.jpeg

场景

假设有两台地位相等的 mysql 服务器,它们之间是主主复制的关系,并且都可以被写入。分别为 server1 和 server2。现在有两个请求同时到达这两台服务器,请求都需要对 t1 表进行操作,其中 server1 的请求需要插入一条id为1的记录,server2 的请求需要更新 id 为1的记录。 由于 mysql 服务器是地位相等的,而且请求的操作对象也相同,因此这两个请求会被同时处理。在处理请求的过程中,server1 会向 t1 表中插入 id 为1的记录,而 server2 也会插入 id 为1的记录,这样就会导致两个请求发生冲突,因为 t1 表中已经存在了 id 为1的记录。

方案一

server1 服务器,按寄数列来自增。server2 服务器,按偶数列来自增。

  • 先配置 server1,修改 /etc/mysql/my.cnf
1
2
3
4
# 自增的步长,例如,如果auto-increment-increment的值为2,则每次生成的新记录的值将比前一个记录的值大2。
auto-increment-increment=2
# 初始值的偏移量,例如,如果auto-increment-offset的值为1,auto-increment-increment的值为2,则第一次生成的新记录的值将为1,第二次生成的新记录的值将为3。
auto-increment-offset=1
  • 再配置 server2,修改 /etc/mysql/my.cnf
1
2
auto-increment-increment=2
auto-increment-offset=2
  • 分别重启 mysql
1
$ service mysql restart
  • 登录 server1->mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 创建表t1
mysql> CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(10) NOT NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 向表t1插入记录
mysql> INSERT INTO `test`.`t1`(`name`) VALUES ('Emily');
mysql> INSERT INTO `test`.`t1`(`name`) VALUES ('James');
mysql> INSERT INTO `test`.`t1`(`name`) VALUES ('Olivia');
# 查看表t1
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | Emily |
+----+-------+
| 3 | James |
+----+-------+
| 5 | Olivia|
+----+-------+
  • 登录 server2->mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 向表t1插入记录
mysql> INSERT INTO `test`.`t1`(`name`) VALUES ('William');
mysql> INSERT INTO `test`.`t1`(`name`) VALUES ('Sophia');
# 查看表t1
mysql> select * from t1;
+----+---------+
| id | name |
+----+---------+
| 1 | Emily |
+----+---------+
| 3 | James |
+----+---------+
| 5 | Olivia |
+----+---------+
| 6 | William |
+----+---------+
| 8 | Sophia |
+----+---------+

方案二

如果后期需要加服务器,那么单纯依靠方案一就会有一定的限制。因为递增 id 是基于自增长的方式生成的,如果服务器数量增加,那么就需要调整每台服务器上的自增长 id 的起始值和步长,这样就会带来一定的管理和维护成本。我们可以使用 redis 来解决这个问题,采用以下两种方式生成不重复的 id。

  • 使用 incr 命令

incr 命令是 redis 中的一个自增命令,可以对指定的 key 进行自增。我们可以使用类似于global:userid 这样的 key 作为自增的目标,每次执行 incr 命令,就可以得到一个不重复的 id。例如,我们可以在 php 中使用以下代码来生成不重复的 id:

1
2
3
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$id = $redis->incr('global:userid');
  • 使用 uuid

uuid 是一种全局唯一标识符,可以用于生成不重复的 id。在 redis 中,我们可以使用 php 的 uniqid 函数来生成 uuid。以下是使用 uuid 生成不重复 id 的示例代码:

1
2
3
4
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$id = uniqid();
$redis->set("global:userid:$id", 1);

这里将 value 设置为1,其实这个值没有什么实际意义,只是为了占据这个 key 对应的值,以避免其他客户端使用相同的 key 时会出现问题。在实际应用中,我们可以将 value 设置为一些有实际意义的值,例如用户的信息或者其他业务数据。

方案三

此方案是方案二的优化,需要注意的是,方案二中两种生成不重复 id 的方式都没有考虑到并发访问的情况,如果多个客户端同时访问 redis,那么就需要采取一些措施来确保生成的 id 不会重复。我们可以使用 redis 的事务机制或者分布式锁来保证每次生成的 id 都是唯一的。

  • 事务机制

事务机制是 redis 的一种原子性操作,可以保证多个操作的执行结果是一致的。我们可以使用redis 的事务机制来保证每次生成的 id 都是唯一的。具体实现步骤如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
// 开启事务
$redis->multi();
// 自增计数器
$redis->incr('global:userid');
// 提交事务
$result = $redis->exec();
if ($result) {
// 获取自增后的ID
$id = $result[0];
// 将ID存储到Redis中
$redis->set("global:userid:$id", 1);
}

在这个示例中,我们使用了 redis 的 multi 命令开启了一个事务,然后使用了 incr 命令对全局计数器进行自增操作。最后,我们使用 exec 命令提交事务,并通过判断执行结果判断事务是否成功。如果事务成功,我们就可以获取自增后的 id,并将其存储到 redis 中。

  • 分布式锁

分布式锁是一种常用的保证多个客户端之间访问共享资源的一致性的方法。在生成不重复 id 的过程中,我们可以使用 redis 的分布式锁来保证每次生成的 id 都是唯一的。具体实现步骤如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
// 分布式锁的key
$lock_key = "global:userid:lock";
// 获取锁的超时时间,单位为秒
$timeout = 10;
$lock_result = false;
while ($timeout > 0) {
// 尝试获取分布式锁,如果返回1,表示获取成功;如果返回0,表示获取失败。
$lock_result = $redis->setnx($lock_key, 1);
if ($lock_result) {
// 设置锁的过期时间为60秒,为了防止锁被一直占用,我们需要在获取锁之后为其设置一个过期时间
$redis->expire($lock_key, 60);
break;
} else {
// 等待1秒后重试
sleep(1);
$timeout -= 1;
}
}
if ($lock_result) {
// 对全局计数器进行自增操作
$id = $redis->incr('global:userid');
// 将ID存储到Redis中
$redis->set("global:userid:$id", 1);
// 释放分布式锁
$redis->del($lock_key);
}

在这个示例中,我们使用了 redis 的 setnx 命令尝试获取一个分布式锁,如果获取成功,则对全局计数器进行自增操作,并将 id 存储到 redis 中。如果获取失败,则等待一段时间后重试。需要注意的是,为了避免锁被长时间占用,我们需要为锁设置一个过期时间,并在使用完毕后及时释放锁。

关联

[[MySQL 数据库同步实现双机互备]]
[[Linux 集群部署解决方案一]]

-------------本文结束感谢您的阅读-------------
0%