前言：前面的三篇文章基本讲解了一些MGR的基础运维工作，MGR在数据库层是高可用的集群，但是对于应用来说并不是，我们需要有一个中间件能够自动探测到挂掉的节点，并能够让应用连接到新节点，我们今天以ProxySQL为例来讲解如何配置MGR+ProxySQL实现应用高可用。

一、ProxySQL安装

ProxySQL的安装及其简单，可以直接下载rpm包进行安装，ProxySQL官网

wget https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm

直接安装可能会报错，需要安装几个依赖

yum install -y perl-DBD-MySQL perl-DBI

安装完依赖以后直接安装rpm包即可

[root@xucl src]# rpm -ivh proxysql-1.4.9-1-centos7.x86_64.rpm 
Preparing...                          ################################# [100%]
    package proxysql-1.4.9-1.x86_64 is already installed

那我这里已经装好了

二、ProxySQL配置文件

ProxySQL配置文件目录在/etc/proxysql.cnf，我们用默认配置即可，简单撸一下配置文件

[root@xucl src]# cat /etc/proxysql.cnf |grep -v "#"|grep -v "^$"
datadir="/var/lib/proxysql"
admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:6032"
}
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:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.5.30"
    connect_timeout_server=3000
    monitor_username="monitor"
    monitor_password="monitor"
    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
}
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)

datadir：数据存放目录
admin_variables：管理变量，包含管理用户密码及绑定的ip和端口
mysql_variables：运行变量，包含监听端口、监控用户名密码、监控频率、最大连接数等等
mysql_servers、mysql_users、mysql_query_rules、scheduler、mysql_replication_hostgroups等可以先不管，我们到命令行下进行修改

三、启动ProxySQL并配置MGR

ProxySQL启动很简单，直接

[root@xucl src]# service proxysql start
Starting ProxySQL: DONE!

登录到ProxySQL

[root@xucl src]# mysql -h127.0.0.1 -uadmin -padmin -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
admin@127.0.0.1 [(none)]>

配置mysql_server

admin@127.0.0.1 [main]>show create table mysql_servers\G
*************************** 1. row ***************************
       table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
    hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
    compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
    max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.00 sec)
admin@127.0.0.1 [main]>insert into mysql_servers(hostgroup_id,hostname,port) values(2,'172.16.230.158',3306),(2,'172.16.230.158',3307),(2,'172.16.230.158',3308);
Query OK, 3 rows affected (0.00 sec)
admin@127.0.0.1 [main]>save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
admin@127.0.0.1 [main]>load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 [main]>select * from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | 172.16.230.158 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.230.158 | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.230.158 | 3308 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

配置user

admin@127.0.0.1 [main]>insert into mysql_users(username,password,default_hostgroup,default_schema) values('xucl','xuclxucl',2,'xucl');
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 [main]>save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)
admin@127.0.0.1 [main]>load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 [(none)]>select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| xucl     | xuclxucl | 1      | 0       | 2                 | xucl           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

创建mgr状态检查函数和视图
去git下载相应的脚本，在主节点上创建

root@localhost [(none)]>source /usr/local/src/mysql_gr_routing_check-master/addition_to_sys.sql
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

设置monitor账号密码(也可以用默认的账号monitor，需要再mgr集群中创建)

admin@127.0.0.1 [main]>UPDATE global_variables SET variable_value='xucl' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 [main]>UPDATE global_variables SET variable_value='xuclxucl' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 [main]>LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 [main]>SAVE MYSQL VARIABLES TO DISK;
Query OK, 95 rows affected (0.01 sec)

设置group_replication_hostgrous

admin@127.0.0.1 [main]>insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,
    -> reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
    -> values (2,4,3,1,1,1,0,100);
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 [main]>save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
admin@127.0.0.1 [main]>load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)

定义如下：

writer_hostgroup：写节点groupid为2
backup_writer_hostgroup：备选写节点groupid为4
reader_hostgroup：只读节点groupid为3
offline_hostgroup：offline节点状态为1

查看成员状态

root@localhost [sys]>select * from gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | YES       |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)

查看成员组状态

admin@127.0.0.1 [(none)]>select hostgroup_id, hostname, port, status  from runtime_mysql_servers;
+--------------+----------------+------+--------+
| hostgroup_id | hostname       | port | status |
+--------------+----------------+------+--------+
| 2            | 172.16.230.158 | 3306 | ONLINE |
| 3            | 172.16.230.158 | 3307 | ONLINE |
| 4            | 172.16.230.158 | 3308 | ONLINE |
+--------------+----------------+------+--------+
3 rows in set (0.00 sec)

从上面看到，只有3306端口对应的实例是可写的，其他两个实例为可读实例

查看监控log

admin@127.0.0.1 [main]>select * from mysql_server_group_replication_log order by time_start_us desc limit 5;
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| hostname       | port | time_start_us    | success_time_us | viable_candidate | read_only | transactions_behind | error |
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| 172.16.230.158 | 3308 | 1528259591720722 | 1567            | YES              | YES       | 0                   | NULL  |
| 172.16.230.158 | 3307 | 1528259591719236 | 1425            | YES              | YES       | 0                   | NULL  |
| 172.16.230.158 | 3306 | 1528259591717542 | 3117            | YES              | NO        | 0                   | NULL  |
| 172.16.230.158 | 3308 | 1528259586720722 | 1417            | YES              | YES       | 0                   | NULL  |
| 172.16.230.158 | 3307 | 1528259586719211 | 2930            | YES              | YES       | 0                   | NULL  |
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
5 rows in set (0.00 sec)

可以看到监控正常

四、连接测试

xucl@127.0.0.1 [(none)]>select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

可以看到连接到默认的组2上，也就是3306端口所在的实例了

五、宕机测试

在3306节点实例停止group replication

root@localhost [(none)]>stop group_replication;
Query OK, 0 rows affected (9.54 sec)

再次查看成员状态

admin@127.0.0.1 [(none)]>select * from runtime_mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | 172.16.230.158 | 3308 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.230.158 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 172.16.230.158 | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

可以看到3308的实例作为写节点了
连接测试一下

xucl@127.0.0.1 [(none)]>select @@port;
+--------+
| @@port |
+--------+
|   3308 |
+--------+
1 row in set (0.00 sec)

ProxySQL已经自动帮我们完成了节点切换，并且速度还是非常快的

MySQL知识库

【MySQL】MGR系列文章（四）结合中间件

一、ProxySQL安装

二、ProxySQL配置文件

三、启动ProxySQL并配置MGR

四、连接测试

五、宕机测试