MySQL之八---MHA高可用与读写分离
MHA高可用与读写分离
基本结构
（1）一主一从
（2）一主多从
（3）多级主从
（4）双主
（5）循环复制
高级应用架构演变
高性能架构
读写分离架构(读性能较高)
代码级别
MySQL proxy (Atlas,mysql router,proxySQL(percona),maxscale)、
amoeba(taobao)
xx-dbproxy等。
分布式架构(读写性能都提高):
分库分表——cobar--->TDDL(头都大了),DRDS
Mycat--->DBLE自主研发等。
NewSQL-->TiDB
高可用架构
（3）单活:MMM架构——mysql-mmm（google）
（4）单活:MHA架构——mysql-master-ha（日本DeNa）,T-MHA
（5）多活:MGR ——5.7 新特性 MySQL Group replication(5.7.17) --->Innodb Cluster
（6）多活:MariaDB Galera Cluster架构,(PXC)Percona XtraDB Cluster、MySQL Cluster(Oracle rac)架构
架构介绍:
1主2从，master：db01 slave：db02 db03
MHA 高可用方案软件构成
Manager软件：选择一个从节点安装
Node软件：所有节点都要安装
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 添加或删除配置的server信息
Node工具包主要包括以下几个工具：
这些工具通常由MHA Manager的脚本触发，无需人为操作
save_binary_logs 保存和复制master的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的
purge_relay_logs 清除中继日志（不会阻塞SQL线程）
MHA环境搭建
| 主库 | 51 | node | |
|---|---|---|---|
| 从库 | 52 | node | |
| 从库 | 53 | node | manager |
准备环境（1主2从GTID）
主从配置软连接
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
配置各节点互信
db01：
ssh-keygen -t rsa ssh-copy-id -i ~/.ssh/id_rsa.pub 10.0.0.51 ssh-copy-id -i ~/.ssh/id_rsa.pub 10.0.0.52 ssh-copy-id -i ~/.ssh/id_rsa.pub 10.0.0.53
各节点验证
db01:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date
db02:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date
db03:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date安装mha软件
①mha官网：https://code.google.com/archive/p/mysql-master-ha/
github下载地址：https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
②rz mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rz mha4mysql-node-0.58- 0.el7.centos.noarch.rpm
[root@db01 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm [root@db02 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm [root@db03 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
db01创建mha用户
create user mha@'10.0.0.%' identified with mysql_native_password by 'mha'; grant all privileges on *.* to mha@'10.0.0.%' ;
Manager软件安装（db03）
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes yum install -y mha4mysql-manager*.rpm
配置文件准备(db03)
创建配置文件目录
mkdir -p /etc/mha
创建日志目录
mkdir -p /var/log/mha/app1
编辑mha配置文件
vim /etc/mha/app1.cnf [server default] manager_log=/var/log/mha/app1/manager manager_workdir=/var/log/mha/app1 master_binlog_dir=/data/3306/binlog/ user=mha password=mha ping_interval=2 repl_password=123 repl_user=repl ssh_user=root [server1] hostname=10.0.0.51 port=3306 [server2] hostname=10.0.0.52 candidate_master=1 port=3306 [server3] hostname=10.0.0.53 port=3306
互信检查
[root@db03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf [root@db03 ~]# ssh 10.0.0.51 hostname db01 [root@db03 ~]# ssh 10.0.0.52 hostname db02 [root@db03 ~]# ssh 10.0.0.53 hostname db03
主从状态检查
[root@db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
主从需要重新配置
报错总结：
create user repl@'10.0.0.%' identified with mysql_native_password by '123'; grant replication slave on *.* to repl@'10.0.0.%' ; change master to master_host='10.0.0.51', master_user='repl', master_password='123' , MASTER_AUTO_POSITION=1; start slave;
开启MHA
[root@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 &
查看MHA状态
[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:9823) is running(0:PING_OK), master:10.0.0.51 [root@db03 ~]# mysql -umha -pmha -h 10.0.0.51 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 51 | +---------------+-------+ [root@db03 ~]# mysql -umha -pmha -h 10.0.0.52 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 52 | +---------------+-------+ [root@db03 ~]# mysql -umha -pmha -h 10.0.0.53 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 53 | +---------------+-------+
高可用
企业高可用标准：
全年无故障时间 无故障时间 故障时间
99.9% 0.1% = 525.6 min KA+双主 ：人为干预
99.99% 0.01% = 52.56 min MHA :半自动化
99.999% 0.001% = 5.256 min PXC , MGR ,MGC
99.9999% 0.0001% = 0.5256 min 自动化,云化,平台化
MHA的软件结构
一堆perl写的脚本
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 添加或删除配置的server信息
node 组件
save_binary_logs 保存和复制master的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的
purge_relay_logs 清除中继日志（不会阻塞SQL线程）
MHA FailOver 原理
-
通过
masterha_manger启动 MHA manager 进程 -
监控
通过配置文件获取所有节点信息：系统、网络、SSH连接性，重点是主从状态
manager自动调用
masterha_master_monitor脚本，每隔ping_interval秒检测主库心跳，最多四次机会，如果都没有心跳，主库宕机。 -
选主
-
首先生成用来存放不同状态节点的数组：
-
alive数组：所有存活节点 -
lastest数组：最接近主库日志量的节点(position或者GTID) -
pref数组：设置权重candidate_master>0的节点 -
bad数组：不适合做主库的节点（没开binlog、日志差异在100000000字节以上的、设定了no_master）
-
-
根据规则匹配选主
-
满足
lastest并且满足pref数组，并且不在bad数组中的节点。优先选择为新主，如果存在多个，则按照节点号码顺序选择。 -
满足
lastest，并且不在bad数组中的节点。优先选择为新主，如果存在多个，则按照节点号码顺序选择。 -
满足
pref，并且不在bad数组中的节点。优先选择为新主，如果存在多个，则按照节点号码顺序选择。-
默认情况下，如果一个slave落后master 100M的relay logs的话，即使有权重，也会失效.
-
如果
check_repl_delay=0，即使落后很多日志，也强制选择其为备选主
-
-
满足
alive，并且不在bad数组中的节点。优先选择为新主，如果存在多个，则按照节点号码顺序选择。 -
如果还没找到，选主失败。
-
-
-
数据补偿
-
如果ssh如果能连接到主库，从库对比主库GTID 或者position号，立即保存缺失部分binlog，scp到各个从库(/var/tmp/xxxx临时文件)进行恢复(
save_binary_logs)。 -
如果ssh连接不上，对比从库之间的relaylog的差异(
apply_diff_relay_logs) ，并且恢复。 -
冗余方案（0.56），MHA提供
binlog server功能，实时拉取主库的binlog日志到备份节点
最靠谱的方法：5.7 + GTID + 增强半同步
-
-
Failover（
masterha_master_switch）-
所有从库解除身份
-
构建新的主从关系
-
-
应用透明
VIP漂移（
master_ip_failover） -
故障切换通知(send_reprt)
-
自愈(待开发...)
MHA 是一次性的高可用软件，一次故障转移后会自动关闭，注意及时修复！！！
MHA 故障模拟及处理
-
宕机主库（主库）
systemctl stop mysql
-
查看 manager 日志，监视故障转移过程（manager）
tail -f /var/log/mha/app1/manager
# 末尾必须显示successfully，才算正常切换成功。
-
修复主库（主库）
systemctl start mysql
-
恢复主从结构（主库）
CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3306, MASTER_AUTO_POSITION=1; start slave;
-
修改配置文件（manager）
方法一：
[server1] hostname=10.0.0.51 port=3306
方法二：
masterha_conf_host --command=add --conf=/etc/mha/app1.cnf --hostname=10.0.0.51 --block=server5 --params="port=3306" masterha_conf_host --command=delete --conf=/etc/mha/app1.cnf --block=server1
-
检测互信和主从状态（manager）
masterha_check_ssh --conf=/etc/mha/app1.cnf masterha_check_repl --conf=/etc/mha/app1.cnf
-
启动MHA（manager）
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状态（manager）
masterha_check_status --conf=/etc/mha/app1.cnf
Manager 参数介绍
# 服务默认配置 [server default] manager_log=/var/log/mha/app1/manager # 日志文件存放路径 manager_workdir=/var/log/mha/app1 # 工作目录 master_binlog_dir=/data/3306/ # 主库二进制文件存放目录 user=mha # MHA需要使用的用户 password=mha # 用户密码 ping_interval=2 # Ping主库间隔时间，尝试三次没有回应时，自动进行failover repl_password=123 # 主从复制密码 repl_user=repl # 主从复制用户 ssh_user=root # ssh用户 # VIP脚本路径 master_ip_failover_script=/usr/local/bin/master_ip_failover # 故障邮件提醒脚本路径 report_script=/usr/local/bin/send_report # 在线切换角色脚本路径 master_ip_online_change_script=/usr/local/bin/master_ip_online_change # 设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave candidate_master=1 # 默认情况下，如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间。如果设置 check_repl_delay=0,MHA在触发切换选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，可以保证候选主在切换后一定是新master。 check_repl_delay=0
# 数据库实例
[server1] hostname=10.0.0.51 port=3306 [server2] hostname=10.0.0.52 port=3306 [server3] hostname=10.0.0.53 port=3306 # 日志补偿的冗余方案 binlog server [binlog1] no_master=1 hostname=10.0.0.53 master_binlog_dir=/data/3306/binlog_server
-
上传脚本，增加执行权限（manager）
[root@db03 ~]# cd /usr/local/bin/ [root@db03 bin]# rz -E master_ip_failover [root@db03 bin]# chmod +x /usr/local/bin/*
注意：window下创建的脚本文件，需要转换结尾
[root@db03 ~]# dos2unix /usr/local/bin/master_ip_failover
dos2unix: converting file /usr/local/bin/master_ip_failover to Unix format ...
-
修改脚本内容（manager）
vim /usr/local/bin/master_ip_failover ... ... my $vip = '10.0.0.55/24'; my $key = '1'; my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; my $ssh_Bcast_arp= "/sbin/arping -I eth0 -c 3 -A 10.0.0.55"; ... ...
没有arping命令，需要安装net-tools
yum install net-tools -y
-
manager配置文件增加参数（manager）
vi /etc/mha/app1.cnf [server default] master_ip_failover_script=/usr/local/bin/master_ip_failover
-
主库手动配置VIP（主库）
方法一：
ifconfig eth0:1 10.0.0.55/24
方法二：
ip a a 10.0.0.55/24 brd 10.0.0.255 dev eth0 label eth0:1
注意：必须和
master_ip_failover文件中的网卡名一致：eth0:1($key = '1';)
-
重启MHA（manager）
masterha_stop --conf=/etc/mha/app1.cnf 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 测试
mysql -umha -pmha -h10.0.0.55
参数
master_ip_failover_script=/usr/local/bin/master_ip_failover 注意：/usr/local/bin/master_ip_failover，必须事先准备好
MHA 故障邮件提醒
-
上传脚本，增加执行权限（manager）
[root@db03 ~]# cd /usr/local/bin/ [root@db03 bin]# rz -E send_report [root@db03 bin]# chmod +x /usr/local/bin/*
-
修改脚本内容（manager）
vim /usr/local/bin/send_report ... ... my $smtp='smtp.qq.com'; # smtp服务器 my $mail_from='22654481@qq.com'; # 发件人邮箱地址 my $mail_user='22654481'; # 发件人用户名 QQ号 my $mail_pass='gemghsvgkeyzcagh'; # 发件人授权码 #my $mail_to=['to1@qq.com','to2@qq.com']; my $mail_to='22654481@qq.com'; # 收件人邮箱地址 ... ...
-
manager配置文件增加参数（manager）
vi /etc/mha/app1.cnf [server default] report_script=/usr/local/bin/send_report
-
重启MHA（manager）
masterha_stop --conf=/etc/mha/app1.cnf 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 &
-
关闭主库，看警告邮件
systemctl stop mysqld
模拟主库宕机
[root@db02 ~]# systemctl stop mysqld
修复MHA 架构1主两从
[root@db02 ~]# systemctl start mysqld
mysql> CHANGE MASTER TO
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
vim /etc/mha/app1.cnf
[server2]
hostname=10.0.0.52
port=3306
重启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 & [root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
binlog server（manager）
binlog server：日志补偿的冗余方案（5.6版本后，支持GTID并开启）
-
创建必要目录（从库）
mkdir -p /data/binlog_server chown -R mysql.mysql /data/*
-
获取主库日志起点（从库）
[root@db03 bin]# mysql -e "show slave status \G"|grep "Master_Log"
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 419
Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 419
-
检查主库状态（manager）
masterha_check_status --conf=/etc/mha/app1.cnf
-
进入和binlog文件不同的目录（从库）
cd /data/binlog_server
-
拉取主库binlog日志到当前路径（从库）
mysqlbinlog -R --host=10.0.0.52 --user=mha --password=mha --raw --stop-never mysql-bin.000002 &
-
manager配置文件增加参数（manager）
vim /etc/mha/app1.cnf [binlog1] no_master=1 hostname=10.0.0.53 master_binlog_dir=/data/binlog_server
-
重启MHA（manager）
masterha_stop --conf=/etc/mha/app1.cnf 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 & 故障处理
主库宕机，binlogserver 自动停掉，manager 也会自动停止。
处理思路：
1、重新获取新主库的binlog到binlogserver中
2、重新配置文件binlog server信息
3、最后再启动MHA
-
MHA 故障修复流程
-
检查并修复故障节点数据库实例
systemctl start mysql
-
检查并修复1主2从环境
CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3306, MASTER_AUTO_POSITION=1; start slave;
-
检查并修复manager配置文件（manager）
vi /etc/mha/app1.cnf [server1] hostname=10.0.0.51 port=3306
故障后会删除一个[serverN]，修复好后重新添加即可
-
检查并修复 VIP（主库）
ip a ifconfig eth0:1 10.0.0.55/24 ip a a 10.0.0.55/24 brd 10.0.0.255 dev eth0 label eth0:1
-
检查并修复 binlog server（从库）
重新获取新主库的binlog到
binlog server中mysql -e "show slave status \G"|grep "Master_Log" pkill mysqlbinlog rm -rf /data/binlog_server/* cd /data/binlog_server mysqlbinlog -R --host=10.0.0.51 --user=mha --password=mha --raw --stop-never mysql-bin.000003 &
-
使用检查脚本最后确认（manager）
masterha_check_ssh --conf=/etc/mha/app1.cnf masterha_check_repl --conf=/etc/mha/app1.cnf
-
启动manager（manager）
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 在线切换
手动切换
-
停止
masterha_stop --conf=/etc/mha/app1.cnf
-
切换角色
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive
--new_master_host=10.0.0.52
--orig_master_is_new_slave --running_updates_limit=10000
注意：
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
-
主库不记录二进制日志，获取只读锁
FLUSH NO_WRITE_TO_BINLOG TABLES; flush tables with read lock;
-
手动切换VIP
自动切换
-
上传脚本，增加执行权限（manager）
[root@db03 ~]# cd /usr/local/bin/ [root@db03 bin]# rz -E master_ip_online_change [root@db03 bin]# chmod +x /usr/local/bin/*
-
修改脚本内容（manager）
vim /usr/local/bin/master_ip_online_change ... ... my $vip = "10.0.0.55"; my $key = "1"; my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig eth0:$key $vip down"; my $ssh_Bcast_arp= "/sbin/arping -I eth0 -c 3 -A 10.0.0.55"; ... ...
-
manager配置文件增加参数（manager）
vi /etc/mha/app1.cnf [server default] master_ip_online_change_script=/usr/local/bin/master_ip_online_change
-
关闭MHA（manager）
masterha_stop --conf=/etc/mha/app1.cnf
-
检查主从状态（manager）
masterha_check_repl --conf=/etc/mha/app1.cnf
-
切换角色（manager）
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=10.0.0.51 --orig_master_is_new_slave --running_updates_limit=10000
# 注意：
# 主库配置
# mysql -e "FLUSH NO_WRITE_TO_BINLOG TABLES;" It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching.
Is it ok to execute on 10.0.0.51(10.0.0.51:3306)? (YES/no):yes ... ... Starting master switch from 10.0.0.51(10.0.0.51:3306) to 10.0.0.52(10.0.0.52:3306)? (yes/NO): yes
-
重构binlog server（从库）
mysql -e "show slave status \G"|grep "Master_Log" pkill mysqlbinlog rm -rf /data/3306/binlog_server/* cd /data/3306/binlog_server mysqlbinlog -R --host=10.0.0.51 --user=mha --password=mha --raw --stop-never mysql-bin.000006 &
-
启动manager（manager）
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 &
-
检查manager状态（manager）
masterha_check_status --conf=/etc/mha/app1.cnf
管理员在高可用架构维护的职责
1. 搭建：MHA+VIP+SendReport+BinlogServer
2. 监控及故障处理
3. 高可用架构的优化
核心是：尽可能降低主从的延时，让MHA花在数据补偿上的时间尽量减少。
5.7 版本，开启GTID模式，开启从库SQL并发复制。
读写分离
mysql-proxy：Oracle，https://downloads.mysql.com/archives/proxy/
Atlas：Qihoo，https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
dbproxy：美团，https://github.com/Meituan-Dianping/DBProxy
Cetus：网易乐得，https://github.com/Lede-Inc/cetus
Amoeba：https://sourceforge.net/projects/amoeba/
Cobar：阿里巴巴，Amoeba的升级版
Mycat：基于Cobar， http://www.mycat.io/
ProxySQL：https://proxysql.com/
ProxySQL
ProxySQL： MySQL中间件，两个版本：官方版和percona版，percona版是基于官方版基础上修改，C++语言开发，轻量级但性能优异(支持处理千亿级数据)，具有中间件所需的绝大多数功能，包括：
多种方式的的读/写分离
定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
缓存查询结果
后端节点监控
下载proxySQL https://proxysql.com/ https://github.com/sysown/proxysql/releases
基于RPM下载安装：https://github.com/sysown/proxysql/releases ProxySQL组成
服务脚本：/etc/init.d/proxysql
配置文件：/etc/proxysql.cnf
主程序：/usr/bin/proxysql
ProxySQL是基于MySQL的一款开源的中间件的产品，是一个灵活的MySQL代理层，可以实现读写分离，支持 Query路由功能，支持动态指定某个SQL进行缓存，支持动态加载配置信息（无需重启 ProxySQL 服务），支持故障切换和SQL的过滤功能。 相关 ProxySQL 的网站： https://www.proxysql.com/ https://github.com/sysown/proxysql/wiki
安装proxySQL
[root@db03 ~]# yum install -y proxysql-2.0.10-1-centos7.x86_64.rpm [root@db03 ~]# systemctl start proxysql [root@db03 ~]# netstat -tulnp tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 2115/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 2115/proxysql
ProxySQL中管理结构自带系统库
在ProxySQL，6032端口共五个库：
main,disk,stats ,monitor,stats_history
main: main 库中有如下信息： mysql_servers:后端可以连接 MySQL 服务器的列表 mysql_users: 配置后端数据库的账号和监控的账号。 mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表。 mysql_replication_hostgroups : 节点分组配置信息 注： 表名以 runtime开头的表示ProxySQL当前运行的配置内容，不能直接修改。不带runtime是下文图中Mem相关的配置。 disk
ProxySQL管理接口的多层配置关系 整套配置系统分为三层： 顶层 RUNTIME 中间层 MEMORY （主要修改的配置表） 持久层 DISK 和 CFG FILE
RUNTIME ： 代表 ProxySQL 当前正在使用的配置，无法直接修改此配置，必须要从下一层 （MEM层）“load” 进来。 MEMORY： MEMORY层上面连接 RUNTIME 层，下面disk持久层。这层可以在线操作`ProxySQL 配置，随便修改，不会影响生产环境。确认正常之后在加载达到`RUNTIME`和持久化的磁盘上。修改方法：` insert、update、delete、select。 DISK`和`CONFIG FILE： 持久化配置信息。重启时，可以从磁盘快速加载回来。
不同层次间移动配置
LOAD xxxx TO RUNTIME;
user相关配置
MEM 加载到runtime` `LOAD MYSQL USERS TO RUNTIME; runtime保存至MEM` `SAVE MYSQL USERS TO MEMORY; disk 加载到 MEM LOAD MYSQL USERS FROM DISK; MEM 到 disk SAVE MYSQL USERS TO DISK; CFG 到 MEM LOAD MYSQL USERS FROM CONFIG server 相关配置 MEM 加载到runtime` `LOAD MYSQL SERVERS TO RUNTIME; runtime 保存至MEM` `SAVE MYSQL SERVERS TO MEMORY; disk 加载到 MEM LOAD MYSQL SERVERS FROM DISK; MEM 到 disk SAVE MYSQL SERVERS TO DISK; CFG到 MEM
LOAD MYSQL SERVERS FROM CONFIG
mysql query rules配置
MEM 加载到runtime` `LOAD MYSQL QUERY RULES TO RUNTIME; runtime保存至MEM` `SAVE MYSQL QUERY RULES TO MEMORY; disk 加载到 MEM LOAD MYSQL QUERY RULES FROM DISK; MEM 到 disk SAVE MYSQL QUERY RULES TO DISK; CFG 到 MEM
LOAD MYSQL QUERY RULES FROM CONFIG
MySQL variables配置 MEM 加载到runtime` `LOAD MYSQL VARIABLES TO RUNTIME; runtime 保存至 MEM SAVE MYSQL VARIABLES TO MEMORY; disk 加载到MEM LOAD MYSQL VARIABLES FROM DISK; MEM 到 disk SAVE MYSQL VARIABLES TO DISK; CFG 到 ME LOAD MYSQL VARIABLES FROM CONFIG
总结： 日常配置其实大部分时间在
`MEM配置，然后load到RUNTIME，然后SAVE到DIsk。cfg很少使用。 例如 ：load xxx to runtime;save xxx to disk;
注意： 只有
`load到runtime状态时才会验证配置。在保MEM或disk时，都不会发生任何警告或错误。当load到runtime时，如果出现错误，将恢复为之前保存得状态，这时可以去检查错误日志。
从库设定read_only参数
set global read_only=1; #设定普通用户只读权限 set global super_read_only=1; #设定超级管理员只读权限
配置读写组编号
[root@db03 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 insert into mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) values (10,20,'proxy'); load mysql servers to runtime; save mysql servers to disk; select * from mysql_replication_hostgroups \G
添加主机到ProxySQL
(db03)mysql> insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.0.0.51',3306); insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.52',3306); insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.53',3306); load mysql servers to runtime; save mysql servers to disk;
创建主库监控用户
(db01) create user monitor@'%' identified with mysql_native_password by '123'; grant replication client on *.* to monitor@'%';
proxySQL修改variables表
法一：
set mysql-monitor_username='monitor'; set mysql-monitor_password='123'; load mysql variables to runtime; save mysql variables to disk;
法二 ：
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; UPDATE global_variables SET variable_value='123' WHERE variable_name='mysql-monitor_password'; load mysql variables to runtime; save mysql variables to disk;
查询监控日志
db03 [(none)]>select * from mysql_server_connect_log; db03 [(none)]>select * from mysql_server_ping_log; db03 [(none)]>select * from mysql_server_read_only_log; db03 [(none)]>select * from mysql_server_replication_lag_log; db03 [(none)]>select * from mysql_servers
注意：主库的10 是写状态 20是读状态 从库的20是读状态；主库的读写功能都具备
配置主库应用用户
create user root@'%' identified with mysql_native_password by '123'; grant all on *.* to root@'%';
proxysql 插入表
insert into mysql_users(username,password,default_hostgroup) values('root','123',10);
load mysql users to runtime;
save mysql users to disk;
select * from mysql_users;
早期版本开启事务持续化
update mysql_users set transaction_persistent=1 where username='root'; load mysql users to runtime; save mysql users to disk;
db03读写规则
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
values (1,1,'^select.*for update$',10,1); #满足第一条不在往下面走；匹配不到继续往下走； insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',20,1); load mysql query rules to runtime; save mysql query rules to disk;
注：
select … for update规则的rule_id必须要小于普通的select规则的rule_id，ProxySQL是根据rule_id的顺序进行规则匹配。
测试读写分离
[root@db03 ~]# mysql -uroot -p123 -P 6033 -h 127.0.0.1 -e "begin;select @@server_id;commit"####走写节点 只在写节点 [root@db03 ~]# mysql -uroot -p123 -P 6033 -h 127.0.0.1 -e "select @@server_id;" select * from mysql_servers delete from mysql_servers where hostgroup_id=20 and hostname='10.0.0.51' ###读状态不走51 load mysql query rules to runtime; save mysql query rules to disk; [root@db03 ~]# mysql -uroot -p123 -P 6033 -h 127.0.0.1 -e "select @@server_id;" db03 [(none)]>select * from status_mysql_query_digest \G
ProxySQL应用扩展——花式路由规则
ProxySQL基于端口的路由
set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034'; save mysql variables to disk;
重启生效
systemctl restart proxysql
设定路由规则
delete from mysql_query_rules; # 为了测试，先清空已有规则 insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply) values(1,1,6033,10,1), (2,1,6034,20,1); load mysql query rules to runtime; save mysql query rules to disk;
基于用户的路由
insert into mysql_users(username,password,default_hostgroup)
values('writer','123',10),('reader','123',20);
load mysql users to runtime;
save mysql users to disk;
delete from mysql_query_rules; # 为了测试，先清空已有规则
insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply)
values(1,1,'writer',10,1),(2,1,'reader',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
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以上
安装配置
yum install -y Atlas* cd /usr/local/mysql-proxy/conf mv test.cnf test.cnf.bak vi test.cnf [mysql-proxy] admin-username = user admin-password = pwd proxy-backend-addresses = 10.0.0.55:3306 proxy-read-only-backend-addresses = 10.0.0.51:3306,10.0.0.53:3306 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=utf8 启动atlas /usr/local/mysql-proxy/bin/mysql-proxyd test start ps -ef |grep proxy
Atlas功能测试
测试读操作：
mysql -umha -pmha -h 10.0.0.53 -P 33060
db03 [(none)]>select @@server_id;
测试写操作：
mysql> begin;select @@server_id;commit;
生产用户要求
开发人员申请一个应用用户 app( select update insert) 密码123456,要通过10网段登录
1. 在主库中,创建用户
grant select ,update,insert on *.* to app@'10.0.0.%' identified by '123456';
/usr/local/mysql-proxy/bin/encrypt 123456 ---->制作加密密码 vim 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 mysql> select * from help;
查询后端所有节点信息：
mysql> SELECT * FROM backends ; +-------------+----------------+-------+------+ | backend_ndx | address | state | type | +-------------+----------------+-------+------+ | 1 | 10.0.0.55:3306 | up | rw | | 2 | 10.0.0.51:3306 | up | ro | | 3 | 10.0.0.53:3306 | up | ro | +-------------+----------------+-------+------+ 3 rows in set (0.00 sec)
动态添加删除节点
REMOVE BACKEND 3;
动态添加节点
ADD SLAVE 10.0.0.53:3306;
保存配置到配置文件
SAVE CONFIG;
自动分表
介绍
使用Atlas的分表功能时，首先需要在配置文件test.cnf设置tables参数。
tables参数设置格式：数据库名.表名.分表字段.子表数量，
比如：
你的数据库名叫school，表名叫stu，分表字段叫id，总共分为2张表，那么就写为school.stu.id.2，如果还有其他的分表，以逗号分隔即可。
关于读写分离建议
MySQL-Router ---> MySQL官方 ProxySQL --->Percona Maxscale ---> MariaDB
很高兴各位朋友能欣赏作品，本文版权归作者和博客园共有，欢迎转载，请在文章页面明显位置给出原文出处，否则将追究法律责任。 原文链接： https://www.cnblogs.com/strugger-0316
