MySQL 读写分离

  Strong         1,471次浏览         1次留言
MySQL 

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>

//
# MySQL  
Docker 简介
MySQL 主主复制

评论

标签云

Docker MySQL KVM 服务 OpenStack Linux

最新资源

最新评论

标签

Docker 5
MySQL 10
KVM 1
服务 8
OpenStack 2
Linux 32
关注我
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×

最新文章
Docker compose容器编排

Docker compose容器编排1、Docker compose简介docker-compose是docker编排工具，用于管理docker容器，在如今的微服务时代，一个项目会存在多个服务，使用docker一个个部署操作的话就会很麻烦，如果使用docker编排工具一键部署，统一管理就会便捷很多，
Docker 镜像制作

Docker镜像制作1、如何制作Docker镜像 制作属于自己的Docker镜像，一般有两种方式： 第一种为commit方式，利用已有的镜像，运行为容器后安装定制自己需要的环境，然后由容器生成镜像； 另一种就是build方式，通过编写Dockerfile文件添加构建镜像指令生成镜像。 1、docker commit制作镜像 docker commit 命令主要作用是将已经配置好运行环境的容器重新提交为镜像；此时提交成功的镜像就可以使用，并且环境已经配置好，不需要配置环境。 格式： docker commit [选项] <容器ID或容器名> [<仓库名>[:<标签>]] 选项： --author //指定修改的作者 --message //记录本次修改的内容
Docker 容器

Docker容器1、Docker容器概述 Docker容器是镜像运行的实例。容器是在一个资源隔离的环境中运行应用程序和其依赖项的、轻量化的虚拟化技术。容器运行在独立的沙箱环境中，这保证了容器内的进程不会影响到容器外的任何进程。 1.1 容器的优点 敏捷环境：容器技术的最大优势是比创建VM(虚拟机)实例更快的速度。它们的轻量化在性能和占用空间方面的开销更小。 提高生产力：提高开发、部署以及升级效率；每个容器都可以被看作是一个不同的微服务，因此可以独立升级，而不需要考虑它们的同步。 版本控制：容器的每个镜像都可以进行版本控制，因此可以跟踪不同版本的容器，注意版本之间的差异。 计算环境可移植性：docker的镜像封装了程序运行所依赖的环境，所以只要安装docker程序，并下载镜像在任何环境都可以运行容器。 标准化：大多数容器基于开放标准，可以运行在所有主要的Linux发行版，微软等。 安全：容器将一个容器的进程与另一个容器以及底层基础架构隔离开来；因此一个容器中的任何升级或更改都不会影响其他容器。
Docker 镜像

Docker镜像简介1、Docker镜像概述 1.1 什么是Docker镜像？ Docker镜像是一种轻量级，可执行的独立软件包，用来打包软件运行环境和基于运行环境开发的程序，它包含运行某个程序所需要所有内容，包括代码、库文件、环境变量和配置文件。 简单来说Docker镜像就好比是一个模板，可以通过这个模板来创建容器服务；一个镜像可以创建多个容器（程序运行在容器中）。1.2 Docker镜像获取方式 从远程仓库下载 朋友拷贝给你 自己制作docker镜像（DockerFile） 2、Docker镜像详解 2.1 UnionFS（联合文件系统） UnionFS（联合文件系统）是一种分层、轻量级并且高性能的文件系统，它支持对文件系统的修改作为一次提交来一层一层的叠加，同时可以将不通目录挂载到同一个虚拟文件系统下。UnionFS（联合文件系统）是Docker镜像的基础，镜像可以通过分层来进行继承，基于基础镜像可以制作各种应用镜像。 UnionFS（联合文件系统）特性：一次同时加载多个文件系统，但从外面看起来，只能看到一个文件系统，联合加载会把各层文件系统叠加起来，这样最终的文件系统会包含所有底层的文件和目录。
Docker 简介

Docker容器简介1、Docker概述1.1 Docker为什么会出现？一款软件产品必须经过：开发 -> 上线开发人员负责将程序开发制作出来。运维人员负责上线程序，修改应用配置。在这里存在一个问题：开发环境和上线环境不一致；可能会导致，开发人员在自己的电脑上可以正常运行代码，运维人员将程序上
分类
Docker docker
MySQL mysql
OpenStack openstack
Linux linux
标签
Docker docker
MySQL mysql
KVM kvm
服务 服务
OpenStack openstack
Linux linux