MySQL主从复制高级进阶

2023-08-28 108

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： MySQL主从复制高级进阶

屏幕截图 2023-08-28 195743.png

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 <
[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 <
[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 <
[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 <
[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

MySQL主从复制高级进阶

1. 延时从库

# 2. 找到relay的截取终点

# 3. 截取relay

# 4. 恢复relay到从库

# 5.从库mysqldump，主库source

2. 过滤复制 *****

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL主从复制高级进阶

1. 延时从库

# 2. 找到relay的截取终点

# 3. 截取relay

# 4. 恢复relay到从库

# 5.从库mysqldump，主库source

2. 过滤复制 *****

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像