MySQL8.4主从复制

发布于：2025-05-29 ⋅ 阅读:(19) ⋅ 点赞:(0)

参考资料：

https://dev.mysql.com/doc/refman/8.4/en/replication.html

1 什么是复制
  • MySQL Replication是官方提供的主从同步方案，是使用最广的同步方案；
  • Replication使来自于一个MySQL数据库服务器（源Source）的数据能够复制到一个或者多个MySQL服务器（副本）；
  • 默认情况下，复制是异步的；
  • 副本不需要永久连接即可从源接收更新
  • 可以指定库，指定表进行复制
  • 根据主从复制的特点，可以实现MySQL读写分离

2 复制的优势&缺点
2.1 复制的优势
  • 高可用：通过复制机制，MySQL实现跨主机的数据复制，从而获得一定的高可用能力
  • 性能扩展：通过配置多个副本实现读写分离，整体提升读写性能
  • 异地容灾：通过配置异机房异地副本，实现数据容灾

2.2 复制的缺点
  • 主库宕机时不支持自动切换，需要手动切换，存在单点问题
  • 主库与从库建同步数据在延迟问题，容易造成数据不一致问题
  • 从库过多是对主库的负载以及网络带宽都会带来很大的负担
  • 读写分离时需要业务方明确配置读库和写库

2 复制的方式
  • 基于binlog位置点进行复制

https://dev.mysql.com/doc/refman/8.4/en/binlog-replication-configuration-overview.html

  • 基于GTID的复制

https://dev.mysql.com/doc/refman/8.4/en/replication-gtids.html

  • 以上两种复制方式都是通过binlog同步实现数据同步，只不过找需要同步binlog的方式不同
3 复制同步数据的类型
3.1 异步复制
  • 默认情况下，MySQL采用异步复制的方式
  • 即执行事务的线程不会等复制binlog的线程

  • MySQL主库接收到客户端提交事务的请求后，先写入binlog，再提交事务，更新存储引擎中的数据
  • 事务提交完成后，给客户端返回操作成功的响应
  • 同时，从库会有一个专门的复制线程，从主库接收Binlog，然后把binlog写入中继日志，再返回给主库复制成功的响应
  • 从库还有另一个回放binlog的线程，读取中继日志，然后回放binlog更新存储引擎中的数据
  • 主句提交事务线程和复制数据线程是两个单独的线程，互相不会等待，这就是异步复制
  • 异步复制的劣势在于存在主从延迟，如果主节点宕机，可能会丢数据

3.2 半同步复制

从MySQL5.7开始增加了半同步复制方式，其与异步复制相比主要区别如下：

  • 主节点在收到客户端的请求后，必须在完成本节点日志写入的同时，需要等待至少一个从节点完成数据同步的响应后，才会给客户端响应
  • 从节点只有在写入relay log并完成刷盘之后，才会向主节点响应
  • 从节点响应超时时，主节点会将同步机制退化为异步复制，在至少一个从节点恢复，并完成数据追赶后，主节点会将同步机制恢复为半同步复制

相比较于异步复制，半同步复制的区别在于

  • 在一定程度上提高了数据的可用性
  • 在未退化至异步复制时，如果主节点宕机，此时数据至少已复制至一套从节点
  • 向客户端响应时需要至少一台从节点完成响应，整体性能会有所降低

半同步复制参数

  • rpl_semi_sync_source_wait_for_replica_count：至少等待数据复制到几个从节点再返回，这个数据配置的越大，丢数据风险越小，但是集群的性能和可用性就越差
  • rpl_semi_sync_source_wait_point：控制主库执行事务的线程，在提交事务之前（AFTER_SYNC）等待复制还是在提交事务之后(AFTER_COMMIT)等待复制，默认是AFTER_SYNC，也就是先等待复制，再提交事务，这样可以保证数据的一致性

4 设计理念-复制状态机
  • 在MySQL中，无论是复制还是备份恢复，依赖的都是全量备份+binlog；
  • 全量备份相当于备份那一时刻的一个数据快照；
  • Binlog则记录了每次数据更新的变化，也就是操作日志；
  • 基于快照+操作日志的方法，并不是MySQL特有的
  • 比如Redis Cluster中，它的全量备份称为Snapshot，操作日志叫backlog
  • redis主从复制方式几乎和MySQL是一模一样的
  • Elastcisearch的是translog

  • 复制数据时，只要基于一个快照，按照顺序执行快照之后的所有操作日志，就可以获得一个完全一样的状态
  • 在从节点持续地从主节点上复制操作日志并执行，就可以让从节点上的状态数据与主节点保持同步
  • 主从同步做数据复制时，一般可以采用几种复制策略
  • 异步复制
    • 主节点上线记录操作日志，再更新状态数据
    • 然后异步把操作日志复制到所有从节点上，并在从节点执行操作日志，得到和主节点相同的状态数据
    • 性能最好
    • 可能存在主从延迟
    • 主节点宕机时，可能会丢数据
  • 半同步复制
    • 主节点等待操作日志最少复制到N个节点之后，再更新状态
    • 在性能，高可用，数据可靠性等几方面取得平衡

5 基于binlog位点复制

https://dev.mysql.com/doc/refman/8.4/en/binlog-replication-configuration-overview.html

5.1 复制原理

  • 主库会生成多个binlog日志文件
  • 从库的I/O线程请求指定文件和指定位置的binlog日志
  • 主库dump线程按照从库发送给来的位点信息读取binlog，然后推送binlog给从库
  • 从库I/O线程将得到的binlog写到本地relay log文件中
  • 从库的SQL线程读取和解析relay log文件
  • 从库的SQL线程重放relay log中sql
5.2 示例
5.2.1 准备主库
  • 准备目录
mkdir -p /opt/mysql/6666/{data,log,tmp}

  • 准备配置文件
vi /opt/mysql/6666/my.cnf

[mysql]
#设置mysql客户端默认编码
default-character-set=utf8
[mysqld]
port=6666
pid-file= /opt/mysql/6666/mysqld.pid
socket= /opt/mysql/6666/mysqld.sock
datadir= /opt/mysql/6666/data
log-error=/opt/mysql/6666/mysqld.log
tmpdir=/opt/mysql/6666/tmp
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#服务器唯一ID，默认是1
server-id=10
#启用二进制日志
log-bin=mysql-bin
#最大连接数
max_connections=1000
#设置默认时区
default-time_zone='+8:00'
# 0:区分大小写
# 1:不区分大小写
lower_case_table_names=1
user = mysql

  • 初始化库
chown -R mysql:mysql /opt/mysql/6666
mysqld --defaults-file=/opt/mysql/6666/my.cnf --initialize

  • 启库
mysqld --defaults-file=/opt/mysql/6666/my.cnf &

  • 初始化账号密码
#查看临时密码
cat /opt/mysql/6666/mysqld.log|grep "A temporary password"
#登录
mysql -uroot -p'-251LZNGo;RF' --socket=/opt/mysql/6666/mysqld.sock
>ALTER USER 'root'@'localhost' identified by '123456';
>flush privileges;

  • 创建同步用户
 mysql -uroot -p'123456' --socket=/opt/mysql/6666/mysqld.sock
 >CREATE USER 'repl'@'%' IDENTIFIED  BY '123456';
 >GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
 >flush privileges;

5.2.2 准备从库
  • 准备目录
mkdir -p /opt/mysql/6667/{data,log,tmp}

  • 准备配置文件
vi /opt/mysql/6667/my.cnf

[mysql]
#设置mysql客户端默认编码
default-character-set=utf8
[mysqld]
port=6667
pid-file= /opt/mysql/6667/mysqld.pid
socket= /opt/mysql/6667/mysqld.sock
datadir= /opt/mysql/6667/data
log-error=/opt/mysql/6667/mysqld.log
tmpdir=/opt/mysql/6667/tmp
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#服务器唯一ID，默认是1
server-id=11
#启用二进制日志
log-bin=mysql-bin
#最大连接数
max_connections=1000
#设置默认时区
default-time_zone='+8:00'
# 0:区分大小写
# 1:不区分大小写
lower_case_table_names=1
user = mysql

  • 初始化库
chown -R mysql:mysql /opt/mysql/6667
mysqld --defaults-file=/opt/mysql/6667/my.cnf --initialize

  • 启库
mysqld --defaults-file=/opt/mysql/6667/my.cnf &

  • 初始化账号密码
#查看临时密码
cat /opt/mysql/6667/mysqld.log|grep "A temporary password"
#登录
mysql -uroot -p'MJjAjbd8dw(o' --socket=/opt/mysql/6667/mysqld.sock
>ALTER USER 'root'@'localhost' identified by '123456';
>flush privileges;

5.2.3 配置主从
  • 查看主库binlog信息
mysql -uroot -p'123456' --socket=/opt/mysql/6666/mysqld.sock
>SHOW BINARY LOG STATUS\G
  • 从库配置主库信息
mysql -uroot -p'123456' --socket=/opt/mysql/6667/mysqld.sock
> CHANGE REPLICATION SOURCE TO
        SOURCE_HOST='192.168.221.100',
		SOURCE_PORT=6666,
        SOURCE_USER='repl',
        SOURCE_PASSWORD='123456',
        SOURCE_LOG_FILE='mysql-bin.000002',
        SOURCE_LOG_POS=1344,
        GET_SOURCE_PUBLIC_KEY=1;

  • 开启主从同步
mysql -uroot -p'123456' --socket=/opt/mysql/6667/mysqld.sock
> start replica;

  • 查看主从同步状态
mysql -uroot -p'123456' --socket=/opt/mysql/6667/mysqld.sock
> show replica status\G

5.2.4 主从测试
  • 主库添加数据
mysql -uroot -p'123456' --socket=/opt/mysql/6666/mysqld.sock
>  create database test;
   use test;
   create table test (id int,name varchar(10));
   insert into test values(1,'a'),(2,'b'),(3,'c');

  • 从库验证数据
mysql -uroot -p'123456' --socket=/opt/mysql/6667/mysqld.sock
> show database;
  use test;
  select * from test;

5.3 基于binlog位点复制缺点
  • 首次配置主从复制时需在主库查看binlog信息，并在从库配置开始复制binlog位点信息。操作比较麻烦
  • 当主从复制异常复制中断时，再次配置主从同步时
    • 需要查找同步中断的binlog位点信息，再次设置开始同步点
    • 再次开始同步时，如果因为数据不一致同步数据发生异常时，需要识别异常原因，并设置slave_skip_errors同步跳过错误码继续进行同步，此时会将某一类型错误全部跳过，容易出现不可预期的问题

6 基于GTID复制

https://dev.mysql.com/doc/refman/8.4/en/replication-gtids.html

6.1 GTID复制原理
  • GTID是MySQL全局事务ID
  • 由server_uuid+transaction id组成
  • GTID不仅在主数据库中唯一，并且在由主从集群中也是唯一的

  • 从库中配置主库连接信息，开启主从同步建立连接
  • 从库将已同步主库的GTID集合发送给主库
  • 主库根据自己已生成的GTID集合和从库发过来的GTID集合计算差集
  • 主库从自己的binlog文件中找GTID差集的binlog信息
  • 然后将找到的binlog推送给从库
  • 从库的I/O现成读取binlog生成relay log文件，SQL线程解析relay log，然后执行SQL语句

6.2 GTID同步优势
  • 不需要要人工去找需要同步的位点信息
  • 当同步发生异常时，可以明确找到哪些事物发生的异常，并且可以跳过指定事务继续同步，不会引发其他的问题
6.3 示例
6.3.1 准备主库
  • 准备目录
mkdir -p /opt/mysql/8888/{data,log,tmp}

  • 准备配置文件
vi /opt/mysql/8888/my.cnf

[mysql]
#设置mysql客户端默认编码
default-character-set=utf8
[mysqld]
port=8888
pid-file= /opt/mysql/8888/mysqld.pid
socket= /opt/mysql/8888/mysqld.sock
datadir= /opt/mysql/8888/data
log-error=/opt/mysql/8888/mysqld.log
tmpdir=/opt/mysql/8888/tmp
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#服务器唯一ID，默认是1
server-id=10
#启用二进制日志
log-bin=mysql-bin
#最大连接数
max_connections=1000
#设置默认时区
default-time_zone='+8:00'
# 0:区分大小写
# 1:不区分大小写
lower_case_table_names=1
user = mysql
#启用全局事务标识符（GTID）模式
gtid_mode=on
#强制GTID的一致性。这意味着在执行事务时，MySQL将确保所有涉及的服务器都使用相同的GTID集。
enforce_gtid_consistency=on

  • 初始化库
chown -R mysql:mysql /opt/mysql/8888
mysqld --defaults-file=/opt/mysql/8888/my.cnf --initialize

  • 启库
mysqld --defaults-file=/opt/mysql/8888/my.cnf &

  • 初始化账号密码
#查看临时密码
cat /opt/mysql/8888/mysqld.log|grep "A temporary password"
#登录
mysql -uroot -p')AP#kv:VN4Sv' --socket=/opt/mysql/8888/mysqld.sock
>ALTER USER 'root'@'localhost' identified by '123456';
  flush privileges;

  • 创建同步用户
 mysql -uroot -p'123456' --socket=/opt/mysql/6666/mysqld.sock
 >CREATE USER 'repl'@'%' IDENTIFIED  BY '123456';
  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  flush privileges;

6.3.2 准备从库
  • 准备目录
mkdir -p /opt/mysql/8889/{data,log,tmp}

  • 准备配置文件
vi /opt/mysql/8889/my.cnf

[mysql]
#设置mysql客户端默认编码
default-character-set=utf8
[mysqld]
port=8889
pid-file= /opt/mysql/8889/mysqld.pid
socket= /opt/mysql/8889/mysqld.sock
datadir= /opt/mysql/8889/data
log-error=/opt/mysql/8889/mysqld.log
tmpdir=/opt/mysql/8889/tmp
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#服务器唯一ID，默认是1
server-id=11
#启用二进制日志
log-bin=mysql-bin
#最大连接数
max_connections=1000
#设置默认时区
default-time_zone='+8:00'
# 0:区分大小写
# 1:不区分大小写
lower_case_table_names=1
user = mysql
#启用全局事务标识符（GTID）模式
gtid_mode=on
#强制GTID的一致性。这意味着在执行事务时，MySQL将确保所有涉及的服务器都使用相同的GTID集。
enforce_gtid_consistency=on

  • 初始化库
chown -R mysql:mysql /opt/mysql/8889
mysqld --defaults-file=/opt/mysql/8889/my.cnf --initialize

  • 启库
mysqld --defaults-file=/opt/mysql/8889/my.cnf &

  • 初始化账号密码
#查看临时密码
cat /opt/mysql/8889/mysqld.log|grep "A temporary password"
#登录
mysql -uroot -p'%*jqFI-#9p5l' --socket=/opt/mysql/8889/mysqld.sock
>ALTER USER 'root'@'localhost' identified by '123456';
 flush privileges;

6.3.3 配置主从
  • 从库配置主库信息
mysql -uroot -p'123456' --socket=/opt/mysql/8889/mysqld.sock
>  CHANGE REPLICATION SOURCE TO
         SOURCE_HOST='192.168.221.100',
		 SOURCE_PORT=8888,
         SOURCE_USER='repl',
         SOURCE_PASSWORD='123456',
         SOURCE_AUTO_POSITION = 1,
		 GET_SOURCE_PUBLIC_KEY=1;

  • 开启主从同步
mysql -uroot -p'123456' --socket=/opt/mysql/8889/mysqld.sock
> start replica;

  • 查看主从同步状态
mysql -uroot -p'123456' --socket=/opt/mysql/8889/mysqld.sock
> show replica status\G
6.3.4 主从测试
  • 主库添加数据
mysql -uroot -p'123456' --socket=/opt/mysql/8888/mysqld.sock
>  create database test;
   use test;
   create table test (id int,name varchar(10));
   insert into test values(1,'a'),(2,'b'),(3,'c');

  • 从库验证数据
mysql -uroot -p'123456' --socket=/opt/mysql/8889/mysqld.sock
>  use test;
   select * from test;
6.3.5 主从同步异常模拟
  • test表增加主键
mysql -uroot -p'123456' --socket=/opt/mysql/8888/mysqld.sock
>  ALTER TABLE test ADD PRIMARY KEY id(id);

  • 从库添加id为4的数据
mysql -uroot -p'123456' --socket=/opt/mysql/8889/mysqld.sock
> insert into test values (4,'d');

  • 主库添加id为4的数据
mysql -uroot -p'123456' --socket=/opt/mysql/8888/mysqld.sock
> insert into test values (4,'d');

  • 查看主从同步状态

  • 从库跳过指定gtid
mysql -uroot -p'123456' --socket=/opt/mysql/8889/mysqld.sock
> stop replica sql_thread; --停止从库SQL线程
  SET @@SESSION.GTID_NEXT= '2acbb02a-3be0-11f0-b760-000c298f539e:10'; --上一步查询出来出错的gtid信息
  begin;commit;  -- 空事务
  SET GTID_NEXT='AUTOMATIC'; -- 还原GTID设置
  start replica sql_thread; --开启从库SQL线程
  show replica status\G;  --查看主从状态
7 半同步复制

https://dev.mysql.com/doc/refman/8.4/en/replication-semisync-installation.html

7.1 主库开启半同步复制
  • 安装插件
mysql -uroot -p'123456' --socket=/opt/mysql/6666/mysqld.sock
> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
  • 开启半同步复制
mysql -uroot -p'123456' --socket=/opt/mysql/6666/mysqld.sock
> SET GLOBAL rpl_semi_sync_source_enabled=1;
   show variables like "%semi_sync%";
7.2 从库开启半同步复制
  • 安装插件
mysql -uroot -p'123456' --socket=/opt/mysql/6667/mysqld.sock
> INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
  • 开启半同步复制
mysql -uroot -p'123456' --socket=/opt/mysql/6667/mysqld.sock
> SET GLOBAL rpl_semi_sync_replica_enabled=1;
   show variables like "%semi_sync%";
7.3 从库重启IO线程
mysql -uroot -p'123456' --socket=/opt/mysql/6667/mysqld.sock
> STOP REPLICA IO_THREAD;
   START REPLICA IO_THREAD;

7.4 半同步复制测试
  • 修改主库配置binlog同步两个从节点再响应
mysql -uroot -p'123456' --socket=/opt/mysql/6666/mysqld.sock
> set global rpl_semi_sync_source_wait_for_replica_count=2;
  • 主库增加数据测试

数据插入一直卡着，直至达到设置的超时时间才返回相应。

8 延迟复制
  • 配置主从时增加SOURCE_DELAY 配置
  • 延迟复制只是延迟SQL线程执行SQL时间，I/O现成会及时同步数据

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布