ProxySQL 搭建 MySQL 高可用架构（实现读写分离）

最新推荐文章于 2024-07-15 16:20:35 发布

一个老实巴交的农民

最新推荐文章于 2024-07-15 16:20:35 发布

阅读量545

点赞数 2

分类专栏： mysql 文章标签： mysql

原文链接：https://ke.qq.com/course/2739817

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文知识点，学于 “知数堂 MySQL DBA 实战课程”，讲师吴炳锡

如果对课程感兴趣可以私聊我，我推荐您报名学习（这样我能赚个鸡腿钱 O(∩_∩)O哈哈~）

或者点击链接直达：https://ke.qq.com/course/2739817

环境：

操作系统： CentOS Linux release 7.8.2003 (Core)

MySQL版本：8.0.21

ProxySQL 版本：ProxySQL version 2.0.15-20-g32bb92c, codename Truls

机器名 IP port comment
client 192.168.31.146 proxysql
master 192.168.31.11 3310 master
slave01 192.168.31.98 3310 slave
slave02 192.168.31.206 3310 slave

环境搭建

1. 修改 yum 源
	#cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
	[proxysql_repo]
	name= ProxySQL YUM repository
	baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
	gpgcheck=1
	gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
	EOF

2. 安装 proxysql
	#yum install proxysql OR yum install proxysql-version
	
	2.1 启动 proxysql
		#systemctl start proxysql
	2.2 校验是否启动成功
		#ps aux|grep proxysql
		proxysql   3595  0.0  0.3  60032  7336 ?        S    10:15   0:00 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
		proxysql   3596  0.4  0.8 161636 16596 ?        Sl   10:15   0:00 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
		root       3629  0.0  0.0 112828   984 pts/1    R+   10:15   0:00 grep --color=auto proxysql
	2.3 登录 proxysql
		#mysql -h127.0.0.1 -P3032 -uadmin -padmin
		mysql> use main;

配置 MySQL 主从环境

# 主库创建复制账号
mysql> create user 'rpl'@'%' identified by '123456';
mysql> grant replication slave on *.* to 'rpl'@'%';


# 在两个从库上执行下面的语句，启动复制结构
mysql> change master to master_host='192.168.31.11', master_user='rpl', master_password='123456', master_port=3310, master_ssl=1, master_auto_position=1;

mysql> start slave;

mysql> show slave status\G
*************************** 1. row ***************************
                            ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ...
1 row in set (0.00 sec)

这里需要注意的是，proxysql 识别主从库的方式是：判断 read_only = 1 | 0（1：从库，0：主库）
所以可以在从库执行 set global read_only = 1; （我这里是直接写在配置文件中的）

创建连接账号 & 管理账号

master：
mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global super_read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'monitor'@'192.168.31.%' identified with mysql_native_password by 'monitor';
Query OK, 0 rows affected (0.03 sec)

mysql> grant replication client on *.* to 'monitor'@'%';
Query OK, 0 rows affected (0.04 sec)

mysql> alter user 'test'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.04 sec)

mysql> grant all privileges on *.* to 'test'@'%';


slave：
mysql> flush privileges;

proxysql 连接 mysql-server

这里用 ‘100’ 表示主库，‘101’ 表示从库

# 创建分组
mysql> insert into mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment) values(100, 101, 'zst3310');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql_replication_hostgroups ;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 100              | 101              | read_only  | zst3310 |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

# 添加数据库到 server 中
mysql> insert into mysql_servers(hostgroup_id, hostname, port, max_connections) values(100, '192.168.31.11', 3310, 200);
Query OK, 1 row affected (0.01 sec)

mysql> insert into mysql_servers(hostgroup_id, hostname, port, max_connections) values(101, '192.168.31.98', 3310, 200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id, hostname, port, max_connections) values(101, '192.168.31.206', 3310, 200);
Query OK, 1 row affected (0.00 sec)

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.31.11  | 3310 | 0         | ONLINE | 1      | 0           | 200             | 0                   | 0       | 0              |         |
| 101          | 192.168.31.98  | 3310 | 0         | ONLINE | 1      | 0           | 200             | 0                   | 0       | 0              |         |
| 101          | 192.168.31.206 | 3310 | 0         | ONLINE | 1      | 0           | 200             | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

# 加载到 runtime 并保存
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)

# 配置账号
mysql> insert into mysql_users(username, password, default_hostgroup, default_schema, max_connections) values('test', '123456', 100, 'zst', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.01 sec)

mysql> save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test     | 123456   | 1      | 0       | 100               | zst            | 0             | 1                      | 0            | 1       | 1        | 1000            |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+

# 至此，已经配置好了一个简单的主从结构
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.31.11  | 3310 | 0         | ONLINE | 1      | 0           | 200             | 0                   | 0       | 0              |         |
| 101          | 192.168.31.206 | 3310 | 0         | ONLINE | 1      | 0           | 200             | 0                   | 0       | 0              |         |
| 101          | 192.168.31.98  | 3310 | 0         | ONLINE | 1      | 0           | 200             | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

校验

现在重新建立一个会话，并连接上去
因为指定了 default_schema，所以 'zst' 是默认 schema，可以直接 show tables;
可以看出，现在连接的是主库

#mysql -h 192.168.31.146 -P6033 -utest -p123456
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 3
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2020, 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.

mysql> show tables;
+---------------+
| Tables_in_zst |
+---------------+
| t1            |
+---------------+
1 row in set (0.01 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| master     |
+------------+
1 row in set (0.00 sec)

实现简单的读写分离

如果想让 select 请求全部转到 slave 上，可以通过配置 mysql_query_rules 表来实现。
生产环境千万别这么干！！

insert into mysql_query_rules(rule_id, active, match_pattern, dest_hostgroup, apply) values(1, 1, "^select", 101, 1);
load mysql query rules to runtime;
save mysql query rules to disk;

实际上这种做法是不严谨的，比如"select for update" 语句就处理不了。而且官方手册上也不建议这样做
所以可以借鉴官方的做法，使用正则的方式来实现

mysql> INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES(1,1,'^SELECT.*FOR UPDATE$',100,1), (2,1,'^SELECT',101,1);
Query OK, 2 rows affected (0.00 sec)

mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.00 sec)

这时候，在测试连接上执行 select @@hostname; 就会发生变化了

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave02    |
+------------+
1 row in set (0.00 sec)

mysql> \q
Bye

#mysql -h 192.168.31.146 -P6033 -utest -p123456
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 4
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2020, 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.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave01    |
+------------+
1 row in set (0.01 sec)

mysql> select @@hostname for update;
+------------+
| @@hostname |
+------------+
| master     |
+------------+
1 row in set (0.00 sec)

mysql> select id, c1, @@hostname from t1;
+----+-------------------+------------+
| id | c1                | @@hostname |
+----+-------------------+------------+
|  1 | abcde1234@163.com | slave01    |
|  2 | 123425405@qq.com  | slave01    |
+----+-------------------+------------+
2 rows in set (0.00 sec)

mysql> select id, c1, @@hostname from t1 where id=1 for update;
+----+-------------------+------------+
| id | c1                | @@hostname |
+----+-------------------+------------+
|  1 | abcde1234@163.com | master     |
+----+-------------------+------------+
1 row in set (0.00 sec)

这种做法还是比较粗暴的，而官方推荐的做法是，找出执行时间最长的、执行次数最多的 sql 所对应的 hash code 值，然后对 hash code 做读写分离
参考官方手册：https://github.com/sysown/proxysql/wiki/ProxySQL-Read-Write-Split-(HOWTO)
下面贴出部分内容

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5;

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES
(1,1,'0x38BE36BDFFDBE638',20,1);

一个老实巴交的农民

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
ProxySQL 搭建 MySQL 高可用架构（实现读写分离）

本文知识点，学于 “知数堂 MySQL DBA 实战课程”，讲师吴炳锡如果对课程感兴趣可以私聊我，我推荐您报名学习（这样我能赚个鸡腿钱 O(∩_∩)O哈哈~）或者点击链接直达：https://ke.qq.com/course/2739817环境：操作系统：CentOS Linux release 7.8.2003 (Core)MySQL版本：8.0.21ProxySQL 版本：ProxySQL version 2.0.15-20-g32bb92c, codename Tru..
复制链接

扫一扫