DAY11 - MySQL安全与审计

无故障率故障时间解决方案 99.9% 0.1%（525.6min） keepalived+双主架构，但需要人为干预 99.99% 0.01%（52.56min） MHA ORCH TMHA，具有自动监控，自动切换，自动数据补偿，但还是属于半自动化比较适合非金融类互联网公司 eg: facebook taobao前端-TMHA–>polaradb 99.999% 0.001%（5.256min） PXC MGR MGC，数据是高一致性比较适合金融类互联网公司 99.9999% 0.0001%（0.5256min）自动化、云计算化、平台化，仍然属于概念阶段

基础章节-01-MySQL数据库服务中级课程

1.00 课程知识章节说明

目前在互联网的实际应用中，各个企业都会比较关注自身网站的数据信息，既要保证数据信息的安全性，同时也要保证数据存储读取效率并且在特殊的场景下，还要对存储的数据信息进行检索和分析；因此数据库服务业务已经在各行各业应用非常的广泛对于互联网领域的技术人员，对于数据库服务知识的掌握，也将是在求职时必备的技能，有些时候还会绝对入职的定级和薪资水平。

1.15 数据库服务冗余架构

1.15.1 数据库服务高可用前言介绍

数据库中的高可用功能，主要是用于避免数据库服务或数据信息的损坏问题，其中数据损坏的类型有：数据物理损坏：磁盘、主机、程序实例、数据文件误删除数据逻辑损坏：drop update … 其中，数据库高可用技术的出现主要解决的是数据逻辑损坏问题，而主从架构技术主要解决的是数据物理损坏问题；数据库高可用解决方案选型依据：（全年无故障率）

1.15.2 数据库服务高可用软件介绍

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton研发，此人目前就职于Facebook公司，MHA是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。 MySQL进行故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换过程中； MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。 MHA主要有两部分组成： MHA Manager（管理节点）可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave上。 MHA Node（数据节点）运行在每台MySQL服务器上 MHA Manager 会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master；然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序是完全透明的； MHA软件结构介绍：（MHA中的所有组件就是perl语言编写的功能脚本）

节点信息软件组件作用介绍 MHA Manager（管理节点） masterha_manger 用于启动MHA

masterha_check_ssh 用于检查MHA的SSH配置互信状况

masterha_check_repl 用于检查MySQL复制状态，以及配置信息

masterha_master_monitor 用于检测master是否宕机

masterha_check_status 用于检测当前MHA运行状态

masterha_master_switch 用于控制故障转移（自动或者手动）

masterha_conf_host 添加或删除配置的ser ver信息 MHA Node（数据节点） save_binar y_logs 保存和复制master的二进制日志

apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他slave

purge_relay_logs 清除中继日志（不会阻塞SQL线程）主机角色主机名称地址信息主库服务器 192.168.30.101 3306 从库服务器 192.168.30.102 3306 从库服务器 192.168.30.103(兼做管理节点) 3306

1.15.3 数据库服务高可用环境构建

① MHA高可用架构基础环境：为了实现MHA高可用架构构建，需要准备好三节点数据库+GTID复制环境：对原有数据库服务环境清理：（基于GTID环境构建）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# 在所有主从节点均进行清理操作：
[root@xiaoQ-01 ~]# pkill mysqld
[root@xiaoQ-01 ~]# rm -rf /data/3306/*
[root@xiaoQ-01 ~]# rm -rf /data/binlog /*
[root@xiaoQ-01 ~]# mv /etc/my.cnf /tmp
[root@xiaoQ-01 ~]# mkdir -p /data/3306/data /data/binlog
[root@xiaoQ-01 ~]# chown -R mysql.mysql /data/*
-- 所有数据库主从节点均进行以上清理操作；
# 主库db01配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
ser ver_id=51
port=3306

secure-file-priv=/tmp autocommit=0

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
2
log-slave-updates=1
[mysql]

prompt=db01 [\d]>

1
2
3
4
5
6
7
8
9
EOF
# 从库db02配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
ser ver_id=52
port=3306

secure-file-priv=/tmp autocommit=0

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
2
log-slave-updates=1
[mysql]

prompt=db02 [\d]>

② MHA高可用软件安装部署：

1
2
3
4
5
6
7
8
9
EOF
# 从库db03配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
ser ver_id=53
port=3306

secure-file-priv=/tmp autocommit=0

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
2
log-slave-updates=1
[mysql]

prompt=db03 [\d]>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
EOF
# 进行数据库所有节点初始化操作
[root@xiaoQ-01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@xiaoQ-02 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@xiaoQ-03 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
# 启动数据库所有节点服务
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld start
[root@xiaoQ-02 ~]# /etc/ init.d/mysqld start
[root@xiaoQ-03 ~]# /etc/ init.d/mysqld start
# 重构主从关系-主库操作

db01 [(none)]>create user repl@‘192.168.30.%’ identified with mysql_native_password by ‘123456’;

1
Quer y OK, 0 rows affected (0.01 sec)

db01 [(none)]>grant replication slave on . to repl@‘192.168.30.%’;

1
2
3
Quer y OK, 0 rows affected (0.00 sec)
-- 主库上创建主从复制用户信息
# 重构主从关系-从库操作

db02 [(none)]>change master to

1
2
3
4
5
6
7
master_host='192.168.30.101',
master_user='repl',
master_password='123456',
master_auto_position=1;
-- 表示让从库自己找寻复制同步数据的起点；
-- 在第一次启动gtid功能时，会读取从库中的binlog日志信息，根据主库uuid信息，获取从库中执行过的主库gtid信息
-- 从从库中没有执行过的主库gtid信息之后进行进行数据同步操作

db02 [(none)]> start slave;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 其他从库一并操作
# 创建程序命令软链接
[root@xiaoQ-01 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@xiaoQ-01 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
-- 所有节点均执行以上操作，因为MHA程序加载数据库命令，会默认在/usr/bin下面进行加载（会影响数据补偿和监控功能）
# 配置各节点互信
[root@xiaoQ-01 ~]# rm -rf /root/.ssh
[root@xiaoQ-01 ~]# ssh-keygen
[root@xiaoQ-01 ~]# cd /root/.ssh
[root@xiaoQ-01 ~]# mv id_rsa.pub authorized_keys
[root@xiaoQ-01 ~]# scp  -r  /root/.ssh  192.168.30.102:/root
[root@xiaoQ-01 ~]# scp  -r  /root/.ssh  192.168.30.103:/root

ssh 192.168.30.101 date ssh 192.168.30.102 date ssh 192.168.30.103 date

1
2
3
4
5
6
7
-- 各节点验证
# 安装软件程序
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
-- 所有节点安装Node软件依赖包
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

1.15.4 数据库服务高可用工作原理

在熟悉高可用服务工作原理前，可以先思考下应用高可用服务可以解决哪些需求，或者也可以理解为解决哪些痛点： ① 如何在高可用架构中，当主库宕机异常后，使之及时的发现主库服务程序产生了运行异常？解决此痛点问题，需要实现高可用的监控需求； ② 如何在高可用架构中，当主库宕机异常后，可以找到可以替代主库的服务器主机进行切换？解决此痛点问题，需要实现高可用的选主功能；（并且选择数据量越接近主库的从库成为新主） ③ 如何在高可用架构中，当主库宕机异常后，新的主库接管后可以保证与原有主库数据一致？解决此痛点问题，需要实现高可用的数据补偿；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Manager软件安装（db03）
# 在db01主库中创建mha需要的用户
create user mha@'192.168.30.%' identified with mysql_native_password by 'mha';
grant all privileges on *.* to mha@'192.168.30.%';
-- 在主库创建完毕后，主从复制功能，核实所有从库也都有mha用户信息
# Manager配置文件准备(db03)
mkdir -p /etc/mha
-- 创建配置文件目录
mkdir -p /var/log /mha/app1
-- 创建日志目录
cat > /etc/mha/app1.cnf <<EOF

[ser ver default] manager_log=/var/log /mha/app1/manager

1
-- MHA的工作日志设置

manager_workdir=/var/log /mha/app1

1
2
3
4
5
6
-- MHA的工作目录

master_binlog_dir=/data/binlog
-- 主库的binlog目录
user=mha
-- 监控用户，利用此用户连接各个节点，做心跳检测（主要是检测主库的状态）

password=mha

1
-- 监控密码

ping_inter val=2

1
-- 心跳检测的间隔时间

repl_password=123456

1
-- 复制密码

repl_user=repl

1
-- 复制用户（用于告知从节点通过新主同步数据信息的用户信息）

ssh_user=root

1
-- ssh互信的用户（可以利用互信用户从主库scp获取binlog日志信息，便于从库进行数据信息补偿）

[ser ver1]

1
-- 节点信息....

hostname=192.168.30.101

1
port=3306

[ser ver2] hostname=192.168.30.102

1
port=3306

candidate_master=1 [ser ver3] hostname=192.168.30.103

1
2
3
4
port=3306
EOF
-- 编辑mha配置文件
# MHA状态检查（db03）

masterha_check_ssh –conf=/etc/mha/app1.cnf Wed Dec 28 20:54:42 2022 - [info] All SSH connection tests passed successfully.

1
-- 在MHA管理节点，进行ssh互信功能检查，并且显示成功表示检查通过

masterha_check_repl –conf=/etc/mha/app1.cnf MySQL Replication Health is OK.

1
2
-- 在MHA管理节点，检查主从关系与配置文件信息是否正确
# 开启MHA-manager

开启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 &

1
2
# 查看MHA状态
[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 (pid:30770) is running(0:PING_OK), master :192.168.30.101

1
-- 显示以上提示信息，表示MHA基础环境搭建成功了，但还不能在生产环境使用，还需要有后续的操作配置

数组信息简述作用说明 alive 存活数组主要用于探测存活的节点状态；当主库宕机后，探测的就是两个从库节点 latest 最新数组表示获取日志最新的从库信息，即数据量最接近主库的从库（根据GTID信息或 position信息） pref 备选数组在数组中具有candidate_master参数判断条件，此参数可以放入配置文件节点中，便于节点优先选择为新主 bad 不选数组如果设定了参数：no_master=1，表示相应节点不参与竞选主；如果设定了参数：log_bin=0（二进制日志没开），表示相应节点不参与竞选主；如何设定了参数：check_slave_delay，检查从库延迟主库100M数据信息日志量，表示节点不参与竞选主 ④ 如何在高可用架构中，当主库宕机异常后，将应用程序的读写请求对接切换到新的主库上？解决此痛点问题，需要实现高可用的应用透明；（VIP技术） ⑤ 如何在高可用架构中，当主库宕机异常后，能够及时向管理员发起告知提醒使之进行修复？(MHA切换是一次性的) 解决此痛点问题，需要实现高可用的报警功能； ⑥ 如何在高可用架构中，当主库宕机异常后，当整体主库系统环境都异常时实现数据的补偿？解决此痛点问题，需要实现高可用的额外补偿； ⑦ 如何在高可用架构中，当主库宕机异常后，根据主库服务器的异常情况进行原有主库修复？解决此痛点问题，需要实现高可用的自愈功能；（待开发，只有云平台RDS具有此功能）

MHA的设计原理分析（Failover 过程） 01 MHA软件启动根据启动命令，分析MHA软件启动原理：根据以上启动命令，需要先调取MHA启动脚本文件masterha_manager ，然后在调取加载MHA软件的配置文件–conf=…/app1.cnf 会根据加载的MHA的配置文件不同，实现管理多个高可用架构环境，进行高可用业务的架构环境的区分； –remove_dead_master_conf 参数表示在主节点出现宕机情况时，将会从集群中被踢出，即从配置文件中删除掉故障节点； –ignore_last_failover 默认MHA服务是不能频繁进行故障切换的，需要有一定的间隔时间，加此参数表示忽略切换的间隔时间；最后将MHA启动运行的信息放入到日志文件中即可 /var/log/mha/app1/manager.log 2>&1

02 MHA实现监控利用MHA启动脚本文件masterha_manager 会自动调用监控脚本文件masterha_master_monitor ，并且每隔配置文件指定时间； ping_inter val=2 进行脚本监控一次，从而判断主节点是否处于存活状态，连续4次还没有主库心跳，即说明主库宕机；

03 MHA选主过程在MHA中进行选主时，根据选主源码文件信息分析，主要会利用到四个数组：alive latest pref bad，并且会识别节点编号信息；在进行选主时，主要会关注竞选新主节点的日志量、以及是否设置candidate_master参数配置信息； MHA选主判断总结（利用if判断选主的情况）循环对比latest数组和pref数组的slave，如果存在相同的slave，并且这个slave不在bad数组当中，该slave会被推选为新的master DB02节点即满足latest数组信息，又满足perf数组信息，但不满足bad数据信息，即会被选为新主，有多个按照号码顺序选举；如果pref和bad数组当中的个数为0，则选择latest数组当中的第一个slave为master； 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
2
# 监控脚本验证主节点存活方法
[root@xiaoQ-03 ~]# mysql -umha -pmha -h192.168.30.101 -e "select user();"

mysql: [Warning] Using a password on the command line interface can be insecure.

1
2
3
4
5
+-------------------+
| user()            |
+-------------------+
| mha@xiaoQ-03      |
+-------------------+

优先级 alive数组 latest数组 pref数组 bad数组选主策略多个选择情况 01 满足满足满足不满足优选选择按照节点号码顺序选择 02 满足满足不满足不满足优选选择按照节点号码顺序选择 03 满足不满足满足不满足优选选择按照节点号码顺序选择 04 满足不满足不满足不满足优选活着节点按照节点号码顺序选择 DB02节点没有candidate_master参数配置，又没有不选数组里的三种情况配置，即db02恰好是latest，为新主；循环对比alive数组和pref数组当中的slaves，如果有一个slave相同，并且不在bad数组当中，该节点就会成为新的master； DB02节点即不满足latest，也不满足bad，但是满足pref，也会被选择作为新主；循环latest数组，如果又循环到slave不在bad数组当中，这个slave就会成为master，就算添加了candidate_master=1参数；该slave也不一定会成为主库； DB02节点即满足latest数组，不是bad数组，也会成为新的主；从活着的slave当中进行循环，如果循环到slave不在bad数组当中，那么这个slave就会成为主库； DB02节点是活着的，不满足bad，也可以成为新的主；如果进行了多次选择都找不到主库，那么主库选择失败，failover失败；选主策略简述表：

说明：在进行手工指定切换新主时，即应用了prio_new_master_host参数信息时，会最优先选择相应节点为新主；

04 MHA数据补偿在进行数据补偿之前，需要让新主库与原有宕机主库进行对比，获悉需要补偿的数据量情况，即对比差的数据日志量信息；然后可以从binlog日志中，进行补充数据信息的截取，随之进行数据信息补偿，但是有种特殊情况，若原有主库无法访问了；所以进行数据补偿操作，也需要分各种情景进行处理：原主库SSH连接正常：各个从节点自动调用：save_binar y_logs 脚本文件，立即保存缺失部分的bin_log，到各节点/var/tmp/目录；原主库SSH连接异常：各个从节点自动调用：apply_diff_relay_logs 脚本文件，进行relay_log日志差异信息补偿；额外特殊数据补充：（利用主库日志冗余机制） MHA提供了binlog_ser ver功能，可以实时拉取主库的binlog日志到备份节点，从而进行数据额外补偿；

05 MHA业务切换自动解除原有的主从关系，实现新的主从关系的建立；

06 MHA应用透明实现MHA的VIP功能，利用脚本实现，上传mha_script.tar文件到/usr/local/bin目录中，然后进行解压处理；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 所有从库解除主从关系操作
stop slave;
reset slave;
# 所有从库重构主从关系操作
change master to ...
# 上传MHA所需的脚本文件
[root@xiaoQ-03 ~]# cd /usr/local/bin/
[root@xiaoQ-03 bin]# chmod +x /usr/local/bin/*
# 修改MHA脚本文件的信息
[root@xiaoQ-03 bin]# cp master_ip_failover master_ip_failover.bak
[root@xiaoQ-03 bin]# dos2unix /usr/local/bin/*
[root@xiaoQ-03 bin]# vim master_ip_failover

13 my $vip = ‘192.168.30.110/24’; 14 my $key = ‘1’; 15 my $if = ’eth0’; 16 my $ssh_start_vip = “/sbin/ ifconfig $if:$key $vip”; 17 my $ssh_stop_vip = “/sbin/ ifconfig $if:$key down”; 18 my $ssh_Bcast_arp= “/sbin/arping -I $if -c 3 -A 192.168.30.110”;

1
2
3
4
5
6
7
# 修改配置文件
[root@xiaoQ-03 ~]# vim /etc/mha/app1.cnf

master_ip_failover_script=/usr/local/bin/master_ip_failover
# 重启MHA服务
[root@xiaoQ-03 bin]# masterha_stop --conf=/etc/mha/app1.cnf
[root@xiaoQ-03 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 &

说明：进行MHA的VIP地址漂移时，只能在局域网环境进行漂移，不能实现跨网段的VIP地址漂移；

07 MHA故障报警实现MHA的报警功能，利用脚本实现，上传mha_script.tar文件到/usr/local/bin目录中，然后进行解压处理；

08 MHA额外补偿利用binlog_ser ver作为额外的日志补偿的冗余方案，即实时保存主库的bin_log日志文件到特定节点目录中；

1.15.5 数据库服务高可用故障切换

模拟进行指定主库节点故障情况，检查核实MHA相应功能脚本是否能够正确运行：

1
2
# 手工在主库上添加VIP
[root@xiaoQ-03 bin]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 (pid:103046) is running(0:PING_OK), master :192.168.30.101

1
-- 核实此时的MHA的主库节点

ifconfig eth0:1 192.168.30.110/24

1
2
3
4
5
-- 在主库节点手工添加vip地址信息
# 进行VIP地址连接测试
-- 可以使用navcat软件，连接MHA的vip地址，查看所连主机信息是否为主节点，当故障转移后可以核实VIP地址是否持续连接；
# 准备脚本文件
[root@xiaoQ-03 bin]# cp send_report send_report.bak

28 my $smtp=‘smtp.qq.com’;

1
-- smtp服务器地址域名

29 my $mail_from='330882721@qq.com’;

1
-- 发件箱信息配置

30 my $mail_user=‘330882721’;

1
-- 用户名 QQ号

31 my $mail_pass=‘ypokkranqlgkcbba’;

1
-- 邮箱授权码

32 my $mail_to='330882721@qq.com’; or my $mail_to=[’to1@qq.com’,’to2@qq.com’];

1
2
3
-- 收件箱信息配置
# 修改配置文件
[root@xiaoQ-03 ~]# vim /etc/mha/app1.cnf

report_script=/usr/local/bin/send_report

1
2
3
# 重启MHA服务
[root@xiaoQ-03 bin]# masterha_stop --conf=/etc/mha/app1.cnf
[root@xiaoQ-03 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
2
3
4
5
6
# 创建日志存放目录
[root@xiaoQ-03 ~]# mkdir -p /data/binlog_ser ver/
[root@xiaoQ-03 ~]# chown -R mysql.mysql /data/*
[root@xiaoQ-03 ~]# cd /data/binlog_ser ver
[root@xiaoQ-03 binlog_ser ver]# mysql -e "show slave status\G"|grep "Master_Log"
Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 1201

1
Relay_Master_Log_File: mysql-bin.000002

Exec_Master_Log_Pos: 1201

1
2
3
4
-- 拉取日志的起点，需要按照目前从库的已经获取到的二进制日志点为起点
[root@xiaoQ-03 binlog_ser ver]# mysqlbinlog -R --host=192.168.30.101 --user=mha --password=mha --raw --stop-never mysql-bin.000002 &
# 编写配置文件信息
[root@xiaoQ-03 ~]# vim /etc/mha/app1.cnf

[binlog1] no_master=1 hostname=192.168.30.103

1
2
3
4
master_binlog_dir=/data/binlog_ser ver/
# 重启MHA服务
[root@xiaoQ-03 bin]# masterha_stop --conf=/etc/mha/app1.cnf
[root@xiaoQ-03 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
2
# 确认目前的MHA的状态是良好的
[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf

实现MHA高可用切换的六个步骤：

01 MHA健康检查报错，显示主数据库节点无法正常连接

02 MHA进行重新选主，根据数组信息选择合适的备用新主节点

03 MHA进行节点关闭，选择完新的主节点后会将原有主节点的VIP地址消除

04 MHA进行节点切换，在新的主节点上进行非同步数据信息的补偿，

05 MHA进行主从重构，将从库连接到新的主库上

app1 (pid:30770) is running(0:PING_OK), master :192.168.30.101

1
2
3
4
5
# 模拟DB01数据库节点宕机
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld stop
Shutting down MySQL........... SUCCESS!
# 监控DB03日志信息的变化
[root@xiaoQ-03 ~]# tail -f /var/log /mha/app1/manager

Mon Jan 2 15:32:26 2023 - [warning] Got error on MySQL select ping: 1053 (Ser ver shutdown in progress) Mon Jan 2 15:32:26 2023 - [info] Executing SSH check script: exit 0 Mon Jan 2 15:32:26 2023 - [info] HealthCheck: SSH to 192.168.30.101 is reachable. Mon Jan 2 15:32:28 2023 - [warning] Got error on MySQL connect: 2003 (Can’t connect to MySQL ser ver on ‘192.168.30.101’ (111)) … 省略部分信息…

1
# 日志信息分析

[waring] Got error on MySQL connect: 2003 (Can’t connect to MySQL ser ver on ‘192.168.30.101’ (111)) [waring] Connection failed 2 time(s).. [waring] Got error on MySQL connect: 2003 (Can’t connect to MySQL ser ver on ‘192.168.30.101’ (111)) [waring] Connection failed 3 time(s).. [waring] Got error on MySQL connect: 2003 (Can’t connect to MySQL ser ver on ‘192.168.30.101’ (111)) [waring] Connection failed 4 time(s).. [waring] Master is not reachable from health checker! [waring] Master 192.168.30.101(192.168.30.101:3306) is not reachable!

1
-- 对故障主节点进行4次健康检查，主节点数据库服务仍旧无法连接，即判定主节点故障

[info] Connecting to a master ser ver failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/app1.cnf again, and tr ying to connect to all ser vers to check ser ver status.. [info] Reading application default configuration from /etc/mha/app1.cnf …. [info] Starting master failover …. [info] ** Phase 1: Configuration Check Phase completed. [info] * Phase 2: Dead Master Shutdown Phase.. [info] Forcing shutdown so that applications never connect to the current master.. Disabling the VIP on old master : 192.168.30.101 [info] * Phase 3: Master Recover y Phase.. [info] * Phase 3.1: Getting Latest Slaves Phase.. …. [info] * Phase 3.3: Determining New Master Phase.. [info] New master is 192.168.30.102(192.168.30.102:3306) [info] Starting master failover.. [info] * Phase 3.3: New Master Recover y Phase.. [info] Executing binlog save command: save_binar y_logs –command=save –start_file=mysql-bin.000002 –start_pos=1201 – output_file=/var/tmp/saved_binlog_binlog1_20230102153233.binlog –handle_raw_binlog=0 –skip_filter=1 –disable_log_bin=0 –manager_version=0.58 – oldest_version=8.0.26 –binlog_dir=/data/binlog_ser ver/ [info] Additional events were not found from the binlog ser ver. No need to save. Enabling the VIP - 192.168.30.110/24 on the new master - 192.168.30.102 Mon Jan 2 15:32:36 2023 - [info] OK. Mon Jan 2 15:32:36 2023 - [info] ** Finished master recover y successfully. [info] * Phase 3: Master Recover y Phase completed.

06 MHA切换完毕过程，架构故障转移切换完毕后做清理阶段，并进行最终汇报

实现MHA高可用切换的最终结果：

01 MHA故障节点转移后，邮件信息提示：

02 核实MHA的VIP地址已经发生漂移：

03 核实MHA进行切换的新主从关系，以及配置文件中的故障节点信息已经踢除：

[info] * Phase 4: Slaves Recover y Phase.. [info] * Phase 4.1: Starting Slaves in parallel.. [info] Resetting slave 192.168.30.103(192.168.30.103:3306) and starting replication from the new master 192.168.30.102(192.168.30.102:3306).. [info] Executed CHANGE MASTER. [info] Slave started. [info] All new slave ser vers recovered successfully. [info] * Phase 5: New master cleanup phase.. —– Failover Report —– Master failover to 192.168.30.102(192.168.30.102:3306) completed successfully. Mon Jan 2 15:32:37 2023 - [info] Sending mail..

1
2
# 获取新主从关系
[root@xiaoQ-03 ~]# mysql -e "show slave status\G"

*************************** 1. row ***************************

1
2
3
4
5
6
7
Slave_IO_State: Waiting for source to send event

Master_Host: 192.168.30.102
Master_User : repl
Master_Port: 3306
# 查看MHA配置文件信息
[root@xiaoQ-03 ~]# cat /etc/mha/app1.cnf

[binlog1] hostname=192.168.30.103

1
master_binlog_dir=/data/binlog_ser ver/

no_master=1 [ser ver default] manager_log=/var/log /mha/app1/manager manager_workdir=/var/log /mha/app1

1
master_binlog_dir=/data/binlog

04 MHA程序终止（管理程序manager终止）

以上故障模拟操作完成后，查看MHA程序进程已经终止：

1.15.6 数据库服务高可用修复方法

MHA故障通用修复方法步骤：（前提是MHA进行正常故障切换，通过日志可以检查是否进行正常的故障转移） 01 检查节点状态：在实际生产环境中，如果主库异常无法实现重新启动修复，可能就需要准备一台新的节点主机，重新构建1主2从的架构；但是如果使用新的节点主机，进行主从架构重构，修复高可用环境，就需要考虑新主机的在恢复数据的时间损耗；总之，需要将新节点的数据信息进行同步后，再将新节点变为新的从库，从而修复高可用主从关系，具体如何修复数据需要考虑实际情况

02 检查主从关系：

03 修复主从关系： master_ip_failover_script=/usr/local/bin/master_ip_failover password=mha ping_inter val=2 repl_password=123456 repl_user=repl report_script=/usr/local/bin/send_report ssh_user=root

1
user=mha

[ser ver2] hostname=192.168.30.102

1
port=3306

[ser ver3] hostname=192.168.30.103

1
2
3
4
5
port=3306
-- 故障节点信息已从配置文件中清理
[root@xiaoQ-03 binlog_ser ver]# ps -ef|grep mha
root     109328   1798  0 15:48 pts/0    00:00:00 grep --color=auto mha
[root@xiaoQ-03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 is stopped(2:NOT_RUNNING).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
# 检查节点数据库运行状态
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld status
ERROR! MySQL is not running
-- 发现数据库服务有异常节点进行恢复
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld start
Starting MySQL. SUCCESS!
-- 恢复DB01异常数据库服务节点
[root@xiaoQ-03 ~]# mysql -e "show slave status\G"|grep "Master_Host"

Master_Host: 192.168.30.102
-- 确认切换后的新主库信息
[root@xiaoQ-02 ~]# /etc/ init.d/mysqld status
SUCCESS! MySQL running (27332)
-- 在DB02上核实节点运行的状态
[root@xiaoQ-01 ~]# mysql -e "show slave status\G"|grep "Master_Host"
-- 在DB01上无法获取主从关系信息，需要修复DB01主从，最终实现1主2从效果
# 在DB01上修复主从：
[root@xiaoQ-01 ~]# mysql

db02 [(none)]>change master to

1
2
3
4
master_host='192.168.30.102',
master_user='repl',
master_password='123456',
master_auto_position=1;

db02 [(none)]> start slave;

1
2
3
4
# 在DB01上核实主从关系
[root@xiaoQ-01 ~]# mysql -e "show slave status\G"|grep "Master_Host"

Master_Host: 192.168.30.102

04 检查虚拟地址：

05 恢复日志同步：

06 调整配置文件：

1
[root@xiaoQ-02 ~]# ip a s eth0

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:61:cd:06 brd ff:ff:ff:ff:ff:ff inet 192.168.30.102/24 brd 192.168.30.255 scope global noprefixroute eth0 valid_lft forever preferred_lft forever inet 192.168.30.110/24 brd 192.168.30.255 scope global secondar y eth0:1 valid_lft forever preferred_lft forever inet6 fe80::560c:13cd:6107:b0de/64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft forever inet6 fe80::727b:5d03:94c0:9382/64 scope link noprefixroute valid_lft forever preferred_lft forever ifconfig eth0:1 192.168.30.110/24

1
2
3
4
5
6
7
8
9
-- 如果上面vip漂移失败，出现问题可以手工添加VIP
# 检查binlog_ser ver状态
[root@xiaoQ-03 ~]# ps -ef|grep mysqlbinlog
-- binlog_ser ver日志同步进程消失
# 修复binlog_ser ver状态
[root@xiaoQ-03 ~]# rm -rf /data/binlog_ser ver/*
[root@xiaoQ-03 ~]# cd /data/binlog_ser ver/
[root@xiaoQ-03 binlog_ser ver]# mysql -e "show slave status\G"|grep "Master_Log"
Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 1229

1
Relay_Master_Log_File: mysql-bin.000002

Exec_Master_Log_Pos: 1229

1
2
3
4
5
6
[root@xiaoQ-03 binlog_ser ver]# mysqlbinlog -R --host=192.168.30.102 --user=mha --password=mha --raw --stop-never mysql-bin.000002 &
[root@xiaoQ-03 binlog_ser ver]# ll
总用量 4
-rw-r----- 1 root root 1229 1月   2 16:28 mysql-bin.000002
# 确认核实配置文件中三个节点信息
[root@xiaoQ-03 binlog_ser ver]# cat /etc/mha/app1.cnf

[binlog1] hostname=192.168.30.103

1
master_binlog_dir=/data/binlog_ser ver/

no_master=1 [ser ver default] manager_log=/var/log /mha/app1/manager manager_workdir=/var/log /mha/app1

1
master_binlog_dir=/data/binlog

master_ip_failover_script=/usr/local/bin/master_ip_failover password=mha ping_inter val=2 repl_password=123456 repl_user=repl report_script=/usr/local/bin/send_report ssh_user=root

1
user=mha

[ser ver2] hostname=192.168.30.102

1
port=3306

[ser ver3] hostname=192.168.30.103

1
port=3306

[ser ver1] hostname=192.168.30.101

1
2
port=3306
-- 添加DB01故障节点到配置文件中

07 核实互信情况：

08 恢复启动MHA：

1.15.7 数据库服务高可用维护操作

实现MHA高可用主节点在线切换（手工操作）可以在主库没有故障的情况下，利用手工方式将主库业务切换到其它的从库节点上，从而解放原有主库节点（维护性操作时应用）；

1
2
3
4
5
[root@xiaoQ-03 ~]# masterha_conf_host --command=add --conf=/etc/mha/app1.cnf --hostname=192.168.30.101 --block=ser ver1 --params="port=3306"
-- 利用命令脚本添加新的节点信息
[root@xiaoQ-03 ~]# masterha_conf_host --command=delete --conf=/etc/mha/app1.cnf --block=ser ver1
-- 利用命令脚本删除指定的节点信息
[root@xiaoQ-03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf

Mon Jan 2 16:42:35 2023 - [info] All SSH connection tests passed successfully.

1
[root@xiaoQ-03 binlog_ser ver]# masterha_check_repl --conf=/etc/mha/app1.cnf

MySQL Replication Health is OK.

1
[root@xiaoQ-03 ~]# 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
[root@xiaoQ-03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 (pid:109463) is running(0:PING_OK), master :192.168.30.102

1
# 关闭MHA服务程序

masterha_stop –conf=/etc/mha/app1.cnf

1
2
-- 关闭mha程序是保证手工切换时，不会受到mha自动切换的影响
# 执行MHA手工切换

masterha_master_switch –conf=/etc/mha/app1.cnf –master_state=alive –new_master_host=192.168.30.101 –orig_master_is_new_slave – running_updates_limit=10000 …省略部分信息… It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.30.102(192.168.30.102:3306)? (YES/no):

1
2
3
4
-- 以上说明信息，表示在进行切换之前，在原有主库节点执行FLUSH NO_WRITE_TO_BINLOG TABLES这个命令
-- 此命令表示，关闭所有打开的表，强制关闭所有正在使用的表，不写入binlog；
-- 因为此时VIP还没有漂移，表示禁止原主库继续写入数据信息
# 关闭原主库的写入功能

db02 [(none)]>FLUSH NO_WRITE_TO_BINLOG TABLES;

1
Quer y OK, 0 rows affected (0.01 sec)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.30.102(192.168.30.102:3306)? (YES/no): yes

1
2
3
4
Starting master switch from 192.168.30.102(192.168.30.102:3306) to 192.168.30.101(192.168.30.101:3306)? (yes/NO): yes
-- 进行再一次核实确认，是否进行手工切换

master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it

ok to proceed? (yes/NO): yes

1
2
-- 表示master_ip_online_change_script此脚本没有定义，如果没有禁止当前主库写入的禁止，业务应用仍旧访问当前主库
-- 因为此时VIP还没有进行转移；

[info] Switching master to 192.168.30.101(192.168.30.101:3306) completed successfully.

1
2
3
-- 提示切换完成
# 进行MHA切换核验
[root@xiaoQ-03 ~]# 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
2
-- 重新启动MHA程序
[root@xiaoQ-03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 is stopped(2:NOT_RUNNING). [1]+ 退出 1 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
-- 检查状态失败，mha没有启动成功，因为vip信息并没有进行有效漂移

masterha_master_switch –conf=/etc/mha/app1.cnf –master_state=alive –new_master_host=192.168.30.102 –orig_master_is_new_slave – running_updates_limit=10000

1
2
3
-- 临时先切换回原有主节点，恢复MHA服务状态

| 序号 | 参数信息 | 解释说明 |

|—|—|—|

1
2
3
4
| 01 | --master_state | 执行手工切换的状态，alive表示主节点存活状态进行切换 |
| 02 | --new_master_host | 指定切换后的新主库节点的地址信息 |
| 03 | --orig_master_is_new_slave | 将原有主库指定为新的从库角色 |
| 04 | --running_updates_limit | 指定切换过程的时间限制，超过指定时间未完成切换，即切换失败，单位毫秒 |

执行切换命令参数信息说明：

在进行MHA高可用节点在线手工切换时，有以下信息需要注意：在进行MHA高可用手工切换时，无法自动调整原有主库的binlog_ser ver，需要手工重新拉取新主库的binlog；在进行MHA高可用手工切换时，无法进行触发邮件脚本功能，邮件发送功能只能在MHA产生故障转移时触发；在进行MHA高可用手工切换时，需要进行架构主从关系的切换，以及可以调整转移VIP地址信息；在进行MHA高可用手工切换时，需要对切换前的主库进行锁定（FTWRL flush tables with read lock)，避免数据不一致

进行MHA手工在线切换的合理操作：

01 应用master_ip_online_change_script功能脚本

功能描述：此脚本可以在线进行切换时，自动锁定原主库，以及将原主库VIP地址进行自动飘移；编写应用切换脚本文件信息：

1
[root@xiaoQ-03 ~]# 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] 65799

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

app1 (pid:65799) is running(0:PING_OK), master :192.168.30.102

1
2
3
4
5
-- mha服务状态恢复
# 编写MHA手工切换脚本文件：
[root@xiaoQ-03 ~]# cd /usr/local/bin/
[root@xiaoQ-03 bin]# cp master_ip_online_change master_ip_online_change.bak
[root@xiaoQ-03 bin]# vim master_ip_online_change

21 my $vip = “192.168.30.110”; 22 my $key = “1”; 23 my $ssh_start_vip = “/sbin/ ifconfig eth0:$key $vip”; 24 my $ssh_stop_vip = “/sbin/ ifconfig eth0:$key $vip down”; 25 my $ssh_Bcast_arp= “/sbin/arping -I eth0 -c 3 -A 192.168.30.110”;

1
2
3
4
5
6
7
# 修改MHA服务程序配置文件：
[root@xiaoQ-03 ~]# vim /etc/mha/app1.cnf

master_ip_online_change_script=/usr/local/bin/master_ip_online_change
# 关闭MHA服务程序进行核查：
[root@xiaoQ-03 ~]# masterha_stop --conf=/etc/mha/app1.cnf
[root@xiaoQ-03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 is stopped(2:NOT_RUNNING).

1
[root@xiaoQ-03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf

MySQL Replication Health is OK.

1
2
# 进行MHA服务手工在线切换：
[root@xiaoQ-03 ~]# masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.30.101 --orig_master_is_new_slave --

running_updates_limit=10000 …省略部分信息… It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.30.102(192.168.30.102:3306)? (YES/no): yes

1
2
-- FLUSH NO_WRITE_TO_BINLOG TABLES 命令在原有主库节点需要再执行一次；
Starting master switch from 192.168.30.102(192.168.30.102:3306) to 192.168.30.101(192.168.30.101:3306)? (yes/NO): yes

Sat Jan 7 13:48:50 2023 - [info] Switching master to 192.168.30.101(192.168.30.101:3306) completed successfully.

1
2
3
4
5
# 重构binlogser ver功能
[root@xiaoQ-03 ~]# cd /data/binlog_ser ver/
[root@xiaoQ-03 binlog_ser ver]# rm -rf ./*
[root@xiaoQ-03 binlog_ser ver]# mysql -e "show slave status\G"|grep "Master_Log"
Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 236

1
Relay_Master_Log_File: mysql-bin.000005

Exec_Master_Log_Pos: 236

1
2
3
[root@xiaoQ-03 binlog_ser ver]# mysqlbinlog -R --host=192.168.30.101 --user=mha --password=mha --raw --stop-never mysql-bin.000005 &
-- 此功能不进行重新配置，会导致MHA服务无法正常启动（若已经启动过，可以将进程杀死，重新启动）
# 进行MHA服务手工切换核验：

主机角色主机名称地址信息主库服务器 192.168.30.101 3306 从库服务器 192.168.30.102 3306 从库服务器 192.168.30.103(兼做管理节点) 3306

1.16 数据库服务读写分离

1.16.1 数据库服务读写分离概述介绍

在应用数据库主从架构或者数据库冗余架构时，都可以有效保证数据库逻辑故障或物理故障对业务的影响；但是，这两种架构进行应用时，业务访问过程只是访问主数据库节点，进行读写操作，当并发量大时无型中会对主节点造成不小压力；因此，可以设计一种新型的业务访问架构方式，可以实现将写数据请求发送到主节点，将读数据请求发送到从节点；最终，可以有效减少主节点的业务访问压力，这样设计的数据库架构称之为读写分离架构；

1.16.2 数据库服务读写分析原理分析

读写分离架构最终目的：实现业务写的请求到达主库，实现业务读的请求到达从库，从而减少主库的压力，实现不同请求的压力分担；可以利用读写分离中间件实现以上的功能需求：atlas(360公司出品) proxySQL 利用读写分离中间件的设置，当业务请求有select查询时，将请求发送给从库，当业务请求有update insert等修改时，将请求发送给主库

proxySQL是基于MySQL的一款开源的中间件的产品，是一个灵活的MySQL代理层，可以实现读写分离： proxySQL数据库中间件支持Quer y路由功能； pxoxySQL数据库中间件支持动态指定某个SQL进行缓存； proxySQL数据库中间件支持动态加载配置信息（无需重启ProxySQL服务） proxySQL数据库中间件支持故障切换和SQL的过滤功能 ProxySQL的参考网站连接： https://www.proxysql.com/ https://github.com/sysown/proxysql/releases

1.16.3 数据库服务读写分离架构搭建

步骤一：读写分离架构部署环境规划

为了实现读写分离架构构建，需要准备好三节点数据库+GTID复制环境+MHA环境（普通主从环境也可以构建）；

步骤二：读写分离架构软件下载安装

通过官方网站或者github可以下载proxySQL软件程序，并上传到数据库服务器中进行安装；

1
[root@xiaoQ-03 ~]# 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
2
-- 重新启动MHA程序
[root@xiaoQ-03 binlog_ser ver]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 (pid:17462) is running(0:PING_OK), master :192.168.30.101

1
2
# 上传安装软件程序
[root@xiaoQ-03 ~]# rpm -ivh proxysql-2.4.6-1-centos7.x86_64.rpm

警告：proxysql-2.4.6-1-centos7.x86_64.rpm: V4 RSA/SHA512 Signature, 密钥 ID 8217c97e: NOKEY 准备中… ################################# [100%]

1
2
3
# 启动运行软件程序

| 序号 | 库信息 | 配置信息 | 解释说明 |

|—|—|—|—|

1
| 01 | main | mysql_ser vers | 表示后端可以连接mysql服务器的列表 |

mysql_users 表示配置后端数据库的连接账号和监控账号

mysql_quer y_rules 表示指定quer y路由到后端不同服务器的规则列表

mysql_replication_hostgroups 表示节点分组配置信息，可以配置多个写或读节点到一个组中 02 disk

表示持久化的磁盘配置信息 03 stats

表示统计信息的汇总 04 monitor

表示监控收集的信息，比如数据库的监控状态等 05 stats_histor y

表示收集的有关软件内部功能的历史指标

步骤三：读写分离架构软件管理配置

在连接进入6032端口之后，表示进行proxysql的管理终端环境，终端环境中会加载五个重要的功能库：

说明：一般服务是通过配置文件保存功能配置信息，proxySQL是通过数据库中的表进行配置信息的存储设置；

1
2
[root@xiaoQ-03 ~]# systemctl start proxysql
[root@xiaoQ-03 ~]# netstat -lntup

Active Internet connections (only ser vers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

1
2
3
4
5
6
tcp        0            0         0.0.0.0:6032                  0.0.0.0:*               LISTEN      83020/proxysql
tcp        0            0         0.0.0.0:6033                  0.0.0.0:*               LISTEN      83020/proxysql
-- 启动生成的6032端口为管理端口，用于配置数据库中间件的功能信息连接此端口
-- 启动生成的6033端口为访问端口，用于提供对外的业务访问此端口
# 连接进入到proxySQL管理终端
[root@xiaoQ-03 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032

mysql: [Warning] Using a password on the command line interface can be insecure.

1
2
3
4
Welcome to the MySQL monitor.  Commands end with ; or \ g.
Your MySQL connection id is 1
Ser ver version: 5.5.30 (ProxySQL Admin Module)
# 查看proxySQL终端数据库信息

show databases;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
+-----+-----------------+----------------------------------------------+
| seq | name            | file                                         |
+-----+-----------------+----------------------------------------------+
| 0   | main            |                                              |
| 2   | disk            | /var/lib/proxysql/proxysql.db                |
| 3   | stats           |                                              |
| 4   | monitor         |                                              |
| 5   | stats_histor y  | /var/lib/proxysql/proxysql_stats.db          |
+-----+-----------------+----------------------------------------------+
5 rows in set (0.00 sec)
-- 不需要use到相应数据库中进行操作，可以操作的数据表信息如下
# 查看proxySQL终端数据表信息

show tables ;

1
+-----------------------------------------------------------------+

| tables

1
+-----------------------------------------------------------------+

ProxySQL管理接口的多层配置关系：

ProxySQL整套配置系统分为三层：第一层：RUNTIME: 代表proxySQL当前正在使用的配置，无法直接修改此配置，必须要从下一层（MEM层）load加载进来；第二层：MEMORY（主要修改的配置表） memor y层上面连接runtime层，下面连接disk持久化存储层；在这层可以在线操作ProxySQL配置，随意进行修改，不会影响生产环境，确认正常之后再加载到runtime和持久化保存到磁盘上具体修改操作方法为：insert、update、delete、select；第三层：DISK/CFG FILE 持久化配置信息，重启时可以从磁盘快速加载回来；

1
2
3
4
5
6
+-----------------------------------------------------------------+
32 rows in set (0.00 sec)
-- 表名以runtiem_开头的表示proxySQL服务中当前运行的配置内容，不能直接修改，不带runtime是下文图中mem相关的配置
# 01 user相关配置
LOAD MYSQL USERS TO RUNTIME;
-- MEM加载到runtime

SAVE MYSQL USERS TO MEMORY;

1
2
3
-- RUNTIME保存至MEM
LOAD MYSQL USERS FROM DISK;
-- DISK加载到MEM

SAVE MYSQL USERS TO DISK;

1
2
3
4
5
6
-- MEM保存至DISK
LOAD MYSQL USERS FROM CONFIG
-- CFG加载到MEM
# 02 ser ver相关配置
LOAD MYSQL SERVERS TO RUNTIME;
-- MEM加载到RUNTIME

SAVE MYSQL SERVERS TO MEMORY;

1
2
3
-- RUNTIME保存至MEM
LOAD MYSQL SERVERS FROM DISK;
-- DISK加载到MEM

SAVE MYSQL SERVERS TO DISK;

1
2
3
-- MEM保存至DISK
LOAD MYSQL SERVERS FROM CONFIG
-- CFG加载到MEM

需要注意：只有load到runtime状态时才会验证配置，在保存到mem或disk时，都不会发生任何警告或错误；当load到runtime时，如果出现了错误信息，将恢复为之前保存的状态，这时可以根据错误日志信息做检查；总结：日常配置过程大部分时间是在mem中进行配置，然后load到runtime，或者save到disk中，对于cfg很少使用；

ProxySQL基于SQL语句进行读写分离实践配置： ①. 在mysql_replication_hostgroup表中，配置读写组编号： proxySQL会根据ser ver的read only的取值将服务器进行分组： read_only=0的ser ver，即master会被分到编号为10的写组； read_only=1的ser ver，即slave会被分到编号为20的读组；（所以需要将从库设置：set global read_only=1 ）

② 添加主机到ProxySQL

1
2
3
# 03 MYSQL QUERY RULES相关配置
LOAD MYSQL QUERY RULES TO RUNTIME;
-- MEM加载到RUNTIME

SAVE MYSQL QUERY RULES TO MEMORY;

1
2
3
-- RUNTIME保存至MEM
LOAD MYSQL QUERY RULES FROM DISK;
-- DISK加载到MEM

SAVE MYSQL QUERY RULES TO DISK;

1
2
3
4
5
6
-- MEM保存至DISK
LOAD MYSQL QUERY RULES FROM CONFIG
-- CFG加载到MEM
# 03 MYSQL VARIABLES相关配置
LOAD MYSQL VARIABLES TO RUNTIME;
-- MEM加载到RUNTIME

SAVE MYSQL VARIABLES TO MEMORY;

1
2
3
-- RUNTIME保存至MEM
LOAD MYSQL VARIABLES FROM DISK;
-- DISK加载到MEM

SAVE MYSQL VARIABLES TO DISK;

1
2
3
-- MEM保存至DISK
LOAD MYSQL VARIABLES FROM CONFIG
-- CFG加载到MEM

db03 [(none)]>insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,‘proxy’);

1
Quer y OK, 1 row affected (0.00 sec)

db03 [(none)]>save mysql ser vers to disk;

1
Quer y OK, 0 rows affected (0.01 sec)

db03 [(none)]>load mysql ser vers to runtime;

1
Quer y OK, 0 rows affected (0.00 sec)

db03 [(none)]>select * from mysql_replication_hostgroups\G *************************** 1. row *************************** writer_hostgroup: 10 reader_hostgroup: 20 check_type: read_only comment: proxy

1
1 row in set (0.00 sec)

db03 [(none)]>insert into mysql_ser vers(hostgroup_id,hostname,port) values (10,‘192.168.30.110’,3306);

1
Quer y OK, 1 row affected (0.00 sec)

db03 [(none)]>insert into mysql_ser vers(hostgroup_id,hostname,port) values (20,‘192.168.30.102’,3306);

1
Quer y OK, 1 row affected (0.00 sec)

db03 [(none)]>insert into mysql_ser vers(hostgroup_id,hostname,port) values (20,‘192.168.30.103’,3306);

1
Quer y OK, 1 row affected (0.00 sec)

db03 [(none)]>save mysql ser vers to disk;

1
Quer y OK, 0 rows affected (0.01 sec)

db03 [(none)]>load mysql ser vers to runtime;

1
Quer y OK, 0 rows affected (0.00 sec)

db03 [(none)]>select * from mysql_ser vers\G; *************************** 1. row *************************** hostgroup_id: 20

③ 创建监控用户，并开启监控利用监控用户对后端节点的运行情况进行监控数据同步，一旦后端节点出现数据同步异常，就不要再向故障节点发送相应业务请求； hostname: 192.168.30.102 port: 3306 gtid_port: 0 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: *************************** 2. row *************************** hostgroup_id: 20 hostname: 192.168.30.103 port: 3306 gtid_port: 0 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: *************************** 3. row *************************** hostgroup_id: 10 hostname: 192.168.30.110 port: 3306 gtid_port: 0 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment:

1
2
3 rows in set (0.00 sec)
# 主库创建监控用户

db01 [(none)]>create user monitor@’%’ identified with mysql_native_password by ‘123’;

1
Quer y OK, 0 rows affected (0.02 sec)

db01 [(none)]>grant replication client on . to monitor@’%’;

1
2
Quer y OK, 0 rows affected (0.01 sec)
# 在proxysql中修改variables表配置信息

db03 [(none)]>set mysql-monitor_username=‘monitor’;

1
Quer y OK, 1 row affected (0.00 sec)

db03 [(none)]>set mysql-monitor_password=‘123’;

1
2
Quer y OK, 1 row affected (0.00 sec)
-- 以上变量信息修改为方法一

db03 [(none)]>update global_variables set variable_value=‘monitor’ where variable_name=‘mysql-monitor_username’;

1
Quer y OK, 1 row affected (0.00 sec)

db03 [(none)]>update global_variables set variable_value=‘123’ where variable_name=‘mysql-monitor_password’;

1
2
Quer y OK, 1 row affected (0.00 sec)
-- 以上变量信息修改为方法一

db03 [(none)]>load mysql variables to runtime;

1
Quer y OK, 0 rows affected (0.01 sec)

db03 [(none)]>save mysql variables to disk;

1
2
Quer y OK, 154 rows affected (0.00 sec)
# 检查核实配置信息

db03 [(none)]>select @@mysql-monitor_username\G *************************** 1. row *************************** @@mysql-monitor_username: monitor

1
1 row in set (0.00 sec)

④ 创建应用用户信息创建数据库应用用户信息，利用应用用户，可以使proxySQL进行数据库节点的操作管理；

⑤ 实用的读写规则配置

1
select ... for update规则的rule_id必须要小于普通的select规则的rule_id，proxySQL是根据rule_id的顺序进行规则匹配的；

db03 [(none)]>select @@mysql-monitor_password\G *************************** 1. row *************************** @@mysql-monitor_password: 123

1
2
1 row in set (0.00 sec)
# 查询监控日志信息

db03 [(none)]>select * from mysql_ser ver_connect_log;

1
2
3
4
5
6
7
+-------------------+------+-------------------------+---------------------------------+----------------------------------------+
| hostname          | port | time_start_us           | connect_success_time_us         | connect_error                          |
+-------------------+------+-------------------------+---------------------------------+----------------------------------------+
| 192.168.30.110    | 3306 | 1674026545375939        | 2831                            | NULL                                   |
| 192.168.30.103    | 3306 | 1674026546137911        | 1480                            | NULL                                   |
| 192.168.30.102    | 3306 | 1674026546899730        | 3781                            | NULL                                   |
-- 检查确认所有节点的连接访问情况

db03 [(none)]>select * from mysql_ser ver_ping_log;

1
2
3
4
5
6
7
+-------------------+------+-------------------------+-----------------------------+---------------+
| hostname          | port | time_start_us           | ping_success_time_us        | ping_error    |
+-------------------+------+-------------------------+-----------------------------+---------------+
| 192.168.30.102    | 3306 | 1674026696004217        | 1139                        | NULL          |
| 192.168.30.103    | 3306 | 1674026696095455        | 194                         | NULL          |
| 192.168.30.110    | 3306 | 1674026696186794        | 1466                        | NULL          |
-- 检查确认所有节点的网络连通情况

db03 [(none)]>select * from mysql_ser ver_read_only_log limit 3;

1
2
3
4
5
6
7
+-------------------+------+-------------------------+----------------------+-------------+--------+
| hostname          | port | time_start_us           | success_time_us      | read_only   | error  |
+-------------------+------+-------------------------+----------------------+-------------+--------+
| 192.168.30.110    | 3306 | 1674027579464285        | 1325                 | 0           | NULL   |
| 192.168.30.102    | 3306 | 1674027579479777        | 1743                 | 1           | NULL   |
| 192.168.30.103    | 3306 | 1674027579494993        | 308                  | 1           | NULL   |
-- 检查确认所有节点的只读状态信息（获取主库或从库主机信息）

db03 [(none)]>select * from mysql_ser ver_replication_lag_log;

1
2
3
Empty set (0.00 sec)
-- 检查确认所有节点的主从延时情况
# 主库创建应用用户

db01 [(none)]>create user root@’%’ identified with mysql_native_password by ‘123’;

1
Quer y OK, 0 rows affected (0.00 sec)

db01 [(none)]>grant all on . to root@’%’;

1
2
Quer y OK, 0 rows affected (0.00 sec)
# 在proxysql中添加数据库节点的管理用户信息

db03 [(none)]>insert into mysql_users(username,password,default_hostgroup) values(‘root’,‘123’,10);

1
Quer y OK, 1 row affected (0.00 sec)

db03 [(none)]>load mysql users to runtime;

1
Quer y OK, 0 rows affected (0.00 sec)

db03 [(none)]>save mysql users to disk;

1
2
3
4
Quer y OK, 0 rows affected (0.00 sec)
# 早期版本，需要开启事务的持续化（忽略）
update mysql_users set transaction_persistent=1 where username='root';
load mysql users to runtime;

save mysql users to disk;

1
-- 事务路由分配持续性，同一个事务的语句不会被分配到不同的组

insert into mysql_quer y_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,’^select.*for update$’,10,1); insert into mysql_quer y_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,’^select’,20,1);

1
-- 其余数据库操作语句信息，默认路由放置到主节点进行执行

load mysql quer y rules to runtime; save mysql quer y rules to disk;

步骤操作说明涉及数据表信息涉及操作信息 01 设置从库只读模式

read_only=1 02 添加主机组信息 mysql_replication_hostgroups

03 添加主机组节点信息 mysql_ser vers

04 添加用户信息（监控用户应用用户） global_variables mysql_users

05 添加读写分离规则 mysql_quer y_rules

⑥ 测试读写分离效果

读写分离配置过程总结：

步骤四：读写分离架构软件配置扩展

① 基于端口进行读写分离路由

② 基于用户进行读写分离路由

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
[root@xiaoQ-03 ~]# mysql -uroot -p123 -P6033 -h127.0.0.1 -e "begin;select @@ser ver_id;commit"
+-----------------+
| @@ser ver_id    |
+-----------------+
| 51              |
+-----------------+
-- 非查询操作走的是主节点
[root@xiaoQ-03 ~]# mysql -uroot -p123 -P6033 -h127.0.0.1 -e "select @@ser ver_id;"
+-----------------+
| @@ser ver_id    |
+-----------------+
| 52              |
+-----------------+
[root@xiaoQ-03 ~]# mysql -uroot -p123 -P6033 -h127.0.0.1 -e "select @@ser ver_id;"
+-----------------+
| @@ser ver_id    |
+-----------------+
| 53              |
+-----------------+
-- 查询操作走的是从节点

select * from stats_mysql_quer y_digest\G

1
2
-- 这个表对于分析SQL语句至关重要，是分析语句性能、定制路由规则指标的最主要来源
# 修改proxySQL监听SQL流量的端口号，监听多端口信息

set mysql-interfaces=‘0.0.0.0:6033;0.0.0.0:6034’

1
# 使监听端口配置信息生效

save mysql variables to disk;

1
2
[root@xiaoQ-03 ~]# systemctl restart proxysql
# 设定相应读写分离路由规则

delete from mysql_quer y_rules;

1
-- 为了测试效果，先清空已有规则信息

insert into mysql_quer y_rules(rule_id,active,proxy_port,destination_hostgroup,apply) values(1,1,6033,10,1),(2,1,6034,20,1); load mysql quer y rules to runtime; save mysql quer y rules to disk;

1
-- 除了基于端口进行分离，还可以基于监听地址（修改字段proxy_addr即可），也可以基于客户端地址（修改字段client_addr字段即可）；

insert into mysql_users(username,password,default_hostgroup) values (‘write’,‘123’,10),(‘reader’,‘123’,20); load mysql users to runtime; save mysql users to disk; delete from mysql_quer y_rules;

1
-- 为了测试效果，先清空已有规则信息

insert into mysql_quer y_rules(rule_id,active,username,destination_hostgroup,apply) values (1,1,‘write’,10,1),(2,1,‘reader’,20,1); load mysql users to runtime; save mysql users to disk;

基础章节-01-MySQL数据库服务中级课程#

1.00 课程知识章节说明#

1.15 数据库服务冗余架构#

1.15.1 数据库服务高可用前言介绍#

1.15.2 数据库服务高可用软件介绍#

1.15.3 数据库服务高可用环境构建#

1.15.5 数据库服务高可用故障切换#

01 MHA健康检查报错，显示主数据库节点无法正常连接#

02 MHA进行重新选主，根据数组信息选择合适的备用新主节点#

03 MHA进行节点关闭，选择完新的主节点后会将原有主节点的VIP地址消除#

04 MHA进行节点切换，在新的主节点上进行非同步数据信息的补偿，#

05 MHA进行主从重构，将从库连接到新的主库上#

06 MHA切换完毕过程，架构故障转移切换完毕后做清理阶段，并进行最终汇报#

01 MHA故障节点转移后，邮件信息提示：#

02 核实MHA的VIP地址已经发生漂移：#

03 核实MHA进行切换的新主从关系，以及配置文件中的故障节点信息已经踢除：#

04 MHA程序终止（管理程序manager终止）#

1.15.6 数据库服务高可用修复方法#

1.15.7 数据库服务高可用维护操作#

01 应用master_ip_online_change_script功能脚本#

1.16 数据库服务读写分离#

1.16.1 数据库服务读写分离概述介绍#

1.16.2 数据库服务读写分析原理分析#

1.16.3 数据库服务读写分离架构搭建#

基础章节-01-MySQL数据库服务中级课程

1.00 课程知识章节说明

1.15 数据库服务冗余架构

1.15.1 数据库服务高可用前言介绍

1.15.2 数据库服务高可用软件介绍

1.15.3 数据库服务高可用环境构建

1.15.5 数据库服务高可用故障切换

01 MHA健康检查报错，显示主数据库节点无法正常连接

02 MHA进行重新选主，根据数组信息选择合适的备用新主节点

03 MHA进行节点关闭，选择完新的主节点后会将原有主节点的VIP地址消除

04 MHA进行节点切换，在新的主节点上进行非同步数据信息的补偿，

05 MHA进行主从重构，将从库连接到新的主库上

06 MHA切换完毕过程，架构故障转移切换完毕后做清理阶段，并进行最终汇报

01 MHA故障节点转移后，邮件信息提示：

02 核实MHA的VIP地址已经发生漂移：

03 核实MHA进行切换的新主从关系，以及配置文件中的故障节点信息已经踢除：

04 MHA程序终止（管理程序manager终止）

1.15.6 数据库服务高可用修复方法

1.15.7 数据库服务高可用维护操作

01 应用master_ip_online_change_script功能脚本

1.16 数据库服务读写分离

1.16.1 数据库服务读写分离概述介绍

1.16.2 数据库服务读写分析原理分析

1.16.3 数据库服务读写分离架构搭建