MySQL读写分离原来如此简单

满怀忧思不如先干再说！

前言

??这篇我们来说说基于Mycat实现读写分离，实现的相关技术还有sharding-sphere、TDDL等，技术解决的问题都是一致的，技术之间思想基本相同，所以我们只需要发现系统中可能存在的问题，理解理论上的解决方案和找到一个应用上的落地实现的技术就可以了。话不多说我们以以下几方面介绍：

现存数据库存在问题

解决方案

MyCat使用和基本原理

单库应用

??最简单的软件系统和数据库架构就是这样的只有一个单独的数据库，想想我们的系统刚开发，或者你是大学生在做毕业设计的时候，架构是不就是下图这样的：

问题

??如上图我们的应用程序和MySQL数据库直连，数据库的配置信息是写在我们的应用配置文件中（脑子里要闪现出项目中的数据库配置），数据库只有一个实例来提供服务，之后我们的项目要发布到 络上提供服务，如果 站推广做的好就会有越来越多的用户使用，访问量就会很大，App可以通过nginx负载均衡等，我们的数据库还是单机在卖命？压力就会很大，一旦数据库宕机就GG了逆风翻盘已不存在，客户心里就骂骂咧咧的：垃圾软件卸载，所有的数据都存储在一个数据库中或者一个表中，数据越来越多怎么办？单表数据超过几百万条，几千万条上亿条怎么维护？等等总结几点如下：

应用程序直连数据库，耦合性高

用户并发高，数据库压力大，无论读还是写没有明确划分

数据库单实例运行是肯定不行的

表中数据越来越多怎么维护等等

解决方案

??通过上边问题相信大家可以看出本质，我们可以部署多台MySQL搭建集群，配置读和写操作不同的数据库，数据进行拆分，存储在多张表中，应用程序与数据库降低耦合，添加中间件，总结几点如下

添加数据库中间件降低耦合

读写分离，提高数据库响应速度

数据库分表分库，更好维护数据

上边我们说到数据库会出现什么问题以及解决的思想，就是使用数据库中间件（MyCat）来做读写分离，分表分库等，下边我们就说说如何具体使用！

数据库中间件

什么时候使用中间件

??随着业务的发展，数据库里的表越来越大，一两年以后，2千万、甚至上亿记录的表就会出现了（一般可以简单认为表比较复杂的时候，MySQL几百万上千万的时候，Oracle几千万的时候，就会出现复杂查询或变更有性能问题），这时候可能会导致复杂的查询慢，插入和修改数据慢，修改表的DDL执行太慢导致无法修改列类型或者加索引或者加字段等等。怎么办呢？这时候我们就可以读写分离，分表分库
??如果我们的业务发展到了需要降低单库单表的压力、或者读写分离，而研发团队又不大，自己对这一块的技术积累不足以自己开发一些中间层代码去搞定问题，就需要考虑引入数据中间件了。

常用中间件

Mycat:
?? 区爱好者在阿里cobar基础上进行二次开发，解决了cobar当时存 在的一些问题，并且加入了许多新的功能在其中。目前MyCAT 区活 跃度很高，目前已经有一些公司在使用MyCAT。总体来说支持度比 较高，也会一直维护下去，

OneProxy:
??数据库界大牛，前支付宝数据库团队领导楼总开发，基于mysql官方 的proxy思想利用c进行开发的，OneProxy是一款商业收费的中间件， 楼总舍去了一些功能点，专注在性能和稳定性上。有朋友测试过说在 高并发下很稳定。

Vitess:
??这个中间件是Youtube生产在使用的，但是架构很复杂。 与以往中间件不同，使用Vitess应用改动比较大要 使用他提供语言的API接口，我们可以借鉴他其中的一些设计思想。

Kingshard:
??Kingshard是前360Atlas中间件开发团队的陈菲利用业务时间 用go语言开发的，目前参与开发的人员有3个左右， 目前来看还不是成熟可以使用的产品，需要在不断完善。

Atlas:
??360团队基于mysql proxy 把lua用C改写。原有版本是支持分表， 目前已经放出了分库分表版本。在 上看到一些朋友经常说在高并 发下会经常挂掉，如果大家要使用需要提前做好测试。

MySQL Route
??MySQL官方Oracle公司发布的中间件

国内使用较多的是Mycat，免费，简单，功能强大， 区完善，后两者虽是官方提供的但是拿到国内就有点水土不服，毕竟国内 站的吞吐量是非常大的。现在也有选择使用 sharding-sphere 的

Mycat安装

??下载上传Linux解压安装在/usr/local目录下即可，版本为1.6，下图就是mycat目录结构，几乎所有软件都是这些目录就不一个一个说了！

mycat主要配置文件

schema.xml：定义逻辑库，表、分片节点等内容

rule.xml：定义分片规则

server.xml：定义用户以及系统相关变量，如端口等

一主一从读写分离

??单个数据库宕机会导致数据获取不到，数据库一般都是多个，而且我们项目中一般都是读得多写得少，比如我们使用淘宝看商品要比购买的点击次数多得多，我们为了提升项目并发量，数据库会实现读写分离，就好比火车站的入口和出口分开一样。
??我们先来个一主一从配置，主库负责写入数据，从库负责读取数据，从库和主库需要数据同步，可以使用MySQL(5.5版本之后)自带的主从复制实现，先搭建MySQL主从复制，再配置一主一从实现读写分离，一个主机负责处理所有的写请求，一台从机负责所有的读请求，架构图如下

Mysql主从复制原理

??主数据库会将所有的写操作写入到二进制文件（binlog）中，从数据库读取binlog日志再将读取到的binlog写入到中继日志（Relaylog）中，再通过SQL线程执行语句，实现主从复制数据同步，与Redis一样主数据库只能有一台，从数据库可以有多台，不同之处在于Redis是全量复制，而MySQL是从确认主从关系后开始复制，之前的数据是不同步的，读取二进制文件和写入中继日志都会发生IO所以会存在延时

# Server IDserver-id =1# 启动二进制日志log-bin = mysql-bin# Mysql有一些系统库不需要复制，可以配置多个binlog-ignore-db = mysql binlog-ignore-db = sysbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schema# 设置需要复制的数据库，这里设置了上边不复制项就可以不设置binlog-do-db = mycatdb# 设置logbin日志格式binlog_format = STATEMENT

从数据库配置

# Server IDserver-id =2# 启用中继日志relay-log = mysql-relay

重启Mysql查看状态，并且关闭防火墙

# 重启systemctl restart mysqld # 查看状态systemctl status mysqld# 关闭防火墙systemctl stop firewalld# 开机禁用systemctl disable firewalld# 查看防火墙状态systemctl status firewalld

创建可以进行复制的从节点用户slave

# 创建用户名为slave，密码为123456grant replication slave on *.* to 'slave'@'%' identified by '123456';# 刷新flush privileges;# 查看master状态show master status;

记录下File和Position的值，到这里就不要操作主Mysql，防止服务器状态值变化，因为mysql主从复制是从主数据库当前状态开始复制

配置主从关系

change master to master_host='主IP',master_user='上方我们创建的用户',master_password='上方密码',master_log_file='File',master_log_pos=Position;# 例如change master to master_host='192.168.11.201',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=582;

如果之前环境搭建过主从复制会 错，运行stop slave;和restart master;

启动从服务器复制功能

# 启动从服务start slave;# 查看从服务器状态show slave statusG;

哎呦？出错了？

强调：Slave_IO_Running和Slave_SQL_Running都要为YES才行！！！

不慌

??程序员基本素养看日志，问题提示主从使用了相同的server UUID，这个UUID是在auto.cnf文件中，也就是你安装mysql时在my.cnf文件中配置的datadir路径下，输出之后发现确实是一样的

[root@stt201 data]# cat auto.cnf [auto]server-uuid=dbbcb5a4-7331-11ea-b078-000c29382f46[root@stt202 data]# cat auto.cnf [auto]server-uuid=dbbcb5a4-7331-11ea-b078-000c29382f46

盘它

??停止从库的mysqld服务，删除auto.cnf文件，再启动数据库服务即可：

systemctl stop mysqld #停止服务rm -rf auto.cnf #删除文件systemctl start mysqld #启动服务systemctl status mysqld #查看状态

一套创建库创建表插入数据连招安排

-- 创建、添加数据都是写，你说在哪弄？-- 创建库CREATE DATABASE mycatdb;-- 创建表CREATE TABLE IF NOT EXISTS mycatdb.tb_mycat(	id INT NOT NULL,    name VARCHAR(20) NOT NULL,    PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 插入数据INSERT INTO mycatdb.tb_mycat(id,name) values(1,"添添"),(2,"小添"),(3,"晓添");-- 主库和从库查询数据若都可以正常显示，说明木得问题SELECT id,name FROM mycatdb.tb_mycat;

Mycat配置读写分离

分为以下两个步骤：

修改server.xml文件

修改schema.xml文件

修改server.xml文件

修改schema.xml文件

??删除<schema>标签间表信息，<datanode>标签只留一个，<dataHost>标签只留一个，<writeHost>，<readHost>只留一对

强调：schema中的name属性值是我们mycat记录的逻辑库的名字，区分大小写，dataNode标签的database属性值对应的是真实的物理数据库名字，大家一定要记住并区分开，我们通过操作Mycat的逻辑库，Mycat帮我们去操作真正的物理数据库，啊呦欧克~~~

dataHost标签中的balance属性配置读写分离类型，默认为0，实际生产过程中设置为1（主双从）或者3（单主单从）

0：不开启读写分离，所有读操作都发送到当前可用的writeHost上

1：全部的readHost与stand by writeHost参与SELECT 语句的负载均衡

2：所有的读操作随机在writeHost和readHost上分发

3：所有读请求随机分发到readHost上执行，writeHost不负担读压力

启动Mycat吧

前提

安装JDK，并且配置环境变量，Mycat基于Java编写，需要JVM

在mysql中创建mycat用户密码为123456(和我们server.xml文件中一致)

mysql启动远程访问

-- 表示任意ip后可通过root用户密码123456访问GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;-- 刷新flush privileges;

sql都准备好，就是懂你~~~

启动方式

控制台启动：到mycat/bin目录下运行 ./mycat console；推荐使用，可以第一时间看到日志方便定位问题

后台启动：到mycat/bin目录下运行 ./mycat start；

如果看到图上红框内容说明mycat启动成功

Mycat登陆

登录后台管理窗口

用于管理维护Mycat

mysql -umycat -p123456 -P 9066 -h 192.168.11.201

登陆数据窗口

用于通过Mycat查询数据，我们开发是使用8066端口搞事情

mysql -umycat -p123456 -P 8066 -h 192.168.11.201

查看数据

-- 登陆mycatmysql -umycat -p123456 -P 8066 -h 192.168.11.201-- 查看数据库，看下图，查出来的是我们的逻辑库，区分大小写哦show databases;-- 使用逻辑库use MYCATDB;-- 查看库下所有表show tables;-- 查看数据SELECT * FROM tb_mycat;

验证读写分离

-- 进入主mysql，添加一条数据，@@hostname为当前主机的名字-- 主数据库和从数据库的主机名不同，所以数据也不一样INSERT INTO mycatdb.tb_mycat(id,name) values (4,@@hostname);-- 我们登陆mycat查看数据，如果是从数据库的数据那么就证明成功SELECT * FROM MYCATDB.tb_mycat;

呀呀呀！咱们到这里就实现MySQL的主从复制，使用Mycat实现Mysql单主单从的读写分离，心里还真有点小激动呢！到这里大家跟着做出来，喝杯茶，下方留个言啥的岂不美哉！

双主双从读写分离

介绍

??上边我们介绍了一主一从实现读写分离，但是如果主库或者从库宕机就无法正常提供写和读的服务。我们使用双主双从，master1和master2为主服务器，都有对应的slave，当Master1宕机时Master2负责写请求，M1和M2互为备机，提高MySQL可用性，架构图如下

集群规划

编	角色	IP地址	主机名
1	Master1	192.168.11.201	stt201
2	Slave1	192.168.11.202	stt202
3	Master2	192.168.11.203	stt203
4	Slave2	192.168.11.204	stt204

双主双从配置

Master1配置my.cnf文件

# Server IDserver-id =1# 启动二进制日志log-bin = mysql-bin# Mysql有一些系统库不需要复制，可以配置多个binlog-ignore-db = mysqlbinlog-ignore-db = sysbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schema# 设置需要复制的数据库，这里设置了上边不复制项就可以不设置binlog-do-db = mycatdb# 设置logbin日志格式binlog_format = STATEMENT#步进值auto_imcrement。一般有n台主MySQL就填nauto_increment_increment=2 #起始值。一般填第n台主MySQL。此时为第一台主MySQLauto_increment_offset=1# 在作为从数据库时，有写入操作也要更新二进制文件log-slave-updates = 1

Master2配置

# Server IDserver-id =3# 启动二进制日志log-bin = mysql-bin# Mysql有一些系统库不需要复制，可以配置多个binlog-ignore-db = mysqlbinlog-ignore-db = sysbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schema# 设置需要复制的数据库，这里设置了上边不复制项就可以不设置binlog-do-db = mycatdb# 设置binlog日志格式binlog_format = STATEMENT#步进值auto_imcrement。一般有n台主MySQL就填nauto_increment_increment=2 #起始值。一般填第n台主MySQL。此时为第2台主MySQLauto_increment_offset=2log-slave-updates = 1

Slave1配置

# Server IDserver-id =2# 启用中继日志relay-log = mysql-relay

Slave2配置

# Server IDserver-id =4# 启用中继日志relay-log = mysql-relay

启动

前提

先删除mycatdb库(为什么呢？)

drop database mycatdb;

主从都重启mysql服务

systemctl restart mysqldsystemctl status mysqld

在两台主服务器上创建并授权slave用户，我们上边创建过的

-- 查看是否有用户存在

                                                        
声明：本站部分文章及图片源自用户投稿，如本站任何资料有侵权请您尽早请联系jinwei@zod.com.cn进行处理,非常感谢！