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 组，实现正确的读写分离。

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

拓扑图

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

2. 两台 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"

4. 通过 mysql 客户端命令登录 ProxySQL 配置系统管理 ProxySQL

[20@root ~]# mysql -uproxysql -p123 -h127.0.0.1 -P6032
...
MySQL [(none)]>

5. 测试读写分离

# 为了测试出读写分离的效果来，我们向三台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

2. 使用 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'

2. 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=

3. 检查 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.  <=出现此字符串则正常

4. 后台启动 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 |
+-----------+------+------+-----------+

2. 模拟故障，将 master 节点 mariadb.service 服务关闭

[22@root ~]# systemctl stop mariadb.service

3. 测试故障转移