一、Mysql的主从复制原理及实现
1.为什么需要主从复制?
-
在业务复杂的系统中,有这么一个情景,有一句sql语句需要锁表,导致暂时不能使用读的服务,那么就很影响运行中的业务,使用主从复制,让主库负责写,从库负责读,这样,即使主库出现了锁表的情景,通过读从库也可以保证业务的正常运作。
-
做数据的热备
-
架构的扩展。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。
2.什么是mysql的主从复制?
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。
3.mysql复制原理
-
master服务器将数据的改变记录二进制binlog日志,当master上的数据发生改变时,则将其改变写入二进制日志中;
-
slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变,则开始一个I/OThread请求master二进制事件
-
同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至从节点本地的中继日志中,从节点将启动SQL线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致,最后I/OThread和SQLThread将进入睡眠状态,等待下一次被唤醒。
也就是说:
- 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
- 从库会生成两个线程,一个I/O线程,一个SQL线程;
- I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
- SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;
注意点:
- master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)
- slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和master数据保持一致了。
- Mysql复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。
- Mysql复制最好确保master和slave服务器上的Mysql版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本)
- master和slave两节点间时间需同步
4.具体步骤
- 从库通过手工执行change master to 语句连接主库,提供了连接的用户一切条件(user 、password、port、ip),并且让从库知道,二进制日志的起点位置(file名 position 号),并且开启主从复制
start slave
。 - 从库的IO线程和主库的dump线程建立连接。
- 从库根据change master to 语句提供的file名和position号,IO线程向主库发起binlog的请求。
- 主库dump线程根据从库的请求,将本地binlog以events的方式发给从库IO线程。
- 从库IO线程接收binlog events,并存放到本地relay-log中,传送过来的信息,会记录到http://master.info中。
- 从库SQL线程应用relay-log,并且把应用过的记录到relay-log.info中,默认情况下,已经应用过的relay会自动被清理。
二、基于Docker搭建Mysql主从环境
1.使用docker pull mysql
命令下载镜像
2.分别启动Master和Slave节点容器
docker run -p 3306:3306 --name mysql-master -v /mydata/mysql/mysql-master/logs:/logs -v /mydata/mysql/mysql-master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
docker run -p 3307:3306 --name mysql-slave -v /mydata/mysql/mysql-slave/logs:/logs -v /mydata/mysql/mysql-slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
3.分别进入Master和Slave的Mysql容器,并登录Mysql,开启远程访问权限
docker exec -it mysql-master /bin/bash #slave容器需换成docker exec -it mysql-slave /bin/bash mysql-slave
mysql -uroot -p
use mysql;
select host,user from user;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
4 master容器修改my.cnf文件
-
将my.cof文件复制到本地
docker cp mysql-master:/etc/mysql/my.cnf /mydata/mysql/mysql-master/my.cnf
-
编辑文件,增加如下配置
server-id = 1 log-bin = mysql-bin
-
修改完成后在上传到容器中
docker cp /mydata/mysql/mysql-master/my.cnf mysql-master:/etc/mysql/my.cnf
5.slave容器修改my.cnf文件
-
docker cp mysql-slave:/etc/mysql/my.cnf /mydata/mysql/mysql-slave/my.cnf
-
server-id = 2 # 注意:server-id必须保证和master中的不同 log-bin = mysql-bin
-
docker cp /mydata/mysql/mysql-slave/my.cnf mysql-slave:/etc/mysql/my.cnf
6.重启master容器和slave容器
docker restart mysql-master mysql-slave
7.进入master容器,并登录mysql,使用show master status;
命令查看状态
8.配置和改变slave服务器用于连接master服务器的参数
-
重新进入slave容器
docker exec -it mysql-slave bash mysql -uroot -p
-
执行命令
change master to MASTER_HOST='47.94.93.93', MASTER_USER='root', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=46817;
上述参数分别表示需要同步的主机IP,用户名,密码,binlog文件,binlog位置等信息,binlog文件和位置即上一步查到的信息。
-
开启主从复制
start slave;
-
查看主从状态
show slave status\G;
-
Slave_IO_Running Slave_SQL_Running 都为Yes表示主从复制搭建成功。
-
如果两个参数中有一个No,一个Yes,就需要先stop slave;并重新执行第七步和第八步
三、数据切分
数据切分,简单的说,就是通过某种条件,将我们之前存储在一台数据库上的数据,分散到多台数据库中,从而达到降低单台数据库负载的效果。数据切分,根据其切分的规则,大致分为两种类型,垂直切分和水平切分。
1.垂直切分
垂直切分就是按照不同的表或者Schema切分到不同的数据库中,例如,传统电商项目中,订单表和商品表在同一个数据库中,而我们现在要对其切分,使得订单表和商品表分别落到不同的物理机中的不同的数据库中,使其完全隔离,从而达到降低数据库负载的效果。
垂直切分的特点就是规则简单,易于实施,可以根据业务模块进行划分,各个业务之间耦合性低,相互影响也较小。
优点:
- 拆分后业务清晰,拆分规则明确;
- 系统之间容易扩展和整合;
- 数据维护简单
缺点:
- 部分业务表无法join,只能通过接口调用,提升了系统的复杂度;
- 跨库事务难以处理;
- 垂直切分后,某些业务数据过于庞大,仍然存在单体性能瓶颈;
2.水平切分
水平切分相比垂直切分,更为复杂。它需要将一个表中的数据,根据某种规则拆分到不同的数据库中,例如:订单尾号为奇数的订单放在了订单数据库1中,而订单尾号为偶数的订单放在了订单数据库2中。这样,原本存在一个数据库中的订单数据,被水平的切分成了两个数据库。在查询订单数据时,我们还要根据订单的尾号,判断这个订单在数据库1中,还是在数据库2中,然后将这条SQL语句发送到正确的数据库中,查出订单。
优点:
- 解决了单库大数据、高并发的性能瓶颈;
- 拆分规则封装好,对应用端几乎透明,开发人员无需关心拆分细节;
- 提高了系统的稳定性和负载能力;
缺点:
- 拆分规则很难抽象
- 分片事务一致性难以解决
- 二次扩展时,数据迁移、维护难度大。比如:开始我们按照用户id对2求模,但是随着业务的增长,2台数据库难以支撑,还是继续拆分成4个数据库,那么这时就需要做数据迁移了。
3.总结
世界上的万物没有完美的,有利就有弊,就像数据切分一样。无论是垂直切分,还是水平切分,它们解决了海量数据的存储和访问性能问题,但也随之而来的带来了很多新问题,它们的共同缺点有:
- 分布式的事务问题;
- 跨库join问题;
- 多数据源的管理问题
针对多数据源的管理问题,有两种思路
- 客户端模式,在每个应用模块内,配置自己需要的数据源,直接访问数据库,在各模块内完成数据的整合;Sharding-JDBC
- 中间代理模式,中间代理统一管理所有的数据源,数据库层对开发人员完全透明,开发人员无需关注拆分的细节。Mycat
四、基于Docker搭建Mycat
1.几个概念
全局表:一个真实的业务系统中,往往存在变动不频繁,数据量总体变化不大的类似字典表的表对于这类的表,在分片的情况下,当业务表因为规模而进行分片以后,业务表与这些附属的字典表之间的关联,就成了比较棘手的问题,所以 Mycat 中通过数据冗余来解决这类表的 join,即所有的分片都有一份数据的拷贝,所有将字典表或者符合字典表特性的一些表定义为全局表。
子表:例如有两张表分别为订单表和订单明细表,订单表会有订单的id,金额、状态、收货人姓名、收货人手机号、收货人地址、创建时间等等,都是和订单主体相关的。订单明细表里面通常都会有订单的id,购买商品的id、商品的名称、商品购买人的金额,存放类似这样的信息。这时候最好将含有同一个订单id的数据放到同一个数据库中,防止跨库join。在mycat中将订单明细表作为订单表的子表,就可以避免这个问题。
2. 下载mycat.tar.gz,并在同包下创建Dockerfile文件
wget http://dl.mycat.io/1.6.6.1/Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz
mv Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz mycat.tar.gz
Dockerfile文件如下:
FROM openjdk:8
ADD mycat.tar.gz /usr/local/
VOLUME /usr/local/mycat/conf
ENV MYCAT_HOME=/usr/local/mycat
EXPOSE 8066 9066
CMD ["/usr/local/mycat/bin/mycat", "console","&"]
3.在该目录下打包生成镜像
docker build -t mycat-1.6 .
4.启动mycat容器
docker run --name mycat -p 8066:8066 -p 9066:9066 -v /mydata/mycat/logs/:/usr/local/mycat/logs/ -d mycat-1.6
5. 将mycat容器中的conf文件夹复制到宿主机上,方便修改配置
docker cp mycat:/usr/local/mycat/conf /mydata/mycat
6.修改 server.xml文件,配置用户,密码和schemas
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">0</property>
<property name="useGlobleTableCheck">0</property>
<property name="sequnceHandlerType">2</property>
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">1</property>
<property name="memoryPageSize">1m</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
<property name="useZKSwitch">true</property>
</system>
<user name="root">
<property name="password">123456</property>
<property name="schemas">mycat</property> <!--此处需和schemas.xml中保持一致-->
</user>
</mycat:server>
7.修改schemas.xml文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mycat" checkSQLschema="false" sqlMaxLimit="100">
<table name="user" dataNode="node3306,node3308" rule="auto-sharding-long" />
</schema>
<dataNode name="node3306" dataHost="host3306" database="testdb" />
<dataNode name="node3308" dataHost="host3308" database="testdb" />
<dataHost name="host3306" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="47.94.93.93:3306" user="root" password="123456">
<readHost host="hostS2" url="47.94.93.93:3307" user="root" password="123456" />
</writeHost>
</dataHost>
<dataHost name="host3308" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="47.94.93.93:3308" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
具体标签和配置信息请查看Mycat配置文件Schema.xml详解
8.修改rule.xml文件,更改分片规则
在schemas.xml文件中Table 标签中的rule属性值即分片规则,上面使用的是auto-sharding-long。接下来列举一下Mycat常用的分片规则,具体配置在rule.xml文件中。
1.分片枚举
通过在配置文件中配置可能的枚举 id,自己配置分片,本规则适用于特定的场景,比如有些业务需要按照省份或区县来做保存,而全国省份区县固定的,这类业务使用本条规则,配置如下:
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
10000=0
10010=1
2.范围约定
此分片适用于,提前规划好分片字段某个范围属于哪个分片,
start <= range <= end.
range start-end ,data node index
K=1000,M=10000
<tableRule name="auto-sharding-long">
<rule>
<columns>user_id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>
0-10000000=0
10000001-20000000=1
或者
0-500M=0
500M-1000M=1
1000M-1500M=2
3.取模
此规则为对分片字段求摸运算。columns 标识将要分片的表字段,algorithm 分片函数。根据 id 进行十进制求模晕算,相比固定分片 hash,此种在批量插入时可能存在批量插入单事务插入多数据分片,增大事务一致性难度。
<tableRule name="mod-long">
<rule>
<columns>user_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property>
</function>
- 上面 columns 标识将要分片的表字段,algorithm 分片函数,
此种配置非常明确即根据 id 进行十进制求模预算,相比固定分片 hash,此种在批量插入时可能存在批量插入单
事务插入多数据分片,增大事务一致性难度。
其他分片规则请上官网进行查看运用。
9.使用docker restart mycat
命令重启Mycat
10.在springboot中使用mycat
spring:
datasource:
url: jdbc:mysql://47.94.93.93:8066/mycat
driver-class-name: com.mysql.jdbc.Driver
username: root
password: 123456
五、基于Sharding-JDBC的读写分离、分库分表
1.定义
Sharding-JDBC是一个开源的分布式的关系型数据库的中间件,客户端代理模式,在应用程序中进行配置,就可以完成数据库的读写分离,分库分表。定位为轻量级的java框架,以jar包提供服务,可以理解为增强版的jdbc驱动。Sharding-JDBC的4种配置方式,分别为Java Api、Yaml、SpringBoot、Spring命名空间。
2.与Mycat的区别
- Mycat是服务端代理 Sharding-Jdbc是客户端代理
- Mycat不支持同一库内的水平切分、Sharding-Jdbc支持
3.数据分片定义
-
逻辑表
水平拆分的数据库(表)的相同逻辑和数据结构表的总称。例:订单数据根据主键尾数拆分为 10 张表,分别是 t_order_0 到 t_order_9,他们的逻辑表名为 t_order。 -
真实表
在分片的数据库中真实存在的物理表。即上个示例中的 t_order_0 到 t_order_9。 -
数据节点
数据分片的最小单元。由数据源名称和数据表组成,例:ds_0.t_order_0。 -
绑定表
指分片规则一致的主表和子表。例如:t_order 表和 t_order_item 表,均按照 order_id 分片,则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联,关联查询效率将大大提升。 -
广播表
指所有的分片数据源中都存在的表,表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景,例如:字典表。 -
单表
指所有的分片数据源中只存在唯一一张的表。适用于数据量不大且不需要做任何分片操作的场景。
4.在SpringBoot项目中进行分库分表
1.数据库建表
2.添加maven依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0-beta</version>
</dependency>
3.SpringBoot对于分库分表的配置
#配置数据源
spring.shardingsphere.datasource.names=ds-0,ds-1
#ds-0对应的真实数据库
spring.shardingsphere.datasource.ds-0.jdbc-url=jdbc:mysql://47.94.93.93:3306/sharding_order?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds-0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=123456
spring.shardingsphere.datasource.ds-0.max-active=16
#ds-1对应的真实数据库
spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:mysql://47.94.93.93:3308/shard_order?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=123456
spring.shardingsphere.datasource.ds-1.max-active=16
# 配置分库策略
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=database-inline
# 绑定表
spring.shardingsphere.rules.sharding.binding-tables=t_order,t_order_item
# 广播表
spring.shardingsphere.rules.sharding.broadcast-tables=area
# 配置分片算法
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=ds-$->{user_id % 2}
# 配置分表策略
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ds-$->{0..1}.t_order_$->{1..2}
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=t-order-inline
## 主键生成策略
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=uuid
# 配置分片算法
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.props.algorithm-expression=t_order_$->{user_id % 2+1}
spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=ds-$->{0..1}.t_order_item_$->{1..2}
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=t-order-item-inline
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.key-generator-name=uuid
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-item-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-item-inline.props.algorithm-expression=t_order_item_$->{user_id % 2+1}
# sharding-jdbc提供了两种主键生成策略UUID、SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=123
spring.shardingsphere.rules.sharding.key-generators.uuid.type=UUID
spring.shardingsphere.rules.sharding.key-generators.uuid.props.worker-id=124
4.SpringBoot对于读写分离的配置
spring.shardingsphere.datasource.names=write-ds-0,ds-1,write-ds-0-read-0
spring.shardingsphere.datasource.write-ds-0.jdbc-url=jdbc:mysql://47.94.93.93:3306/sharding_order?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.write-ds-0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.write-ds-0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.write-ds-0.username=root
spring.shardingsphere.datasource.write-ds-0.password=123456
spring.shardingsphere.datasource.write-ds-0-read-0.jdbc-url=jdbc:mysql://47.94.93.93:3307/sharding_order?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.write-ds-0-read-0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.write-ds-0-read-0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.write-ds-0-read-0.username=root
spring.shardingsphere.datasource.write-ds-0-read-0.password=123456
spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:mysql://47.94.93.93:3308/shard_order?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=123456
## 负载均衡算法配置 (select时,对所有读数据源进行轮询读取)
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN # 负载均衡算法类型
spring.shardingsphere.rules.readwrite-splitting.data-sources.ds-0.write-data-source-name=write-ds-0 # 写数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.ds-0.read-data-source-names=write-ds-0-read-0 # 读数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.ds-0.load-balancer-name=round_robin # 负载均衡算法名称