MySQL 读写分离

Strong 2021年03月15日 1,107次浏览

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集群,可以实现负载均衡的效果。

实验拓扑图
mysqlproxy.jpg

实验环境:

主机IP地址
Master192.168.52.128
Slave192.168.52.129
Proxy192.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读写分离配置

实验环境:

主机IP地址
Master192.168.52.128
Slave192.168.52.129
Mycat192.168.52.130
//MySQL主从复制配置此处略

//MySQL主服务器中创建名为test的数据库,并在test库中创建名为testtable的表,用于后续测试
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.001 sec)

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

MariaDB [(none)]> use test
Database changed

MariaDB [test]> create table testtable(id int(10),name varchar(10));
Query OK, 0 rows affected (0.010 sec)

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| testtable      |
+----------------+
1 row in set (0.001 sec)

//授权一个用户,用于测试
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)


//下载mycat工具并解压,放至合适的位置
[root@mycat ~]# wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

[root@mycat ~]# tar -xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz 

[root@mycat ~]# mv mycat /usr/local/mycat

//设置环境变量
[root@mycat ~]# echo 'PATH=$PATH:/usr/local/mycat/bin' >> /etc/profile.d/mycat.sh
[root@mycat ~]# source /etc/profile.d/mycat.sh

//安装java环境
[root@mycat ~]# dnf -y install java

[root@mycat ~]# java -version
openjdk version "1.8.0_242"
OpenJDK Runtime Environment (build 1.8.0_242-b08)
OpenJDK 64-Bit Server VM (build 25.242-b08, mixed mode)

//配置mycat读写分离
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="TESTDB01">
                <table name="testtable" primaryKey="id" dataNode="TESTDB01">
                </table>
        </schema>

        <dataNode name="TESTDB01" dataHost="host01" database="test" />

        <dataHost name="host01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM01" url="jdbc:mysql://192.168.52.150:3306" user="root" password="1">
                        <readHost host="hostS01" url="jdbc:mysql://192.168.52.151:3306" user="root" password="1"/>
                </writeHost>
        </dataHost>
</mycat:schema>

//配置解析:
schema 标签	//用于定义逻辑库,划分不同的逻辑库
name 属性		//定义逻辑库的名字,必须唯一不能重复
checkSQLschema 属性	//判断是否检查发给Mycat的SQL是否含有库名,为true时会将SQL中的库名删除掉
sqlMaxLimit 属性	//用于限制返回结果集的行数,值为-1时表示关闭该限制。如果没有开启限制则默认取server.xml里配置的限制
randomDataNode 属性	//定义将一些随机语句发送到该数据节点中
dataNode 属性		//用于绑定逻辑库到某个具体的 物理database 上,1.3 版本如果配置了 dataNode,则不可以配置分片表,1.4 可以配置默认分片

table 标签	//定义逻辑表,使用了schema 标签定义逻辑库之后,还需要使用table 标签定义逻辑表
name 属性		//定义逻辑表的名字,必须唯一不能重复且需要与数据库中的物理表名一致。使用逗号分割配置多个表,即多个表使用这个配置
primaryKey 属性		//指定逻辑表中的主键,也是需要与物理表的主键一致
dataNode 属性		//指定物理表所在数据节点的名称,配置多个数据节点时需按索引顺序并使用逗号分隔,或指定一个索引范围:dn1$0-743。注意数据节点定义之后,顺序不能再发生改变,否则会导致数据混乱
rule 属性		//属性用于指定分片规则名称,对应rule.xml中tableRule标签的name属性,如无需分片可以不指定
splitTableNames 属性	//定义是否允许多个表的定义,即多个表使用这个配置

dataNode 标签	//用于定义数据节点,数据节点指向的是存储逻辑表的物理数据库
name 属性		//定义数据节点的名称,必须唯一
dataHost 属性		//指定分片所在的物理主机
database 属性		//指定物理数据库的名称

dataHost 标签	//用于定义后端物理数据库主机信息
name 属性		//用于定义主机名称,必须唯一
maxCon 属性		//指定每个读/写实例连接池的最大连接数。也就是说,标签内嵌套的writeHost、readHost 标签都会使用这个属性的值来实例化出连接池的最大连接数
minCon 属性		//指定每个读写实例连接池的最小连接数,即初始化连接池的大小
dbType 属性		//指定后端连接的数据库类型,目前支持二进制的mysql协议,还有其他使用JDBC连接的数据库
dbDriver 属性		//指定连接后端数据库使用的驱动,目前可选的值有native和JDBC
slaveThreshold 属性	//用于定义主从复制延时阈值,当延时过高读写分离筛选器会过滤掉此Slave机器,防止读到很久之前的旧数据
balance 属性		//指定读写分离的负载均衡类型,目前的取值有4 种:
	0:不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上
	1:全部的readHost与stand by writeHost参与select语句的负载均衡
	2:所有读操作都随机在writeHost、readhost 上分发
	3:所有读请求随机分发到 wiriterHost 对应的readhost 执行。即 writerHost 不负担读压力,全部读请求由 readhost 执行。注意该取值只在1.4及其以后版本有,1.3没有
writeType 属性		//指定写实例的负载均衡类型,目前的取值有4 种:
	-1:表示不自动切换
	0:所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个writeHost。重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties
	1:所有写操作都随机的发送到配置的writeHost,1.5 以后废弃不推荐使用
	2:基于MySQL主从同步的状态决定是否切换(1.4 新增)
switchType 属性		//用于指定主从切换的方式:
	-1:表示不自动切换
	1:默认值,自动切换
	2:基于MySQL主从同步的状态决定是否切换,心跳检测语句为:show slave status
	3:基于MySQL galary cluster的切换机制(适合集群,1.4.1新增),心跳检测语句为show status like 'wsrep%'

heartbeat 标签	//指明用于和后端数据库进行心跳检查的语句

writeHost 标签	//配置写实例,即主从中的master节点
readHost 标签	//配置读实例,即主从中的salve节点,readHost是writeHost的子标签,与writeHost有绑定关系
host 属性		//用于标识不同实例名称,一般writeHost名称使用M1作为后缀,readHost则使用S1作为后缀
url 属性		//用于配置数据库的连接地址,如果是使用native的dbDriver,则一般为address:port这种形式。用JDBC或其他的dbDriver,则需要特殊指定。例如,当使用JDBC 时则可以这么写:jdbc:mysql://localhost:3306/
user 属性		//配置数据库用户名
password 属性		//配置数据库密码
weight 属性		//配置某个数据库在 readhost 中作为读节点的权重
usingDecrypt 属性	//指定是否对密码加密,默认为0, 若需要开启则配置为1

//配置用户及访问权限
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml
	<system>
		<property name="serverPort">3306</property>
	</system>
	
        <user name="hzz" defaultAccount="true">
                <property name="password">hzz123!</property>
                <property name="schemas">TESTDB</property>
                <property name="defaultSchema">TESTDB</property>
        </user>

        <user name="hzz">
                <property name="password">hzz123!</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
                <property name="defaultSchema">TESTDB</property>
        </user>

//