延时从库 *****
1.1 介绍及配置

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

SQL_Remaining_Delay: NULL

1.2 延时从库处理逻辑故障

1.2.1 延时从库的恢复思路

(1) 监控到数据库逻辑故障

(2) 停从库SQL线程,记录已经回放的位置点(截取日志起点)

stop slave sql_thread ;

show slave status \G

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 320 #relay-log执行到了哪个位置

(3) 截取relaylog

起点:

show slave status \G

Relay_Log_File ,Relay_Log_Pos

终点: drop之前的位置点

show relaylog events in 'localhost-relay-bin.000002'

进行截取

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

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

2. 找到relay的截取终点

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

| db01-relay-bin.000002 | 1299 | Query | 7 | 1228 | drop database delay

截取relay

[root@db01 data]# cd /data/3308/data/

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

恢复relay到从库

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

mysql> set sql_log_bin=0;

mysql> source /tmp/relay.sql

过滤复制 *****
2.0 快速恢复测试环境

从库 :

mysql -S /data/3308/mysql.sock

drop database delay ;

stop slave;

reset slave all;

主库:

mysql -S /data/33078/mysql.sock

reset master;

从库:

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

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;

2.1 过滤复制应用

主库:

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:

例子:

vim /etc/my.cnf

replicate_do_db=repl

[root@db01 data]# systemctl restart mysqld3308

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

enforce-gtid-consistency=true

log-slave-updates=1

gtid-mode=on --启用gtid类型，否则就是普通的复制架构

enforce-gtid-consistency=true --强制GTID的一致性

log-slave-updates=1 --slave更新是否记入日志

2.3.3 GTID复制配置过程：

(1) 清理环境

pkill mysqld

\rm -rf /data/mysql/data/* #清理数据

\rm -rf /data/binlog/* #清理数据二进制日志

rm -rf /data/3309/data/* #我自己的3309实例，数据放在/data/3309/data/下，二进制日志放在/data/3309/下

rm -rf /data/3309/*

(2) 准备配置文件

主库db01：

cat >/data/3309/my.cnf <<EOF

[mysqld]

basedir=/application/mysql/

datadir=/data/3309/data

socket=/data/3309/mysql.sock

log_error=/data/3309/mysql3309.log_error #自己设置的错误日志的名字

server_id=10

port=3309

secure-file-priv=/tmp

autocommit=0

log_bin=/data/3309/mysql-bin

binlog_format=row

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

EOF

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

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=11

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

(4) 启动数据库

/etc/init.d/mysqld start

(5) 构建主从：

master:51

slave:52,53

51:

grant replication slave on . to repl@'%' identified by '123';

52\53:

change master to

master_host='192.168.164.130',

master_user='repl',

master_password='123',

master_port=3309,

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='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11';

告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。

半同步（了解） ***

解决主从复制数据一致性问题.

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

MHA高可用 ****
5.1 搭建体验

(1)配置关键程序软连接

ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

ln -s /application/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.164.129:/root

scp -r /root/.ssh 192.168.164.131:/root

各节点验证

db01:

ssh 192.168.164.130 date

ssh 192.168.164.129 date

ssh 192.168.164.131 date

db02:

ssh 192.168.164.130 date

ssh 192.168.164.129 date

ssh 192.168.164.131 date

db03:

ssh 192.168.164.130 date

ssh 192.168.164.129 date

ssh 192.168.164.131 date

(3)安装软件包

yum install perl-DBD-MySQL -y

rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

mysql8.0要使用MHA的话，需要修改密码加密模式，mysql8.0默认使用sha2，要改为native

使用node0.57以上版本

(4) 在db01主库中创建mha需要的用户

grant all privileges on . to mha@'192.168.164.%' identified by 'mha';

(5) Manager软件安装（db03）

yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

yum install -y perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

rpm -ivh mha4mysql-manager-0.56-0.el6.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=/data/3309 #主库的二进制日志所在的目录

user=mha

password=mha

ping_interval=2

repl_password=123

repl_user=repl

ssh_user=root

[server1]

hostname=192.168.164.130

port=3309

[server2]

hostname=192.168.164.129

port=3309

[server3]

hostname=192.168.164.131

port=3309

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状态

[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf

主库宕机处理过程

监控节点 (通过配置文件获取所有节点信息)
系统,网络,SSH连接性
主从状态,重点是主库
选主
(1) 如果判断从库(position或者GTID),数据有差异,最接近于Master的slave,成为备选主
(2) 如果判断从库(position或者GTID),数据一致,按照配置文件顺序,选主.
(3) 如果设定有权重(candidate_master=1),按照权重强制指定备选主.
1. 默认情况下如果一个slave落后master 100M的relay logs的话，即使有权重,也会失效.
2. 如果check_repl_delay=0的化,即使落后很多日志,也强制选择其为备选主
数据补偿
(1) 当SSH能连接,从库对比主库GTID 或者position号,立即将二进制日志保存至各个从节点并且应用(save_binary_logs )
(2) 当SSH不能连接, 对比从库之间的relaylog的差异(apply_diff_relay_logs)
Failover
将备选主进行身份切换,对外提供服务
其余从库和新主库确认新的主从关系
应用透明(VIP)
故障切换通知(send_reprt)
二次数据补偿(binlog_server)
MHA 应用透明(vip)
应用透明是指在应用和数据库之间加入中间件，实现：当发生数据库切换的时候，应用不需要做任何更改，就像没有发生过数据库切换一样

mysql自带的vip只能同机房使用，不能跨机房跨网络使用

db03:

touch /data/3309/master_ip_failover #粘贴事先准备好的脚本到这个路径

vim /data/3309/master_ip_failover

my $vip = '192.168.164.132/24';

my $key = '1';

my $ssh_start_vip = "/sbin/ifconfig ens160:$key $vip";

my $ssh_stop_vip = "/sbin/ifconfig ens160:$key down";

[root@db03 bin]# yum install -y dos2unix

[root@db03 bin]# dos2unix /usr/local/bin/master_ip_failover

[root@db03 bin]# chmod +x /data/3309/master_ip_failover

[root@db03 bin]# vim /etc/mha/app1.cnf

master_ip_failover_script=/data/3309/master_ip_failover

db01:手工添加vip

[root@db01 ~]# ifconfig ens160:1 192.168.164.132/24

db03 : 重启MHA

[root@db03 bin]# masterha_stop --conf=/etc/mha/app1.cnf

[root@db03 bin]# 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 &

[root@db03 bin]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 (pid:14410) is running(0:PING_OK), master:192.168.164.130

[root@db03 bin]#

MHA 故障提醒
[root@db03 ~]# cp -a /data/email/* /usr/local/bin/

[root@db03 ~]# cd /usr/local/bin/

[root@db03 ]# chmod +x /usr/local/bin/*

[root@db03 bin]# vim /etc/mha/app1.cnf

report_script=/usr/local/bin/send

重启MHA:

[root@db03 bin]# masterha_stop --conf=/etc/mha/app1.cnf

[root@db03 bin]# 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 &

/usr/local/bin/sendEmail -o tls=no -f 13172491294@163.com -t 861700361@qq.com -s smtp.163.com:25 -xu 13172491294 -xp MUVXHOFMRBBKPHFK -u "MHA Waring" -m "YOUR MHA MAY BE FAILOVER" &>/tmp/sendmail.log

红色部分为授权码，不是用户密码，/tmp/sendmail.log为邮件日志路径

额外的数据补偿(binlog_server)
(1)一般放在SSD上

找一台额外的机器，必须要有5.6以上的版本，支持gtid并开启，我们直接用的第二个slave（db03）

vim /etc/mha/app1.cnf

[binlog1]

no_master=1

hostname=192.168.164.131

master_binlog_dir=/data/binlog_server

(2) 创建必要目录

mkdir -p /data/binlog_server

chown -R mysql.mysql /data/*

(3) 拉取主库binlog日志

cd /data/binlog_server -----》必须进入到自己创建好的目录

mysqlbinlog -R --host=192.168.164.130 --port=3309 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &

ps -ef | grep mysqlbinlog

注意：show master status;

拉取日志的起点,需要按照目前主库正在使用的binlog为起点.

(4) 重启MHA-manager

[root@db03 bin]# masterha_stop --conf=/etc/mha/app1.cnf

[root@db03 bin]# 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 &

故障模拟及故障处理
8.1 宕掉 db01 数据库

/etc/init.d/mysqld stop

8.2 恢复故障
恢复思路

1看manager进程状态的两条语句

ps -ef | grep manager

masterha_check_status --conf=/etc/mha/app1.cnf

2.如果配置文件中已经剔除了故障节点信息，说明切换过程已经基本成功

如果配置文件中还保留故障节点信息，说明切换过程没有完成

3.看日志 /var/log/mha/app1/manager

(1) 启动故障节点并恢复其数据

[root@db01 ~]# /etc/init.d/mysqld start

systemctl start mysqld3309

(2) 恢复1主2从(db01)

[root@db03 bin]# grep "CHANGE MASTER TO" /var/log/mha/app1/manager

Thu Jul 18 18:31:54 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';

[root@db03 bin]#

db01 [(none)]>CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';

db01 [(none)]>start slave;

(3) 恢复配置文件(db03)

[server1]

hostname=10.0.0.51

port=3306

[server2]

hostname=10.0.0.52

port=3306

[server3]

hostname=10.0.0.53

port=3306

report_script=/usr/local/bin/send

4MHA状态检查

masterha_check_ssh --conf=/etc/mha/app1.cnf

masterha_check_repl --conf=/etc/mha/app1.cnf

(5)恢复binlogserver

cd /data/binlog_server

rm -rf /data/binlog_server/*

mysqlbinlog -R --host=192.168.164.130 --port=3309 --user=mha --password=mha --raw --stop-never mysql-bin.000003 &

6检查主库vip状态

(7) 启动MHA

[root@db03 bin]# 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 &

[1] 16543

[root@db03 bin]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 (pid:16543) is running(0:PING_OK), master:10.0.0.52

MHA配合Atlas实现读写分离
Atlas 介绍

Atlas是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。

它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。

360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。

下载地址

https://github.com/Qihoo360/Atlas/releases

注意：

1、Atlas只能安装运行在64位的系统上

2、Centos 5.X安装 Atlas-XX.el5.x86_64.rpm，Centos 6.X安装Atlas-XX.el6.x86_64.rpm。

3、后端mysql版本应大于5.1，建议使用Mysql 5.6以上

2.安装配置

yum install -y Atlas*

cd /usr/local/mysql-proxy/conf

mv test.cnf test.cnf.bak

vim test.cnf

[mysql-proxy]

admin-username = user

admin-password = pwd

proxy-backend-addresses = 192.168.164.132:3309

proxy-read-only-backend-addresses = 192.168.164.130:3309,192.168.164.131:3309

pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=

daemon = true

keepalive = true

event-threads = 8

log-level = message

log-path = /usr/local/mysql-proxy/log

sql-log=ON

proxy-address = 0.0.0.0:33060

admin-address = 0.0.0.0:2345

charset=utf8mb4

mysql8.0默认使用33060 端口，如果在8.0上使用Atlas，proxy-address = 0.0.0.0:33060 端口要改成别的

Atlas和MGR不兼容

启动atlas

/usr/local/mysql-proxy/bin/mysql-proxyd test start

ps -ef |grep proxy

Atlas功能测试

测试读操作：

mysql -umha -pmha -h 192.168.164.131 -P 33060

Atlas安装在哪个位置，-h 参数的值就是哪个位置

db03 [(none)]>select @@server_id;

测试写操作：

mysql> begin;select @@server_id;commit;

生产用户要求

开发人员申请一个应用用户 app( select update insert) 密码123456,要通过10网段登录

4.1 在主库中,创建用户

grant select ,update,insert on . to app@'10.0.0.%' identified by '123456';

4.2. 在atlas中添加生产用户

/usr/local/mysql-proxy/bin/encrypt 123456 ---->制作加密密码

vim /usr/local/mysql-proxy/conf/test.cnf

pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=,app:/iZxz+0GRoA=

/usr/local/mysql-proxy/bin/mysql-proxyd test restart

[root@db03 conf]# mysql -uapp -p123456 -h 10.0.0.53 -P 33060

Atlas基本管理

连接管理接口

mysql -uuser -ppwd -h127.0.0.1 -P2345

select * from help;

SELECT * FROM backends;

set offline 2;

set online 2;

REMOVE BACKEND 3;

ADD SLAVE 10.0.0.53:3306;

ADD PWD oldguo:123456;

发表于 2022-02-14 17:21 近知阅读(38) 评论(0) 编辑收藏举报

主从复制高级进阶

1.停止从库SQL 线程,获取relay的位置点

2. 找到relay的截取终点

SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11';

互信检查

公告

主从复制高级进阶

1.停止 从库SQL 线程,获取relay的位置点

2. 找到relay的截取终点

SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11';

互信检查

公告

1.停止从库SQL 线程,获取relay的位置点