关于Linux下MySQL主备集群负载均衡之读写分离（关于Linux下MySQL主备集群负载均衡之读写分离）-爱玩科技

小君 2023-07-19 11:31:23 635

关于Linux下MySQL主备集群负载均衡之读写分离（关于Linux下MySQL主备集群负载均衡之读写分离）另一种是通过在服务端去区分，通过MySQL Proxy的方式实现。客户端的请求都到MySQL Proxy上，如果客户端要执行查询数据的请求，则交给从服务器来处理；如果客户端要对数据进行增、删、改的操作，则交给主服务器来处理；从库的负载可以通过DNS负载、LVS Keepalived、硬件负载均衡器F5、TCP代理(HAproxy Nginx)、或者在应用中管理从库负载均衡。比如做简单的数据源池做线性负载等。有些负载均衡技术本身能够实现这一点，有些需要应用自己知道哪些节点是可读的或可写的。常见的读写分离一种是通过客户端去区分读写，比如上面那个图，写在主库，读通过负载均衡到多个从库。在应用层面粗粒度通过配置不同数据源分离读写实现，同时还需要考虑从库异步复制导致的脏数据问题，需要监控延迟复制来决策读写的分配。可以考虑在编码层次基于查询，版本，请求时间戳，会话等做一些读写策略，不能容忍脏数据的读可

写在前面

分享一些MySQL(MariaDB)集群主从结构数据读写分离的笔记，关于读写分离：
一如果对于读密集型应用，可以容忍从库异步复制延迟导致的脏数据，读写分离是一种不错的负载均衡方式
如果对于脏数据零容忍，不建议这样搞，出了故障还需要考虑这个因素，不太方便定位问题
二是读写分离需要做体量评估，不能为了读写分离去读写分离，系统负载正常，完全没必要，如果扩了资源还是频繁的sql timeout，读写分离是解决方法之一
博文偏实战，内容涉及：为什么需要负载均衡？MaxScale配置主从集群的读写分离
食用方式：了解Linux，MySQL
理解不足小伙伴帮忙指正

「只要足够开心烦恼就追不上哦 ^_^ 」

一、为什么需要负载均衡？

需要负载均衡的理由：

「可扩展性」 ：负载均衡对某些扩展策略有所帮助，比如流量控制，例如数据库读写流量分离时从备库读数据降低主库读的工作负载，提升写的负载能力，在比如大型的Web应用，对于搜索类请求需要最小化延迟，可以负载到最近的数据中心，对于上传来讲需要最大化吞吐量需要负载到带宽没有占满的链路，即使跳的远一点。
「高效性」 ：负载均衡有助于更有效地使用资源，控制流量被路由到何处。如果服务器处理能力各不相同，这就尤为重要：你可以把更多的工作分配给性能更好的机器。
「高可用性」 : 一个灵活的负载均衡解决方案能够使用时刻保持可用的服务器。
「匿名性」 ：客户端无须知道是否存在负载均衡设置。负载均衡器给客户端看到的只是一个代理一个虚拟服务器。
「一致性」 ：如果应用是有状态的（数据库事务，网站会话等），那么负载均衡器就应将相关的查询指向同一个服务器，以防止状态丢失。应用无须去跟踪到底连接的是哪个服务器。

关于Linux下MySQL主备集群负载均衡之读写分离（关于Linux下MySQL主备集群负载均衡之读写分离）(1)

从集群角度考虑，MySQL做主备集群复制如果只用作备份，有些浪费，和负载均衡结合使用一种相辅相成的作用。

所以MySQL的负载均衡架构通常和数据分片及复制紧密相关。我们今天要讲的读/写分离策略即属于负载均衡的一个实现。

有些负载均衡技术本身能够实现这一点，有些需要应用自己知道哪些节点是可读的或可写的。

客户端读写分离

常见的读写分离一种是通过客户端去区分读写，比如上面那个图，写在主库，读通过负载均衡到多个从库。

在应用层面粗粒度通过配置不同数据源分离读写实现，同时还需要考虑从库异步复制导致的脏数据问题，需要监控延迟复制来决策读写的分配。可以考虑在编码层次基于查询，版本，请求时间戳，会话等做一些读写策略，不能容忍脏数据的读可以放到写节点

从库的负载可以通过DNS负载、LVS Keepalived、硬件负载均衡器F5、TCP代理(HAproxy Nginx)、或者在应用中管理从库负载均衡。比如做简单的数据源池做线性负载等。

服务端读写分离

另一种是通过在服务端去区分，通过MySQL Proxy的方式实现。客户端的请求都到MySQL Proxy上，如果客户端要执行查询数据的请求，则交给从服务器来处理；如果客户端要对数据进行增、删、改的操作，则交给主服务器来处理；

MySQL Proxy相关的工具有很多，有自带的mysql-proxy插件，也有MyCat等中间件，今天和小伙伴分享通过MaxScale来实现的读写分离不管使用那种方式，个人觉得如果对于脏数据零容忍的应用更多的应该在硬件资源上面考虑，并且大多数的读写分离解决方案都需要监控延迟复制来决策读写的分配。做的不好，总感觉有点不靠谱...

二、配置主从集群的读写分离

关于Linux下MySQL主备集群负载均衡之读写分离（关于Linux下MySQL主备集群负载均衡之读写分离）(2)

MariaDB MaxScale是MariaDB企业服务器、MariaDB ColumnStore和MariaDB Xpand的高级数据库代理，为它们提供企业高可用性、可伸缩性、安全和集成服务，同时抽象出底层数据库基础设施，以简化应用程序开发和数据库管理。

官方地址：https://mariadb.com/

读写分离工作原理

由 MaxScale面向客户端提供服务，收到SQL写请求时，交给master 服务器处理，收到SQL读请求时，交给slave服务器处理，这里我们已经搭建好一个主从结构的MySQL集群，关于集群搭建小伙伴可以看我之前的文章，有详细教程，所以这里只需要安装MaxScale 然后配置启动测试就OK

关于Linux下MySQL主备集群负载均衡之读写分离（关于Linux下MySQL主备集群负载均衡之读写分离）(3)

MaxScale代理服务：192.168.26.152
主库：192.168.26.153(写)
从库：192.168.26.154(读)
客户端： 192.168.26.155

安装 MaxScale

可以参考官方文档：https://mariadb.com/docs/deploy/topologies/primary-replica/enterprise-server-10-6/install-mxs/

安装包下载： https://downloads.mariadb.com/files/MaxScale

需要的依赖包下载：https://rpmfind.net/linux/rpm2html/search.php

依赖安装

┌──[root@vms152.liruilongs.github.io]-[~] └─$yum -y install libatomic ...... 软件包 libatomic-4.8.5-44.el7.x86_64 已安装并且是最新版本无须任何处理

┌──[root@vms152.liruilongs.github.io]-[~] └─$wget https://rpmfind.net/linux/centos/7.9.2009/os/x86_64/Packages/trousers-0.3.14-2.el7.x86_64.rpm --2022-09-29 20:13:22-- https://rpmfind.net/linux/centos/7.9.2009/os/x86_64/Packages/trousers-0.3.14-2.el7.x86_64.rpm ...... ┌──[root@vms152.liruilongs.github.io]-[~] └─$rpm -ivh trousers-0.3.14-2.el7.x86_64.rpm 准备中... ################################# [100%] 正在升级/安装... 1:trousers-0.3.14-2.el7 ################################# [100%] ┌──[root@vms152.liruilongs.github.io]-[~] └─$

┌──[root@vms152.liruilongs.github.io]-[~] └─$wget https://rpmfind.net/linux/centos/7.9.2009/os/x86_64/Packages/nettle-2.7.1-8.el7.x86_64.rpm --2022-09-29 20:14:52-- https://rpmfind.net/linux/centos/7.9.2009/os/x86_64/Packages/nettle-2.7.1-8.el7.x86_64.rpm ....... ┌──[root@vms152.liruilongs.github.io]-[~] └─$rpm -ivh nettle-2.7.1-8.el7.x86_64.rpm 准备中... ################################# [100%] 正在升级/安装... 1:nettle-2.7.1-8.el7 ################################# [100%] ┌──[root@vms152.liruilongs.github.io]-[~] └─$

┌──[root@vms152.liruilongs.github.io]-[~] └─$wget https://rpmfind.net/linux/centos/7.9.2009/os/x86_64/Packages/gnutls-3.3.29-9.el7_6.x86_64.rpm --2022-09-29 20:15:50-- https://rpmfind.net/linux/centos/7.9.2009/os/x86_64/Packages/gnutls-3.3.29-9.el7_6.x86_64.rpm ..... ┌──[root@vms152.liruilongs.github.io]-[~] └─$rpm -ivh gnutls-3.3.29-9.el7_6.x86_64.rpm 准备中... ################################# [100%] 正在升级/安装... 1:gnutls-3.3.29-9.el7_6 ################################# [100%] ┌──[root@vms152.liruilongs.github.io]-[~] └─$

安装完依赖包安装 maxscale

安装 MaxScale

┌──[root@vms152.liruilongs.github.io]-[~] └─$rpm -ivh maxscale-2.5.21-1.rhel.7.x86_64.rpm 警告：maxscale-2.5.21-1.rhel.7.x86_64.rpm: 头V4 RSA/SHA512 Signature 密钥 ID e3c94f49: NOKEY 准备中... ################################# [100%] 正在升级/安装... 1:maxscale-2.5.21-1.rhel.7 ################################# [100%] Created symlink from /etc/systemd/system/multi-user.target.wants/maxscale.service to /usr/lib/systemd/system/maxscale.service. ┌──[root@vms152.liruilongs.github.io]-[~] └─$配置 MaxScale

┌──[root@vms152.liruilongs.github.io]-[~] └─$cp /etc/maxscale.cnf /etc/maxscale.cnf.bak ┌──[root@vms152.liruilongs.github.io]-[~] └─$vim /etc/maxscale.cnf

备份配置文件，然后修改

┌──[root@vms152.liruilongs.github.io]-[~] └─$cat /etc/maxscale.cnf # MaxScale documentation: # https://mariadb.com/kb/en/mariadb-maxscale-25/ # Global parameters # # Complete list of configuration options: # https://mariadb.com/kb/en/mariadb-maxscale-25-mariadb-maxscale-configuration-guide/ [maxscale] # 定义线程的个数，一个线程对应一个CPU核心数，线程数量要小于等于CPU核心数 threads=auto # Server definitions # # Set the address of the server to the network # address of a MariaDB server. # # 指定要代理的数据库服务器信息 [server1] type=server address=192.168.26.153 #需要数据库服务器IP port=3306 protocol=MariaDBBackend [server2] type=server address=192.168.26.154 port=3306 protocol=MariaDBBackend # Monitor for the servers # # This will keep MaxScale aware of the state of the servers. # MariaDB Monitor documentation: # https://mariadb.com/kb/en/maxscale-25-monitors/ # 定义要监控的数据库服务器 [MariaDB-Monitor] type=monitor module=mariadbmon servers=server1 server2 #指定监控用户maxscalemon，用于登录后端服务器，检查服务器的运行状态和主从状态 user=maxscalemon password=liruilong monitor_interval=10000 # Service definitions # # Service Definition for a read-only service and # a read/write splitting service. # # ReadConnRoute documentation: # https://mariadb.com/kb/en/mariadb-maxscale-25-readconnroute/ #定义只读服务器配置，需要注释掉，我们配置的是读写分离 #[Read-Only-Service] #type=service #router=readconnroute #servers=server1 #user=myuser #password=mypwd #router_options=slave # ReadWriteSplit documentation: # https://mariadb.com/kb/en/mariadb-maxscale-25-readwritesplit/ #定义读写分离服务器配置 [Read-Write-Service] type=service router=readwritesplit servers=server1 server2 ##指定做读写分离服务的数据库服务器 user=maxscaleroute password=liruilong ##客户端通过用户名和密码向数据库发送请求，先访问maxscale代理服务器，再由代理服务器将请求转发 ##给后端数据库服务器；maxscale代理服务器是通过路由用户连接后端服务器，检测客户端的用户名和密码在 ##后端数据库中是否存在 # Listener definitions for the services # # These listeners represent the ports the # services will listen on. # #[Read-Only-Listener] #type=listener #service=Read-Only-Service #protocol=MariaDBClient #port=4008 # 定义读写分离服务使用的端口号 [Read-Write-Listener] type=listener service=Read-Write-Service protocol=MariaDBClient port=4006 ┌──[root@vms152.liruilongs.github.io]-[~] └─$创建监控用户和路由用户

创建监控用户maxscalemon，用于登录后端服务器，检查服务器的状态

grant replication slave replication client on *.* to maxscalemon@"%" identified by "liruilong";

replication slave 能够同步数据，查看从服务器上slave的状态；
replication client 可以获取数据库服务的状态(数据库服务是否允许，主从是否正常)

MariaDB [(none)]> grant replication slave replication client on *.* to maxscalemon@"%" identified by "liruilong"; Query OK 0 rows affected (0.00 sec)

创建路由用户maxscalerouter 检测客户端的用户名和密码在后端数据库中是否存在

只是检查用户是否存在，所以此用户只需对mysql库下表有查询权限即可

MariaDB [(none)]> grant select on mysql.* to maxscaleroute@"%" identified by "liruilong"; Query OK 0 rows affected (0.00 sec)

在mysql库的user表中，查看监控用户和路由用户

MariaDB [(none)]> select user host from mysql.user; --------------- ----------------------------- | user | host | --------------- ----------------------------- | maxscalemon | % | | maxscaleroute | % | | repluser | % | | root | 127.0.0.1 | | root | ::1 | | root | localhost | | root | vms153.liruilongs.github.io | --------------- ----------------------------- 7 rows in set (0.00 sec) MariaDB [(none)]>

在154从库的mysql库的user表中，查看同步过去监控用户和路由用户

┌──[root@vms154.liruilongs.github.io]-[~] └─$mysql -uroot -pliruilong -e'select user host from mysql.user;' --------------- ----------------------------- | user | host | --------------- ----------------------------- | maxscalemon | % | | maxscaleroute | % | | tom | % | | root | 127.0.0.1 | | root | ::1 | | root | localhost | | root | vms154.liruilongs.github.io | --------------- ----------------------------- ┌──[root@vms154.liruilongs.github.io]-[~] └─$

启动MaxScale服务

┌──[root@vms152.liruilongs.github.io]-[~] └─$maxscale -f /etc/maxscale.cnf -U maxscale ┌──[root@vms152.liruilongs.github.io]-[~] └─$netstat -ntulp | grep maxscale tcp 0 0 127.0.0.1:8989 0.0.0.0:* LISTEN 1169/maxscale tcp6 0 0 :::4006 :::* LISTEN 1169/maxscale ┌──[root@vms152.liruilongs.github.io]-[~] └─$ps -C maxscale PID TTY TIME CMD 1169 ? 00:00:00 maxscale

查看日志，配置文件有个报错，解决下

┌──[root@vms152.liruilongs.github.io]-[~] └─$tail -n 5 /var/log/maxscale/maxscale.log 2022-09-29 22:38:24 error : Monitor timed out when connecting to server server2[192.168.26.26.154:3306] : 'Unknown MySQL server host '192.168.26.26.154' (-2)' 2022-09-29 22:38:24 notice : [mariadbmon] Selecting new master server. 2022-09-29 22:38:24 warning: [mariadbmon] No running master candidates detected and no master currently set. Accepting a non-running server as master. 2022-09-29 22:38:24 notice : [mariadbmon] Setting 'server1' as master. 2022-09-29 22:39:15 warning: [mariadbmon] The current master server 'server1' is no longer valid because it has been down over 5 (failcount) monitor updates and it does not have any running slaves but there is no valid alternative to swap to. ┌──[root@vms152.liruilongs.github.io]-[~] └─$kill -9 1169 ┌──[root@vms152.liruilongs.github.io]-[~] └─$vim /etc/maxscale.cnf ┌──[root@vms152.liruilongs.github.io]-[~] └─$maxscale -f /etc/maxscale.cnf -U maxscale ┌──[root@vms152.liruilongs.github.io]-[~] └─$测试 MaxScale检查全局配置

使用maxctrl show maxscale命令查看全局maxscale配置。

┌──[root@vms152.liruilongs.github.io]-[~] └─$maxctrl show maxscale ┌──────────────┬───────────────────────────────────────────────────────┐ │ Version │ 2.5.21 │ ├──────────────┼───────────────────────────────────────────────────────┤ │ Commit │ eb659891d7b507958f3c5f100d1ebe5f0f68afaf │ ├──────────────┼───────────────────────────────────────────────────────┤ │ Started At │ Sun 09 Oct 2022 14:50:14 GMT │ ├──────────────┼───────────────────────────────────────────────────────┤ │ Activated At │ Sun 09 Oct 2022 14:50:14 GMT │ ├──────────────┼───────────────────────────────────────────────────────┤ │ Uptime │ 43 │ ├──────────────┼───────────────────────────────────────────────────────┤ │ Parameters │ { │ │ │ "admin_auth": true │ │ │ "admin_enabled": true │ │ │ "admin_gui": true │ │ │ "admin_host": "127.0.0.1" │ │ │ "admin_log_auth_failures": true │ ......................... │ │ "writeq_low_water": 8192 │ │ │ } │ └──────────────┴───────────────────────────────────────────────────────┘ ┌──[root@vms152.liruilongs.github.io]-[~] └─$检查服务器配置

使用maxctrl list servers和maxctrl show server命令查看配置的服务器对象。

获取服务器对象的完整列表:

server1 ：192.168.26.153:3306 ││ Master Running
server2 ：192.168.26.154:3306 ││ Slave Running

┌──[root@vms152.liruilongs.github.io]-[~] └─$maxctrl list servers ┌─────────┬────────────────┬──────┬─────────────┬─────────────────┬──────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────┤ │ server1 │ 192.168.26.153 │ 3306 │ 0 │ Master Running │ │ ├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────┤ │ server2 │ 192.168.26.154 │ 3306 │ 0 │ Slave Running │ │ └─────────┴────────────────┴──────┴─────────────┴─────────────────┴──────┘ ┌──[root@vms152.liruilongs.github.io]-[~] └─$

对于每一个服务器对象，查看配置:

┌──[root@vms152.liruilongs.github.io]-[~] └─$maxctrl show server server1 ┌─────────────────────┬───────────────────────────────────────────┐ │ Server │ server1 │ ├─────────────────────┼───────────────────────────────────────────┤ │ Address │ 192.168.26.153 │ ├─────────────────────┼───────────────────────────────────────────┤ │ Port │ 3306 │ ├─────────────────────┼───────────────────────────────────────────┤ │ State │ Master Running │ ├─────────────────────┼───────────────────────────────────────────┤ │ Version │ 5.5.68-MariaDB │ ├─────────────────────┼───────────────────────────────────────────┤ │ Last Event │ master_up │ ├─────────────────────┼───────────────────────────────────────────┤ │ Triggered At │ Sun 09 Oct 2022 14:50:14 GMT │ ├─────────────────────┼───────────────────────────────────────────┤ │ Services │ Read-Write-Service │ ├─────────────────────┼───────────────────────────────────────────┤ │ Monitors │ MariaDB-Monitor │ ├─────────────────────┼───────────────────────────────────────────┤ │ Master ID │ -1 │ ├─────────────────────┼───────────────────────────────────────────┤ │ Node ID │ 153 │ ├─────────────────────┼───────────────────────────────────────────┤ │ Slave Server IDs │ │ ├─────────────────────┼───────────────────────────────────────────┤ │ Current Connections │ 0 │ ├─────────────────────┼───────────────────────────────────────────┤ │ Total Connections │ 0 │ ├─────────────────────┼───────────────────────────────────────────┤ │ Max Connections │ 0 │ ├─────────────────────┼───────────────────────────────────────────┤ │ Statistics │ { │ │ │ "active_operations": 0 │ ........................ │ │ "total_connections": 0 │ │ │ } │ ├─────────────────────┼───────────────────────────────────────────┤ │ Parameters │ { │ │ │ "address": "192.168.26.153" │ ............................. │ │ "ssl_version": "MAX" │ │ │ } │ └─────────────────────┴───────────────────────────────────────────┘ ┌──[root@vms152.liruilongs.github.io]-[~] └─$maxctrl show server server2检查监控配置

使用maxctrl list monitors和maxctrl show monitor命令查看已配置的监视器。

获取监控器的完整列表:

┌──[root@vms152.liruilongs.github.io]-[~] └─$maxctrl list monitors ┌─────────────────┬─────────┬──────────────────┐ │ Monitor │ State │ Servers │ ├─────────────────┼─────────┼──────────────────┤ │ MariaDB-Monitor │ Running │ server1 server2 │ └─────────────────┴─────────┴──────────────────┘

对于每个监控，查看监控配置:

┌──[root@vms152.liruilongs.github.io]-[~] └─$maxctrl show monitor MariaDB-Monitor ┌─────────────────────┬──────────────────────────────────────────────────────┐ │ Monitor │ MariaDB-Monitor │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Module │ mariadbmon │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ State │ Running │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Servers │ server1 │ │ │ server2 │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Parameters │ { │ │ │ "assume_unique_hostnames": true │ ................................ │ │ "slave_conditions": "none" │ │ │ "switchover_on_low_disk_space": false │ │ │ "switchover_timeout": 90 │ │ │ "user": "maxscalemon" │ │ │ "verify_master_failure": true │ │ │ } │ ├─────────────────────┼──────────────────────────────────────────────────────┤ │ Monitor Diagnostics │ { │ │ │ "master": "server1" │ │ │ "master_gtid_domain_id": null │ │ │ "primary": null │ │ │ "server_info": [ │ │ │ { │ │ │ "gtid_binlog_pos": null │ ................................ │ │ "last_sql_error": "" │ │ │ "master_host": "192.168.26.153" │ │ │ "master_port": 3306 │ │ │ "master_server_id": 153 │ │ │ "seconds_behind_master": 0 │ │ │ "slave_io_running": "Yes" │ │ │ "slave_sql_running": "Yes" │ │ │ } │ │ │ ] │ │ │ } │ │ │ ] │ │ │ "state": "Idle" │ │ │ } │ └─────────────────────┴──────────────────────────────────────────────────────┘ ┌──[root@vms152.liruilongs.github.io]-[~] └─$检查服务配置

使用maxctrl list services和maxctrl show service命令查看已配置的路由服务。

获取路由服务的完整列表:

┌──[root@vms152.liruilongs.github.io]-[~] └─$maxctrl list services ┌────────────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Targets │ ├────────────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤ │ Read-Write-Service │ readwritesplit │ 0 │ 0 │ server1 server2 │ └────────────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘

查看详细信息

┌──[root@vms152.liruilongs.github.io]-[~] └─$maxctrl show services ┌─────────────────────┬─────────────────────────────────────────────────────────────┐ │ Service │ Read-Write-Service │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Router │ readwritesplit │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ State │ Started │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Started At │ Sun Oct 9 22:50:17 2022 │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Current Connections │ 0 │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Total Connections │ 0 │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Max Connections │ 0 │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Cluster │ │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Servers │ server1 │ │ │ server2 │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Services │ │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Filters │ │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Parameters │ { │ │ │ "auth_all_servers": false │ │ │ "causal_reads": "false" │ │ │ "causal_reads_timeout": 10000 │ .......................... │ │ "transaction_replay_max_size": "1073741824" │ │ │ "transaction_replay_retry_on_deadlock": false │ │ │ "use_sql_variables_in": "all" │ │ │ "user": "maxscaleroute" │ │ │ "version_string": null │ │ │ } │ ├─────────────────────┼─────────────────────────────────────────────────────────────┤ │ Router Diagnostics │ { │ │ │ "queries": 0 │ ............................. │ │ "server_query_statistics": [] │ │ │ } │ └─────────────────────┴─────────────────────────────────────────────────────────────┘ ┌──[root@vms152.liruilongs.github.io]-[~] └─$检查读写分离

创建一个普通用户用于测试，并且授权数据库liruilong_db

┌──[root@vms153.liruilongs.github.io]-[~] └─$mysql -uroot -pliruilong Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 50 Server version: 5.5.68-MariaDB MariaDB Server Copyright (c) 2000 2018 Oracle MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> grant all on liruilong_db.* to liruilong@"%" identified by "liruilong"; Query OK 0 rows affected (0.00 sec) MariaDB [(none)]> select user host from mysql.user; --------------- ----------------------------- | user | host | --------------- ----------------------------- | liruilong | % | | maxscalemon | % | | maxscaleroute | % | | repluser | % | | root | 127.0.0.1 | | root | ::1 | | root | localhost | | root | vms153.liruilongs.github.io | --------------- ----------------------------- 8 rows in set (0.00 sec) MariaDB [(none)]>

登录测试

┌──[root@vms153.liruilongs.github.io]-[~] └─$mysql -uliruilong -pliruilong Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 51 Server version: 5.5.68-MariaDB MariaDB Server Copyright (c) 2000 2018 Oracle MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show grants; ---------------------------------------------------------------------------------------------------------- | Grants for liruilong@% | ---------------------------------------------------------------------------------------------------------- | GRANT USAGE ON *.* TO 'liruilong'@'%' IDENTIFIED BY PASSWORD '*73CA7DD1B0BD11DCA665AB9C635C2188533331B3' | | GRANT ALL PRIVILEGES ON `liruilong_db`.* TO 'liruilong'@'%' | ---------------------------------------------------------------------------------------------------------- 2 rows in set (0.00 sec) MariaDB [(none)]>

从库查看是否同步

┌──[root@vms154.liruilongs.github.io]-[~] └─$mysql -uroot -pliruilong -e'select user host from mysql.user;' --------------- ----------------------------- | user | host | --------------- ----------------------------- | liruilong | % | | maxscalemon | % | | maxscaleroute | % | | tom | % | | root | 127.0.0.1 | | root | ::1 | | root | localhost | | root | vms154.liruilongs.github.io | --------------- ----------------------------- ┌──[root@vms154.liruilongs.github.io]-[~] └─$

代理机器上查看路由端口

┌──[root@vms152.liruilongs.github.io]-[~] └─$ maxctrl list listeners ┌─────────────────────┬──────┬──────┬─────────┬────────────────────┐ │ Name │ Port │ Host │ State │ Service │ ├─────────────────────┼──────┼──────┼─────────┼────────────────────┤ │ Read-Write-Listener │ 4006 │ :: │ Running │ Read-Write-Service │ └─────────────────────┴──────┴──────┴─────────┴────────────────────┘ ┌──[root@vms152.liruilongs.github.io]-[~] └─$

客户机连接MaxScale代理，查到登录用户测试库等信息

┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'show grants' ---------------------------------------------------------------------------------------------------------- | Grants for liruilong@% | ---------------------------------------------------------------------------------------------------------- | GRANT USAGE ON *.* TO 'liruilong'@'%' IDENTIFIED BY PASSWORD '*73CA7DD1B0BD11DCA665AB9C635C2188533331B3' | | GRANT ALL PRIVILEGES ON `liruilong_db`.* TO 'liruilong'@'%' | ---------------------------------------------------------------------------------------------------------- ┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'show databases;' -------------------- | Database | -------------------- | information_schema | | liruilong_db | | test | -------------------- ┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'use liruilong_db;show tables;' ------------------------ | Tables_in_liruilong_db | ------------------------ | user | ------------------------

客户机连接MaxScale代理，通过代理插入一条数据

┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'use liruilong_db;insert into user values(2 now());' ┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'use liruilong_db;select * from user;' ------ --------------------- | id | create_date | ------ --------------------- | 1 | 2022-09-29 00:22:36 | | 1 | 2022-09-29 11:08:38 | | 1 | 2022-09-29 13:43:09 | | 1 | 2022-09-29 13:51:33 | | 1 | 2022-09-29 13:54:41 | | 2 | 2022-09-30 13:29:59 | ------ --------------------- ┌──[root@vms155.liruilongs.github.io]-[~] └─$

可以正常读取，下面在从(读)库插入一条数据

┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.154 -uliruilong -pliruilong -e'use liruilong_db;insert into user values(154 now());' ┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.154 -uliruilong -pliruilong -e'use liruilong_db;select * from user;' ------ --------------------- | id | create_date | ------ --------------------- | 1 | 2022-09-29 00:22:36 | | 1 | 2022-09-29 11:08:38 | | 1 | 2022-09-29 13:43:09 | | 1 | 2022-09-29 13:51:33 | | 1 | 2022-09-29 13:54:41 | | 2 | 2022-09-30 13:29:59 | | 154 | 2022-09-30 13:32:18 | ------ ---------------------

主库没有数据，但是通过代理读到了数据，说明读是通过从读取。

┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.153 -uliruilong -pliruilong -e'use liruilong_db;select * from user;' ------ --------------------- | id | create_date | ------ --------------------- | 1 | 2022-09-29 00:22:36 | | 1 | 2022-09-29 11:08:38 | | 1 | 2022-09-29 13:43:09 | | 1 | 2022-09-29 13:51:33 | | 1 | 2022-09-29 13:54:41 | | 2 | 2022-09-30 13:29:59 | ------ --------------------- ┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'use liruilong_db;select * from user;' ------ --------------------- | id | create_date | ------ --------------------- | 1 | 2022-09-29 00:22:36 | | 1 | 2022-09-29 11:08:38 | | 1 | 2022-09-29 13:43:09 | | 1 | 2022-09-29 13:51:33 | | 1 | 2022-09-29 13:54:41 | | 2 | 2022-09-30 13:29:59 | | 154 | 2022-09-30 13:32:18 | ------ ---------------------

通过代理插入数据，主从库数据同时存在，说明写是在主库，然后同步给从库

┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.152 -P4006 -uliruilong -pliruilong -e'use liruilong_db;insert into user values(152 now());' ┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.153 -uliruilong -pliruilong -e'use liruilong_db;select * from user;' ------ --------------------- | id | create_date | ------ --------------------- | 1 | 2022-09-29 00:22:36 | | 1 | 2022-09-29 11:08:38 | | 1 | 2022-09-29 13:43:09 | | 1 | 2022-09-29 13:51:33 | | 1 | 2022-09-29 13:54:41 | | 2 | 2022-09-30 13:29:59 | | 152 | 2022-09-30 13:34:26 | ------ --------------------- ┌──[root@vms155.liruilongs.github.io]-[~] └─$mysql -h192.168.26.154 -uliruilong -pliruilong -e'use liruilong_db;select * from user;' ------ --------------------- | id | create_date | ------ --------------------- | 1 | 2022-09-29 00:22:36 | | 1 | 2022-09-29 11:08:38 | | 1 | 2022-09-29 13:43:09 | | 1 | 2022-09-29 13:51:33 | | 1 | 2022-09-29 13:54:41 | | 2 | 2022-09-30 13:29:59 | | 154 | 2022-09-30 13:32:18 | | 152 | 2022-09-30 13:34:26 | ------ --------------------- ┌──[root@vms155.liruilongs.github.io]-[~] └─$博文参考

《高性能Mysql》第三版(High Performance MySQL Third Edition)

https://mariadb.com/docs/deploy/topologies/primary-replica/enterprise-server-10-6/

网站首页

返回栏目

关于Linux下MySQL主备集群负载均衡之读写分离（关于Linux下MySQL主备集群负载均衡之读写分离）

猜您喜欢：

相关文章