MySQL高可用代理ProxySQL

安装

系统为ubuntu20.0

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.cnf:/etc/proxysql.cnf
datadir:   /var/lib/proxysql
errorlog:   /var/lib/proxysql/proxysql.log

启动

systemctl start proxysql

proxysql原理:

proxysql.cnf 中的配置只在初次启动 proxysql 时生效。如果 proxysql 的内置数据库已经创建,后续只会从内置的数据库中读取配置信息,这时再修改 proxysql.cnf 就不起作用了。

root@ubuntu06:/usr/local/proxysql# mysql -uadmin -padmin -P6032 -h127.0.0.1
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, 2024, Oracle and/or its affiliates.
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.
mysql>
mysql>
mysql> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

main:内存数据库。这个库里的表分两类,runtime 开头的表存的是当前实际生效的配置。其它表存的是配置值,可通过 load 命令加载到 runtime 中,通过 save 命令持久化到磁盘中。
disk:sqllite 数据库。proxysql 启动时从这个数据库加载配置项。

 

MySQL主从复制

mysql 主:192.168.152.112:8000
mysql 从:192.168.152.113:8000

proxysql1 192.168.152.112:6032
proxysql2 192.168.152.112:6032

 

proxysql配置

 1,proxysql自身需要连接到被监控的MySQL实例,因此需要再被监控的MySQL实例上创建相关用户

MySQL 主节点192.168.152.112:8000创建monitor用户:

-- 1,MySQL 主节点创建monitor用户:
create user 'proxysql_monitor'@'%' identified with mysql_native_password 
by 'proxysql_monitor_password';
-- ALTER USER 'proxysql_monitor'@'%' IDENTIFIED BY 'proxysql_monitor_password';
-- FLUSH privileges;
grant replication client on *.* to 'proxysql_monitor'@'%';
-- group replication
grant select on performance_schema.replication_group_member_stats to 'proxysql_monitor'@'%';
grant select on performance_schema.replication_group_members to 'proxysql_monitor'@'%';

 MySQL 从节点192.168.152.113:8000创建monitor用户(如果主从复制包含了mysql系统数据库自身,则不用再次创建):

-- 1,MySQL 主节点创建monitor用户:
create user 'proxysql_monitor'@'%' identified with mysql_native_password 
by 'proxysql_monitor_password';
-- ALTER USER 'proxysql_monitor'@'%' IDENTIFIED BY 'proxysql_monitor_password';
-- FLUSH privileges;
grant replication client on *.* to 'proxysql_monitor'@'%';
-- group replication
grant select on performance_schema.replication_group_member_stats to 'proxysql_monitor'@'%';
grant select on performance_schema.replication_group_members to 'proxysql_monitor'@'%';

 

2,proxysql 上(192.168.152.112:6032)配置步骤1中的数据库账号,用以监控目标MySQL的主从复制节点状态

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)配置监控的目标数据库实例(192.168.152.112:8000,192.168.152.113:8000)

-- 主节点
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> 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)
-- 监控账号proxysql_monitor,连接到监控节点的日志查看,一开始有报错正常是因为笔者的环境MySQL主从没有同步mysql系统库,在从节点创建相关的监控用户后就正常了
mysql> select * from mysql_server_connect_log;
+-----------------+------+------------------+-------------------------+-----------------------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-----------------+------+------------------+-------------------------+-----------------------------------------------------------------------------------+
| 192.168.152.112 | 8000 | 1748242184707589 | 4645 | NULL |
| 192.168.152.113 | 8000 | 1748242185385232 | 0 | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
| 192.168.152.113 | 8000 | 1748242244708867 | 0 | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
| 192.168.152.112 | 8000 | 1748242245453236 | 1453 | NULL |
| 192.168.152.112 | 8000 | 1748242304709696 | 1504 | NULL |
| 192.168.152.113 | 8000 | 1748242305526984 | 0 | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
| 192.168.152.113 | 8000 | 1748242364710488 | 0 | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
| 192.168.152.112 | 8000 | 1748242365656714 | 797 | NULL |
| 192.168.152.112 | 8000 | 1748242424711003 | 1054 | NULL |
| 192.168.152.113 | 8000 | 1748242425655353 | 0 | Access denied for user 'proxysql_monitor'@'192.168.152.112' (using password: YES) |
| 192.168.152.112 | 8000 | 1748242484711945 | 972 | NULL |
| 192.168.152.113 | 8000 | 1748242485622272 | 2250 | NULL |
| 192.168.152.112 | 8000 | 1748242544713013 | 1076 | NULL |
| 192.168.152.113 | 8000 | 1748242545666379 | 1753 | NULL |
| 192.168.152.112 | 8000 | 1748242604714363 | 1224 | NULL |
| 192.168.152.113 | 8000 | 1748242605430811 | 1649 | NULL |
| 192.168.152.112 | 8000 | 1748242664715436 | 3282 | NULL |
| 192.168.152.113 | 8000 | 1748242665599634 | 1467 | NULL |
+-----------------+------+------------------+-------------------------+-----------------------------------------------------------------------------------+
18 rows in set (0.00 sec)

 

  4, proxysql上创建读/写组的group Id

-- 指定写组的id为100,读组的id为101,也即第三步骤插入mysql_servers时,对应的服务器的hostgroup_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,配置读写分离的用户


-- 5.1,主节点上创建一个读写的用户
create user 'rw_user'@'%' identified with mysql_native_password 
by 'rw_user_password';
grant all on *.* to 'rw_user'@'%';
-- 5.2,从节点上创建一个只读的用户
create user 'ro_user'@'%' identified with mysql_native_password 
by 'ro_user_password';
grant select on *.* to 'ro_user'@'%';
flush PRIVILEGES;
-- 5.3,配置读写分离用户,
--主节点的rw_user用户指定到default_hostgroup = 100的读写组
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用户指定到default_hostgroup = 101的只读组
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> 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#
root@ubuntu06:/usr/local/proxysql#
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 |
+-------------+
root@ubuntu06:/usr/local/proxysql#

 

    
7,模拟MySQL主从故障转移

1,主节点宕机
强制关闭主节点,模拟故障转移,原主节点下线,192.168.152.112,MySQL主从复制层面完成故障转移之后,在mysqlproxy层面,192.168.152.113升级为主节点(hostgroup_id自动更新为100),
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>
2,主节点宕机后恢复,作为从节点运行,上述的status=SHUNNED会自动更新为ONLINE,此时会更具read_only状态来判断主从节点,而不会自动将原始主节点添加到读写组hostgroup_id中
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)

 

8,proxysql自身的集群

proxysql自身如果是单点状态,同样会存在风险,因此proxysql自身也需要做一个cluster,proxysql自身作cluster比较简单。

1,在proxysql1也即192.168.152.112 节点上执行:

1,在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;
-- 插入proxysql多个节点的信息
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;
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)

1,在proxysql2也即192.168.152.113 节点上执行:

2,在192.168.152.113 节点上执行(与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;
-- 插入proxysql多个节点的信息
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;
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)
--自动同步proxysql1(192.168.152.112:6032)上的信息
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)

 

这里有一个细节:mysql.user表在proxysql1(192.168.152.112:6032)上明文,在proxysql2(192.168.152.113:6032)上是密文

 

 

作者:MSSQL123原文地址:https://www.cnblogs.com/wy123/p/18897129

%s 个评论

要回复文章请先登录注册