ole的知识小站

mysql8多主一从复制_binlogPos_windows_docker环境

—

由

wangjm
于

mysql复制与组复制汇总

参考: https://blog.csdn.net/qq_33330687/article/details/107496954

参考: https://blog.csdn.net/Snji_G/article/details/109538112

参考: https://blog.csdn.net/qq_42533216/article/details/124253198

参考:主从复制场景 https://cloud.tencent.com/developer/article/1662191

参考： mysql5.7文档： https://dev.mysql.com/doc/refman/5.7/en/replication.html

参考： mysql8文档： https://dev.mysql.com/doc/refman/8.0/en/server-configuration.html

主从复制

对于一主多从.

一主多从复制方式 特点 版本
classic异步复制模式 逻辑简单/同步可能有延迟/主宕机时从可能有数据未同步 较早就支持
classic半同步复制模式(after commit wait ack) 数据一致性提高/效率降低/过渡性技术 5.6+
classic增强的半同步复制模式(after binlog wait ack) 数据一致性提高/效率降低/过渡性技术 5.6+
gtid复制模式 全局事务id/从库自动忽略执行过的事务/数据一致性提高 5.6+

对于多主一从,也称多源复制.一般搭配多级复制或更多的从机

多主一从复制方式 特点 版本
classic异步复制模式 逻辑简单/同步可能有延迟/主宕机时从可能有数据未同步 多主一从5.7+
gtid复制模式 全局事务id/从库自动忽略执行过的事务/数据一致性提高 多主一从5.7+

互备与多主多从

互备方式 特点
双主互备classic复制模式 逻辑简单/同步可能有延迟/某主宕机时从可能有数据未同步 5.7+
双主互备gtid复制模式 全局事务id/自动忽略执行过的事务/数据一致性提高 5.7+
组复制模式 带宽与延迟有要求/分区容错性提高 5.7+

参考: https://www.cnblogs.com/ygqygq2/p/6045279.html

参考: https://cloud.tencent.com/developer/article/1119240

参考: https://www.jianshu.com/p/a7265fd9b8b7

高可用

单mysql实例不具备高可用条件.

classic/gtid实现的复制可以靠手动/脚本/工具可以实现主从切换.

组复制模式的实例可以通过组复制协议有限制的实现高可用.

mysql8多主一从复制

超哥说mysql5.6不支持多主一从复制, 要5.7或8才行.

前面的章节试了下用mysql5.6试了下一主多从, 试多主一从的时候发现果然不可以.

现在试下mysql8下多主一从.

参考: https://www.cnblogs.com/machangwei-8/p/15949357.html 参考: https://cloud.tencent.com/developer/article/1382116, 发现mysql5.7才开始支持多主一从.https://www.yisu.com/zixun/35178.html

参考:https://blog.csdn.net/qq_33330687/article/details/107496954

超哥的建议: 直接升mysql8, 但是mysql8sql会更严格一点, 但我们项目里的sql可能需要调整.

参考: https://blog.csdn.net/Snji_G/article/details/109538112

继续使用windows下docker实验.以下实验主要参考: https://cloud.tencent.com/developer/article/1382116

第一个mysql8实例(master/mysql8-instance1)

先在宿主机建立docker容器映射目录

  1. C:\users\data\mysql8-instance1\conf, 并放入自定义的my.cnf. (配置了server-id, 开启了binlog)
  2. C:\users\data\mysql8-instance1\data\mysql, C:\users\data\mysql8-instance1\data\mysql-files. 应该还需要添加C:\users\data\mysql8-instance1\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改), 稍后启动docker时的日志会显示改操作的必要性

创建docker映射目录与配置文件

下面是所需的my.cnf配置文件:

[mysql]

#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock

[mysqld]
lower_case_table_names=1
# innodb_buffer_pool_size = 128M

#user= root
#basedir = /cjdata/vivachekcloud/mysql
#datadir = /cjdata/vivachekcloud/mysql/data
#log-error=/cjdata/vivachekcloud/mysql/data/mysql.err
#pid-file=/cjdata/vivachekcloud/mysql/data/mysql.pid
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
#port = 3306
# server_id = .....
# socket = .....
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

skip-name-resolve
default-time-zone = '+08:00'
datadir = /var/lib/mysql

slow_query_log=1
long_query_time=3
slow_query_log_file = /var/lib/mysql/slow.log

log_bin=ON
log-bin=/var/lib/mysql/mysql-bin
#binlog_format =MIXED
#sync_binlog = 1
expire_logs_days = 30   #default 0, for never delete binlog file
server-id=5

#binlog-do-db = xxx   #database needed to sync
binlog-ignore-db = mysql  #database never sync
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
binlog_ignore_db = sys

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

开启binlog, server-id置为5

上面配置文件已配好, 略.

创建并开启docker

第一个是mysql8.0的实例,名字叫mysql-instance1, 映射宿主端口3316

docker run --name mysql8-instance1 -v /C/users/data/mysql8-instance1/conf:/etc/mysql:rw -v /C/users/data/mysql8-instance1/data/mysql:/var/lib/mysql -v /C/users/data/mysql8-instance1/data/mysql-files:/var/lib/mysql-files -p 3316:3306 -e MYSQL_ROOT_PASSWORD=Nov2014 -e TZ=Asia/Shanghai -d mysql:8.0

docker 看下日志, 可能还需要添加C:\users\data\mysql\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改)

注意:windows环境docker下mysql配置文件映射路径需要放到C:\users\下(否则777权限的mysql配置文件,mysql出于安全考虑会忽略), https://blog.csdn.net/kinginblue/article/details/76863962

注意: 如果映射mysql配置文件夹下没有my.cnf,默认docker实例里mysql数据存到了docker容器里的/usr/data下, 需要在windows映射目录下放配置文件

主mysql8-instance1建立给从库用的账号

#主库登录
mysql -h 127.0.0.1 -P 3306 -uroot -pNov2014
#创建用户,授权(mysql8 创建用户与授权需要分两条语句)
create user rep@'172.17.0.%' identified by '123456';
grant replication slave on *.* to rep@'172.17.0.%' with grant option;
#刷新权限
flush privileges;
#查看用户
select user,host from mysql.user;
#查看权限
show grants for rep@'172.17.0.%';

主mysql8-instance1锁表备份

继续在上一节打开的主库mysql8-instance1连接中操作

#对主库锁表只读，当前窗口不能关闭
flush table with read lock;

#查看主库当前binlog位置.同时记下当前binlog文件名称与pos
show master status;

mysqldump –all-databases, -A： 备份所有数据库 –databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldump把每个名字都当作为数据库名。

windows下注意这个问题:https://blog.csdn.net/OnedayIlove/article/details/104021527, https://blog.csdn.net/qianxiao_1/article/details/79463409

新开命令行窗口(使用cmd , powershell 的重定向会导致乱码,继而导入时失败), 备份

#mysqldump -A -B |gzip>/opt/bak.sql.gz
#mysqldump -A -B > C:\users\wangjm\desktop\master_bak.sql
#mysqldump -h 127.0.0.1 -uroot -p123456 -A -B >C:\users\wangjm\desktop\master_bak.sql

#因为我们主从mysql实例版本不一致,这里指定要复制的各个库, 不复制系统库
mysqldump -h 127.0.0.1 -uroot -pNov2014 -B XXX_DB1 XXX_DB2 >C:\users\wangjm\desktop\master_bak.sql

备份好,看好binlog当前位置POS之后, 就可以解除master的读锁了

unlock table;

第二个mysql8实例(master/mysql8-instance2)

先在宿主机建立docker容器映射目录

  1. C:\users\data\mysql8-instance2\conf, 并放入自定义的my.cnf. (配置了server-id, 开启了binlog)
  2. C:\users\data\mysql8-instance2\data\mysql, C:\users\data\mysql8-instance1\data\mysql-files. 应该还需要添加C:\users\data\mysql8-instance2\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改), 稍后启动docker时的日志会显示改操作的必要性

创建docker映射目录与配置文件

下面是所需的my.cnf配置文件:

[mysql]

#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock

[mysqld]
lower_case_table_names=1
# innodb_buffer_pool_size = 128M

#user= root
#basedir = /cjdata/vivachekcloud/mysql
#datadir = /cjdata/vivachekcloud/mysql/data
#log-error=/cjdata/vivachekcloud/mysql/data/mysql.err
#pid-file=/cjdata/vivachekcloud/mysql/data/mysql.pid
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
#port = 3306
# server_id = .....
# socket = .....
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

skip-name-resolve
default-time-zone = '+08:00'
datadir = /var/lib/mysql

slow_query_log=1
long_query_time=3
slow_query_log_file = /var/lib/mysql/slow.log

log_bin=ON
log-bin=/var/lib/mysql/mysql-bin
#binlog_format =MIXED
#sync_binlog = 1
expire_logs_days = 30   #default 0, for never delete binlog file
server-id= 6

#binlog-do-db = xxx   #database needed to sync
binlog-ignore-db = mysql  #database never sync
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
binlog_ignore_db = sys

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

开启binlog, server-id置为6

上面配置文件已配好, 略.

创建并开启docker

第一个是mysql8.0的实例,名字叫mysql-instance2, 映射宿主端口3317

docker run --name mysql8-instance2 -v /C/users/data/mysql8-instance2/conf:/etc/mysql:rw -v /C/users/data/mysql8-instance2/data/mysql:/var/lib/mysql -v /C/users/data/mysql8-instance2/data/mysql-files:/var/lib/mysql-files -p 3317:3306 -e MYSQL_ROOT_PASSWORD=Nov2014 -e TZ=Asia/Shanghai -d mysql:8.0

docker 看下日志, 可能还需要添加C:\users\data\mysql\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改)

注意:windows环境docker下mysql配置文件映射路径需要放到C:\users\下(否则777权限的mysql配置文件,mysql出于安全考虑会忽略), https://blog.csdn.net/kinginblue/article/details/76863962

注意: 如果映射mysql配置文件夹下没有my.cnf,默认docker实例里mysql数据存到了docker容器里的/usr/data下, 需要在windows映射目录下放配置文件

主mysql8-instance2建立给从库用的账号

#主库登录
mysql -h 127.0.0.1 -P 3306 -uroot -pNov2014
#创建用户,授权(mysql8创建用户与授权需要分两条语句)
create user rep@'172.17.0.%' identified by '123456';
grant replication slave on *.* to rep@'172.17.0.%' with grant option;
#刷新权限
flush privileges;
#查看用户
select user,host from mysql.user;
#查看权限
show grants for rep@'172.17.0.%';

主mysql8-instance2锁表备份

继续在上一节打开的主库mysql8-instance2连接中操作

#对主库锁表只读，当前窗口不能关闭
flush table with read lock;

#查看主库当前binlog位置.同时记下当前binlog文件名称与pos
show master status;

mysqldump –all-databases, -A： 备份所有数据库 –databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldump把每个名字都当作为数据库名。

windows下注意这个问题:https://blog.csdn.net/OnedayIlove/article/details/104021527, https://blog.csdn.net/qianxiao_1/article/details/79463409

新开命令行窗口(使用cmd , powershell 的重定向会导致乱码,继而导入时失败), 备份

#mysqldump -A -B |gzip>/opt/bak.sql.gz
#mysqldump -A -B > C:\users\wangjm\desktop\master_bak.sql
#mysqldump -h 127.0.0.1 -uroot -p123456 -A -B >C:\users\wangjm\desktop\master_bak.sql

#因为我们主从mysql实例版本不一致,这里指定要复制的各个库, 不复制系统库
mysqldump -h 127.0.0.1 -uroot -pNov2014 -B XXX_DB1 XXX_DB2 >C:\users\wangjm\desktop\master_bak.sql

备份好,看好binlog当前位置POS之后, 就可以解除master的读锁了

unlock table;

第三个mysql8实例(master/mysql8-instance3)

先在宿主机建立docker容器映射目录

  1. C:\users\data\mysql8-instance3\conf, 并放入自定义的my.cnf. (配置了server-id, 开启了binlog)
  2. C:\users\data\mysql8-instance3\data\mysql, C:\users\data\mysql8-instance1\data\mysql-files. 应该还需要添加C:\users\data\mysql8-instance3\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改), 稍后启动docker时的日志会显示改操作的必要性

创建docker映射目录与配置文件

下面是所需的my.cnf配置文件:

[mysql]

#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock

[mysqld]
lower_case_table_names=1
# innodb_buffer_pool_size = 128M

#user= root
#basedir = /cjdata/vivachekcloud/mysql
#datadir = /cjdata/vivachekcloud/mysql/data
#log-error=/cjdata/vivachekcloud/mysql/data/mysql.err
#pid-file=/cjdata/vivachekcloud/mysql/data/mysql.pid
#socket=/cjdata/vivachekcloud/mysql/data/mysql.sock
#port = 3306
# server_id = .....
# socket = .....
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

skip-name-resolve
default-time-zone = '+08:00'
datadir = /var/lib/mysql

slow_query_log=1
long_query_time=3
slow_query_log_file = /var/lib/mysql/slow.log

#log_bin=ON
#log-bin=/var/lib/mysql/mysql-bin
#binlog_format =MIXED
#sync_binlog = 1
expire_logs_days = 30   #default 0, for never delete binlog file
server-id= 7

#binlog-do-db = xxx   #database needed to sync
#binlog-ignore-db = mysql  #database never sync
#binlog_ignore_db = information_schema
#binlog_ignore_db = performance_schema
#binlog_ignore_db = sys

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

不需要开启binlog, server-id置为7

上面配置文件已配好, 略.

创建并开启docker

第一个是mysql8.0的实例,名字叫mysql-instance2, 映射宿主端口3317

docker run --name mysql8-instance3 -v /C/users/data/mysql8-instance3/conf:/etc/mysql:rw -v /C/users/data/mysql8-instance3/data/mysql:/var/lib/mysql -v /C/users/data/mysql8-instance3/data/mysql-files:/var/lib/mysql-files -p 3318:3306 -e MYSQL_ROOT_PASSWORD=Nov2014 -e TZ=Asia/Shanghai -d mysql:8.0

docker 看下日志, 可能还需要添加C:\users\data\mysql\data 的users组的修改权限(文件夹右键->属性->安全->编辑->勾选修改)

注意:windows环境docker下mysql配置文件映射路径需要放到C:\users\下(否则777权限的mysql配置文件,mysql出于安全考虑会忽略), https://blog.csdn.net/kinginblue/article/details/76863962

注意: 如果映射mysql配置文件夹下没有my.cnf,默认docker实例里mysql数据存到了docker容器里的/usr/data下, 需要在windows映射目录下放配置文件

在从mysql8-instance3恢复主库备份

在some-mysql2实例(127.0.0.1:3318)上写入备份内容(略):

mysql -h 127.0.0.1 -P 3308 -u root -pNov2014 < C:\users\wangjm\desktop\master_bak.sql

从mysql8-instance3增加连接的master

docker network inspect bridge

其中有相关内容:

"Containers": {
            "305f0b01627ab2a0b62b51cf8ad63047e004bce6804ee6675ecf370fe2731dd6": {
                "Name": "mysql8-instance3",
                "EndpointID": "5a8e90a9c18cdc0d04d844f7f4269c81b8e49a832c926a28d790e0558b71cbff",
                "MacAddress": "02:42:ac:11:00:04",
                "IPv4Address": "172.17.0.4/16",
                "IPv6Address": ""
            },
            "391b61208b83da4fa395380ecc8f24b2b2c2a1632b592ffb03c2b42bf509a5c5": {
                "Name": "mysql8-instance2",
                "EndpointID": "29932810ee88fa2db724e87973acdbef4aef3cf2f9fc0b44254988d770905baa",
                "MacAddress": "02:42:ac:11:00:03",
                "IPv4Address": "172.17.0.3/16",
                "IPv6Address": ""
            },
            "61cdc31cd88b2b69648be9863e2976e1a9d0efc6a0c3fd047cdae62ee7de250b": {
                "Name": "mysql-instance1",
                "EndpointID": "16f90210570e9e4b622b0db9f146cf9fca3e4ded4cc57a4034581ed5df00b2a2",
                "MacAddress": "02:42:ac:11:00:02",
                "IPv4Address": "172.17.0.2/16",
                "IPv6Address": ""
            }
        },

即可知三个实例在容器内部的ip:

mysql8-instance1 (master, 开启binlog), ip: 172.17.0.2

mysql8-instance2(master, 开启binlog), ip: 172.17.0.3

mysql8-instance3(slave, 未开binlog), ip: 172.17.0.4

主库文件和日志位置，是前面在主库执行show master status 查看的.

新打开一个到实例mysql8-instance3 (slave/172.17.0.4)的连接, 分别和两个主实例建立复制关系:

CHANGE MASTER TO
MASTER_HOST='172.17.0.2',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=157
for channel 'mysql8-instance1';

CHANGE MASTER TO
MASTER_HOST='172.17.0.3',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=157
for channel 'mysql8-instance2';

启动从库同步开关并测试主从复制

从库执行:

#开启从库复制
start slave;
show slave status

#若需要单独启动或停止某个同步通道，可使用如下命令：
start slave for channel 'mysql8-instance1';     //启动名称为mysql8-instance1的同步通道
stop slave for channel 'mysql8-instance1';     //停止名称为mysql8-instance1的同步通道

状态如下:

Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error Replicate_Ignore_Server_Ids Master_Server_Id Master_UUID Master_Info_File SQL_Delay SQL_Remaining_Delay Slave_SQL_Running_State Master_Retry_Count Master_Bind Last_IO_Error_Timestamp Last_SQL_Error_Timestamp Master_SSL_Crl Master_SSL_Crlpath Retrieved_Gtid_Set Executed_Gtid_Set Auto_Position Replicate_Rewrite_DB Channel_Name Master_TLS_Version Master_public_key_path Get_master_public_key Network_Namespace
Connecting to source 172.17.0.2 rep 3306 60 mysql-bin.000005 157 305f0b01627a-relay-bin-mysql8@002dinstance1.000001 4 mysql-bin.000005 Connecting Yes 0 0 157 157 None 0 No NULL No 2061 error connecting to master ‘rep@172.17.0.2:3306’ – retry-time: 60 retries: 6 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection. 0 0 mysql.slave_master_info 0 NULL Replica has read all relay log; waiting for more updates 86400 220424 15:58:34 0 mysql8-instance1 0
Connecting to source 172.17.0.3 rep 3306 60 mysql-bin.000004 157 305f0b01627a-relay-bin-mysql8@002dinstance2.000001 4 mysql-bin.000004 Connecting Yes 0 0 157 157 None 0 No NULL No 2061 error connecting to master ‘rep@172.17.0.3:3306’ – retry-time: 60 retries: 6 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection. 0 0 mysql.slave_master_info 0 NULL Replica has read all relay log; waiting for more updates 86400 220424 15:58:34 0 mysql8-instance2 0

发现Slave_IO_Running状态是Connecting,

在my.cnf中加入error_log配置:

[mysqld]
# ...
log-error = /var/lib/mysql/error.log

重启,发现有如下报错:

2022-04-24T08:50:08.524656Z 5 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'mysql8-instance1': error connecting to master 'rep@172.17.0.2:3306' - retry-time: 60 retries: 24 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
2022-04-24T08:51:08.526954Z 7 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'mysql8-instance2': error connecting to master 'rep@172.17.0.3:3306' - retry-time: 60 retries: 25 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

参考:https://www.modb.pro/db/29919, 可知是mysql8之后默认验证方式变了的原因.

我的做法是如下(也可以选择另一条思路: 替换登录验证方式)

  1. 从mysql8-instance3的数据映射目录中复制几个pem文件, 覆盖到mysql-instance1, mysql8-instance2实例映射目录对应位置下,
  2. docker exec -it mysql8-instance3 /bin/bash 进入docker实例中, mysql -h 172.17.0.2 -urep -p123456 --get-server-public-key, 然后exit. mysql -h 172.17.0.3 -urep -p123456 --get-server-public-key, 然后exit exit退出docker实例.
  3. reset slave, 重新配置复制.

我不确定是上面1还是2生效了.但是多源复制是成功了.

在主库修改了一条血糖记录, 发现从库也发生了修改(OK)

在主库某表添加/删除了一个字段, 发现从库也自动添加/删除了字段(OK)

在主库创建了一个数据库, 发现从库也自动新建了数据库(OK)

评论

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注