MySQL 主从复制中创建复制用户的时机探讨
背景
该问题来自某客户,据描述,他们在部署 MySQL 主从复制时,有时候仅在主库上创建复制用户,有时候主从实例上都会去分别创建复制用户,发现这两种方式都可以成功建立复制。针对这一现象,进行了一轮验证,来观察采用不同方式创建复制用户对主从复制的影响。
通常来说,用得较多的方式是在搭建主从复制前,先在主库创建好复制用户,然后做一个 Xtrabackup 物理全备,再拿到从库上恢复并搭建主从。除此以外,还有哪些方式呢?分别对主从复制有哪些影响?一起来看一下。
验证
- MySQL 版本为 5.7.32,主库:10.186.60.62,从库:10.186.60.68。
- 为了演示方便,本次搭建主从复制时均采用 mysqldump 进行逻辑备份。
场景 1:仅在主库创建复制用户
1.主库做一个备份并拷贝到从库
- /usr/local/mysql5732/bin/mysqldump --single-transaction --master-data=2 -B zlm -S /tmp/mysql3332.sock -p > zlm.sql
- scp zlm.sql root@10.186.60.68:~
2.登陆从库执行导入
- mysql> source zlm.sql
由于没有使用参数 --set-gtid-purged=off,导出的语句中会带有 SET @@GLOBAL.GTID_PURGED='xxxx:1-xx' 并执行,导入前需要先在从库上执行 reset master。
3.主库创建复制用户
- mysql> create user repl1 identified by 'repl1';
4.从库配置主从复制并启动
- mysql> change master to master_host='10.186.60.62',master_port=3332,master_user='repl1',master_password='repl1',master_auto_position=1;
- mysql> start slave;
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s2.51cto.com/oss/202103/19/1e56ca6c8f894f6872ea4bc47f668b2f.jpg)
启动复制后,报了 Error 1045 的错误,此处并不是密码错,而是没有给复制用户配置 replication slave 权限,在主库上对 repl1 用户执行赋权后(grant replication slave on *.* to repl1;),再启动复制就正常了。
主从复制正常以后,也会在从库上创建复制用户 repl1。
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s6.51cto.com/oss/202103/19/5cfb446bfa93c0c50d3d7d9ea9c3f6f1.jpg)
从库上并没有创建过复制用户 repl1,主从复制就正常搭建好了,为什么呢?因为 change master to 语句中指定的 master_user 是主库上的复制用户,从库通过这个用户连接到主库进行同步,当开启复制线程后,主库上创建复制用户的语句会在从库上进行回放,于是从库上也会有这个复制用户了。
结论 1
- 搭建主从复制时,在从库创建复制用户不是必须的,仅在主库创建即可,复制用户会同步到从库。
场景 2:主从库单独创建复制用户(create 语句)
1.主库做一个备份并拷贝到从库(gtid_purged=xxxx:1-23)
2.从库执行导入
3.主库创建复制用户并赋权
- mysql> create user repl2 identified by 'repl2';
- mysql> grant replication slave on *.* to repl2;
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s5.51cto.com/oss/202103/19/7197995cbe69aa07cffa92a14bdbe8b5.jpg)
4.从库创建复制用户
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s6.51cto.com/oss/202103/19/0d0178d883bf0a7f9427794fcc894631.jpg)
由于不想在从库上产生由从库 uuid 写入的 binlog 事务,此处设置了 sql_log_bin=0,使事务不被记录到 binlog 中,原因是在数据库管理平台对高可用集群进行管理时,通常是不允许从库上有主库不存在的 GTID 事务的。
5.从库配置主从复制并启动
- mysql> change master to master_host='10.186.60.62',master_port=3332,master_user='repl2',master_password='repl2',master_auto_position=1;
- mysql> start slave;
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s4.51cto.com/oss/202103/19/033e33eaedacdeab0679f3d020ed7dfb.jpg)
由于从库上已经创建了复制用户,当回放到主库的这个事务时会报 Error 1396 的错误。
可以用 create user 语句创建一个重复用户来验证。
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s3.51cto.com/oss/202103/19/97525601664b004e2155a898a48b8010.jpg)
解析主库 binlog,启动复制后执行的第一个事务就是这个 24 的创建用户语句。
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s6.51cto.com/oss/202103/19/e23424eea97d500bd6cdac3d910bbeee.jpg)
结论 2
在从库导入备份后并分别在主、从库单独创建复制用户后,当从库执行到创建用户的事务时会导致复制中断。
场景 3:主从库单独创建复制用户(grant 语句)
1.主库做一个备份并拷贝到从库(gtid_purged=xxxx:1-28)
2.从库执行导入
3.主库创建复制用户
- mysql> grant replication slave on *.* repl3 identified by 'repl3';
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s2.51cto.com/oss/202103/19/22383e9835306ad517be92e7cc0f15b3.jpg)
4.从库创建复制用户
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s6.51cto.com/oss/202103/19/095f6ce736b4100f257be042f340dff4.jpg)
5.从库配置主从复制并启动
- mysql> change master to master_host='10.186.60.62',master_port=3332,master_user='repl2',master_password='repl2',master_auto_position=1;
- mysql> start slave;
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s2.51cto.com/oss/202103/19/f3a8e5b3a36e8f4b17f541a76fedabc8.jpg)
这次启动复制后并没有报错。为何用 grant 语句创建用户就可以,用 create 语句就不行呢?
create 与 grant 语句都会产生事务并记录到 binlog 中,但区别是 grant 语句是一个近似幂等的操作,而 create 语句不是。
解析主库 binlog,29 和 30 都是重复执行 grant 的事务。
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s5.51cto.com/oss/202103/19/5e99331bc9bbc69065b018dc56b80522.jpg)
观察 show slave stauts\G,从库上也把 29,30 这两个事务都回放掉了,重复执行它们并不影响主从复制。
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s3.51cto.com/oss/202103/19/077c1ecf63d7df676f4265fadde329be.jpg)
但要注意的是,在 MySQL 8.0 中已经禁止通过 grant 这种语法来创建用户了。
![技术分享 | MySQL 主从复制中创建复制用户的时机探讨](https://s4.51cto.com/oss/202103/19/35277f724e59c39d647589956807c8ea.jpg)
结论 3
- 从库导入备份并在主从库分别使用 grant 语句创建用户后,在从库回放时不会导致复制中断。
总结
1.根据以上验证结果得知,在搭建主从复制时,采用多种方式创建复制用户都是可行的,但有些方式存在一些限制,如:在主、从实例上分别创建复制用户。虽然执行 grant 语句创建用户不会导致复制中断,但其并不是标准的 MySQL 创建用户语法,在 MySQL 8.0 中已被视为语法错误,因此不推荐采用这样的方式来搭建主从。
2.创建复制用户的方式
Create 语句创建用户时
1.主库创建完复制用户后做备份,再配置主从
2.备份后仅在主库创建复制用户,再配置主从(推荐)
3.如果要在主、从库分别创建复制用户,应先设置 session 级别的 sql_log_bin=0,再配置主从
Grant 语句创建用户时(MySQL 5.7 及以下版本)
1.主库先创建复制用户后备份,再配置主从
2.仅在主库创建复制用户,再配置主从(推荐)
3.主、从库分别创建复制用户,再配置主从
相关文章
- 聊一聊我最近使用的uniCloud是个什么玩意?
- Spring Boot 整合 Redis 实现缓存操作
- 13.9k Star!Go 和 Google TensorFlow 实现的私人照片管理器
- 盘点程序员必备的专业术语,值得看一看
- 使用 Nacos 存储 Sentinel 规则信息
- 听叔一句劝,消息队列的水太深,你把握不住!
- 一篇带给你etcd与分布式锁
- 【前端】你好,我叫TypeScript 02──变量和接口
- 编写Node.js Rest API的十个最佳实践
- 设计模式 - Prototype 原型模式
- 利用Selenium批量下载100首网易云热歌榜音乐
- 比Firebase还好用!神级程序员都在用这个开发工具
- Golang 语言中 kafka 客户端库 Sarama
- 世界级的开源项目:TiDB 如何重新定义下一代关系型数据库
- 用好OnionScan,自己动手制作暗网爬虫
- Java 8 Streams中的数据库CRUD操作
- Java中如何使用嵌入MySQL
- 谷歌发布新编程语言,专治SQL各种“不服”
- JavaEE中遗漏的10个最重要的安全控制
- 如何在O(1)内找到实时序列的最小值?