MySQL 主从读写分离入门——基本原理以及ProxySQL的简单使用

一、读写分离工作原理

读写分离的工作原理:在大型网站业务中,当单台数据库无法满足并发需求时,通过主从同步方式同步数据。设置一台主服务器负责增、删、改,多台从服务器负责查询,从服务器主服务器同步数据以保持一致性,从而提高数据库的并发和负载能力。

简单来说,读写分离就是将数据库操作分为“”和“”两部分,分别由不同的服务器处理。主服务器(通常是单台)主要负责处理写操作(如插入、更新、删除),而从服务器(通常是多台)则主要负责处理读操作(如查询)。主从服务器之间通过主从同步机制保持数据的一致性。通过这种方式,可以显著提高数据库的并发处理能力和负载能力,从而减轻单台服务器的压力。

二、通过ProxySQL对读写分离进行浅层面的理解和运用

1、实验环境:

机器名称IP配置服务角色备注
proxy192.168.20.149proxysql控制器用于监控管理
master192.168.20.150数据库主服务器
slave1192.168.20.146数据库从服务器
slave2192.168.20.148数据库从服务器

 2、实现数据库主从复制

基于GTID实现mysql8.0主从同步,配置过程略。

基本命令:

开启gtid,并设置server_id值
gtid_mode=ON
enforce-gtid-consistency=ON

建立主从同步

mysql> CHANGE MASTER TO
     >     MASTER_HOST = host,
     >     MASTER_PORT = port,
     >     MASTER_USER = user,
     >     MASTER_PASSWORD = password,
     >     MASTER_AUTO_POSITION = 1;

mysql> START SLAVE;

mysql> show slave status \G

........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

..........
 

查看slave,双yes就代表成功

3、安装ProxySQL

本人博客另外一篇文章可以直接拿

yum install -y proxysql

启动 ProxySQL

[root@proxy ~]# systemctl enable --now proxysql
#先启服务,只需要mysql客户端,直接下mariadb就行了
# 管理员登录
[root@proxy ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032

 成功登录后可以查看一下库看看是否正常

4、配置 ProxySQL 所需账户

在 Master (192.168.20.150) 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户

create user 'monitor'@'192.168.%.%' identified with mysql_native_password by 'Monitor@123.com';
grant all privileges on *.* to 'monitor'@'192.168.%.%' with grant option;

#proxysql 的对外访问账户
create user 'proxysql'@'192.168.%.%' identified with mysql_native_password by '123456';
grant all privileges on *.* to 'proxysql'@'192.168.%.%' with grant option;

5、配置proxySQL

创建组:(定义写为1,读为0)

MySQL [(none)]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)

注意:ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server,master被分到编号为1的写组,read_only=1的server,slave则分到编号为0的读组

所以创建完成之后需要在两个从服务器配置文件(/etc/my.cnf)添加read_noly=1。

MySQL [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 1                | 0                | read_only  | proxy   |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

通过查询我们可以清晰的看到我们所分的组

添加主从服务器节点:

在proxySQL端添加主从服务器的节点,并保存

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.20.150',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.20.146',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.20.148',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.00 sec)

 重要的信息是要保证主从服务器都是online状态

为ProxySQL监控MySQL后端节点

MySQL [(none)]> use monitor
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [monitor]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

MySQL [monitor]> set mysql-monitor_password='Monitor@123.com';
Query OK, 1 row affected (0.00 sec)

修改后,保存到runtime和disk
MySQL [monitor]> load mysql variables to runtime;
MySQL [monitor]> save mysql variables to disk;


查看监控账号【ProxySQL】
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
//也可以这样快速定位
MySQL [(none)]> select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor                  |
+--------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| Monitor@123.com          |
+--------------------------+
1 row in set (0.00 sec)

验证监控信息

ProxySQL 监控模块的指标都保存在monitor库的log表中 以下是连接是否正常的监控,对connect指标的监控 ,在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常

心跳信息的监控

 

 查看read_only日志监控:

MySQL [(none)]> select * from mysql_server_read_only_log;

Monitor 模块就会开始监控后端的read_only值,当监控到read_only值,就会按照read_only的值将某些节点自动移到读写组 
一些监控的状态斗志在log相关,都在monitor库下面的 global_variables 变量。 

 ProxySQL配置对外访问账号

前面已经配置:配置ProxySQL 账户,我创建的对外访问账户是:用户:proxysql,密码:123456

将对外访问账号添加到mysql_users表中:

MySQL [monitor]> insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('proxysql','123456',1,1);
Query OK, 1 row affected (0.000 sec)

MySQL [monitor]> load mysql users to runtime;
Query OK, 0 rows affected (0.000 sec)

MySQL [monitor]> save mysql users to disk;
Query OK, 0 rows affected (0.007 sec)

MySQL [monitor]> select * from mysql_users\G
*************************** 1. row ***************************
              username: proxysql
              password: 123456
                active: 1
               use_ssl: 0
     default_hostgroup: 1
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
            attributes: 
               comment: 
1 row in set (0.000 sec)

注:transaction_persistent 如果为1,则一个完整的SQL只可能路由到一个节点;这点非常重要,主要解决这种情况:一个事务有混合的读操作和写操作组成,事务未提交前,如果事务中的读操作和写操作路由到不同节点,那么读取到的结果必然是脏数据。所以一般情况下,该值应该设置为1,尤其是业务中使用到事务机制的情况(默认为0)

6、测试主从同步

[root@slave1 ~]# mysql -h192.168.20.149 -uproxysql -p'123456' -P 6033
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 3
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          21 |
+-------------+
1 row in set (0.00 sec)

#通过proxysql用户,创建一个keme库
mysql> create database keme;
Query OK, 1 row affected (0.00 sec)

在slave2:192.168.20.148上去验证一下,是否同步过去keme这个库

7、添加简单的读写分离规则

MySQL [monitor]> insertintomysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select .* for update$',1,1);
l query rules to runtime;
save mysql query rulQuery OK, 1 row affected (0.000 sec)

es to disk;MySQL 
[monitor]> 
MySQL [monitor]> insert into  values(2,1,'^select',0,1);mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
Query OK, 1 row affected (0.000 sec)

MySQL [monitor]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.000 sec)

MySQL [monitor]> save mysql query rules to disk;
Query OK, 0 rows affected (0.006 sec)

8.测试读写分离

读操作:

 写操作:

简单的读写分离实验就结束了。 

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/420008.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

本届挑战赛冠军方案:基于LLM的多场景智能运维

本文介绍本届挑战赛冠军得主SRE-Copilot团队的参赛方案:基于LLM的多场景智能运维。 基础架构-SRE,负责字节跳动基础架构部门所有组件的SRE工作,沿着成本、稳定性、效率、服务四条主线,致力于打造高扩展、高可用的生产系统。基础架…

2024年【烟花爆竹经营单位安全管理人员】免费试题及烟花爆竹经营单位安全管理人员试题及解析

题库来源:安全生产模拟考试一点通公众号小程序 烟花爆竹经营单位安全管理人员免费试题根据新烟花爆竹经营单位安全管理人员考试大纲要求,安全生产模拟考试一点通将烟花爆竹经营单位安全管理人员模拟考试试题进行汇编,组成一套烟花爆竹经营单…

Linux设置程序任意位置执行(设置环境变量)

问题 直接编译出来的可执行程序在执行时需要写出完整路径比较麻烦,设置环境变量可以实现在任意位置直接运行。 解决 1.打开.bashrc文件 vim ~/.bashrc 2.修改该文件(实现将/home/zhangziheng/file/seqrequester/build/bin,路径下的可执…

四、《任务列表案例》后端程序实现和测试

本章概要 准备工作功能实现前后联调 4.1 准备工作 数据库脚本 CREATE TABLE schedule (id INT NOT NULL AUTO_INCREMENT,title VARCHAR(255) NOT NULL,completed BOOLEAN NOT NULL,PRIMARY KEY (id) );INSERT INTO schedule (title, completed) VALUES(学习java, true),(学…

spring cloud 入门

​ 学习目标: 学习spring cloud项目快速搭建方法,学习nacos注册中心使用,实现两个服务间的调用 学习内容: 一、Spring Cloud介绍 Spring 以 Bean(对象) 为中心,提供 IOC、AOP 等功能。Sprin…

使用链表和数组输出A~Z的ASCII码

输出结果 26个字母以及其对应的ASCII码 一、使用链表创建,注意: 节点需要有next指针初始化时head需要new一下 cur指针代表当前指针,每次不断的New新的节点,pre指针代表当前指针的前一个指针,每次pre的next指针指向cur…

Unity将4个纹理图拼接成1个纹理

需要的效果 最终实现的效果大概如下: 4个贴图上去 这里随便放一个切分的图。 Shader代码如下 直接上代码: // Unity built-in shader source. Copyright (c) 2016 Unity Technologies. MIT license (see license.txt)// Unlit shader. Simplest possible textured shad…

基础小白快速入门Python------>模块的作用和意义

模块, 这个词听起来是如此的高大威猛,以至于萌新小白见了瑟瑟发抖,本草履虫见了都直摇头,好像听上去很难的样子,但是但是,年轻人,请听本少年细细讲述,他只是看起来很难,实…

猫毛过敏养猫人士的必备养猫好物-宠物空气净化器品牌分享

许多猫奴在与猫相处一段时间后突然对猫毛过敏,这真是令人难受。一些人认为对猫咪过敏是因为它们在空气中飘浮的毛发引起的,但实际上大部分人之所以过敏是因为对猫身上一种微小的蛋白质过敏。这种导致过敏的蛋白质附着在猫咪的一些皮屑上。我们都知道猫咪…

uniapp同步将本地图片转换为base64,支持微信、H5、APP

接上篇,少了一个方法的源代码。 先上代码: ploadFilePromiseSync (url) > { return new Promise((resolve, reject) > { // #ifdef MP-WEIXIN uni.getFileSystemManager().readFile({ filePath: url, encoding: base64, success: res > { let …

总结一下linux性能检测和调优手段

1.perf 是 Linux 系统中性能分析工具,用于收集性能相关的信息。它可以用于查看 CPU 使用情况、内存性能、磁盘 I/O 等,以帮助开发者找到性能瓶颈。 以下是一些 perf 常见用法和示例: 1. CPU Profiling a. 查看 CPU 使用率 perf stat -e cpu…

C++ 快速排序快速选择

目录 1、75. 颜色分类 2、912. 排序数组 3、 215. 数组中的第K个最大元素 4、LCR 159. 库存管理 III 1、75. 颜色分类 思路:利用快速排序思路,使用三指针分块进行优化。 [0,left]——小于key[left1,right-1]——等于key[right,nums.size()]——大于k…

金三银四跳槽季,你不得不知道的5个面试技巧

正式进入金三银四招聘季了,即将投入求职大战的小伙伴们,你真的准备好了吗? 别急,在参加面试前,请你看完这篇文章,相信面试成功率会提升不少。 1 “能力不如你,却薪资比你高” 背后隐藏的逻辑 …

前端src中图片img标签资源的几种写法?

在 Vue 项目中引用图片路径有几种不同的方法,具体取决于你的项目结构和配置。以下是几种常见的方式: 1. 静态资源目录 (Public) 如果你的图片放在了项目的 public 目录下(例如,Vite 和 Create Vue App 脚手架工具通常使用这个目…

wps软件怎么压缩文件?这样操作就可以~

WPS Office是一款功能强大的办公软件套件,其中包括文字处理、表格编辑和演示文稿制作等功能。在本文中,我们将介绍如何利用WPS软件以及其他压缩工具进行文件压缩,让您在处理文件时更加便捷高效。 除了这些基本功能外,WPS Office还…

lettuce webdriver 自动化测试---玩转BDD

🔥 交流讨论:欢迎加入我们一起学习! 🔥 资源分享:耗时200小时精选的「软件测试」资料包 🔥 教程推荐:火遍全网的《软件测试》教程 📢欢迎点赞 👍 收藏 ⭐留言 &#x1…

指针进阶(一)

文章目录 1:字符指针变量2:指针数组3:数组指针3.1数组指针的定义3.2:&数组名vs数组名 4:数组参数,指针参数4.1:一维数组传参的本质4.1.1:场景一4.1.2:场景二4.1.3:场景三4.1.4:场景四4.1.5:场景五 4.2:二维数组传参的本质4.2.1:场景一4.2.2:场景二4.2.3:场景三4.2.4:场景四…

光影魔术师:Photoshop 2022——你的创意无限可能

在数字艺术的广阔天地中,有一款软件如同魔法师般,以其强大的功能和无尽的可能性,引领着无数创意者探索未知的视觉世界。它,就是Adobe Photoshop 2022。 无论是Mac还是Windows系统,Photoshop 2022都以其卓越的兼容性&a…

mysql数据库操作小寄巧

目录 json字段查询时间相关只有日期只有时间又有时间又有日期时间比较时间运算 某字段同的取最新数据(软性的新数据覆盖旧数据查找)sql_modeonly_full_group_by的解决办法优化思路 json字段查询 查询某个json字段(xx)的某个属性下…

从http到websocket

阅读本文之前,你最好已经做过一些websocket的简单应用 从http到websocket HTTP101HTTP 轮询、长轮询和流化其他技术1. 服务器发送事件2. SPDY3. web实时通信 互联网简史web和httpWebsocket协议1. 简介2. 初始握手3. 计算响应健值4. 消息格式5. WebSocket关闭握手 实…