mysql读写分离中间件_MySQL实行读写分离的中间件——ProxySQL

最新推荐文章于 2024-08-24 03:48:19 发布

白布里

最新推荐文章于 2024-08-24 03:48:19 发布

阅读量862

点赞数 1

文章标签： mysql读写分离中间件

本文链接：https://blog.csdn.net/weixin_28698129/article/details/113125376

版权

上边介绍了mysql的主从模型，可以提升数据库服务器的性能，保证数据的完整性和可靠性，但是呢，如果说仅此而已的话，那么master依旧会承担巨大的访问和写操作的压力，以至于我们提升又到达一个新的瓶颈。借此，介绍一个可实行读写分离的MySQL的中间件——ProxySQL，依赖于主从模型之上，可以让用户的访问操作发送到slave上，写操作发送到master，这样我们的master的压力就会得到一定的提升；

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 *:*

2.使用mysql客户端工具登录proxysql，用户名和密码都是admin，端口为6032，默认不允许localhost登录，所以要用127.0.0.1IP地址登录；[root@slave2 ~]# mysql -uadmin -padmin -h127.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)

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 slaveMariaDB [(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;

+-------------+------+------------------+----------------------+----------------------------------------------------------------------+

+-------------+------+------------------+----------------------+----------------------------------------------------------------------+

| 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;

+-------------+------+------------------+-----------------+-----------+-------+

+-------------+------+------------------+-----------------+-----------+-------+

| 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 -h172.16.75.4 -P6033 -p123456 -e 'select @@server_id';

+-------------+

| @@server_id |

+-------------+

| 401 |

+-------------+

[root@slave2 ~]# mysql -ureader -h172.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 -h172.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)

对于数据库服务器来说，保证查询高效的速度的同时也需要保证不俗的写入操作，因此，读写分离对提升数据库性能来说显得尤为重要，这关系到用户的体验，所以，MySQL的中间件——ProxySQL，就对在生产环境中显得那么必不可少，毕竟提升体验的好东西。

白布里

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫