第八章 MySQL 主从复制

1. 介绍

通过二进制日志同步数据的工作模式

2. 应用场景

辅助备份
读写分离
高可用
分布式数据库

3. 扩展架构中间件
  • 3.1 读写分离

Atlas (360)
ProxySQL(percona)
MaxScale (Mariadb)
mysql router

  • 3.2 高可用

MMM (google)
MHA (facebook taobao TMHA )
PXC,MGC
InnoDB Cluster(mysql 8.0强烈推荐)
MySQL cluster

  • 3.3 分布式

Mycat (DBLE)
DRDS
PolarDB

5. 主从复制前提(搭建过程)
  • 5.1 两个或以上数据库实例(主库,从库),两台机器不同server_id

    [root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "select @@server_id"
    [root@db01 ~]# mysql -S /tmp/mysql3307.sock -e "select @@server_id"
    
  • 5.2 主库需要开启二进制日志

    [root@db01 /data/3307]# mysql -S /tmp/mysql3307.sock -e "select @@log_bin"
    
  • 5.3 主库要有专用复制用户(replication slave)

    mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified  by '123'"
    
  • 5.5 从库需要追数据(mysqldump,xbk)

    [root@db01 ~]# mysqldump -A --master-data=2 --single-transaction -S /tmp/mysql3307.sock >/tmp/full.sql
    [root@db01 ~]# mysql -S /tmp/mysql3308.sock </tmp/full.sql
    
  • 5.6 告诉从库复制信息(IP,port,user,password,binlog+pos)

    #### change master to....
    mysql -S /tmp/mysql3308.sock	
    mysql> help change master to
    CHANGE MASTER TO
    MASTER_HOST='10.0.0.51',
    MASTER_USER='repl',
    MASTER_PASSWORD='123',
    MASTER_PORT=3307,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=444,
    MASTER_CONNECT_RETRY=10;
    
  • 5.7 开启复制线程

    start slave;
    [root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "show slave status \G"|grep Running:
    
6. 主从复制工作原理

(1)change master to 命令 ,指定主库的连接信息和复制起点,会被记录到master.info文件中
(2) start slave 开启 从库的 IO 和 SQL线程
(3)IO线程读取master.info,请求连接主库,建立连接后,主库分配一个dump线程和从库IO记性通信.
(5) 从库IO线程通过binlog位置点记录,向主库DUMP请求最新的binlog
(6) 主库截取全新二进制日志事件,DUMP线程发送给从库IO线程
(7) 在网络层面,二进制日志存储到TCP/IP缓存中,从库返回TCP/IP ACK确认
(8)IO线程将接收到的日志,存储到 db01-relay-bin.000001,更新master.info位置点信息.
(9) SQL线程读取relay-log.info,获取到上次执行到位置点,向后回放全新的日志
(10)回放完成后,SQL再次更新relay-log.info
(11) binlog dump实时监控binlog变化,一旦有新的,通知从库.
(12) 从库会定期删除应用过的db01-relay-bin.
在这里插入图片描述

7. 主从复制监控
mysql> show slave status \G
*************************** 1. row ***************************
主库有关信息
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
中继日志执行到的位置点(回放了多少):
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
从库线程状态监控
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
过滤复制有关配置
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
从库回放的relay和主库binlog的对应关系
Exec_Master_Log_Pos: 444
Relay_Log_Space: 526
主从延时时间
Seconds_Behind_Master: 0
延时从库有关配置
SQL_Delay: 0
SQL_Remaining_Delay: NULL
GTID复制有关信息
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
8. 主从复制故障分析及处理
  • 8.1 故障监控

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Last_IO_Errno: 0
    Last_IO_Error: 
    Last_SQL_Errno: 0
    Last_SQL_Error: 
    
  • 8.2 原因

    ##### IO  线程:   
    (1) 连接主库 (connecting)
    连接信息有误
    网络不通
    防火墙
    时间戳
    用户没创建
    skip_name_resolve
    (2) 请求日志 
    日志位置点不对.
    主库日志不完整.
    解决办法:  重新搭建主从
    reset master ;
    停主库业务,等待从完全同步完
    执行命令
    恢复主库:   
    stop slave 
    change master to  000001   154
    start slave;
    (3) 落地日志
    relaylog
    处理方法: 
    停止从库线程
    判断并截取缺失部分日志,恢复到从库
    启动从库线程
    
    ##### SQL 线程
    回放relay :  执行SQL语句
    (1) relay无法访问
    (2) 主从版本,SQL_Mode,参数不一致,系统配置不一致
    (3) 需要创建的对象已经存在 , 要修改的对象不存在
        原因1:  主从复制不一致,导致SQL故障
        原因2:  从库提前写入
    (5) 约束冲突
        主键,唯一键
    方法一:
    stop slave; 
    set global sql_slave_skip_counter = 1;
    #将同步指针向下移动一个,如果多次不同步,可以重复操作。
    start slave;
    方法二:
    /etc/my.cnf
    slave-skip-errors = 1032,1062,1007
    常见错误代码:
    1007:对象已存在
    1032:无法执行DML
    1062:主键冲突,或约束冲突
    从库只读:
    read_only         
    super_read_only      
    
    读写分离中间件
    pt-heartbeat 
    pt-sync 
    pt-checksum
    

    https://www.cnblogs.com/liyongjian5179/p/9008890.html

9. 主从延时
  • 9.1 延时监控

    确认有没有延时
    Seconds_Behind_Master: 0
    监控延时的日志,造成延时的位置点.
    Exec_Master_Log_Pos: 485
    Relay_Log_Space: 857
    
  • 9.2 主从延时原因分析

    • 主库:

    (1) 二进制日志书写不及时
    sync_binlog=1
    (2) 库IO有问题
    binlog和数据分离,尽量ssd存储
    (3)Classic replication中 主库可以并发执行事务,但是dump默认是串行工作的
    高并发时,大事务多的时候,会延时很高 . 开启GTID+row ,可以并行传输日志.
    (5) 业务繁忙时 在线DDL(表结构)
    一般是,手工在主和从分别使用PT工具执行DDL.
    其他原因:
    主库负载过大,从库太多,网络延时抖动等

    • 从库:

    (1)relay-log写入
    最好是单独存储到ssd上
    (2) SQL线程回放慢
    Classic replication中,SQL线程只有一个,只能串行回放relaylog.
    高并发时,大事务多的时候,延时较严重.
    5.6 出现了GTID 技术,可以执行多SQL线程,但是只能基于不同database才能.
    5.7 开启GTID,出现了真正的并行SQL回放功能,MTS,基于事务级别并发回放.logical_clock模式

10. 延时从库 (人为)
  • 作用: 帮助我们解决数据库的逻辑损坏

  • 配置原理: 从库方面SQL线程,根据事件的时间戳顺延延时秒数,进行延时回放

    配置 
    [root@db01 ~]# systemctl start mysqld3309
    [root@db01 ~]# mysqldump -A --master-data=2 --single-transaction -S /tmp/mysql3307.sock 	  >/tmp/full.sql
    [root@db01 ~]# mysql -S /tmp/mysql3309.sock </tmp/full.sql
    vim /tmp/full.sql
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=485;
    #### 添加主库信息
    CHANGE MASTER TO
    MASTER_HOST='10.0.0.51',
    MASTER_USER='repl',
    MASTER_PASSWORD='123',
    MASTER_PORT=3307,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=444,
    MASTER_CONNECT_RETRY=10;
    #### 启动
    start slave;
    #### 延时300秒配置:
    stop slave;
    CHANGE MASTER TO MASTER_DELAY = 300;
    start slave;
    show slave status \G
    
  • 延时从库修复逻辑故障

思路
(1)通过监控发现,drop database 操作
(2)停止从库SQL线程,停止误删除数据业务(挂维护页)
(3)手工模拟SQL线程工作
cat relay-log.info获得起点
分析relaylog日志内容,并drop之前的位置点.
(5) 恢复业务
导出故障库恢复到生产
直接将延时从替代原生产业务库

(1) 准备数据 :
create database relaydb charset=utf8mb4;
use relaydb;
create table t1 (id int);
insert into t1 values(1),(2),(3);
create table t2 (id int);
insert into t2 values(1),(2),(3);
create table t3 (id int);
insert into t3 values(1),(2),(3);
(2) 误删除 :
drop database relaydb; 
(3) 停SQL线程:
mysql> stop slave sql_thread;
(5) 截取relaylog 
起点: 
[root@db01 ~]# cat /data/3309/data/relay-log.info 
./db01-relay-bin.000002
482
终点: 
show relaylog events in 'db01-relay-bin.000002';
db01-relay-bin.000002 | 3180 | Query          |         7 |        3446 | drop database relaydb   
 截取   
mysqlbinlog --start-position=482 --stop-position=3180 /data/3309/data/db01-relay-bin.000002 >/tmp/relay.sql
(6) 3309恢复数据
mysql> source /tmp/relay.sql
(7) 解除3309从库身份
mysql> stop slave;
mysql> reset slave all;
11. 过滤复制
主库 :  
binlog_do_db
binlog_ignore_db
从库: 
replicate_do_db=world
replicate_do_db=test
replicate_ignore_db=
replicate_do_table=world.t1
replicate_ignore_table= 
replicate_wild_do_table=world.t*
replicate_wild_ignore_table=
12. 半同步复制(介绍)

解决什么问题?
插件功能.
主从数据一致性问题
和传统复制不同的点是: 从库IO收到日志,并将其写入relaylog文件后,发送一个ACK确认给主库ACK_reciver
如果从库10秒没有回应ACK,主库就会将复制切换为传统复制
PXC percona xtradb cluster
MGR MySQL Group Replication

  • 工作原理:

1.主库执行新的事务,commit时,更新 show master status\G ,触发一个信号给
2.binlog dump 接收到主库的 show master status\G信息,通知从库日志更新了
3.从库IO线程请求新的二进制日志事件
4.主库会通过dump线程传送新的日志事件,给从库IO线程
5.从库IO线程接收到binlog日志,当日志写入到磁盘上的relaylog文件时,给主库ACK_receiver线程
6.ACK_receiver线程触发一个事件,告诉主库commit可以成功了
7.如果ACK达到了我们预设值的超时时间,半同步复制会切换为原始的异步复制.

  • 配置

    加载插件
    主:
    INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
    从:
    INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
    查看是否加载成功:
    show plugins;
    启动:
    主:
    SET GLOBAL rpl_semi_sync_master_enabled = 1;
    从:
    SET GLOBAL rpl_semi_sync_slave_enabled = 1;
    重启从库上的IO线程
    STOP SLAVE IO_THREAD;
    START SLAVE IO_THREAD;
    查看是否在运行
    主:
    show status like 'Rpl_semi_sync_master_status';
    从:
    show status like 'Rpl_semi_sync_slave_status';
    
13. GTID复制
(1) 清理环境
pkill mysqld
\rm -rf /data/mysql/data/*
\rm -rf /data/binlog/*
(2) 准备配置文件
主库db01:
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
slave1(db02):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
slave2(db03):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
(3) 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql/data 
(5) 启动数据库
/etc/init.d/mysqld start
(6) 构建主从:
master:51
slave:52,53
51:
grant replication slave  on *.* to repl@'10.0.0.%' identified by '123';
52\53:
change master to 
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值