MySQL读写分离
1、MySQL读写分离概述
当今MySQL使用相当广泛,随着用户的增多以及数据量的增大,高并发随之而来;然而我们有很多办法可以缓解数据库的压力,分布式数据库集群、负载均衡、读写分离、增加缓存服务器等等。采用读写分离技术缓解数据库的压力是一种不错的方式。
1.1 MySQL读写分离原理
读写分离的基本原理是让主数据库处理事务性的增、删、改操作(INSERT、DELETE、UPDATE),而从数据库处理SELECT查询操作。MySQL主从复制将事务性的增、删、改操作同步到集群中的从数据库。
MySQL Proxy读写分离架构图
在应用和数据库之间增加代理层(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读写分离架构图
3.3 MyCat读写分离配置
实验环境:
主机 | IP地址 |
---|---|
Master | 192.168.52.128 |
Slave | 192.168.52.129 |
Mycat | 192.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>
//