mysql_9 主从复制

mysql_9 主从复制

标签（空格分隔）： mysql

---

介绍

两台或以上数据库实例，通过二进制日志，实现数据的同步关系

前提（搭建过程）

• 时间同步
• 2台实例，server_id 不同，不同的角色划分（主库、从库）、网络畅通
• 主库开binlog、专门开启一个复制用户
• 开启专用复制线程（默认开启）
• 从库"补课" 数据、 主库的连接信息、确认复制的起点
• 从库 开启专用的复制线程

搭建

实例准备 多实例
systemctl start mysql3306
systemctl start mysql3307
systemctl start mysql3308

检查关键信息

检查server_id
mysql -S /tmp/mysql3306.sock -e "select @@server_id"
mysql -S /tmp/mysql3307.sock -e "select @@server_id"
mysql -S /tmp/mysql3308.sock -e "select @@server_id"

检查binlog
mysql -S /tmp/mysql3306.sock -e "select @@log_bin"

主库建立复制用户

mysql -S /tmp/mysql3306.sock -e "grant replication slave on . to repl@'10.0.%' identified by '123' "
mysql -S /tmp/mysql3306.sock -e "select user,host from mysql.user"

主库备份恢复到从库

备份
mysqldump -S /tmp/mysql3306.sock -A --master-data=2 --single-transaction > /tmp/full.sql

恢复到从库中
mysql -S /tmp/mysql3307.sock < /tmp/all.sql
mysql -S /tmp/mysql3308.sock < /tmp/all.sql

告知从库信息

CHANGE MASTER TO
 MASTER_HOST='master2.mycompany.com',    #地址
 MASTER_USER='replication',             # 复制用户
 MASTER_PASSWORD='bigs3cret',               #密码
 MASTER_PORT=3306,                       # 端口号
 MASTER_LOG_FILE='master2-bin.001',      #从哪里开始复制
 MASTER_LOG_POS=4, 
 MASTER_CONNECT_RETRY=30;

主库的all sql 中有从库该从哪里复制
grep "-- CHANGE MASTER TO" /tmp/all.sql

开启专用的复制线程

mysql -S /tmp/mysql3307.sock
start slave
mysql -S /tmp/mysql3308.sock
start slave

验证主从的状态

show slave status
两个yes 表示主从成功

如果失败 去除主从

stop slave;
reset slave all;

主从复制的原理

主从复制涉及到的资源

文件

主库：binlog文件
从库：
relay-log文件： 存储接受主库的binlog
db01-relay-bin.000001
db01-relay-bin.000002
手动定义 show variables like '%relay%'
relay_log_basename= 数据目录/db01-relay-bin

master.info : 连接主库的信息,已经接收到的binlog位置点信息
    默认存储在从库的数据路径下
    master_info_repository= FILE/TABLE       #建议用table

relay.info ： 记录从库会放到的relay-log的位置点
    默认在从库的数据路径下
    relay-log.info
    手动定义 show variables like '%relay%'
    relay_log_info_repository = FILE/TABLE

线程

主库：
Binlog_dump_Thread:
作用：用于接受从库的请求，并且返回binlog日志给从库。
show PROCESSLIST 可以看见

从库:

IO 线程：
请求binlog日志，接受日志

SQL 线程：
回放日志

主从复制原理

1. change master to
   提供了 ip 端口 用户 密码 binlog位置点 会被存储到master.info 文件，执行 start slave 启动sql线程和io线程

2. 连接主库

3. master 分配一个Binlog_dump_Thread 专门用于和从库io线程进行通信

4. 从库的io线程：请求新的日志

5. master binlog_dump_Thread 接受请求 截取日志 返回给 从库的io线程

6. 从库io线程接收到binlog，日志放在tcp/ip，此时网络层面返回ack给主库，主库工作完成

7. 从库io线程将binlog最终写入到relaylog中，并更新主库master.info文件 io线程工作结束

8. 从库 sql线程 读取relay.info 获取上次执行到的位置点

9. 从库 sql线程 向后执行新的relay-log

10. relay-log 参数 relay_log_purge=on 定期删除应用过的relay-log

11. master binlog_dump_Thread 线程实时监控主库的binlog变化，如果有变化，发信号给从库

主从监控

主

show processlist

查看dump线程的情况

show slave hosts

备

show slave status G

主库信息，来自于master.info

从库的relay-log的执行情况，来自于R.info 一般用来判断主从延时

从库线程状态，具体报错信息

过滤复制相关信息

延迟从库的配置信息

gtid相关复制信息

主从故障分析及处理

监控方法

show slave status G

Slave_IO_State: Waiting for master to send event
Master_Host: 130.17.180.71
Master_User: cpc
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 116932874
Relay_Log_File: WINDOWS-V2DDMKC-relay-bin.000004
Relay_Log_Pos: 336557
Relay_Master_Log_File: mysql-bin.000001

io线程
Slave_IO_Running: Yes #正常状态
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

Slave_IO_Running io线程

正常 yes
非正常 no connection
连接主库：
1.网络不正常、防火墙、端口
2.用户 、 密码 不对
relication slave 权限的
3.主库的连接数量上限
4.版本不统一 5.7 native 8.0 sha2
故障模拟：
主从中的线程管理:
start slave # 启动所有线程
stop slave # 关闭所有线程
start slave sql_thread # 单独启动SQL线程
start slave io_thread # 单独启动io线程
stop slave sql_thread # 单独关闭SQL线程
stop slave io_thread # 单独关闭io线程
解除从库身份:
reset slave all
show slave status

请求日志，接受日志

主库二进制日志不完整、损坏、不连续
从库请求的起点问题
主从的server_id（server_uuid）相同
relaylog问题

故障模拟：
主库： reset master;

从库就会报错: last_io_error

正确的做法：
找个不繁忙的时间，
主库：
reset master;
show master status;
备库：
stop slave ;
reset slave all;

    CHANGE MASTER TO
    MASTER_HOST='master2.mycompany.com',    #地址
    MASTER_USER='replication',             # 复制用户
    MASTER_PASSWORD='bigs3cret',               #密码
    MASTER_PORT=3306,                       # 端口号
    MASTER_LOG_FILE='master2-bin.001',      #从哪里开始复制
    MASTER_LOG_POS=4, 
    MASTER_CONNECT_RETRY=30;

等待从库重放完所有主库日志

主库 reset master

从库重新同步主库日志

sql线程故障

主要做什么工作？
回放relay-log中的日志。可以理解为执行relay-log SQL
SQL线程故障本质

为什么SQL线程执行不了SQL语句
原理整理
创建的对象已经存在了。
需要操作的对象不存在
约束冲突。
SQL_MODE,参数、版本

举个例子：
搞错了 在备库上 create databases t1;
在 主库上再 create databases t1;
主库上再建表 create table t2(id int);
发现主从断了 show slave status

解决：
方法1：
从库删掉新建的库，重新start slave;
方法2： 跳过

    stop slave;
    set global sql slave_skip_counter=1;
    start slave
    
    /etc/my.cnf
    slave-skip-errors = 1032,1062,1007
    
    1007:对象已存在
    1032:无法执行DML
    1062：主键冲突，或约束冲突
    
    
pt 第三方工具

终极办法：
重新搭建主从，备份恢复+重新构建

主库出了问题怎么办？

物理    
    1.看主库是否能ssh连接
    2.检查一下binlog是否完整
    3.手动追加日志到最新位置
    4.从库代替主库工作

逻辑 drop
    只能通过备份去恢复

双主情况下，一个宕机了，过了很久，另一个也宕机了 如何恢复

只能备份+binlog恢复

从库怎么称当主库
1.修复最新状态
2.清空从库身份
3.清空binlog日志信息

避免从库进行操作

从库只读 #打开这个选项 所有的普通用户 全部不能写入
select @@read_only
select @@super_read_only

中间件
隔离从库的写操作

主从延迟方面

主库发生了操作，从库很久才跟上来

show slave status;

Second_Behind_Master:0 #从库落后于主库的时间

这个指标只能得出：又或者没有延迟的情况，等于0，不代表没有延迟。

真正评估主从复制延迟的更加精确的指标是：
延迟了多少日志量
主库执行的日志量，从库执行的日志的对比

如何查看：
    主库：binlog位置点
    
    
    从库：relay执行的位置点

如何计算延迟的日志量

主库：
show master status;
备库：
show slave status;
cat 从库目录下的/relay-log.info

position 号 就是字节量 两者相减就是差的字节量

为什么会主从延迟

可以一主三从 从在接2从

主库：
外部： 网络、硬件配置、主库业务繁忙、从库太多

    主库业务繁忙：
        1.拆分业务（分布式）： 组件分离、垂直、水平
        2.大事务的拆分 比如1000w业务，拆分为20次执行。

内部：

二进制日志更新。 sync_binlog=1
5.7之前 没有开启gtid dump线程 是串行的 一个一个给的容易出现性能问题（主库的事务是并行的，日志量大）
5.6 + 开gtid（有了gtid 事务全局唯一了 所以不在乎并发时顺序） dump线程 改为并行的
5.7 + 版本 无需手动开启，系统会自动生成匿名的gtid信息

还是需要尽可能减少大事务，以及锁影响

从库

外部：

网络、硬件配置、参数设定。

内部：

io线程
    写relay-log --> io性能
sql线程
    回放sql
    默认在非GTID下是串行的 一个一个执行的容易出现性能问题（主库的事务、传输是并行的，日志量大）
    还是需要开启gtid 才可以使他变成并行的，进行多线程回放sql
    5.6+ GTID: 基于库级别的SQL线程并发。
    5.7+ 版本 Logic_clock 逻辑时钟。保证了在同库级别下的事务顺序问题。
    所以可以理解为基于事务级别的并发回放。MTS
    
即使有以上的自带的优化机制。还是需要尽可能减少大事务，以及锁影响