一、Mysql的主从复制原理及实现

1.为什么需要主从复制？

1. 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

2. 做数据的热备

3. 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

2.什么是mysql的主从复制？

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

3.mysql复制原理

1. master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

3. 同时主节点为每个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.具体步骤

1. 从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）,并且开启主从复制start slave。
2. 从库的IO线程和主库的dump线程建立连接。
3. 从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。
4. 主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。
5. 从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到http://master.info中。
6. 从库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问题；
• 多数据源的管理问题

针对多数据源的管理问题，有两种思路

1. 客户端模式，在每个应用模块内，配置自己需要的数据源，直接访问数据库，在各模块内完成数据的整合；Sharding-JDBC
2. 中间代理模式，中间代理统一管理所有的数据源，数据库层对开发人员完全透明，开发人员无需关注拆分的细节。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       # 负载均衡算法名称