MySQL主从复制高级进阶

延时从库

SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行

#延时从库配置
[root@mysql ~]# mysql -uroot -p -S /data/3308/mysql.sock
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_delay = 300;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
                    SQL_Delay: 300
          SQL_Remaining_Delay: NULL

延时从库处理逻辑故障

#延时从库处理逻辑故障
(1) 监控到数据库逻辑故障
(2) 停从库SQL线程,记录已经回放的位置点(截取日志起点)
mysql> stop slave sql_thread;
mysql> show slave status\G
                    SQL_Delay: 300
          SQL_Remaining_Delay: NULL
(3) 截取relaylog
#起点:
mysql> show slave status\G
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
#终点:drop之前的位置点
show relaylog events in ‘‘
进行截取
(4) 模拟SQL线程回放日志
从库source
(5) 恢复业务
情况一:
从库替代主库工作
情况二: 
从库导出故障库,还原到主库中.

延时从库故障模拟

#主库
mysql> create database delay charset utf8mb4;
mysql> use delay;
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> drop database delay;

#从库
# 1.停止 从库SQL 线程,获取relay的位置点
mysql> stop slave sql_thread;
mysql> show slave status \G
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320

# 2. 找到relay的截取终点
mysql> show relaylog events in ‘mysql-relay-bin.000002‘;

#3. 截取relay
[root@mysql data]# mysqlbinlog --start-position=320 --stop-position=933 mysql-relay-bin.000002 >/mnt/relay.sql

#4. 恢复relay到从库
mysql> set sql_log_bin=0;
mysql> source /mnt/relay.sql
mysql> set sql_log_bin=1;

过滤复制应用

#主库
mysql> 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:   #模糊表黑名单

#例子:
[root@mysql ~]# vim /data/3308/my.cnf
replicate_do_db=repl

GTID复制

GTID(Global Transaction ID)是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。
核心特性: 全局唯一,具备幂等性

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

#GTID复制配置过程：
#初始化数据(其余操作相同)
[root@sql01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data

#主库(sql01):
[root@sql01 ~]# cat /etc/my.cnf
[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]
socket=/tmp/mysql.sock

#从库(sql02):
[root@sql02 ~]# cat /etc/my.cnf
[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]
socket=/tmp/mysql.sock
[root@sql02 ~]# 

#从库(sql03):
[root@sql03 ~]# cat /etc/my.cnf
[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]
socket=/tmp/mysql.sock
[root@sql03 ~]# 

#启动数据库(其余操作相同)
[root@sql01 ~]# /etc/init.d/mysqld start

#构建主从：
master:110
slave:120,130

#master
mysql> grant replication slave  on *.* to repl@‘10.0.1.%‘ identified by ‘123‘;

#slave
change master to 
master_host=‘10.0.0.110‘,
master_user=‘repl‘,
master_password=‘123‘ ,
MASTER_AUTO_POSITION=1;
start slave;

GTID 复制和普通复制的区别

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

MySQL主从复制高级进阶