mysql的读写分离

什么是读写分离？

读写分离其实就是将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。使用读写分离可以大大降低数据库服务器的负载。

搭建前提：需要3台虚拟机。

server1：172.25.62.1（读写）
server2：172.25.62.2（只读）
server3：172.25.62.3（调度）
首先，必须在server1和server2上安装配置好mysql，安装步骤详见mysql的主从复制（异步复制）

搭建步骤：

首先，在server1和server2两台主机做好gtid的主从复制。

在server1：

1.修改配置文件

[root@server1 mysql]# vim /etc/my.cnf

 29 log-bin=mysql-bin
 30 server_id=1
 31 gtid_mode=ON
 32 enforce-gtid-consistency=ON
 33 log_slave_updates=ON
 34 log_bin=binlog

2.准备一个纯净的mysql环境

[root@server1 mysql]# systemctl stop mysqld  
[root@server1 mysql]# rm -rf *
[root@server1 mysql]# systemctl start mysqld  
[root@server1 mysql]# cat /var/log/mysqld.log | grep password

删除原来mysql的数据，查看初始密码。
3.进行安全初始化，登陆。

[root@server1 ~]# mysql_secure_installation 

重新初始化。

[root@server1 mysql]# mysql -p

登陆。
4.添加用户，数据库作为jtid复制的主库

mysql> grant replication slave on *.* to repl@'172.25.62.%' identified by 'Hang+123';

mysql> show master status;

查看状态。
5.在server1上创建登陆用户

mysql> grant insert,update,select on *.* to hang@'%' identified by 'Hang+123';

登陆用户为hang，全部设定好之后用这个用户登陆。

在server2：

同样，server2也需要一个纯净的数据库环境，这里不再累述。首先要修改配置文件

[root@server2 mysql]# vim /etc/my.cnf
 29 log-bin=mysql-bin
 30 server_id=2
 31 gtid_mode=ON
 32 enforce-gtid-consistency=ON
 33 log_slave_updates=ON
 34 log_bin=binlog

之后登陆mysql进行配置。

mysql> change master to master_host='172.25.62.1', master_user='repl', master_password='Hang+123', master_auto_position=1;

进行授权，设置主库为server1，本机作为从库。

mysql> show slave status\G

查看从库server2的状态，sql线程和io线程是否打开，这里都是yes。

mysql> start slave;

开启从库slave，开始gtid复制，这样jtid的复制就搭建好了，接下来就要进行读写分离的配置了。

在server3：

用server3做前两台主机的调度，设置server1可以读写，server2是只读。
1.获取mysql-proxy安装包，进行解压

[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local

2.制作软链接

[root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

进入前面指定的/usr/local目录，做软链接，之后会生成mysql-proxy目录。
3.创建conf目录，新建文件

[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# vim mysql-proxy.conf

  1 [mysql-proxy]
  2 proxy-address=0.0.0.0:3306
  3 proxy-read-only-backend-addresses=172.25.62.2:3306		##设置只读
  4 proxy-backend-addresses=172.25.62.1:3306
  5 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
  6 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
  7 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
  8 plugins=proxy
  9 log-level=debug
 10 keepalive=true
 11 daemon=true

修改配置文件，对server1和server2进行调度。

[root@server3 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

之后还要修改文件权限。

4.设置最大最小连接数

[root@server3 conf]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

40                 min_idle_connections = 1,
41                 max_idle_connections = 2,		

最小连接数为1，最大连接数为2，超过两个连接时，写的数据回进行读写分离。也就是读的数据在server2上读，写的数据则写在server1上。
5.启动服务

[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

[root@server3 conf]# cat /usr/local/mysql-proxy/log/mysql-proxy.log 

查看日志，可以看到，server3和server1和server2的数据库连接成功，这样读写分离就设置成功了。

登陆：

在另一台主机登陆server3。

[root@foundation62 mysql]# mysql -h 172.25.62.3 -uhang -pHang+123

登陆成功，当登陆连接数超过2个时，读数据在server2上操作，写的数据则存入server1，这样可以大大降低数据库的访问负载。
在server3上可以查看连接数：

[root@server3 conf]# lsof -i:3306