ProxySQL构建MySQL高可用方案

3周前发布 gsjqwyl
19 0 0

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主从故障转移
  1. 主节点宕机,模拟故障转移,原主节点下线,从节点升级为主节点:
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)
  1. 主节点恢复后作为从节点运行,状态更新:
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上是密文:

相关截图1

![

© 版权声明

相关文章

暂无评论

暂无评论...