主从复制的高级应用

主从复制的高级应用

延时从库过滤复制半同步gtid复制

欢迎来到 来到大浪涛天的博客 ！

一、主从复制的高级应用

1. 延时从库

1-1. 延时从库的介绍说明

  • 主从复制主要是为了防止数据库的物理故障，比如数据损坏，比如主库实例结点宕机，这样可以快速的从从库中导出故障的库或者表，也可以直接把从库应用于生产。但是如果主库出现逻辑错误操作，比如误删了数据，或者误删了库，从库如果不设置延时的话，那也保护不了数据，因为主库输入的同时，从库也同步过来跟着回放了，所以延时从库主要是用于防止出现逻辑性的误操作事件。
  • SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
  • 一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间

1-2. 延时从库的设置

mysql>stop slave;

这里的21600是指时间秒
mysql>CHANGE MASTER TO MASTER_DELAY = 21600;

mysql>start slave;

mysql> show slave status G

SQL_Delay: 21600

SQL_Remaining_Delay: NULL

1-3. 延时从库恢复故障的案例

1-3-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之前的位置点,注意relay_log的events显示和binlog的events显示有些区别，左边的是一致，右边的pos是显示的binlog的pos,为了让relaylog和binlog一一对应，所以我们截取的时候看左边的pos位信息就好了。
	show relaylog events in ''	
	进行截取
	
4. 模拟SQL线程回访日志
	从库  source 
	
5. 恢复业务
	情况一: 就一个库的话
	从库替代主库工作
	情况二: 
	从库导出故障库,还原到主库中

1-3-2. 故障恢复演练

1-3-2-1. 数据模拟
  1. 主库执行模拟数据
主库 : 
create database delay charset utf8mb4;
use delay;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
  1. 从库执行停止 从库SQL 线程,获取relay的位置点
mysql> stop slave sql_thread;
mysql> show slave status G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 626
  1. 找到relay的截取终点
mysql> show relaylog events in 'db01-relay-bin.000002';
| db01-relay-bin.000002 | 1299 | Query          |         7 |        1228 | drop database delay
  1. 截取relay
[root@db01 data]# cd /data/3308/data/
[root@db01 data]# mysqlbinlog --start-position=626 --stop-position=1299 db01-relay-bin.000002 >/tmp/relay.sql
  1. 恢复relay到从库
[root@db01 data]# mysql -uroot -p -S /data/3308/mysql.sock 
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
  1. 从从库导出被删除的库，然后从主库导入
mysqldump -uroot -padmin123 -S /data/3307/mysql.sock -B test02 --master-data=2 --single-transaction -R -E --triggers --set-gtid-purged=OFF >test02.sql
source /tmp/test02.sql
  1. 从库身份解除。
db01 [relay]>stop slave;
db01 [relay]>reset slave all

2. 主从过滤复制

2-1. 主从过滤复制的介绍

MySQL主从支持从主库选择某些库进行复制，也可以从从库选择某些库和某些表进行复制，因为大部分的生产场景下，MySQL都是读比写繁忙很多，读的性能也比较低，因此为了缓解主库的压力，都是采用主库写，多个从库读，因此就需要从从库中选择需要同步的库过来，这样能极大的缓解数据库的压力。

2-2. 过滤复制应用


主库: 
show master status ;
从主库中设置需要复制的库的白名单
binlog_do_db
从主库中设置需要复制的库的黑名单
binlog_ignore_db 
从库: 
mysql> show slave status G
从从库中设置需要复制的库的白名单
Replicate_Do_DB: 
从从库中设置需要复制的库的黑名单
Replicate_Ignore_DB:
从从库中设置需要复制的表的白名单
Replicate_Do_Table: 
从从库中设置需要复制的表的黑名单
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table:

2-3. 实现过程

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

db01 [(none)]>show slave status G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: DB02
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 399
               Relay_Log_File: DB02-relay-bin.000008
                Relay_Log_Pos: 360
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: ppt,word
			  
主库：
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)

3. 主从半同步

3-1. 主从半同步的应用场景

1. 解决主从数据一致性问题，在主库完成了dump线程后，二进制日志通过IO线程缓存在从库的缓存中还没有写入relaylog中，从库忽然宕机了，这样从库起来后可能和主库数据不一致。但是需要注意的是当配置了半同步后，主库性能会急剧降低，因为当从库没有返回ACK_receiver线程时，主库就卡在了commit这里，当达到了超时时间半同步复制也会切换成原始的异步同步，一样是无法完全保证主从数据的一致性。


2. ACK ,从库relay落地,IO线程会返回一个ACK,主库的 ACK_reciver .主库事务才能提交.如果一直ACK没收到,超过10秒钟会切换为异步复制.

3-2. 半同步应用原理

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

3-3. 配置半同步复制

加载插件
主:
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';

3-4. GTID复制

3-5. GTID复制的介绍

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

3-6. 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更新是否记入日志

3-7. GTID复制配置过程：

  1. 准备环境
pkill mysqld

m -rf /data/mysql/data/*

m -rf /data/binlog/*
  1. 准备配置文件

主库db01：


cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=81
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=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=82
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=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=83
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
  1. 创建用户
useradd -s /sbin/nologin mysql
  1. 创建必须的目录
mkdir -p /data/binlog/
mkdir -p /data/mysql/data
chown -R mysql.mysql /data
  1. 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql  --datadir=/data/mysql/data
  1. 启动路径systemctl启动
cat >/etc/systemd/system/mysqld.service <<EOF
   [Unit]
   Description=MySQL Server
   Documentation=man:mysqld(8)
   Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
   After=network.target
   After=syslog.target
   [Install]
   WantedBy=multi-user.target
   [Service]
   User=mysql
   Group=mysql
   ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
   LimitNOFILE = 5000
   EOF
  1. 复制启动脚本 /etc/init.d/mysqld start启动
 cp /application/mysql/support-files/mysql.server  /etc/init.d/mysqld
 修改一下
 basedir=/application/mysql
 datadir=/data/mysql/data
  1. 启动数据库
/etc/init.d/mysqld start
  1. 构建主从：
master:81
slave:82,83

51:
grant replication slave  on *.* to repl@'10.0.0.%' identified by '123';

5253:
change master to 
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
  1. GTID 复制和普通复制的区别
（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-11';
   告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。

3-8. 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。
最好的解决方案：重新构建主从环境
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/chacha51/p/13795657.html