目录 :
一、MySQL数据库的读写分离简介
- 1、mysql数据库的读写分离的基础知识(为什么要进行读写分离)
- 2、读写分离的实现方式
二、MySQL数据库读写分离实验
(一)、在server1和server2先配置主从复制
- 1、在server1上进行相关的配置
- 步骤一:在server1上面设置:master节点(基于gtid的异步复制)
- 步骤二:在主配置文件中添加主复制的代码
- 步骤三:初始化数据库
- 步骤四:创建用户并授权
- 2、在server上进行相关的设定
- 步骤一:关闭服务、修改配置文件
- 步骤二:初始化数据库
- 步骤三 :登录数据库,进行slave和master的链接
(二)、在server3上进行mysql-proxy代理的相关配置
- 步骤一:
- 1、安装proxy代理包
- 2、了解proxy目录下的相关文件配置
- 步骤二:切换到解压的目录下,进相关的设定
- 步骤三:创建代理配置文件存放的目录
- 步骤四:修改数据库发生读写分离时的最大和最小连接数
- 步骤五:创建日志文件和进程pid文件所在的目录
- 步骤六:启动mysql-proxy
- 步骤七:查看端口是否开启、查看日志、pid是否正常开启
(三)、测试读写分离
- 步骤一:在server1上面:创建新用户并授于读写权限,创建test数据库
- 步骤二:在server2上查看
- 步骤三:查看主从复制的效果
- 步骤五:查看数据是否同步
(四)、验证proxy是否启用读写分离
- 步骤一:在server3上:安装lsof查看其他服务链接server3主机proxy的代理情况
- 步骤二:查看3306端口被连接的情况
- 步骤三:在真机上运程登录server3数据库端
- 步骤四:断开连接,再次登录(查看proxy的代理原理)
(五)、验证读写分离的工作原理
- 步骤一:真机上写入数据、查看实验效果
- 步骤二:关闭slave、在真机上写入数据查看实验效果
- 实验总结
一、MySQL数据库的读写分离简介
1.mysql数据库的读写分离的基础知识(为什么要进行读写分离)
对于很多大型网站(pv值百万、千万)来说,在所处理的业务中,其中有70%的业务是查询(select)相关的业务操作
(新闻网站,插入一条新闻,查询操作)
剩下的则是写(insert、update、delete,只要能对MySQL的数据造成更改的操作都叫写操作)操作
在使用负载均衡集群之后,可以很大程度的提升网站的整体性能,但是最终的数据处理的压力还是会落到MySQL数据库上
所有很有必要使用一些技术来提升MySQL的负载能力。(读写分离)
写专门交给写服务器处理(一般网站来说写是比较少的 读写比 4:1) 那么需要把读的任务分配多台服务器来完成的架构,就叫做读写分离
2.读写分离的实现方式
- 注意: (1)php程序上自己做逻辑判断,写php代码的时候,自己在程序上做逻辑判读写匹配。select,insert、update、delete做正则匹配,根据结果选择写服务器(主服务器)。如果是select操作则选择读服务器(从服务器器) mysql_connect(‘读写的区分’)
- (2)MySQL- Proxy是实现"读写分离(Read/Write Splitting)"的一个软件(MySQL官方提供 ,也叫中间件),基本的原理是让主数据库处理写操作(insert、update、delete),而从数据库处理查询操作(select)。而数据库的一致性则通过主从复制来实现。所以说主从复制是读写分离的基础。
注意:MySQL-proxy 它能实现读写语句的区分主要依靠的是内部一个lua脚本(能实现读写语句的判断)
注意:如果只在主服务器(写服务器)上完成数据的写操作话;这个时候从服务器上没有执行写操作,是没有数据的
这个时候需要使用另外一个技术来实现主从服务器的数据一致性,这个技术叫做 主从复制技术, 所以说主从复制是读写分离的基础
读写分离(MySQL- Proxy)是指让master处理写操作,让slave处理读操作,非常适用于读操作量比较大的场景,可减轻master的压力
使用mysql-proxy实现mysql的读写分离,mysql-proxy实际上是作为后端mysql主从服务器的代理,它直接接受客户端的请求
对SQL语句进行分析,判断出是读操作还是写操作,然后分发至对应的mysql服务器上
因为数据库的写操作相对读操作是比较耗时的,所以数据库的读写分离,解决的是数据库的写入,影响了查询的效率
- 1、mysql数据库的读写分离的基础知识(为什么要进行读写分离)
- 2、读写分离的实现方式
二、MySQL数据库读写分离实验
1、搭建实验环境
主机名 | IP | 角色 | 服务 |
---|---|---|---|
server1 | 172.25.6.1 | master | mysqld |
server2 | 172.25.6.2 | slave | mysqld |
server3 | 172.25.6.3 | 代理端 | mysql-proxy |
[root@foundation6 ~]# |
172.25.6.250 | 客户端(登录proxy代理端) |
2、实验原理简诉
- 应用:把写和读分开
- 如果业务压力不是很大的时候要做读写分离,取决于硬盘读取的性能,客户才满意
- 读库(配置低),写库(配置高:固态硬盘,高速存储)
- 读写分离->mysql集群、分布式、代理、高可用
- 1、mysql数据库的读写分离的基础知识(为什么要进行读写分离)
- 2、读写分离的实现方式
- 数据不一定来源于同一个地方
- 还会有测试库,模拟上线,测试服务器
- proxy,相当于一个代理
- 实验一共需要量三台主机
- server1做读、写库
- server2做读库
- server3做proxy代理
- 并且两个库(server1和server2)要实现读写分离,但是还要实现数据同步,基于gtid的异步复制(主从复制)
实验部分
(一)、在server1和server2先配置主从复制
1、在server1上
步骤一:
- 1、mysql数据库的读写分离的基础知识(为什么要进行读写分离)
- 2、读写分离的实现方式
在server1上面设置:master节点(基于gtid的异步复制)
[root@server1 ~]# systemctl start mysqld
步骤二:
在主配置文件中添加主复制的代码
- 1、mysql数据库的读写分离的基础知识(为什么要进行读写分离)
- 2、读写分离的实现方式
步骤三:
初始化数据库
- [root@server1 mysql]# grep password /var/log/mysqld.log ##查看密码日志
- [root@server1 ~]# mysql_secure_installation ##初始化数据库
- Securing the MySQL server deployment.
- Enter password for user root: ##输入初始化密码
步骤四:
创建用户并授权
- [root@server1 ~]# mysql -uroot -pYang+123love ##登录数据库
- mysql> alter user root@localhost identified by 'Yang+123love'; ##创建用户并授权
- mysql> show databases;
- mysql> grant replication slave on *.* to repl @'172.25.6.%' identified by 'Yang+123love'; ##授予slave权限
- mysql> show master status;
(二)在server2上进行相关的设定
步骤一:
关闭服务、修改配置文件
- [root@server2 mnt]# systemctl stop mysqld ##关闭服务上一篇实验有官这方面的解释
- [root@server2 mnt]# cd /var/lib/mysql
- [root@server2 mysql]# ls
- [root@server2 mysql]# rm -rf * ##删除之前实验的配置环境
- [root@server2 mysql]# vim /etc/my.cnf
- 加入:
- server_id=2
- log-bin=mysql-bin
- gtid_mode=ON
- enforce_gtid_consistency=true
- [root@server2 ~]# systemctl start mysqld ##重启服务
步骤二:
初始化数据库
- [root@server1 mysql]# grep password /var/log/mysqld.log ##查看密码日志
- [root@server1 ~]# mysql_secure_installation ##初始化数据库
- Securing the MySQL server deployment.
- Enter password for user root: ##输入初始化密码
步骤三 :
登录数据库,进行slave和master的链接
- [root@server2 ~]# mysql -uroot -pYang+123love ##登录数据库
- Enter password:
- mysql> alter user root@localhost identified by 'Yang+123love'; ##进行身份认证
- mysql> change master to master_host='172.25.6.1',master_user='repl',master_password='Yang+123love',master_auto_position=1; ##和matser主机相连
- mysql> start slave; ##开启slave服务
- mysql> show slave status\G
- ...
- Slave_IO_Running: Yes ##查看IO线程
- Slave_SQL_Running: Yes ##sql线程
- ...
(io线程和sql线程为yes是说明配置成功!!!)
3、在server3上进行mysql-proxy代理的相关配置
在server3上面:搭建proxy代理服务器(实现客户端写在server1上面、客户端读server2上的数据)
步骤一:
安装proxy代理包
- [root@server3 ~]# systemctl stop mysqld停止之前的mysqld服务
- [root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/加压到指定目录下
步骤二:
1、切换到解压的目录下,进相关的设定
- [root@server3 ~]# cd /usr/local/ ##切换到解压的目录下
- [root@server3 local]# ll ##查看
- [root@server3 local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy ##更改名称方便实验
- [root@server3 local]# ll查看
2、了解proxy目录下的相关文件配置
- [root@server3 local]# cd mysql-proxy/ ##进入proxy服务的目录
- [root@server3 mysql-proxy]# ls
- bin include lib libexec licenses share
- [root@server3 mysql-proxy]# cd bin/ ##进入脚本目录
- [root@server3 bin]# ls
- mysql-binlog-dump mysql-myisam-dump mysql-proxy ##启动脚本
步骤三:
创建代理配置文件存放的目录
- [root@server3 bin]# ls
- mysql-binlog-dump mysql-myisam-dump mysql-proxy
- [root@server3 bin]# cd ..
- [root@server3 mysql-proxy]# ls
- bin include lib libexec licenses share
- [root@server3 mysql-proxy]# mkdir conf ##创建目录
- [root@server3 mysql-proxy]# ls
- bin conf include lib libexec licenses share
- [root@server3 mysql-proxy]# cd conf/ ##进入目录中
- [root@server3 conf]# ls
- [root@server3 conf]# vim mysql-proxy.conf ##编写代理服务的配置文件
- ...
- [mysql-proxy]
- proxy-address=0.0.0.0:3306 ##mysql-proxy运行的端口
- proxy-read-only-backend-addresses=172.25.6.2:3306 ##slave节点:只读
- proxy-backend-addresses=172.25.6.1:3306 ##master节点:可读可写
- proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本的路径
- pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid ##进程pid的位置
- log-file=/usr/local/mysql-proxy/log/mysql-proxy.log ##日志位置
- plugins=proxy
- log-level=debug ##定义日志级别
- keepalive=true ##mysql-proxy崩溃时尝试重启
- daemon=true ##打入后台
步骤四:
修改数据库发生读写分离时的最大和最小连接数
- [root@server3 bin]# cd ..
- [root@server3 mysql-proxy]# ls
- bin conf include lib libexec licenses share
- [root@server3 mysql-proxy]# find . -name *.lua ##查看配置文件所在的路径
- [root@server3 mysql-proxy]# cd share/doc/mysql-proxy
- [root@server3 mysql-proxy]# pwd
- /usr/local/mysql-proxy/share/doc/mysql-proxy
- [root@server3 mysql-proxy]# ls
- [root@server3 mysql-proxy]# vim rw-splitting.lua ##修改主机数量
- ...
- 40 min_idle_connections = 1, 最小连接数
- 41 max_idle_connections = 2, 最大连接数
- ...
步骤五:
创建日志文件和进程pid文件所在的目录
- [root@server3 mysql-proxy]# cd /usr/local/mysql-proxy/
- [root@server3 mysql-proxy]# ls
- [root@server3 mysql-proxy]# mkdir log ##建立目录,存放进程pid和日志
- [root@server3 mysql-proxy]# ls
步骤六:
启动mysql-proxy
- [root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf ##启动mysql-proxy
- [root@server3 mysql-proxy]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf ##给文加设定权限
报错:
2020-03-26 23:10:10: (critical) Key file contains key 'daemon' which has value that cannot be interpreted.
2020-03-26 23:10:10: (message) Initiating shutdown, requested from mysql-proxy-cli.c:367
2020-03-26 23:10:10: (message) shutting down normally, exit code is: 1解决方法:
在配置文件mysql-proxy.conf 中加入
daemon=1
keepalive=1
重新开启
(开启成功!!!)
其实就编辑了一个配置文件和lua文件
步骤七:
查看端口是否开启、查看日志、pid是否正常开启
- [root@server3 conf]# netstat -anltp
- [root@server3 log]# cat mysql-proxy.pid
- [root@server3 log]# cat /usr/local/mysql-proxy/log/mysql-proxy.log
(查看日志看到两个节点都加进来了,说明已经配置成功!!!)
(二)、测试读写分离
步骤一 :
在server1上面:创建新用户并授于读写权限,创建test数据库
- [root@server1 ~]# mysql -uroot -pYang+123love
- mysql> grant insert,update,select on *.* to yang@'%' identified by 'Yang+123love';新建一个用户yang并对他授权
- mysql> flush privileges;刷新授权表
- mysql> show databases;查看数据库信息
- 1、mysql数据库的读写分离的基础知识(为什么要进行读写分离)
- 2、读写分离的实现方式
步骤二:
在server2上查看
步骤三:
查看主从复制的效果
1、在server1上创建库
- mysql> create database test1;创建test1库
- mysql> show databases;
2、在server2上查看
(查看到在 server1上创建好的库)
步骤四:
1、在server1上创建表格
- mysql> use westos;
- Database changed创建usertb表
- mysql> create table usertb (
- -> username varchar(10) not null,
- -> password varchar(15) not null);
- mysql> desc usertb;查看表结构
2、在server2上(看不到创建的表格)
步骤五:
查看数据是否同步
1、在server1上向表格中写入数据
- mysql> insert into users values ('user1',20');
- mysql> use test1;
- Database changed
- mysql> select * from users;
2、在server2上:
查看数据是否同步
(三)、验证proxy是否启用读写分离
步骤一:
在server3上:
安装lsof查看其他服务链接server3主机proxy的代理情况
[root@server3 ~]# yum install lsof -y
步骤二:
查看3306端口被连接的情况
[root@server3 ~]# lsof -i:3306
步骤三:
在真机上运程登录server3数据库端
- [root@foundation6 ~]# mysql -h 172.25.6.3 -uyang -pYang+123love ##运程登录server3数据库
- MySQL [(none)]> show databases;
查看到的是第一次连接代理的是server1主机
步骤四:
断开连接,再次登录
(还是server1)
步骤五:
断开连接,再次登录
由于在同一个shell中连接两次,所有lsof -i:3306查看到第一次连接到server1,第二次连接到server2
到此,server1与server2均被连接到,说明读写分离启用
。
(四)、验证读写分离的工作原理
步骤一:
1、在server1上写入数据:
- mysql> insert into users values ('user2','22'); ##插入数据
- mysql> select * from users; ##查看创建的数据
2、在server2上查看
3、在真机上查看
(说明在server1端上可以写入数据)
步骤二:
1、在真机上写入数据
MySQL [test1]> insert into users values('user3','23'); ##写入数据
2、在server1端
3、在server2端
总结:server1(master)上能看到数据说明写操作是在master上
而slave可以查看到数据是因为server1(master)和server2(slave)是主从复制的关系
不能说明server2上可以进行写操作,为了更清楚的验证读写分离,我们做下面的实验
步骤三:
关闭server2上的slave服务(stop slave;关闭server2的异步复制)
1、在server2上关闭slave
mysql> stop slave;
2、在真机上插入数据
- MySQL [test1]> insert into users values('user4','24'); ##写入数据
- MySQL [test1]> select * from users; ##查看数据
(发现在真机上查看不到刚刚写入的数据!!!)
2、在server2上查看(也看不到刚写入的数据)
3、在server3上查看真机上写入的数据
结论:当前server2的slave是关闭的所以不能进行主从复制
测试端插入数据server1(master)可以看到说明写操作是在server1上进行的
server2上没有新插入的数据,读操作是在server2上,所以读写分离了
实验总结:
标签:##,数据库,MySQL,企业级,proxy,mysql,server3,root,读写 From: https://www.cnblogs.com/Lqdream/p/16918522.html当访问数据库的真机(用户)数量很多的时候,数据库的代理就会把后端的数据库实现读写分离
在本实验中
server1是写的数据库
server2是读的数据库
当server1和server2满足gtid的异步复制的时候,真机往数据库写入的东西其实是写入了server1,并没有写入server2,server2上面的数据是复制过去的
因此server1、server2、真机上面都能查到刚刚写进去的数据,其实真机查的是server2(读)
当关闭server1和server2的异步复制的时候,真机往数据库写入数据的东西只写进了server1,没有写进去server2,server2也没有复制。
因为server1可以查看到,server2和真机上面都查不到刚刚写进去的数据,此时的真机读的是server