本文主要给大家介绍ProxySQL如何帮助MySQL实行读写分离，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下ProxySQL如何帮助MySQL实行读写分离吧。
从策划到设计制作，每一步都追求做到细腻，制作可持续发展的企业网站。为客户提供
成都网站制作、成都网站设计、网站策划、网页设计、
域名注册、
雅安服务器托管、网络营销、VI设计、 网站改版、漏洞修补等服务。为客户提供更好的一站式互联网解决方案,以客户的口碑塑造优易品牌,携手广大客户,共同发展进步。
ProxySQL是一个基于C++开发的高性能轻量级产品，是一款强大的mysql的中间件，他可以实现多种方式的读写分离。
Master IP：172.16.75.4 CentOS 7.5D server_id：401
Slave IP：172.16.75.3 CentOS 7.5C server_id：301
1.首先，yum安装即可，然后启动，启动端口为6032（我的proxysql安装在了master上）；
[root@slave2 ~]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 50 *:3306 *:*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6032 *:*
LISTEN 0 128 *:6033 *:*
LISTEN 0 128 *:6033 *:*
LISTEN 0 128 *:6033 *:*
LISTEN 0 128 *:6033 *:*
2.使用mysql客户端工具登录proxysql，用户名和密码都是admin，端口为6032，默认不允许localhost登录，所以要用127.0.0.1IP地址登录；
[root@slave2 ~]# mysql -uadmin -padmin -h227.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> 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)
3.主要介绍其中mian库和monitor库；
MySQL [(none)]> show tables from main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers | 插入监控节点，master节点和slave节点
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
20 rows in set (0.00 sec)
4.在mysql_servers表中有几个重要的属性：
hostgroup_id：组ID，用于区分master和slave；
hostname：后端master和slave的IP地址；
port：后端master和slave的IP端口，默认3306；
MySQL [main]> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'172.16.75.4',3306),(20,'172.16.75.3',3306);
Query OK, 2 rows affected (0.02 sec)
MySQL [main]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
MySQL [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.06 sec)
注意：
1.设置的master的hostgroup_id为10（写组）；
2.设置的slave的hostgroup_id为20（读组）；
3.每次在proxysql执行完操作之后，需要手动加载至内存上，然后手动保存至磁盘上，表名中的”_”改为空格； mysql_servers à mysql servers
5.在master上授权一个监控用户，用于监控后端的节点（注意：是master的mysql，不是Proxysql）；
这个用户需要的权限：replication client和replication slave
MariaDB [(none)]> grant replication client,replication slave on *.* to 'monitor'@'%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
6.在proxysql上加入该节点；
MySQL [main]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
MySQL [main]> set mysql-monitor_password='123456';
Query OK, 1 row affected (0.00 sec)
MySQL [main]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [main]> save mysql variables to disk;
Query OK, 95 rows affected (0.03 sec)
通过查看表mysql_server_ping_log了解后端云服务器状态信息；
MySQL [main]> select * from mysql_server_ping_log limit 1,10;
+-------------+------+------------------+----------------------+----------------------------------------------------------------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+-------------+------+------------------+----------------------+----------------------------------------------------------------------+
| 172.16.75.4 | 3306 | 1541505676594192 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505686592082 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
| 172.16.75.4 | 3306 | 1541505686594872 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505696592635 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
| 172.16.75.4 | 3306 | 1541505696595442 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505706593101 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
| 172.16.75.4 | 3306 | 1541505706596427 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505716593471 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
| 172.16.75.4 | 3306 | 1541505716596416 | 0 | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) |
| 172.16.75.3 | 3306 | 1541505726593810 | 0 | Host '172.16.75.4' is not allowed to connect to this MariaDB server |
+-------------+------+------------------+----------------------+----------------------------------------------------------------------+
10 rows in set (0.00 sec)
7.对后端定义的云服务器的分组进行读组和写组的设定，mysql_replication_hostgroups表中添加定义即可，通过查看monitor库中的mysql_server_read_only_log表查看后端节点是否具有read_only权限；
MySQL [main]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10,20);
Query OK, 1 row affected (0.00 sec)
MySQL [main]> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
MySQL [main]> select * from monitor.mysql_server_read_only_log limit 3;
+-------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+-------------+------+------------------+-----------------+-----------+-------+
| 172.16.75.4 | 3306 | 1541506648164762 | 766 | 0 | NULL |
| 172.16.75.3 | 3306 | 1541506648162822 | 3585 | 1 | NULL |
| 172.16.75.3 | 3306 | 1541506649664049 | 993 | 1 | NULL |
+-------------+------+------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)
8.至此，基本配置完毕，我们在后端的master上创建两个用户账户，在proxysql上添加不同的hostgroup_id，完成基于不同用户之间进行读写分离；
Master mysql：
MariaDB [(none)]> grant all on *.* to 'reader'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> grant all on *.* to 'writer'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
Proxysql：
MySQL [main]> insert into mysql_users (username,password,default_hostgroup) values ('reader','123456',20),('writer','123456',10);
Query OK, 2 rows affected (0.00 sec)
MySQL [main]> load mysql users to runtime;
Query OK, 0 rows affected (0.01 sec)
MySQL [main]> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)
MySQL [main]> select * from mysql_users\G
*************************** 1. row ***************************
username: reader
password: 123456
active: 1
use_ssl: 0
default_hostgroup: 20
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
*************************** 2. row ***************************
username: writer
password: 123456
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
2 rows in set (0.00 sec)8.任意一台主机，测试基于用户的读写分离机制；
[root@slave2 ~]# mysql -uwriter -h272.16.75.4 -P6033 -p123456 -e 'select @@server_id';
+-------------+
| @@server_id |
+-------------+
| 401 |
+-------------+
[root@slave2 ~]# mysql -ureader -h272.16.75.4 -P6033 -p123456 -e 'select @@server_id';
+-------------+
| @@server_id |
+-------------+
| 301 |
+-------------+
9.基于SQL语句实现读写分离；
需要在mysql_query_rules表中添加两条正则表达式的规则；
MySQL [main]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.00 sec)
MySQL [main]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [main]> save mysql query rules to disk;
Query OK, 0 rows affected (0.02 sec)
10.任意一台主机测试基于SQL语句读写分离的机制；
[root@slave2 ~]# mysql -ureader -h272.16.75.4 -P6033 -p123456 -e 'set @@autocommit=0;\
start transaction;\
use hellodb;\
insert into coc (ID,ClassID,CourseID) values (100,100,100);\
select @@server_id;\
commit;'
+-------------+
| @@server_id |
+-------------+
| 301 |
+-------------+
Master中的hellodb数据库进行查看；
MariaDB [hellodb]> select * from coc;
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
| 1 | 1 | 2 |
| 2 | 1 | 5 |
| 3 | 2 | 2 |
| 4 | 2 | 6 |
| 5 | 3 | 1 |
| 6 | 3 | 7 |
| 7 | 4 | 5 |
| 8 | 4 | 2 |
| 9 | 5 | 1 |
| 10 | 5 | 9 |
| 11 | 6 | 3 |
| 12 | 6 | 4 |
| 13 | 7 | 4 |
| 14 | 7 | 3 |
+----+---------+----------+
14 rows in set (0.00 sec)
MariaDB [hellodb]> select * from coc;
+-----+---------+----------+
| ID | ClassID | CourseID |
+-----+---------+----------+
| 1 | 1 | 2 |
| 2 | 1 | 5 |
| 3 | 2 | 2 |
| 4 | 2 | 6 |
| 5 | 3 | 1 |
| 6 | 3 | 7 |
| 7 | 4 | 5 |
| 8 | 4 | 2 |
| 9 | 5 | 1 |
| 10 | 5 | 9 |
| 11 | 6 | 3 |
| 12 | 6 | 4 |
| 13 | 7 | 4 |
| 14 | 7 | 3 |
| 100 | 100 | 100 |
+-----+---------+----------+
15 rows in set (0.00 sec)
看完以上关于ProxySQL如何帮助MySQL实行读写分离，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
当前标题：ProxySQL如何帮助MySQL实行读写分离-创新互联
本文路径：
http://www.cdkjz.cn/article/ihhoo.html
