MySQL 之 读写分离

kk起个名吧
1,246 阅读10分钟

一、 读写分离介绍

  1. 什么是读写分离？

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

  1. 为什么要读写分离？

因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。 但是数据库的“读”（读10000条数据可能只要5秒钟）。 所以读写分离，解决的是，数据库的写入，影响了查询的效率。

  1. 什么时候需要读写分离？

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

二、 MySQL读写分离的方式有哪些

1）基于程序代码内部实现

在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。 优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。 但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。

  • MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断。由于使用MySQL Proxy 需要写大量的Lua脚本，这些Lua并不是现成的，而是需要自己去写。这对于并不熟悉MySQL Proxy 内置变量和MySQL Protocol 的人来说是非常困难的。

  • Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。

  • Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。

  • Mycat。是一款流行的基于Java语言编写的数据库中间件，是一个实现了MySql协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。

三、 MySQL 中间件代理服务器

数据库主要分为两大类：关系型数据库与 NoSQL 数据库。

  • 关系型数据库，是建立在关系模型基础上的数据库，其借助于集合代数等数学概念和方法来处理数据库中的数据。主流的 MySQL、Oracle、MS SQL Server 和 DB2 都属于这类传统数据库。
  • NoSQL 数据库，全称为 Not Only SQL，意思就是适用关系型数据库的时候就使用关系型数据库，不适用的时候也没有必要非使用关系型数据库不可，可以考虑使用更加合适的数据存储。主要分为临时性键值存储（Redis、memcached）、永久性键值存储（ROMA、Redis）、面向文档的数据库（MongoDB、CouchDB）、面向列的数据库（Cassandra、HBase），每种 NoSQL 都有其特有的使用场景及优点。

Oracle，mysql 等传统的关系数据库非常成熟并且已大规模商用，为什么还要用 NoSQL 数据库呢？ 主要是由于随着互联网发展，数据量越来越大，对性能要求越来越高，传统数据库存在着先天性的缺陷，即单机（单库）性能瓶颈，并且扩展困难。这样既有单机单库瓶颈，却又扩展困难，自然无法满足日益增长的海量数据存储及其性能要求，所以才会出现了各种不同的 NoSQL 产品，NoSQL 根本性的优势在于在云计算时代，简单、易于大规模分布式扩展，并且读写性能非常高

关系型数据库NoSQL 数据库
特点①数据关系模型基于关系模型，结构化存储，完整性约束②基于二维表及其之间的联系，需要连接、并、交、差、除等数据操作③采用结构化的查询语言(SQL)做数据读写④操作需要数据的一致性，需要事务甚至是强一致性①非结构化的存储②基于多维关系模型③具有特有的使用场景
优点①保持数据的一致性(事务处理)②可以进行 join 等复杂查询③通用化，技术成熟①高并发，大数据下读写能力较强②基本支持分布式，易于扩展，可伸缩③简单，弱结构化存储
缺点①数据读写必须经过 sql 解析，大量数据、高并发下读写性能不足②对数据做读写，或修改数据结构时需要加锁，影响并发操作③无法适应非结构化存储④扩展困难⑤昂贵、复杂①join 等复杂操作能力较弱②事务支持较弱③通用性差④无完整约束复杂业务场景支持较差

四、 Mycat

1. Mycat介绍

  • 一个彻底开源的，面向企业应用开发的大数据库集群
  • 支持事务、ACID、可以替代MySQL的加强版数据库
  • 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
  • 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
  • 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
  • 一个新颖的数据库中间件产品

2. Mycat应用场景

Mycat适用的场景很丰富，以下是几个典型的应用场景：

  • 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换
  • 分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片
  • 多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化报表系统，借助于Mycat的分表能力，处理大规模报表的统计
  • 替代Hbase，分析大数据，作为海量数据实时查询的一种简单有效方案，比如100亿条频繁查询的记录需要在3秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时Mycat可能是最简单有效的选择

3. Mycat 实现读写分离

mycat实验图.png
  • 7-5 客户机
  • 7-1 Mycat （不安装mysql）
  • 7-6 master 主服务器
  • 7-7 slave 从服务器
  1. 环境准备，关闭防火墙
systemctl disable --now firewalld
setenforce 0
  1. 部署主从复制
  • 7-6 主mysql服务器配置（192.168.204.60）
[root@node6 ~]#  mkdir -p /data/mysql
[root@node6 ~]#  chown -R mysql.mysql /data/
[root@node6 ~]#  vim /etc/my.cnf
  server_id=60
  log-bin=/data/mysql/mysql-bin
[root@node6 ~]#  systemctl restart mysqld

[root@node6 ~]#  mysql -uroot -pabc123
mysql> show master status;
mysql> grant replication slave on *.* to test@'192.168.204.%' identified by 'Admin@123';

[root@node6 ~]#  mysql -uroot -pabc123 < hellodb_innodb.sql
//主从配置完成后，在master服务器上上传库文件，并在从服务器上验证主从同步
mycat1.png
  • 7-7 从mysql服务器配置（192.168.204.70）
[root@node7 ~]#  mkdir -p /data/mysql
[root@node7 ~]#  chown -R mysql.mysql /data/
[root@node7 ~]#  vim /etc/my.cnf
  server_id=70
  log-bin=/data/mysql/mysql-bin
[root@node7 ~]#  systemctl restart mysqld

[root@node7 ~]#  mysql -uroot -pabc123
CHANGE MASTER TO
  MASTER_HOST='192.168.204.60',
  MASTER_USER='test',
  MASTER_PASSWORD='Admin@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
  
mysql> start slave;
mysql> show slave status\G;
mycat2.png
  1. 7-1 安装mycat （192.168.204.10）

注意：mycat服务器上不能装mysql

[root@node1 ~]#  cd /data         //上传mycat二进制包
[root@node1 data]#  mkdir /apps   //创建/apps文件夹
[root@node1 data]#  tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/   //解压mycat包至/apps下
[root@node1 data]#  cd /apps
[root@node1 apps]#  echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh  //设置变量环境
[root@node1 apps]#  . /etc/profile.d/mycat.sh
[root@node1 apps]#  mycat start   //启动mycat
[root@node1 apps]#  mycat status  //查看状态
[root@node1 apps]#  tail -f /apps/mycat/logs/wrapper.log  //查看日志文件，启动成功日志末尾会出现successfully
mycat3-1.png mycat3-2.png
  1. 客户端7-5测试连接数据库7-1
[root@node5 ~]#  mysql -uroot -p123456 -h192.168.204.10 -P8066
mycat4.png
  1. 7-1 修改 mycat 配置文件 mycat5-1.png
  • /apps/mycat/conf/server.xml
[root@node1 apps]#  cd mycat/
[root@node1 mycat]#  ls
bin  catlet  conf  lib  logs  tmlogs  version.txt
[root@node1 mycat]#  cd conf/
[root@node1 conf]#  cp server.xml server.xml.bak  //先备份！！！
[root@node1 conf]#  vim server.xml
<property name="serverPort">3306</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
//去掉44行行注释，以及对应的行末注释
//修改45行端口号为3306
//删除46行行末注释：5 * 60 * 1000L; //连接空>    闲检查

[root@node1 conf]#  mycat restart  //重新加载mycat
[root@node1 conf]#  mycat status   //查看状态

mycat5-2.png

  • /apps/mycat/conf/schema.xml

schema.xml是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的。

[root@node1 conf]#  cp schema.xml schema.xml.bak
[root@node1 conf]#  vim schema.xml   //删除所有内容，重新写入以下内容

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="host1" url="192.168.204.60:3306" user="root" password="Admin@123">
                 <readHost host="host2" url="192.168.204.70:3306" user="root" password="Admin@123"/>
                </writeHost>
        </dataHost>
</mycat:schema>

[root@node1 conf]#  mycat restart
[root@node1 conf]#  mycat status

mycat5-3.png

  1. 7-6 主服务器上授权，并在从服务器查看是否同步
[root@node6 ~]#  mysql -uroot -pabc123
mysql> GRANT ALL ON *.* TO 'root'@'192.168.204.%' IDENTIFIED BY 'Admin@123';

[root@node7 ~]#  mysql -uroot -pabc123
mysql> select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| root          | 192.168.204.% |
| test          | 192.168.204.% |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+

mycat6-1.pngmycat6-2.png

  1. 客户机连接mycat并测试读写分离
//主从服务器上都打开通用日志
mysql> set global general_log=1;

//在主从服务器上实时查看通用日志
[root@node6 mysql]#  tail -f /var/lib/mysql/node6.log
[root@node7 mysql]#  tail -f /var/lib/mysql/node7.log

//客户机连接mycat
[root@node5 ~]#  mysql -uroot -p123456 -h 192.168.204.10
在客户机上select查表，并查看主从服务器实时日志，可以看到只有`从服务器`上有日志变化显示
mysql> select * from students;

在客户机上插入数据，并查看主从服务器实时日志，可以看到只有`主服务器`上有日志变化显示，成功实现读写分离
mysql> insert students values(null,'xue',25,'M',1,3);
mycat7-2.png mycat7-1.png
avatar
文章
阅读
粉丝