微服务轮子项目（27） -MySQL 主从复制、主从切换、主主复制-阿里云开发者社区

微服务轮子项目（27） -MySQL 主从复制、主从切换、主主复制

2023-11-14 188

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS AI 助手，专业版

RDS MySQL DuckDB 分析主实例，基础系列 4核8GB

RDS MySQL DuckDB 分析主实例，集群系列 4核8GB

简介： 微服务轮子项目（27） -MySQL 主从复制、主从切换、主主复制

1. 主从复制

1.1 主库安装

1.1.1 创建配置文件

1.创建目录

mkdir -p /opt/mysql/master/conf

2.创建my.cnf文件

vim /opt/mysql/master/conf/my.cnf

内容如下:

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
skip-character-set-client-handshake
binlog_format=ROW
binlog_rows_query_log_events=1
server_id = 1
log-bin= mysql-bin
gtid_mode=on
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
sync_master_info=1
binlog_checksum=CRC32
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=4
binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64
transaction-isolation=READ-COMMITTED
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
expire_logs_days=30
max_connections=3600
# Custom config should go here
!includedir /etc/mysql/conf.d/

备注：

skip-character-set-client-handshake：忽略应用程序想要设置的其他字符集

binlog_format：为设置binlog格式

binlog_rows_query_log_events：在row模式下开启该参数，将把sql语句打印到binlog日志里面，默认是0(off);

server_id：这个的值必需所有mysql实例都不重复 log-bin：binlog的名称

gtid_mode：开启GTID，用来代替classic的复制方法

enforce_gtid_consistency：开启gtid的一些安全限制，阻止不安全的语句执行

master_info_repository=table和relay_log_info_repository=table：master.info和relay.info保存在表中

relay_log_recovery：当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性

sync_master_info：每个事务都会刷新master.info binlog_checksum：默认为NONE，表示在图1的箭头1 不生成checksum，这样就可以兼容旧版本的mysql。此外，就只能设置为CRC32了

slave-parallel-type：DATABASE为默认值，基于库的并行复制方式；LOGICAL_CLOCK基于组提交的并行复制方式

slave_parallel_workers：设置多少个SQL Thread（coordinator线程）来进行并行复制

binlog_transaction_dependency_tracking：控制是否使用WRITESET策略，WRITESET_SESSION是在写集合的基础上增加约束,保证按照前后顺序执行

transaction_write_set_extraction：控制检测事务依赖关系时采用的HASH算法

transaction-isolation：事务隔离级别 read-only：是否只读，0为否，1为是

replicate-ignore-db：配置忽略同步的数据库 expire_logs_days：binlog日志过期时间，默认不过期

max_connections：mysql最大连接数

1.1.2 启动主库

1.创建启动命令

vim /opt/mysql/master/start.sh

内容如下:

MYSQL_DIR=/opt/mysql/master
docker stop mysql-m
docker rm mysql-m
docker run -d \
    -p 3306:3306 \
    --name mysql-m \
    -v ${MYSQL_DIR}/conf/my.cnf:/etc/mysql/my.cnf \
    -v ${MYSQL_DIR}/data/mysql:/var/lib/mysql \
    -v ${MYSQL_DIR}/log:/opt/mysql/log \
    -e MYSQL_ROOT_PASSWORD=1q2w3e4r \
    mysql:5.7.25

MYSQL_ROOT_PASSWORD：为root账号的登录密码

1.1.3 主库创建用于同步的账号

1.登录进主库

## 进去docker容器内
docker exec -it mysql-m bash
## 登录mysql数据库
mysql -uroot -p1q2w3e4r

2.创建backup账号

GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';

1.2 从库安装

进入从库的服务器执行以下操作，建议是不同于主库的服务器，如果服务器相同需要修改3306端口为其他的值。

1.2.1 创建配置文件

创建目录

mkdir -p /opt/mysql/slave/conf

创建my.cnf文件

vim /opt/mysql/slave/conf/my.cnf

内容如下:

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
skip-character-set-client-handshake
binlog_format=ROW
binlog_rows_query_log_events=1
server_id = 2
log-bin= mysql-bin
gtid_mode=on
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
sync_master_info=1
binlog_checksum=CRC32
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=4
binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64
transaction-isolation=READ-COMMITTED
read-only=1
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
expire_logs_days=30
max_connections=3600
# Custom config should go here
!includedir /etc/mysql/conf.d/

不同于主库的配置如下：

server_id：设为2

read-only：设为只读

1.2.2 启动从库

创建启动命令

vim /opt/mysql/slave/start.sh

内容如下:

MYSQL_DIR=/opt/mysql/slave
docker stop mysql-s
docker rm mysql-s
docker run -d \
    -p 3306:3306 \
    --name mysql-m \
    -v ${MYSQL_DIR}/conf/my.cnf:/etc/mysql/my.cnf \
    -v ${MYSQL_DIR}/data/mysql:/var/lib/mysql \
    -v ${MYSQL_DIR}/log:/opt/mysql/log \
    -e MYSQL_ROOT_PASSWORD=1q2w3e4r \
    mysql:5.7.25

1.2.3 关联主库

1.登录进从库

## 进去docker容器内
docker exec -it mysql-s bash
## 登录mysql数据库
mysql -uroot -p1q2w3e4r

2.执行关联master语句

change master to master_host='192.168.28.130',master_port=3306,master_user='backup',master_password='123456',MASTER_AUTO_POSITION=1;

master_host：为主库ip

master_port：主库端口

master_user：主库用于同步的帐号

master_password：主库用于同步的帐号密码

master_auto_position：slave连接master将使用基于GTID的复制协议

1.2.4 启动并查看slave

## 启动slave
start slave;
## 查看slave的状态
show slave status\G

Slave_IO_Running和Slave_SQL_Running 都为Yes就代表配置成功了

Seconds_Behind_Master：为主从延时(ms)

1.2.5 创建从库的普通用户

read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户（如超级管理员root用户）的数据修改操作，所以需要另外创建普通账号来操作从库。

GRANT select,insert,update,delete,create,drop,alter ON *.* to 'zlt'@'%' identified by '1q2w3e4r';

1.3 主库查看同步信息

登录主库，查看binlog线程，执行以下语句查看正在执行的线程:

show processlist;

查看所有从库信息:

show slave hosts;

2. 主从切换

2.1 对主库进行锁表

flush tables with read lock;
• 1

在flush tables with read lock成功获得锁之前，必须等待所有语句执行完成（包括SELECT）。所以如果有个慢查询在执行，或者一个打开的事务，或者其他进程拿着表锁，flush tables with read lock就会被阻塞，直到所有的锁被释放。

2.2 检查master同步状态

在主库执行

show processlist;

如果显示Master has sent all binlog to slave; waiting for more updates，则可以执行下一步了，否则需要等待

2.3 检查slave同步状态

在从库执行

show processlist;

确保显示为 Slave has read all relay log; waiting for more updates

2.4 提升slave为master

1.在从库执行以下语句

stop slave;
reset master;
reset slave all;

reset slave all 命令会删除从库的 replication 参数，之后 show slave status\G 的信息返回为空。

2.创建同步用户，在从库执行

GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';

3.修改配置并重启

修改从库的my.cnf文件，将read-only的值改为0并重启mysql

2.5 将原来master变为slave

1.修改配置并重启

修改主库的my.cnf文件，将read-only的值改为1并重启mysql

2.在主库执行以下语句

reset master;
reset slave;
change master to master_host='192.168.28.130',master_port=3306,master_user='backup',master_password='123456',MASTER_AUTO_POSITION=1;
start slave;

3.查看新slave的状态

## 在主库下执行
show slave status\G

Slave_IO_Running和Slave_SQL_Running都显示Yes就代表成功了

3. 主主复制

3.1 主库配置文件

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
skip-character-set-client-handshake
binlog_format=ROW
binlog_rows_query_log_events=1
server_id = 1
log-bin= mysql-bin
gtid_mode=on
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
sync_master_info=1
binlog_checksum=CRC32
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=4
binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64
transaction-isolation=READ-COMMITTED
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
slave-skip-errors=all
expire_logs_days=30
max_connections=3600
auto_increment_offset = 1
auto_increment_increment = 2
# Custom config should go here
!includedir /etc/mysql/conf.d/

3.2 第二主库配置文件

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
skip-character-set-client-handshake
binlog_format=ROW
binlog_rows_query_log_events=1
server_id = 2
log-bin= mysql-bin
gtid_mode=on
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
sync_master_info=1
binlog_checksum=CRC32
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=4
binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64
transaction-isolation=READ-COMMITTED
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
slave-skip-errors=all
expire_logs_days=30
max_connections=3600
auto_increment_offset = 2
auto_increment_increment = 2
# Custom config should go here
!includedir /etc/mysql/conf.d/

3.3 总结

主主配置与主从配置的区别在于以下2点：

两个库的read-only都为0，同为可写可读
新增auto_increment_offset和auto_increment_increment参数，并且两个库的auto_increment_offset值不相同

微服务轮子项目（27） -MySQL 主从复制、主从切换、主主复制

1. 主从复制

1.1 主库安装

1.1.1 创建配置文件

1.1.2 启动主库

1.1.3 主库创建用于同步的账号

1.2 从库安装

1.2.1 创建配置文件

1.2.2 启动从库

1.2.3 关联主库

1.2.4 启动并查看slave

1.2.5 创建从库的普通用户

1.3 主库查看同步信息

2. 主从切换

2.1 对主库进行锁表

2.2 检查master同步状态

2.3 检查slave同步状态

2.4 提升slave为master

2.5 将原来master变为slave

3. 主主复制

3.1 主库配置文件

3.2 第二主库配置文件

3.3 总结

热门文章

最新文章

相关课程

相关电子书

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

微服务轮子项目（27） -MySQL 主从复制、主从切换、主主复制

1. 主从复制

1.1 主库安装

1.1.1 创建配置文件

1.1.2 启动主库

1.1.3 主库创建用于同步的账号

1.2 从库安装

1.2.1 创建配置文件

1.2.2 启动从库

1.2.3 关联主库

1.2.4 启动并查看slave

1.2.5 创建从库的普通用户

1.3 主库查看同步信息

2. 主从切换

2.1 对主库进行锁表

2.2 检查master同步状态

2.3 检查slave同步状态

2.4 提升slave为master

2.5 将原来master变为slave

3. 主主复制

3.1 主库配置文件

3.2 第二主库配置文件

3.3 总结

热门文章

最新文章

相关课程

相关电子书

推荐镜像