linux企业实战 --MySQL数据库之读写分离

1. MySQL读写分离

Mysql的主从复制和Mysql的读写分离两者有着紧密联系。

• 首先部署主从复制，只有主从复制完了，才能在此基础上进行数据的读写分离。Master数据库处理事务性增、删除、修改、更新操作（CREATE、INSERT、UPDATE、DELETE），而让Slave数据库处理SELECT操作。
• MYSQL读写分离前提是基于MYSQL主从复制，这样可以保证在Master上修改数据，Slave同步之后，WEB应用可以读取到Slave端的数据。
• 简单来说，读写分离就是只在主服务器上写，只在从服务器上读。
• 基本的原理是让主数据库处理事务性查询，而从数据库处理select查询，数据库复制被用来把事务性查询导致的改变更新同步到集群中的从数据库

2. MySQL-proxy实现读写分离

实验环境

主机名	IP	服务器
server1	172.25.42.1	master mysqld
server2	172.25.42.2	slave mysqld
server3	172.25.42.3	mysql-proxy 相当于代理
物理机	172.25.42.250	测试

基于gtid的异步复制（主从复制）。在server3上面：搭建proxy代理服务器（实现客户端写在server1上面、客户端读server2上的数据）

步骤一：解压mysql-proxy的tar包到/usr/local，做软连接

tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/   加压到指定目录下
ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy  

步骤二：新建一个配置文件的目录，创建配置文件

cd /usr/local/mysql-proxy
mkdir conf
cd conf/
vim mysql-proxy.conf      ##编写服务的配置文件

[mysql-proxy]
proxy-address=0.0.0.0:3306    ##mysql-proxy运行的端口
proxy-read-only-backend-addresses=172.25.42.2:3306   ##slave节点：只读
proxy-backend-addresses=172.25.42.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        ##打入后台

步骤三：修改数据库发生读写分离时的最大和最小连接数

把原来的4和8改为1和2，默认超过4个连接才会启动读写分离，改为2个便于测试。

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

步骤四：创建日志文件和进程pid文件所在的目录

[root@server3 mysql-proxy]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
[root@server3 mysql-proxy]# mkdir log建立目录，存放进程pid和日志

步骤五：启动mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

此时报错：

• 原因： 配置文件权限过大
• 更改方法： 修改文件权限

chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

再次启动

步骤六：查看日志

cd /usr/local/mysql-proxy/log/
ls
mysql-proxy.log  mysql-proxy.pid
cat mysql-proxy.log ##查看日志

此时可以看到两个节点都加进来了，server3的proxy配置完成！！

测试

步骤一：物理机中安装mariadb

步骤二：在server1上面:创建新的用户并且授权

mysql -uroot -pDrn+19961126

mysql> grant insert,update,select on *.* to drn@'%' identified by 'Drn+19961126'; 			#新建一个用户并对他授权
mysql> flush privileges;	#刷新授权表
mysql> use nn；
mysql> create table userinfo (
    -> username varchar(10) not null,
    -> password varchar(15) not null);
mysql> desc userinfo; 		#查看表结构

步骤三：server3安装lsof

yum install lsof

步骤四：在物理机上，打开一个shell以drn用户来连接数据库

mysql -h 172.25.42.3 -udrn -pDrn+19961126

多开物理机然后连接一次在server3上losf -i 3306一次，直到出现server2（发现开始读写分离(用户多的时候)）

步骤五：在server3上，用lsof命令对mysql的端口3306进行监测，列出谁在使用3306端口：lsof -i：3306

我们在物理机上再打开两个shell来连接数据库，就可以看到 前两次的连接都指向server1，第三次的连接指向server2，说明启动了读写分离。

步骤六：在物理机上，插入数据

MySQL [nn]> insert into userinfo values ('okokj','75156321'); 
MySQL [nn]> select * from userinfo;

此时slave被关闭
此时slave被打开