ProxySQL+MHA实现读写分离及高可用

Posted by zhubiao on November 14, 2017
ProxySQL+MHA实现读写分离及高可用

ProxySQL+MHA实现读写分离及高可用

通过 MySQL 的复制功能,实现了将Master节点的数据同步到一台或多台Slave节点上,从而减轻Master节点的“读”压力,但仍然还有一些问题未得到解决:

  1. 主服务器仍然承担全部的写请求和部分读请求,未实现读写分离
  2. 主服务存在单点故障

ProxySQL可以解决读写分离或将SQL语句路由到不同的后端MySQL服务器上,ProxySQL相当于MySQL的反向代理服务器,来自客户端的SQL请求经ProxySQL读写分离器根据管理员定义的路由规则路由到不同的后端服务器。

而MHA(Master High Available)是为了解决MySQL主服务器单点故障而设计,当MHA监控到Master节点故障时,会提升最接近Master节点数据的Slave节点成为新的Master节点,并将从节点中差异部分的数据应用于新Master节点上,尽可能将新Master节点的数据补充到最完整,然后其它Slave节点导向新的Master节点,并从新Master节点上补充缺少的数据,达到和新Master数据一致。

当Master节点故障,MHA实行故障转移,提升新的Slave节点成为Master节点后,ProxySQL能够感知到节点 read_only 参数的变化,判断出新的Master节点,从而自动将新Master节点分配到 writer 组,实现正确的读写分离。

下面通过实验演示读写分离和故障转移

拓扑图

Alt text

IP 安装软件 备注
172.18.17.20 keepalived,proxysql 通过ProxySQL实现读写分离,通过keepalived实现高可用
172.18.17.21 keepalived,proxysql 通过ProxySQL实现读写分离通过keepalived实现高可用
172.18.17.22 mariadb-server,MHA(mha4mysql-node) MySQL Master 节点
172.18.17.23 mariadb-server,MHA(mha4mysql-node) MySQL Slave 节点
172.18.17.24 mariadb-server,MHA(mha4mysql-node) MySQL Slave 节点
172.18.17.25 MHA(mha4mysql-manager,mha4mysql-node) 通过MHA实现故障转移


ProxySQL实现读写分离

  1. 三台MySQL主机安装上 mariadb-server,并配置为主从复制架构
# 为了加快实验的部署,实验中使用 ansible 自动化运维工具部署实验
# ansible 中使用的主机清单
[20@root ~]# cat /etc/ansible/hosts
[servs]
172.18.17.20
172.18.17.21
172.18.17.22
172.18.17.23
172.18.17.24
[ha]
172.18.17.20
172.18.17.21
[mysql]
172.18.17.22
172.18.17.23
172.18.17.24
[mha]
172.18.17.21
172.18.17.22
172.18.17.23
172.18.17.24
172.18.17.25
# 1. 三台MySQL服务器上安装 mariadb-server
[20@root ~]# ansible mysql -m yum -a "name=mariadb-server state=installed"
# 2. master 节点配置文件
[server]
server_id=1
skip_name_resolve=ON
innodb_file_per_table=ON
log_bin=master-log
relay_log=relay-log
# 3. slave 节点配置文件
[server]
server_id=2 <=另外一个节点编号为 server_id=3
skip_name_resolve=ON
innodb_file_per_table=ON
log_bin=master-log
relay_log=relay-log
relay_log_purge=0
read_only=1
# 4. 启动mariadb
[20@root ~]# ansible mysql -m service -a "name=mariadb state=started"
# 5. Master 节点上创建三个用户: pmadmin,reuser,pmonitor
# pmadmin 用户用于MHA或ProxySQL监控MySQL,管理MySQL所用
# reuser 用户用于主从复制所用
# pmonitor 用于用于ProxySQL监视MySQL状态所用
MariaDB> grant all on *.* to 'pmadmin'@'172.18.17.%' identified by '123';
MariaDB> grant replication client, replication slave on *.* to 'reuser'@'172.18.17.%' identified by '123';
MariaDB> grant super, replication client on *.* to 'pmonitor'@'172.18.17.%' identified by '123';
# 6. Slave 节点上指定 Master节点,启动 Slave
# 注意:此处日志的位置为主节点创建用户之前,从节点有可能被提升为主节点,所以应该有和主节点一样的用户。
MariaDB> change master to master_host='172.18.17.22', master_user='reuser', master_password='123', master_log_file='master-log.000004', master_log_pos=245;
MariaDB> start slave;
#7. Master 节点视角查看Slave节点信息
MariaDB> show slave hosts;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
| 3 | | 3306 | 1 |
| 2 | | 3306 | 1 |
+-----------+------+------+-----------+
  1. 两台 ProxySQL/MHA 服务器上安装配置 ProxySQL 实现读写分离
    下载 ProxySQL
# 1. 两台 ProxySQL/MHA 服务器上安装 ProxySQL
[20@root ~]# ls proxysql-1.4.3-1-centos7.x86_64.rpm
proxysql-1.4.3-1-centos7.x86_64.rpm
[20@root ~]# scp proxysql-1.4.3-1-centos7.x86_64.rpm 172.18.17.21:
proxysql-1.4.3-1-centos7.x86_64.rpm
[20@root ~]# ansible ha -m shell -a "yum -y install /root/proxysql-1.4.3-1-centos7.x86_64.rpm"

ProxySQL配置文件

# 2. 修改ProxySQL配置文件
[20@root ~]# cat /etc/proxysql.cnf
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="proxysql:123"
mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:3306;/tmp/mysql.sock"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="pmonitor"
monitor_password="123"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
# defines all the MySQL servers
mysql_servers =
(
{
address = "172.18.17.22" #
port = 3306 #
hostgroup = 10 #
status = "ONLINE" #
weight = 1 #
compression = 0 #
},
{
address = "172.18.17.23"
port = 3306
hostgroup = 20
status = "ONLINE"
weight = 1
compression = 0
},
{
address = "172.18.17.24"
port = 3306
hostgroup = 20
status = "ONLINE"
weight = 1
compression = 0
}
)
# defines all the MySQL users
mysql_users:
(
{
username = "pmadmin"
password = "123"
default_hostgroup = 10
active = 1
}
)
#defines MySQL Query Rules
mysql_query_rules:
(
{
rule_id=1
active=1
match_pattern="^(SELECT .* FOR UPDATE|select.*for update)$"
destination_hostgroup=10
apply=1
},
{
rule_id=2
active=1
match_pattern="^(INSERT|insert)"
destination_hostgroup=10
apply=1
},
{
rule_id=3
active=1
match_pattern="^(SELECT|select)"
destination_hostgroup=20
apply=1
}
)
scheduler=
(
)
mysql_replication_hostgroups=
(
{
writer_hostgroup=10
reader-hostgroup=20
comment="production database"
}
)

启动 proxyslq

[20@root ~]# ansible ha -m service -a "name=proxysql state=started"
  1. 通过 mysql 客户端命令登录 ProxySQL 配置系统管理 ProxySQL
[20@root ~]# mysql -uproxysql -p123 -h127.0.0.1 -P6032
...
MySQL [(none)]>
  1. 测试读写分离
# 为了测试出读写分离的效果来,我们向三台MySQL服务器中手动插入不同的数据。
# Master节点上创建数据库 mydb 和表 mytable
MariaDB> create database mydb;
MariaDB> create table mydb.mytable(id int);
# 向 172.18.17.23 slave 节点中插入数据
MariaDB> insert into mydb.mytable values(2);
# 向 172.18.17.24 slave 节点插入数据
MariaDB> insert into mydb.mytable values(3);
# 测试MySQL客户端查询数据,可以看到通过轮询的方式从两台 slave 上读取数据
[20@root ~]# mysql -u pmadmin -p123 -h 172.18.17.20 -P 3306
...
MySQL [(none)]> select * from mydb.mytable;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
MySQL [(none)]> select * from mydb.mytable;
+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.01 sec)
MySQL [(none)]> select * from mydb.mytable;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
MySQL [(none)]> select * from mydb.mytable;
+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
# 测试写入数据
MySQL [(none)]> insert into mydb.mytable values(1);
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> select * from mydb.mytable;
+------+
| id |
+------+
| 3 |
| 1 |
+------+
2 rows in set (0.00 sec)
MySQL [(none)]> select * from mydb.mytable;
+------+
| id |
+------+
| 2 |
| 1 |
+------+
2 rows in set (0.00 sec)

使用ProxySQL时注意事项:

  • 注意ProxySQL配置文件 proxysql.cnf的生效时间点, 只有当第一次服务启动时,没有 proxsyql.db 文件时才会加载配置文件,所以建议后期没有必要对 proxysql.db 配置文件进行修改,服务启动后可以使用 mysql 客户端工具连接到ProxySQL配置系统上进行配置,不需要重启服务,实时生效。
  • ProxySQL配置系统分三层设计,如下所示:
+-------------------------+
| RUNTIME |
+-------------------------+
/|\ |
| |
[1] | [2] |
| \|/
+-------------------------+
| MEMORY |
+-------------------------+ _
/|\ | |\
| | \
[3] | [4] | \ [5]
| \|/ \
+-------------------------+ +-------------------------+
| DISK | | CONFIG FILE |
+-------------------------+ +-------------------------+


MHA实现故障转移

MHA架构由两部分组成,第一部分为 MHA manager,通常部署在单台服务器上,用来管理一个或多个MySQL复制集群;第二部分为 MHA node,部署在每一台MySQL服务器上,包括 MHA manager 服务器。MHA manager 监控 MHA node 节点,当发现 MHA node 节点上的 MySQL Master 节点故障时,将启动故障转移操作。

下面完成MHA架构的部署:

MHA实验环境确认

  • 部署好MySQL主从复制架构
  • MySQL主从服务器均开启 bin_log(二进制日志),relay_log (中继日志)功能
  • MySQL从服务器设置为 ready_only=1(只读),relay_log_purge=0(关闭自动修建中继日志)
  • 所有MHA节点间建立基于key的无密钥ssh通信

除了基于key的无密钥ssh通信环境为准备好,其它我们在上面的实验中已经准备完成,下面使用脚本快速完成该步骤:

  1. 准备以下bash脚本
# 说明:本脚本只使用于我当前分配的IP实验环境,需自行根据自己的实验环境进行修改
[20@root ~]# cat copy_ssh_key.sh
#!/bin/bash
ssh-keygen -t rsa -N '' -q -f /root/.ssh/id_rsa
yum -y install expect &> /dev/null
for i in 0 1 2 3 4;do
path="172.18.17.2$i"
password="224517ok"
/usr/bin/expect <<EOF
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub "$path"
expect {
"yes/no" { send "yes\n"; exp_continue }
"password:" { send "$password\n" }
}
expect eof
EOF
done
  1. 使用 ansible 将脚本推送到每一台MHA节点上,并执行脚本
# 1. 推送脚本到所有 MHA 节点
ansible mha -m copy -a 'src=/root/copy_ssh_key.sh dest=/root/'
# 2. 所有 mha 节点执行脚本
ansible servs -m shell -a '/usr/bin/bash /root/copy_ssh_key.sh'

MHA 架构的部署

  1. 安装 MHA
# 1. MHA master 节点上安装 mha4mysql-manager 和 mha4mysql-node
[20@root ~]# ansible ha -m command -a 'yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm mha4mysql-manager-0.56-0.el6.noarch.rpm'
# 2. MHA node 节点上安装 mya4mysql-node
[20@root ~]# ansible mysql -m command -a 'yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm'
  1. MHA manager 节点上创建如下配置文件
# 1. 创建存放配置文件的目录
[20@root ~]# mkdir /etc/masterha
# 2. 创建配置文件
# 配置文件中指定的目录,不需要手动创建,当我们执行 managerha_check_rpl 检测命令时会自动创建
[20@root ~]# cat /etc/masterha/app.cnf
[server default]
user=pmadmin
password=123
manager_workdir=/app/masterha/app
remote_workdir=/app/masterha/app
manager_log=/app/masterha/app
ssh_user=root
repl_user=reuser
repl_password=123
ping_interval=1
[server1]
hostname=172.18.17.22
#ssh_port=
[server2]
hostname=172.18.17.23
#ssh_port=
[server3]
hostname=172.18.17.24
#ssh_port=
  1. 检查 MHA 环境是否满足要求
# 1. 检查各节点间ssh通信(基于key的无密钥)是否正常
[20@root ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf
...
All SSH connection tests passed successfully. <=出现此字符串则正常
# 2. 检查Mysql复制集群是否正确配置
[20@root ~]# masterha_check_repl --conf=/etc/masterha/app.cnf
...
MySQL Replication Health is OK. <=出现此字符串则正常
  1. 后台启动 managerha_manager
# 1. 手动启动 managerha_manager 并放到后台执行,且与终端无关,并将输出信息导入到日志文件中
[20@root ~]# nohup masterha_manager --conf=/etc/masterha/app.cnf &>> /app/masterha/app/manager.log &
# 2. 检查 managerha_manager 是否启动成功
[20@root ~]# masterha_check_status --conf=/etc/masterha/app.cnf
app (pid:6542) is running(0:PING_OK), master:172.18.17.22 <=成功启动的标志
# 3. 停止 managerha_manager 的操作
[20@root ~]# masterha_stop --conf=/etc/masterha/app.cnf
# 4. 检查 managerha_manager 是否停止
[20@root ~]# masterha_check_status --conf=/etc/masterha/app.cnf
app is stopped(2:NOT_RUNNING). <=停止的标志

测试MHA故障转移,并观察ProxySQL是否自动重新分配读写组成员
查看当前主从节点情况

# 1. 通过 ProxySQL 配置系统观察当前的读写组
[20@root ~]# mysql -uproxysql -p123 -h127.0.0.1 -P 6032 -e 'select hostgroup_id, hostname, status, port, weight from runtime_mysql_servers'
+--------------+--------------+--------+------+--------+
| hostgroup_id | hostname | status | port | weight |
+--------------+--------------+--------+------+--------+
| 10 | 172.18.17.22 | ONLINE | 3306 | 1 |
| 20 | 172.18.17.24 | ONLINE | 3306 | 1 |
| 20 | 172.18.17.23 | ONLINE | 3306 | 1 |
+--------------+--------------+--------+------+--------+
# 2. 从 master 节点视角查看
[22@root ~]# mysql -h 172.18.17.22 -upmadmin -p123 -e 'show slave hosts;'
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
| 3 | | 3306 | 1 |
| 2 | | 3306 | 1 |
+-----------+------+------+-----------+
  1. 模拟故障,将 master 节点 mariadb.service 服务关闭
[22@root ~]# systemctl stop mariadb.service
  1. 测试故障转移