美文网首页
MySQL--主从复制高级进阶

MySQL--主从复制高级进阶

作者: linux_豪哥 | 来源:发表于2019-09-29 15:47 被阅读0次

如果您对数据库感兴趣，可以添加 DBA解决方案QQ群：855439640

1. 延时从库

1.1介绍

是我们认为配置的一种特殊从库.人为配置从库和主库延时N小时.

1.2 为什么要有延时从

数据库故障?
物理损坏
主从复制非常擅长解决物理损坏.
逻辑损坏
普通主从复制没办法解决逻辑损坏

1.3 配置延时从库

SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300                     从库延时时间300S
SQL_Remaining_Delay: NULL          最近的一个事件剩余时间

1.4 延时从库应用

1.4.1 故障恢复思路

(1) 监控到数据库逻辑故障
(2) 停从库SQL线程,记录已经回放的位置点(截取日志起点)
    stop slave sql_thread ;
    show slave status \G
    Relay_Log_File: db01-relay-bin.000002
    Relay_Log_Pos: 320

(3) 截取relaylog
    起点:     
    show slave status \G
    Relay_Log_File ,Relay_Log_Pos
    
    终点: drop之前的位置点
    show relaylog events in ''  
    进行截取
(4) 模拟SQL线程回访日志
    从库  source 
(5) 恢复业务
    情况一: 就一个库的话
    从库替代主库工作
    情况二: 
    从库导出故障库,还原到主库中.

1.4.2 故障模拟及恢复

1.主库数据操作
db01 [(none)]>create database relay charset utf8;
db01 [(none)]>use relay
db01 [relay]>create table t1 (id int);
db01 [relay]>insert into t1 values(1);
db01 [relay]>drop database relay;

2. 停止从库SQL线程

stop slave sql_thread;
  1. 找relaylog的截取起点和终点
relay的起点:
db01 [(none)]>show slave status\G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 498

relay的终点:
show relaylog events in 'db01-relay-bin.000002'
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name              | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| db01-relay-bin.000002 |   4 | Format_desc    |         8 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000002 | 123 | Previous_gtids |         8 |         154 |                                       |
| db01-relay-bin.000002 | 154 | Rotate         |         7 |           0 | mysql-bin.000015;pos=234              |
| db01-relay-bin.000002 | 201 | Format_desc    |         7 |           0 | Server ver: 5.7.26-log, Binlog ver: 4 |
| db01-relay-bin.000002 | 320 | Anonymous_Gtid |         7 |         299 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| db01-relay-bin.000002 | 385 | Query          |         7 |         412 | create database delay charset utf8mb4 |
| db01-relay-bin.000002 | 498 | Anonymous_Gtid |         7 |         477 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| db01-relay-bin.000002 | 563 | Query          |         7 |         576 | use `delay`; create table t1(id int)  |
| db01-relay-bin.000002 | 662 | Anonymous_Gtid |         7 |         641 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| db01-relay-bin.000002 | 727 | Query          |         7 |         714 | BEGIN                                 |
| db01-relay-bin.000002 | 800 | Table_map      |         7 |         760 | table_id: 108 (delay.t1)              |
| db01-relay-bin.000002 | 846 | Write_rows     |         7 |         810 | table_id: 108 flags: STMT_END_F       |
| db01-relay-bin.000002 | 896 | Xid            |         7 |         841 | COMMIT /* xid=31 */                   |
| db01-relay-bin.000002 | 927 | Anonymous_Gtid |         7 |         906 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| db01-relay-bin.000002 | 992 | Query          |         7 |        1001 | drop database delay                   |
+-----------------------+-----+----------------+-----------+-------------+---------------------------------------+
 
截取relay日志
mysqlbinlog --start-position=498 --stop-position=992  /data/3308/data/db01-relay-bin.000002>/tmp/relay.sql
  1. 从库恢复relaylog
set sqo_log_bin=0;
source /tmp/relay.sql

5.从库身份解除

db01 [relay]>stop slave;
db01 [relay]>reset slave all

6.恢复主从关系

可以直接把业务迁到之前的从库
也可以把误删除的数据 恢复到主库 从新构建主从
mysqldump -uroot -B delay -S /data/3308/mysql.sock -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/tmp/full.sql

主库
set sql_log_bin=0;
source /tmp/full.sql;

获取主库二进制日志位置
mysql -uroot -S /data/3307/mysql.sock
show master status;
| mysql-bin.000015 |     1001 |              |      

从库构建主从
mysql -uroot -S /data/3308/mysql.sock
CHANGE MASTER TO 
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000015',
MASTER_LOG_POS=1001,
MASTER_CONNECT_RETRY=10;

从库开启
start slave；

2. 半同步 ***

解决主从数据一致性问题

2.1 半同步复制工作原理的变化

1\. 主库执行新的事务,commit时,进行阻碍和等待
2\. 从库IO线程请求新的二进制日志事件
3\. 主库会通过dump线程传送新的日志事件,给从库IO线程
4\. 从库IO线程接收到binlog日志,当日志写入到磁盘上的relaylog文件时,给主库ACK_receiver线程发送确认
5\. 主库的ACK_receiver(5.7)接收到一个从库的确认已接收到该事务的所有事件为止,告诉主库commit可以成功了
6\. 如果从库的ACK达到了我们预设值的超时时间(默认10秒),半同步复制会切换为原始的异步复制.
半同步会影响性能，因为有等待从库确认的时间

2.2 配置半同步复制

加载插件
主:
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;
rpl_semi_sync_master_timeout   超时时间单位是毫秒 默认10000（10秒）
从:
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';

3 . 过滤复制

3.1 说明

主库：

show master status;
Binlog_Do_DB
只记录某个库的binlog日志
Binlog_Ignore_DB 
不记录某个库的binlog日志

从库：

show slave status\G
Replicate_Do_DB: 
只回放某个库的日志
Replicate_Ignore_DB: 
不回放某个库的日志

3.2 实现过程

mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction  -R --triggers >/backup/full.sql

vim  /backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;

[root@db01 ~]# mysql -S /data/3309/mysql.sock 
source /backup/full.sql

CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start  slave;
[root@db01 ~]# vim /data/3309/my.cnf 
replicate_do_db=ppt
replicate_do_db=word
[root@db01 ~]# systemctl restart mysqld3309

主库：
Master [(none)]>create database word;
Query OK, 1 row affected (0.00 sec)
Master [(none)]>create database ppt;
Query OK, 1 row affected (0.00 sec)
Master [(none)]>create database excel;
Query OK, 1 row affected (0.01 sec)

4. GTID复制

4.1 GTID引入

4.2 GTID介绍

GTID(Global Transaction ID)是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
什么是sever_uuid，和Server-id 区别？
核心特性: 全局唯一,具备幂等性

4.3 GTID核心参数

重要参数：

gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

gtid-mode=on                        --启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true               --强制GTID的一致性
log-slave-updates=1                 --slave更新是否记入日志


log-slave-updates
1、从库只开启log-bin功能，不添加log-slave-updates参数，从库从主库复制的数据不会写入log-bin日志文件里。
2、直接向从库写入数据时，是会写入log-bin日志的。
3、开启log-slave-updates参数后，从库从主库复制的数据会写入log-bin日志文件里。这也是该参数的功能。

4.4 GTID复制配置过程：

4.4.1 清理环境

pkill mysqld
 \rm -rf /data/mysql/data/*
 \rm -rf /data/binlog/*

4.4.2 准备配置文件

主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/data/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=/data/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=/data/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

4.4.3 初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/data/mysql  --datadir=/data/mysql/data

4.4.4 启动数据库

/etc/init.d/mysqld start

4.4.5 构建主从：

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;

MASTER_AUTO_POSITION=1    自动获取主库的gtid号，从库会对比relaylog回放的最后一个gtid去请求

4.5 GTID 从库误写入操作处理

查看监控信息:
Last_SQL_Error: Error 'Can't create database 'oldboy'; database exists' on query. Default database: 'oldboy'. Query: 'create database oldboy'

Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set:  71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1

注入空事物的方法：

stop slave;
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='AUTOMATIC';

这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。
最好的解决方案：重新构建主从环境

4.6 GTID 复制和普通复制的区别

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;

change master to 
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;

（0）在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover
（1）额外功能参数（3个）
（2）change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
（3）在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的 GTID号
（4） mysqldump备份时，默认会将备份中包含的事务操作，以以下方式
    SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';
    告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。

相关文章

  • MySQL--主从复制高级进阶

    如果您对数据库感兴趣，可以添加 DBA解决方案QQ群：855439640 1. 延时从库 1.1介绍 1.2 为什...

  • 主从复制-高级进阶

    1.延时从库 1.1 作用 处理逻辑损坏 1.2 怎么实现的 1.3 怎么处理逻辑损坏 drop 操作：（1） 感...

  • MySQL主从复制高级进阶day13

    主从复制-高级进阶 1.延时从库 1.1作用 处理逻辑损坏 比如drop database之类的操作 2.过滤复...

  • MySQL-lesson9--主从复制高级进阶

    MySQL-lesson9--主从复制高级进阶 1. 延时从库 1.1介绍 1.2 为什么要有延时从 1.3 配置...

  • 九，主从复制高级进阶

    1，延时从库 介绍 配置 延时从库处理逻辑故障 故障演练 主库： 从库 恢复到从库 2，过滤复制 例子 3，GTI...

  • MySQL--进阶

    数据库设计 需求分析 需求设计 概要设计 抽取实体:业务模型->实体模型(类) 数据库设计:业务模型/实体模型->...

  • MYSQL--进阶

    MYSQL主从复制 类别 基于日志点的复制支持MMM和MHA架构 基于GTID方式的复制GTID= source_...

  • MySQL-主从复制高级进阶

    半同步复制(了解) 3 解决主从数据一致性问题 半同步复制工作原理的变化 配置半同步复制 过滤复制 说明 主库： ...

  • mysql--高级特性

    1.分区表： 概念： 底层用多个物理子表组成。对于应用层来说可以不做变化，我们无需改变原有的SQL语句 建表时使用...

  • 主从复制高级

    主从复制高级 延时从库 故障恢复 半同步复制 主从复制过滤 gtid的复制 故障：

网友评论

      本文标题：MySQL--主从复制高级进阶

      本文链接：https://www.haomeiwen.com/subject/opgfpctx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|MySQL--主从复制高级进阶|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！