MySQL读写分离

1、MySQL读写分离概述

当今MySQL使用相当广泛，随着用户的增多以及数据量的增大，高并发随之而来；然而我们有很多办法可以缓解数据库的压力，分布式数据库集群、负载均衡、读写分离、增加缓存服务器等等。采用读写分离技术缓解数据库的压力是一种不错的方式。

1.1 MySQL读写分离原理

读写分离的基本原理是让主数据库处理事务性的增、删、改操作（INSERT、DELETE、UPDATE），而从数据库处理SELECT查询操作。MySQL主从复制将事务性的增、删、改操作同步到集群中的从数据库。

MySQL Proxy读写分离架构图
MySQL读写分离.jpg

在应用和数据库之间增加代理层（MySQL Proxy），代理层接收应用对数据库的请求，根据不同请求类型转发到不同的实例，在实现读写分离的同时可以实现负载均衡。

1.2 MySQL读写分离的好处

物理服务器增加，机器处理能力提升，拿硬件换性能。
主从只负责各自的读和写，极大程度缓解X锁和S锁争用。
slave可以配置myiasm引擎，提升查询性能以及节约系统开销。
slave可以单独设置一些参数来提升其读的性能。
master的写是并发，slave通过主库发送来的binlog同步数据。
增加冗余，提高可用性。

2、MySQLProxy实现读写分离

MySQL Proxy是MySQL官方提供的数据库代理工具；MySQL Proxy实际上是在客户端请求与MySQLServer之间建立了一个连接池，所有客户端请求都是发向MySQL Proxy，然后经由MySQL Proxy进行相应的分析，判断出是读操作还是写操作，分发至对应的MySQLServer上；对于多节点Slave集群，可以实现负载均衡的效果。

实验拓扑图

实验环境：

主机	IP地址
Master	192.168.52.128
Slave	192.168.52.129
Proxy	192.168.52.130

配置MySQL主从复制

//主服务器和从服务器安装并初始化mariadb
[root@master ~]# dnf -y install mariadb mariadb-server
[root@master ~]# systemctl enable mariadb
[root@master ~]# systemctl start mariadb
[root@master ~]# mysql_secure_installation 

[root@slave ~]# dnf -y install mariadb mariadb-server
[root@slave ~]# systemctl enable mariadb
[root@slave ~]# systemctl start mariadb
[root@slave ~]# mysql_secure_installation 

//主服务器关闭防火墙和selinux
[root@master ~]# systemctl stop firewalld.service 
[root@master ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master ~]# setenforce 0

//从服务器关闭防火墙和selinux
[root@slave ~]# systemctl stop firewalld.service 
[root@slave ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@slave ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@slave ~]# setenforce 0

//在主服务器上配置主从复制，开启二进制日志，设置服务id
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log_bin=mysql-bin			//开启二进制日志
server_id=1				//服务id号，不可重复,为0则拒绝从服务器连接
innodb_flush_log_at_trx_commit=1	//每个事务提交时立即写入binlog
sync_binlog=1				//事务提交后立刻刷新binlog
:wq!

[root@master ~]# systemctl restart mariadb.service 

//在主服务器授权一个数据库用户用于复制，创建完成后在从服务器测试登录
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'zcfz'@'%' IDENTIFIED BY 'hzz123!';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

//在从服务器配置主从复制，设置服务id
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server_id=2			//服务id号，不可重复,为0则拒绝从服务器连接
:wq!

[root@slave ~]# systemctl restart mariadb.service 

//登录到从服务器数据库，在数据库中配置主服务器信息
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.52.128',MASTER_PORT=3306,MASTER_USER='zcfz',MASTER_PASSWORD='hzz123!';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.001 sec)

//查看从服务器主从复制是否开启
MariaDB [(none)]> SHOW SLAVE STATUS \G 
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.52.128
                   Master_User: zcfz
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 638
                Relay_Log_File: mariadb-relay-bin.000003
                 Relay_Log_Pos: 937
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
.....省略.....

MySQL Proxy实现读写分离

//proxy节点关闭防火墙和selinux
[root@proxy ~]# systemctl stop firewalld.service 
[root@proxy ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@proxy ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@proxy ~]# setenforce 0

//在proxy节点下载MySQL-Proxy工具
[root@proxy ~]# wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

//解压MySQL-Proxy工具
[root@proxy ~]# tar -xvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@proxy ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

//设置MySQL-Proxy工具环境变量
[root@proxy ~]# echo 'export PATH=$PATH:/usr/local/mysql-proxy/bin' >> ~/.bash_profile 
[root@proxy ~]# source ~/.bash_profile 

//配置MySQL-Proxy配置文件设置读写分离
[root@proxy ~]# mkdir /usr/local/mysql-proxy/conf
[root@proxy ~]# mkdir /usr/local/mysql-proxy/logs

[root@proxy ~]# vim /usr/local/mysql-proxy/conf/mysql-proxy.conf
[mysql-proxy]
plugins=proxy						//proxy插件
user=root 						//以root身份运行mysql-proxy
proxy-address=192.168.52.130:3306			//监听本机中IP地址和端口，默认端口4040。
proxy-backend-addresses=192.168.52.128:3306		//进行写的数据库master
proxy-read-only-backend-addresses=192.168.52.129:3306	//进行读的数据库slave
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua	//指定读写分离脚本文件
pid-file=/usr/local/mysql-proxy/logs/mysql-proxy.pid	//pid文件位置
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log    //生成日志文件
log-level=debug						//日志等级为debug
keepalive=true     					//心跳检测，崩溃时，尝试重启
daemon=true        					//守护进程
:wq！

//修改配置文件权限，防止信息泄露，如果不修改启动报错
[root@proxy ~]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf 

//设置读写分离连接数量
[root@proxy ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,	//默认超过4个连接数时，才开始读写分离，改为1
                max_idle_connections = 8,

                is_debug = false
        }
end

//开启MySQL-Porxy实现读写分离
[root@proxy ~]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

//在主服务器和从服务器上授权一个用户进行登录测试，主服务器和从服务器用户需一致
//只需在主服务器授权一个用户，主从复制会将此用户同步至从服务器
MariaDB [(none)]> GRANT ALL ON *.* TO 'hzz'@'%' IDENTIFIED BY 'hzz123!';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

//测试，在测试端上登录到proxy代理
[root@test ~]# mysql -uhzz -phzz123! -h192.168.52.130

//登录到proxy代理后,创建一个数据库和表
MariaDB [(none)]> create database hzz;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hzz                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> create table hzz.hzztable(id int(7));
Query OK, 0 rows affected (0.004 sec)

//然后在从服务器上停止主从复制
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.001 sec)

//回到测试端，登录到proxy代理，插入一条数据，发现select查看不到，说明读写分离成功
MariaDB [(none)]> insert into hzz.hzztable(id)value(1);
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> select * from hzz.hzztable;
0 row in set (0.001 sec)

3、MyCat实现读写分离

3.1 MyCat简介

MyCat是一款由阿里Cobar演变而来的用于支持数据库的读写分离、分表分库的分布式中间件。MyCat支持Oracle、MSSQL、MYSQL、PG、DB2关系型数据库，同时也支持MongoDB等非关系型数据库。

3.2 MyCat工作原理

Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL，对SQL做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据块，并将返回的结果做适当的处理，最终再返回给用户。

MyCat读写分离架构图
mycat

3.3 MyCat读写分离配置