ProxySQL构建MySQL高可用方案
安装
所使用的操作系统为ubuntu20.0。通过以下命令下载ProxySQL的deb安装包并进行安装:
wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql_2.4.2-ubuntu20_amd64.deb
dpkg -i proxysql_2.4.2-ubuntu20_amd64.deb
原本打算采用编译方式安装,结果编译耗时30多分钟,启动时还出现无法打开配置文件的情况,无奈之下改用deb包安装。
默认的安装位置如下:
– proxysql的配置文件proxysql.cnf
位于/etc/proxysql.cnf
– 数据目录datadir
为/var/lib/proxysql
– 错误日志文件errorlog
为/var/lib/proxysql/proxysql.log
启动ProxySQL服务:
systemctl start proxysql
proxysql原理

proxysql.cnf
文件中的配置仅在初次启动proxysql时生效。若proxysql的内置数据库已创建,后续将仅从内置数据库读取配置信息,此时修改proxysql.cnf
将不起作用。
MySQL主从复制
- MySQL主服务器地址:192.168.152.112:8000
- MySQL从服务器地址:192.168.152.113:8000
- ProxySQL实例1地址:192.168.152.112:6032
- ProxySQL实例2地址:192.168.153.112:6032
ProxySQL配置
1. 创建监控用户
在MySQL主节点(192.168.152.112:8000)创建监控用户:
-- 在MySQL主节点创建monitor用户
create user 'proxysql_monitor'@'%' identified with mysql_native_password by 'proxysql_monitor_password';
grant replication client on *.* to 'proxysql_monitor'@'%';
-- 针对组复制相关权限
grant select on performance_schema.replication_group_member_stats to 'proxysql_monitor'@'%';
grant select on performance_schema.replication_group_members to 'proxysql_monitor'@'%';
若主从复制包含mysql系统数据库自身,在MySQL从节点(192.168.152.113:8000)创建监控用户时无需重复操作。
2. 配置ProxySQL监控账号
在ProxySQL实例上(192.168.152.112:6032)配置监控账号:
mysql -uadmin -padmin -P6032 -h127.0.0.1
set mysql-monitor_username = 'proxysql_monitor';
set mysql-monitor_password = 'proxysql_monitor_password';
load mysql variables to runtime;
save mysql variables to DISK;
3. 配置监控的目标数据库实例
在ProxySQL实例上(192.168.152.112:6032)配置要监控的目标数据库实例:
-- 插入主节点信息
insert into mysql_servers ( hostgroup_id, hostname, port, max_replication_lag)VALUES ( 100, '192.168.152.112', 8000, 3);
-- 插入从节点信息
insert into mysql_servers ( hostgroup_id, hostname, port, max_replication_lag)VALUES ( 101, '192.168.152.113', 8000, 3);
load mysql servers to runtime; save mysql servers to disk;
查看mysql_servers
表:
mysql> select * from mysql_servers;
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 192.168.152.112 | 8000 | 0 | ONLINE | 1 | 0 | 1000 | 3 | 0 | 0 | |
| 101 | 192.168.152.113 | 8000 | 0 | ONLINE | 1 | 0 | 1000 | 3 | 0 | 0 | |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)
4. 创建读/写组的group Id
insert into mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, COMMENT) VALUES(100, 101, 'read_only', 'mysql m-s cluster');
load mysql servers to runtime;
save mysql servers to disk;
5. 配置读写分离的用户
在主节点创建读写用户:
create user 'rw_user'@'%' identified with mysql_native_password by 'rw_user_password';
grant all on *.* to 'rw_user'@'%';
在从节点创建只读用户:
create user 'ro_user'@'%' identified with mysql_native_password by 'ro_user_password';
grant select on *.* to 'ro_user'@'%';
flush PRIVILEGES;
配置读写分离用户:
-- 为主节点的rw_user用户指定到读写组
insert into mysql_users
(username, password, default_hostgroup, transaction_persistent, backend, frontend, comment)
values ('rw_user', 'rw_user_password', 100, 1, 1, 1, 'read/write user');
-- 为从节点的ro_user用户指定到只读组
insert into mysql_users
(username, password, default_hostgroup, transaction_persistent, backend, frontend, comment)
values ('ro_user', 'ro_user_password', 101, 1, 1, 1, 'read only user');
load mysql users to runtime;
save mysql users TO disk;
查看mysql_users
表:
mysql> select * from mysql_users;
+----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------+
| rw_user | rw_user_password | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | read/write user |
| ro_user | ro_user_password | 1 | 0 | 101 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | read only user |
+----------+------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------+
2 rows in set (0.00 sec)
6. 读写分离测试
测试rw_user账号连接:
mysql -urw_user -prw_user_password -P6033 -h127.0.0.1 -e "select @@server_id"
测试ro_user账号连接:
mysql -uro_user -pro_user_password -P6033 -h127.0.0.1 -e "select @@server_id"
示例输出:
root@ubuntu06:/usr/local/proxysql# mysql -urw_user -prw_user_password -P6033 -h127.0.0.1 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 18000 |
+-------------+
root@ubuntu06:/usr/local/proxysql# mysql -uro_user -pro_user_password -P6033 -h127.0.0.1 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 18001 |
+-------------+
7. 模拟MySQL主从故障转移
- 主节点宕机,模拟故障转移,原主节点下线,从节点升级为主节点:
mysql> select * from runtime_mysql_servers;
+--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 192.168.152.113 | 8000 | 0 | ONLINE | 1 | 0 | 1000 | 3 | 0 | 0 | |
| 101 | 192.168.152.112 | 8000 | 0 | SHUNNED | 1 | 0 | 1000 | 3 | 0 | 0 | |
| 101 | 192.168.152.113 | 8000 | 0 | ONLINE | 1 | 0 | 1000 | 3 | 0 | 0 | |
+--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
- 主节点恢复后作为从节点运行,状态更新:
mysql> select * from runtime_mysql_servers;
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 192.168.152.113 | 8000 | 0 | ONLINE | 1 | 0 | 1000 | 3 | 0 | 0 | |
| 101 | 192.168.152.112 | 8000 | 0 | ONLINE | 1 | 0 | 1000 | 3 | 0 | 0 | |
| 101 | 192.168.152.113 | 8000 | 0 | ONLINE | 1 | 0 | 1000 | 3 | 0 | 0 | |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
ProxySQL自身的集群
为避免单点故障,ProxySQL自身需构建集群。
在ProxySQL1(192.168.152.112)节点上执行:
set admin-admin_credentials = 'admin:admin;clusteradmin:clusteradmin';
set admin-cluster_username='clusteradmin';
set admin-cluster_password='clusteradmin';
load admin variables to runtime;
save admin variables to DISK;
insert into proxysql_servers(hostname, port, weight, comment) values('192.168.152.112', 6032, 1, 'proxysql node 1');
insert into proxysql_servers(hostname, port, weight, comment) values('192.168.152.113', 6032, 1, 'proxysql node 2');
load proxysql servers to runtime;
save proxysql servers to DISK;
查看proxysql_servers
表:
mysql> select * from proxysql_servers;
+-----------------+------+--------+-----------------+
| hostname | port | weight | comment |
+-----------------+------+--------+-----------------+
| 192.168.152.112 | 6032 | 1 | proxysql node 1 |
| 192.168.152.113 | 6032 | 1 | proxysql node 2 |
+-----------------+------+--------+-----------------+
2 rows in set (0.01 sec)
在ProxySQL2(192.168.152.113)节点上执行相同初始化语句后,会自动同步信息:
mysql> select * from mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+----------------
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+----------------
| ro_user | *D738C9F311FE0C81C55A34E241BE55B6243D63C3 | 1 | 0 | 101 | | 0 | 1 | 0 | 0 | 1 | 10000
| rw_user | *31E35F0357C0338CA567D6DC2D1E1EE8B1D7D6A6 | 1 | 0 | 100 | | 0 | 1 | 0 | 0 | 1 | 10000
| rw_user | *31E35F0357C0338CA567D6DC2D1E1EE8B1D7D6A6 | 1 | 0 | 100 | | 0 | 1 | 0 | 1 | 0 | 10000
| ro_user | *D738C9F311FE0C81C55A34E241BE55B6243D63C3 | 1 | 0 | 101 | | 0 | 1 | 0 | 1 | 0 | 10000
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+----------------
4 rows in set (0.00 sec)
这里需要注意,在ProxySQL1上mysql.user
表是明文,在ProxySQL2上是密文:

![