MySQL-16-主从复制进阶

延时从库

介绍

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

为什么要有延时从库

数据库故障

   物理损坏，普通的主从复制非常擅长解决物理损坏

   逻辑损坏，普通主从复制没办法解决逻辑损坏

配置延时从库

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			        # 延时时间为300秒

SQL_Remaining_Delay: NULL  	# 距离执行第一个事务还剩多少时间

延时从库应用

故障恢复思路

1主1从,从库延时5分钟,主库误删除1个库

1. 5分钟之内 侦测到误删除操作

2. 停从库SQL线程

3. 截取relaylog

    起点:停止SQL线程时,relay最后应用位置

    终点:误删除之前的position(GTID)

4. 恢复截取的日志到从库

5. 从库身份解除,替代主库工作

故障模拟及恢复

主库数据操作

db01 [(none)]>create database relay charset utf8mb4;

db01 [(none)]>use relay

db01 [relay]>create table t1 (id int);

db01 [relay]>insert into t1 values(1);

db01 [relay]>drop database relay;

停止从库SQL线程

mysql> stop slave sql_thread;

找relaylog的截取起点和终点

起点:

mysql> show slave status\G;

Relay_Log_File: db01-relay-bin.000002

Relay_Log_Pos: 320

终点:这里是1233

mysql> show relaylog events in 'db01-relay-bin.000002';

截取relaylog日志文件

[root@db01 data]# mysqlbinlog --start-position=320 --stop-position=1233 /data/3308/data/db01-relay-bin.000002>/tmp/relay.sql

从库恢复relaylog

source /tmp/relay.sql

从库身份解除

db01 [relay]>stop slave;

db01 [relay]>reset slave all

恢复情况

第一种：如果主库只有一个库，那么可以考虑从库直接替代主库进行工作

第二种：从库导出被误删除的库恢复到主库，然后在建立主从关系

半同步（了解）

解决主从数据不一致的问题

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

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';

过滤复制

环境准备

从库 :

mysql -S /data/3308/mysql.sock

drop database relay ;

stop slave;

reset slave all;

主库:

mysql -S /data/33078/mysql.sock

reset master;

从库:

[root@db01 ~]# mysql -S /data/3308/mysql.sock

mysql >

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=154,

MASTER_CONNECT_RETRY=10;

start slave;

配置过滤复制

主库：

直接配置需要复制的库才写binlog日志，这样，从库就只能复制到写了binlog日志的库，但是这样太暴力，不建议这样使用

主库配置文件加入如下参数，注意只要这两个参数中的一个即可

[root@db01 data]# vim /data/3307/my.cnf

binlog_do_db="aa"    		    # 表示只有aa库才写binlog日志

binlog_ignore_db="bb"       # 表示除了bb库都写binlog日志

[root@db01 data]# systemctl restart mysqld3307

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000004 |      154 | aa           |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

从库：

配置文件加入只回放哪些库，主库的binlog日志还是要全部取过来的

[root@db01 data]# vim /data/3308/my.cnf

replicate_do_db=mydb

[root@db01 data]# systemctl restart mysqld3308

mysql> show slave status\G

Replicate_Do_DB: repl

Replicate_Ignore_DB:

GTID主从复制*****

GTID介绍

GTID(Global Transaction ID)是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。

它的官方定义如下：

GTID = source_id ：transaction_id

7E11FA47-31CA-19E1-9E56-C43AA21293967:29

什么是sever_uuid，和Server-id 区别？

核心特性: 全局唯一,具备幂等性

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

GTID复制配置过程

1 清理环境(实验环境)

pkill mysqld

rm -rf /data/mysql/data/*

rm -rf /data/binlog/*

2 准备配置文件

准备3台虚拟机服务器 10.0.0.51（db01主库）、10.0.0.52（db02从库）、10.0.0.53（db03从库）

主库db01：

cat > /etc/my.cnf <<EOF

[mysqld]

basedir=/application/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=/application/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=/application/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=/data/mysql  --datadir=/data/mysql/data 

4 启动数据库（三台主机都要执行）

systemctl start mysqld

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;

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。

最好的解决方案：重新构建主从环境

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;

GTID复制：

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-16-主从复制进阶

延时从库

介绍

为什么要有延时从库

配置延时从库

延时从库应用

故障恢复思路

故障模拟及恢复

半同步（了解）

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

配置半同步复制

过滤复制

环境准备

配置过滤复制

GTID主从复制*****

GTID介绍

GTID核心参数

GTID复制配置过程

GTID 从库误写入操作处理

GTID 复制和普通复制的区别

最新文章

热门文章