proxy sql实现读写分离
一、proxysql介绍
- 读写分离
- 基于用户、schema、语句对SQL进行路由
- 缓存结果，减轻后端数据库服务器压力
- 健康状态检测
二、proxysql实现读写分离
1、环境描述
192.168.140.10 主库 MySQL 8.0
192.168.140.11 从库 MySQL 8.0
192.168.140.12 proxysql
2、两台数据库配置读写分离，从库添加read_only参数
3、安装proxysql
cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key
EOF
[root@proxysql ~]# yum install -y proxysql mariadb
[root@proxysql ~]# systemctl enable --now proxysql
[root@proxysql ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 16544/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 16544/proxysql
6032端口: proxysql管理端口，用于对Proxysql进行配置、状态查看
6033端口: proxysql业务连接端口
3.1 proxysql内置库说明
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)
说明：
1、main库，最主要的库，对proxysql进行配置大多数要修改main库中的表，为内存数据库，需要持久化保存
2、disk库， 和main库一致，是main库的持久化
3、stats库，统计信息库
4、monitor库，对后端服务器做健康状态检查的信息库
5、stats_history库，历史状态信息
3.2 main库中主要的表说明
mysql> show tables from main;
+----------------------------------------------------+
| tables |
+----------------------------------------------------+
| coredump_filters |
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_firewall_whitelist_rules |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_hostgroup_attributes |
| mysql_query_rules | // 路由SQL语句的表
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers | // 存储后端数据库服务器连接信息的表
| mysql_servers_ssl_params |
| mysql_users | // 存储连接后端数据库用户的表
| proxysql_servers |
| restapi_routes |
| runtime_checksums_values |
| runtime_coredump_filters |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_firewall_whitelist_rules |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users |
| runtime_mysql_galera_hostgroups |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_hostgroup_attributes |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_servers_ssl_params |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_restapi_routes |
| runtime_scheduler |
| scheduler |
+----------------------------------------------------+
38 rows in set (0.01 sec)
4、在后端主库创建允许proxysql连接的用户
mysql> CREATE USER 'proxyuser'@"192.168.183.12" identified by 'WWW.1.com';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT all ON *.* TO 'proxyuser'@'192.168.183.12';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
5、在proxysql上添加后端服务器信息、并指定读、写组
[root@proxysql ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032
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)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1, "192.168.183.10",3306,1,"write group");
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2, "192.168.183.11",3306,1,"read group");
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 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1 | 192.168.183.10 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | write group |
| 2 | 192.168.183.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | read group |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
2 rows in set (0.00 sec)
说明：
数字1代表写组
数字2代表读组
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)
6、在Proxysql上添加连接后端数据库的用户
mysql> insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values("proxyuser","WWW.1.com",1,1);
Query OK, 1 row 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 | attributes | comment |
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| proxyuser | WWW.1.com | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | |
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)
7、在proxysql添加健康状态检查的用户
7.1 在后端主库创建健康状态检查用户
mysql> CREATE USER 'healthuser'@"%" IDENTIFIED BY 'WWW.1.com';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT select ON *.* TO 'healthuser'@"%";
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
7.2 proxysql上添加该用户
mysql> set mysql-monitor_username="healthuser";
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password="WWW.1.com";
Query OK, 1 row affected (0.01 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql variables to disk;
Query OK, 162 rows affected (0.00 sec)
8、添加读写分离的路由规则
mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(1,1,"^SELECT.*FOR UPDATE$
",1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(2,1,"^SELECT",2,1);
Query OK, 1 row affected (0.00 sec)
mysql> select rule_id, active, match_digest, destination_hostgroup, apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1 | 1 | ^SELECT.*FOR UPDATE$ | 1 | 1 |
| 2 | 1 | ^SELECT | 2 | 1 |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)
mysql> save admin variables to disk;
Query OK, 49 rows affected (0.01 sec)
9、测试读写分离
9.1 登录Proxysql业务端，任意执行读写操作
[root@proxysql ~]# mysql -uproxyuser -pWWW.1.com -h 127.0.0.1 -P6033
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 2
Server version: 5.5.30 (ProxySQL)
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> create database test1;
Query OK, 1 row affected (0.01 sec)
mysql> create database test2;
Query OK, 1 row affected (0.01 sec)
mysql> select user,host from mysql.user;
+------------------+----------------+
| user | host |
+------------------+----------------+
| healthuser | % |
| repluser | % |
| proxyuser | 192.168.183.12 |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+----------------+
7 rows in set (0.00 sec)
mysql>
9.2 登录proxysql管理端，通过hostgroup_id查看读写分离效果
mysql> select * from stats_mysql_query_digest;
+-----------+--------------------+-----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent |
+-----------+--------------------+-----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| 2 | information_schema | proxyuser | | 0xf02b330c823d739 | select user,host from mysql.user | 1 | 1719230970 | 1719230970 | 5030 | 5030 | 5030 | 0 | 7 |
| 1 | information_schema | proxyuser | | 0x5b29ed96765bad6c | create database test2 | 1 | 1719230951 | 1719230951 | 7884 | 7884 | 7884 | 1 | 0 |
| 1 | information_schema | proxyuser | | 0xec77b5d4dcabfb9f | create database test1 | 1 | 1719230948 | 1719230948 | 4983 | 4983 | 4983 | 1 | 0 |
| 1 | information_schema | proxyuser | | 0x2033e45904d3df0 | show databases | 1 | 1719230934 | 1719230934 | 12909 | 12909 | 12909 | 0 | 4 |
| 1 | information_schema | proxyuser | | 0x226cd90d52a2ba0b | select @@version_comment limit ? | 1 | 1719230929 | 1719230929 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------------+-----------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
5 rows in set (0.02 sec)
本站资源均来自互联网，仅供研究学习，禁止违法使用和商用，产生法律纠纷本站概不负责！如果侵犯了您的权益请与我们联系！
转载请注明出处： 免费源码网-免费的源码资源网站 » 9. proxy sql实现读写分离
发表评论 取消回复