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上是密文：
暂无评论...