首页数据库用ProxySQL怎样实现MySQL读写分离？

用ProxySQL怎样实现MySQL读写分离？

时间2024-03-24 10:54:02发布访客分类数据库浏览873
导读：这篇文章主要介绍用ProxySQL怎样实现MySQL读写分离，本文具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章能有所收获，接下来小编带着大家一起了解看看。 ProxySQL是一个基于C++开发的高性能轻量级产品，是一款...

这篇文章主要介绍用ProxySQL怎样实现MySQL读写分离，本文具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章能有所收获，接下来小编带着大家一起了解看看。

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
StateRecv-QSend-QLocalAddress:PortPeerAddress:Port
LISTEN050*:3306*:*
LISTEN0128*:111*:*
LISTEN0128*:6032*:*
LISTEN0128*:6033*:*
LISTEN0128*:6033*:*
LISTEN0128*:6033*:*
LISTEN0128*:6033*:*

2.使用mysql客户端工具登录proxysql，用户名和密码都是admin，端口为6032，默认不允许localhost登录，所以要用127.0.0.1IP地址登录；

[root@slave2~]#mysql-uadmin-padmin-h227.0.0.1-P6032
WelcometotheMariaDBmonitor.Commandsendwith;
    or\g.
YourMySQLconnectionidis2
Serverversion:5.5.30(ProxySQLAdminModule)

Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers.

Type'help;
    'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

MySQL[(none)]>
    showdatabases;
    
+-----+---------------+-------------------------------------+
|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|
+-----+---------------+-------------------------------------+
5rowsinset(0.00sec)

3.主要介绍其中mian库和monitor库；

MySQL[(none)]>
    showtablesfrommain;
    
+--------------------------------------------+
|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|
+--------------------------------------------+
20rowsinset(0.00sec)

4.在mysql_servers表中有几个重要的属性：

hostgroup_id：组ID，用于区分master和slave；

hostname：后端master和slave的IP地址；

port：后端master和slave的IP端口，默认3306；

MySQL[main]>
    insertintomysql_servers(hostgroup_id,hostname,port)values(10,'172.16.75.4',3306),(20,'172.16.75.3',3306);
    
QueryOK,2rowsaffected(0.02sec)

MySQL[main]>
    loadmysqlserverstoruntime;
    
QueryOK,0rowsaffected(0.01sec)

MySQL[main]>
    savemysqlserverstodisk;
    
QueryOK,0rowsaffected(0.06sec)

注意：

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)]>
    grantreplicationclient,replicationslaveon*.*to'monitor'@'%'identifiedby'123456';
    
QueryOK,0rowsaffected(0.02sec)

6.在proxysql上加入该节点；

MySQL[main]>
    setmysql-monitor_username='monitor';
    
QueryOK,1rowaffected(0.00sec)

MySQL[main]>
    setmysql-monitor_password='123456';
    
QueryOK,1rowaffected(0.00sec)

MySQL[main]>
    loadmysqlvariablestoruntime;
    
QueryOK,0rowsaffected(0.00sec)

MySQL[main]>
    savemysqlvariablestodisk;
    
QueryOK,95rowsaffected(0.03sec)

通过查看表mysql_server_ping_log了解后端云服务器状态信息；

MySQL[main]>
    select*frommysql_server_ping_loglimit1,10;
    
+-------------+------+------------------+----------------------+----------------------------------------------------------------------+
|hostname|port|time_start_us|ping_success_time_us|ping_error|
+-------------+------+------------------+----------------------+----------------------------------------------------------------------+
|172.16.75.4|3306|1541505676594192|0|Accessdeniedforuser'monitor'@'172.16.75.4'(usingpassword:YES)|
|172.16.75.3|3306|1541505686592082|0|Host'172.16.75.4'isnotallowedtoconnecttothisMariaDBserver|
|172.16.75.4|3306|1541505686594872|0|Accessdeniedforuser'monitor'@'172.16.75.4'(usingpassword:YES)|
|172.16.75.3|3306|1541505696592635|0|Host'172.16.75.4'isnotallowedtoconnecttothisMariaDBserver|
|172.16.75.4|3306|1541505696595442|0|Accessdeniedforuser'monitor'@'172.16.75.4'(usingpassword:YES)|
|172.16.75.3|3306|1541505706593101|0|Host'172.16.75.4'isnotallowedtoconnecttothisMariaDBserver|
|172.16.75.4|3306|1541505706596427|0|Accessdeniedforuser'monitor'@'172.16.75.4'(usingpassword:YES)|
|172.16.75.3|3306|1541505716593471|0|Host'172.16.75.4'isnotallowedtoconnecttothisMariaDBserver|
|172.16.75.4|3306|1541505716596416|0|Accessdeniedforuser'monitor'@'172.16.75.4'(usingpassword:YES)|
|172.16.75.3|3306|1541505726593810|0|Host'172.16.75.4'isnotallowedtoconnecttothisMariaDBserver|
+-------------+------+------------------+----------------------+----------------------------------------------------------------------+
10rowsinset(0.00sec)

7.对后端定义的云服务器的分组进行读组和写组的设定，mysql_replication_hostgroups表中添加定义即可，通过查看monitor库中的mysql_server_read_only_log表查看后端节点是否具有read_only权限；

MySQL[main]>
    insertintomysql_replication_hostgroups(writer_hostgroup,reader_hostgroup)values(10,20);
    
QueryOK,1rowaffected(0.00sec)

MySQL[main]>
    loadmysqlserverstoruntime;
    
QueryOK,0rowsaffected(0.00sec)

MySQL[main]>
    savemysqlserverstodisk;
    
QueryOK,0rowsaffected(0.03sec)

MySQL[main]>
    select*frommonitor.mysql_server_read_only_loglimit3;
    
+-------------+------+------------------+-----------------+-----------+-------+
|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|
+-------------+------+------------------+-----------------+-----------+-------+
3rowsinset(0.00sec)

8.至此，基本配置完毕，我们在后端的master上创建两个用户账户，在proxysql上添加不同的hostgroup_id，完成基于不同用户之间进行读写分离；

Master mysql：

MariaDB[(none)]>
    grantallon*.*to'reader'@'%'identifiedby'123456';
    
QueryOK,0rowsaffected(0.01sec)

MariaDB[(none)]>
    grantallon*.*to'writer'@'%'identifiedby'123456';
    
QueryOK,0rowsaffected(0.00sec)

Proxysql：

MySQL[main]>
    insertintomysql_users(username,password,default_hostgroup)values('reader','123456',20),('writer','123456',10);
    
QueryOK,2rowsaffected(0.00sec)

MySQL[main]>
    loadmysqluserstoruntime;
    
QueryOK,0rowsaffected(0.01sec)

MySQL[main]>
    savemysqluserstodisk;
    
QueryOK,0rowsaffected(0.03sec)

MySQL[main]>
    select*frommysql_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
2rowsinset(0.00sec)

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]>
    insertintomysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values
(1,1,'^SELECT.*FORUPDATE$',10,1),(2,1,'^SELECT',20,1);
    
QueryOK,2rowsaffected(0.00sec)

MySQL[main]>
    loadmysqlqueryrulestoruntime;
    
QueryOK,0rowsaffected(0.00sec)

MySQL[main]>
    savemysqlqueryrulestodisk;
    
QueryOK,0rowsaffected(0.02sec)

10.任意一台主机测试基于SQL语句读写分离的机制；

[root@slave2~]#mysql-ureader-h272.16.75.4-P6033-p123456-e'set@@autocommit=0;
    \
starttransaction;
    \
usehellodb;
    \
insertintococ(ID,ClassID,CourseID)values(100,100,100);
    \
select@@server_id;
    \
commit;
    '
+-------------+
|@@server_id|
+-------------+
|301|
+-------------+

Master中的hellodb数据库进行查看；

MariaDB[hellodb]>
    select*fromcoc;
    
+----+---------+----------+
|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|
+----+---------+----------+
14rowsinset(0.00sec)

MariaDB[hellodb]>
    select*fromcoc;
    
+-----+---------+----------+
|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|
+-----+---------+----------+
15rowsinset(0.00sec)

以上就是关于用ProxySQL实现MySQL读写分离的介绍，上述SQL语句具有一定的参考价值，有需要的朋友可以了解看看，希望对大家有帮助，想要了解更多可以继续浏览网络其他相关的文章。

文本转载自网络

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！


若转载请注明出处： 用ProxySQL怎样实现MySQL读写分离？
本文地址： https://pptw.com/jishu/652012.html
MySQL复制过滤器的两种过滤方式是什么? Spring Security异常分类是怎样的，如何理解

游客 回复需填写必要信息
↑