MySQL主从复制高级进阶

 

1. 延时从库 

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

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY =60;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 60
SQL_Remaining_Delay: NULL
1.2 延时从库处理逻辑故障
1.2.1 延时从库的恢复思路
(1) 监控到数据库逻辑故障
(2) 停从库SQL线程,记录已经回放的位置点(截取日志起点)
    stop slave sql_thread ;
    show slave status \G
    Relay_Log_File: mysql-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.2.2 故障演练
主库 : 
create database delay charset utf8mb4;
use delay;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;

#等60秒后，再执行删除操作
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: 479

# 2. 找到relay的截取终点

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

# 3. 截取relay

cd /usr/local/mysql/data 
mysqlbinlog --start-position=912 --stop-position=1520 slave-relay-bin.000002 >/tmp/relay.sql    

# 4. 恢复relay到从库

mysql> start slave sql_thread;
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
mysql> set sql_log_bin=1;

# 5.从库mysqldump，主库source

2. 过滤复制   *****

2.0 快速恢复测试环境
从库 :
stop slave;
reset slave all;

主库: 
reset master;

从库: 

CHANGE MASTER TO 
MASTER_HOST='192.168.8.10',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
 

2.1 过滤复制应用

主库: 
show master status ;

从库: 
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: 

例1：从库仅复制repl
vim /etc/my.cnf
replicate_do_db=repl

systemctl restart mysqld

验证：主库创建多个库（repl,repl1,repl2）,查看从库同步数据情况。

例2：从库仅拒绝hehe
vim /etc/my.cnf
replicate_ignore_db=hehe

systemctl restart mysqld

验证：主库创建多个库（hehe,hehe1,hehe2）,查看从库同步数据情况。

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

2.3.2 GTID核心参数
重要参数：

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

2.3.3 GTID复制配置过程：
db01: 192.168.8.10
db02: 192.168.8.20
db03: 192.168.8.30

(1)修改配置文件
主库db01：

cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=1
port=3306
secure-file-priv=/tmp
autocommit=1
log_bin=mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01>
EOF

slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=2
port=3306
secure-file-priv=/tmp
autocommit=1
log_bin=mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02>
EOF

slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=3
port=3306
secure-file-priv=/tmp
autocommit=1
log_bin=mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03>
EOF

(3) 启动数据库
systemctl restart mysqld

(4) 构建主从：
master:db01
slave:db02,db03

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

db02\db03:
change master to 
master_host='192.168.8.10',
master_user='repl',
master_port=3306,
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;

2.3.4  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='gc498ec-7e78-11e8-9u78-000cdde345d:1-9';
    告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。

4. 半同步     ***
解决主从复制数据一致性问题.
从库relay落地,IO线程会返回一个ACK,主库的 ACK_reciver .主库事务才能提交.
如果一直ACK没收到,超过10秒钟会切换为异步复制.
注：半同步是通过主库延迟commit事务来实现主从一致性的。

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

5. MHA高可用  *****
5.1 搭建体验
(1)配置关键程序软连接
ln -s /usr/local/mysql/bin/mysqlbinlog    /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql          /usr/bin/mysql

(2)配置互信

db01：
rm -rf /root/.ssh 

ssh-keygen

cd /root/.ssh 

mv id_rsa.pub authorized_keys

scp  -r  /root/.ssh  192.168.8.20:/root 
scp  -r  /root/.ssh  192.168.8.30:/root 

各节点验证

ssh 192.168.8.10 hostname
ssh 192.168.8.20 hostname
ssh 192.168.8.30 hostname

(3)所有节点安装软件包
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm 

(4) 在db01主库中创建mha需要的用户
grant all privileges on *.* to mha@'192.168.8.%' identified by 'mha';

(5) Manager软件安装（db03）
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum install -y perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm 

(6) 配置文件准备(db03)
    创建配置文件目录
 mkdir -p /etc/mha
    创建日志目录
 mkdir -p /var/log/mha/app1
    编辑mha配置文件
cat > /etc/mha/app1.cnf <<EOF
[server default]
manager_log=/var/log/mha/app1/manager        
manager_workdir=/var/log/mha/app1            
master_binlog_dir=/usr/local/mysql       
user=mha                                   
password=mha                               
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root                               
[server1]                                   
hostname=192.168.8.10
port=3306                                  
[server2]            
hostname=192.168.8.20
port=3306
[server3]
hostname=192.168.8.30
port=3306
EOF

(7)  状态检查(db03)
### 互信检查
 masterha_check_ssh  --conf=/etc/mha/app1.cnf 
 masterha_check_repl  --conf=/etc/mha/app1.cnf 
 
 
(8) 开启MHA(db03)：
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

(9)  查看MHA状态
masterha_check_status --conf=/etc/mha/app1.cnf

(10) 模拟主库故障

主库：
systemctl stop mysqld

从库：
show slave status \G