MySQL高可用
日志
错误日志
错误日志记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
该日志是默认开启的，默认存放目录 /var/log/
1 | show variables like '%log_error%'; |
二进制日志
介绍
二进制日志（binlog）记录了所有的 DDL（数据定义语言）语句和 DML（数据操作语言）语句，但不包括数据查询（SELECT、SHOW）语句。
作用：①. 灾难时的数据恢复；②. MySQL的主从复制。
1 | show variables like '%log_bin%'; |
1 | 参数说明： |
格式
MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：
| 日志格式 | 含义 |
|---|---|
| STATEMENT | 基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。 |
| ROW | 基于行的日志记录，记录的是每一行的数据变更。（默认） |
| MIXED | 混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。 |
1 | show variables like '%binlog_format%'; |
查看
由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看.
1 | 语法： |
删除
对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：
手动清理
1
2
3
4
5
6
7
8-- 删除全部 binlog 日志，删除之后，日志编号，将从 binlog.000001重新开始
reset master;
-- 删除 * 编号之前的所有日志
purge master logs to 'binlog.*';
-- 删除日志为 "yyyy-mm-dd hh24:mi:ss" 之前产生的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss';自动清理
也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。
1
show variables like '%binlog_expire%';
查询日志
查询日志中记录了客户端的所有操作语句。默认情况下，查询日志是关闭的。
1 | show variables like '%general%'; |
开启了查询日志之后，在MySQL的数据存放目录 /var/lib/mysql/ 下就会出现QUERY_LOG文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中，长时间运行后，该日志文件将会非常大。
慢查询日志
慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL语句的日志，默认关闭的。long_query_time 默认为10 秒，最小为 0， 精度可以到微秒。
1 | show variables like '%slow_query%'; |
默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。
1 | -- 是否记录执行较慢的管理语句 |
主从复制
主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。
MySQL 复制的优点主要包含以下三个方面：
- 主库出现问题，可以快速切换到从库提供服务。
- 实现读写分离，降低主库的访问压力。
- 可以在从库中执行备份，以避免备份期间影响主库服务。
原理
MySQL主从复制的核心就是 **二进制日志(binlog)**，具体的过程如下：
主从复制分成三步：
- Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
- 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
- slave重做中继日志中的事件，将改变反映它自己的数据。
搭建
准备服务器
准备主从服务器
在两台服务器中分别安装好MySQL，并完成基础的初始化准备(安装、密码配置等操作)工作。 其中：
- 192.168.253.134 作为主服务器master
- 192.168.253.135 作为从服务器slave
开启防火墙端口
1
2
3sudo ufw allow 3306
或关闭
sudo ufw disable
检测mysql安装状态
主库配置
修改配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf1
2
3
4
5
6
7
8
9
10
11# mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，默认为1
= 1000
# 开启binlog,指定日志文件目录及名称
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
#是否只读,1 代表只读, 0 代表读写
= 0
# 指定同步的数据库
binlog_do_db = ni9nes_DB
# 忽略的数据库, 指不需要同步的数据库
#binlog_ignore_db = include_database_name登录mysql，创建远程连接的账号，并授予主从复制权限
1
2
3
4
5-- 创建用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
-- 为 'replica'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;重启MySQL服务器
1
sudo service mysql restart
通过指令，查看二进制日志坐标
1
show master status;
1
2
3
4
5字段含义说明：
file : 从哪个日志文件开始推送日志文件
position ： 从哪个位置开始推送日志
binlog_do_db : 指定需要同步的数据库
binlog_ignore_db : 指定不需要同步的数据库
从库配置
修改配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf1
2
3
4# mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可
= 1001
# 是否只读,1 代表只读, 0 代表读写
= 1重新启动MySQL服务
1
sudo service mysql restart
登录从库mysql，设置主从配置
1
CHANGE MASTER TO MASTER_HOST='192.168.253.134', MASTER_USER='replica',MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='mysql-bin.000030',MASTER_LOG_POS=154;
参数名 含义 MASTER_HOST 主库IP地址 MASTER_USER 连接主库的用户名 MASTER_PASSWORD 连接主库的密码 MASTER_LOG_FILE binlog日志文件名 MASTER_LOG_POS binlog日志文件位置 开启同步操作
1
start slave;
查看主从同步状态
1
show slave status;
注意: 若虚拟机为复制的多个实例, 需要修改server_uuid, 避免报错
sudo vi /var/lib/mysql/auto.cnf
分库分表
介绍
随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：
IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。 请求数据太多，带宽不够，网络IO瓶颈。
CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。
为了解决上述问题，我们需要对数据库进行分库分表处理。
分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。
拆分策略
分库分表的形式主要包括两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表，所以组成的拆分策略最终如下：
垂直拆分
垂直分库
以表为依据，根据业务将不同表拆分到不同库中。
特点：
- 每个库的表结构都不一样。
- 每个库的数据也不一样。
- 所有库的并集是全量数据。
垂直分表
以字段为依据，根据字段属性将不同字段拆分到不同表中。
特点：
- 每个表的结构都不一样。
- 每个表的数据也不一样，一般通过一列（主键/外键）关联。
- 所有表的并集是全量数据。
水平拆分
水平分库
以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
特点：
- 每个库的表结构都一样。
- 每个库的数据都不一样。
- 所有库的并集是全量数据。
水平分表
以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
特点：
- 每个表的表结构都一样。
- 每个表的数据都不一样。
- 所有表的并集是全量数据。
实现技术
MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言。
MyCat概述
Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉不到mycat的存在。
开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。 具体的分库分表的策略，只需要在MyCat中配置即可。
在MyCat的整体结构中，分为两个部分：上面的逻辑结构、下面的物理结构。
在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理，而具体的数据存储还是在物理结构，也就是数据库服务器中存储的。
MyCat搭建
下载
1 | 官网: http://www.mycat.org.cn/ |
安装
Mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，下面介绍MyCat的Linux中的环境搭建。我们需要在准备好的服务器中安装如下软件。
| 服务器 | 安装软件 | 说明 |
|---|---|---|
| 192.168.253.134 | JDK、Mycat | MyCat中间件服务器 |
| 192.168.253.135 | MySQL | 分片服务器 |
| 192.168.253.136 | MySQL | 分片服务器 |
| 192.168.253.137 | MySQL | 分片服务器 |
1 | wget http://dl.mycat.org.cn/1.6.7.6/20210831141727/Mycat-server-1.6.7.6-release-20210831141727-linux.tar.gz |
解压后, 如果mysql版本不对, 可以访问
https://dev.mysql.com/downloads/connector/j/获取jar包替换/usr/local/mycat/lib/mysql-connctor-java-xxx.jar
目录
1 | bin : 存放可执行文件，用于启动停止mycat |
配置
主要配置项包括schema.xml 和 server.xml
schema.xml
在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="NI9NES_DB" checkSQLschema="true" sqlMaxLimit="100">
<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>
<dataNode name="dn1" dataHost="dhost1" database="ni9nes" />
<dataNode name="dn2" dataHost="dhost2" database="ni9nes" />
<dataNode name="dn3" dataHost="dhost3" database="ni9nes" />
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="192.168.253.135:3306" user="root" password="123456" />
<!-- <writeHost host="master" url="jdbc:mysql://192.168.253.135:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456" /> -->
</dataHost>
<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="192.168.253.136:3306" user="root" password="123456" />
</dataHost>
<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="192.168.253.137:3306" user="root" password="123456" />
</dataHost>
</mycat:schema>server.xml
需要在server.xml中配置用户名、密码，以及用户的访问权限信息，具体的配置如下：
1
2
3
4
5
6
7
8
9
10
11<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">NI9NES_DB</property>
<property name="defaultSchema">NI9NES_DB</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">NI9NES_DB</property>
<property name="readOnly">true</property>
</user>
启动
配置完毕后，先启动涉及到的3台分片服务器，然后启动MyCat服务器。切换到Mycat的安装目录，执行如下指令，启动Mycat：
1 | 启动 |
Mycat启动之后，占用端口号 8066。可以使用mysql客户端使用8066端口访问
mysql -h192.168.253.134 -P8066 -uroot -p
查看运行
启动完毕之后，可以查看logs目录下的启动日志，查看Mycat是否启动完成。
1 | tail -f /usr/local/mycat/logs/wrapper.log |
在往 TB_ORDER 表中插入数据时：
- 如果id的值在1-500w之间，数据将会存储在第一个分片数据库中。
- 如果id的值在500w-1000w之间，数据将会存储在第二个分片数据库中。
- 如果id的值在1000w-1500w之间，数据将会存储在第三个分片数据库中。
- 如果id的值超出1500w，在插入数据时，将会报错。
为什么会出现这种现象，数据到底落在哪一个分片服务器到底是如何决定的呢？ 这是由逻辑表配置时的一个参数 rule 决定的，而这个参数配置的就是分片规则，
MyCat配置解析
schema.xml
主要包含以下三组标签：
schema标签
1
2
3<schema name="NI9NES_DB" checkSQLschema="true" sqlMaxLimit="100">
<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>1) . schema 定义逻辑库
schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。
MyCat中的逻辑库的概念，等同于MySQL中的database概念, 需要操作某个逻辑库下的表时, 也需要切换逻辑库(
use xxx)。核心属性：
- name：指定自定义的逻辑库库名
- checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
- sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录
2). schema 中的table定义逻辑表
table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义 。
核心属性：
- name：定义逻辑表表名，在该逻辑库下唯一
- dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
- rule：分片规则的名字，分片规则名字是在rule.xml中定义的
- primaryKey：逻辑表对应真实表的主键
- type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配
置为 global
datanode标签
1
2
3<dataNode name="dn1" dataHost="dhost1" database="ni9nes" />
<dataNode name="dn2" dataHost="dhost2" database="ni9nes" />
<dataNode name="dn3" dataHost="dhost3" database="ni9nes" />核心属性：
- name：定义数据节点名称
- dataHost：数据库实例主机名称，引用自 dataHost 标签中name属性
- database：定义分片所属数据库
datahost标签
1
2
3
4
5
6<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="192.168.253.135:3306" user="root" password="123456" />
<!-- <writeHost host="master" url="jdbc:mysql://192.168.253.135:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" /> -->
</dataHost>该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
核心属性：
- name：唯一标识，供上层标签使用
- maxCon/minCon：最大连接数/最小连接数
- balance：负载均衡策略，取值 0,1,2,3
- writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
- dbDriver：数据库驱动，支持 native、jdbc
rule.xml
rule.xml 中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化。主要包含两类标签：tableRule、Function。
1 | <tableRule name="auto-sharding-long"> |
server.xml
server.xml配置文件包含了MyCat的系统配置信息，主要有两个重要的标签：system、user。
1). system标签
1 | <system> |
主要配置MyCat中的系统配置信息，对应的系统配置项及其含义，如下：
| 属性 | 取值 | 含义 |
|---|---|---|
| charset | utf8 | 设置Mycat的字符集, 字符集需要与MySQL的字符集保持一致 |
| nonePasswordLogin | 0,1 | 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户 |
| useHandshakeV10 | 0,1 | 使用该选项主要的目的是为了能够兼容高版本的jdbc驱动, 是否采用HandshakeV10Packet来与client进行通信, 1:是, 0:否 |
| useSqlStat | 0,1 | 开启SQL实时统计, 1 为开启 , 0 为关闭 ;开启之后, MyCat会自动统计SQL语句的执行情况 ;mysql -h 127.0.0.1 -P 9066 -u root -p 查看MyCat执行的SQL, 执行效率比较低的SQL , SQL的整体执行情况、读写比例等 ; show @@sql ; show @@sql.slow ; show @@sql.sum ; |
| useGlobleTableCheck | 0,1 | 是否开启全局表的一致性检测。1为开启 ，0为关闭 。 |
| sqlExecuteTimeout | 1000 | SQL语句执行的超时时间 , 单位为 s ; |
| sequnceHandlerType | 0,1,2 | 用来指定Mycat全局序列类型，0 为本地文件，1 为数据库方式，2 为时间戳列方式，默认使用本地文件方式，文件方式主要用于测试 |
| sequnceHandlerPattern | 正则表达式 | 必须带有MYCATSEQ或者 mycatseq进入序列匹配流程 注意MYCATSEQ_有空格的情况 |
| subqueryRelationshipCheck | true,false | 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false |
| useCompression | 0,1 | 开启mysql压缩协议 , 0 : 关闭, 1 : 开启 |
| fakeMySQLVersion | 5.5,5.6 | 设置模拟的MySQL版本号 |
| defaultSqlParser | 由于MyCat的最初版本使用了FoundationDB的SQL解析器, 在MyCat1.3后增加了Druid解析器, 所以要设置defaultSqlParser属性来指定默认的解析器; 解析器有两个 :druidparser 和 fdbparser, 在MyCat1.4之后,默认是druidparser,fdbparser已经废除了 | |
| processors | 1,2…. | 指定系统可用的线程数量, 默认值为CPU核心 x 每个核心运行线程数量; processors 会影响processorBufferPool, processorBufferLocalPercent, processorExecutor属性, 所有, 在性能调优时, 可以适当地修改processors值 |
| processorBufferChunk | 指定每次分配Socket Direct Buffer默认值为4096字节, 也会影响BufferPool长度,如果一次性获取字节过多而导致buffer不够用, 则会出现警告, 可以调大该值 | |
| processorExecutor | 指定NIOProcessor上共享businessExecutor固定线程池的大小; MyCat把异步任务交给 businessExecutor线程池中, 在新版本的MyCat中这个连接池使用频次不高, 可以适当地把该值调小 | |
| packetHeaderSize | 指定MySQL协议中的报文头长度, 默认4个字节 | |
| maxPacketSize | 指定MySQL协议可以携带的数据最大大小, 默认值为16M | |
| idleTimeout | 30 | 指定连接的空闲时间的超时长度;如果超时,将关闭资源并回收, 默认30分钟 |
| txIsolation | 1,2,3,4 | 初始化前端连接的事务隔离级别,默认为REPEATED_READ , 对应数字为3 READ_UNCOMMITED=1; READ_COMMITTED=2; REPEATED_READ=3;SERIALIZABLE=4; |
| sqlExecuteTimeout | 300 | 执行SQL的超时时间, 如果SQL语句执行超时,将关闭连接; 默认300秒; |
| serverPort | 8066 | 定义MyCat的使用端口, 默认8066 |
| managerPort | 9066 | 定义MyCat的管理端口, 默认9066 |
2). user标签
1 | <user name="root" defaultAccount="true"> |
配置MyCat中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，权限描述方式及配置说明如下：
在进行权限操作时，我们只需要将 privileges 标签的注释放开。 在 privileges 下的schema标签中配置的dml属性配置的是逻辑库的权限。 在privileges的schema下的table标签的dml属性中配置逻辑表的权限。
MyCat分片
垂直拆分
环境准备
| 服务器 | 安装软件 | 说明 |
|---|---|---|
| 192.168.253.134 | JDK、Mycat | MyCat中间件服务器 |
| 192.168.253.135 | MySQL | 分片服务器 |
| 192.168.253.136 | MySQL | 分片服务器 |
| 192.168.253.137 | MySQL | 分片服务器 |
配置
1) . schema.xml
1 | <schema name="shopping" checkSQLschema="true" sqlMaxLimit="100"> |
2) . server.xml
1 | <user name="root" defaultAccount="true"> |
重新启动服务
1 | bin/mycat restart |
表结构/数据导入
1 | mysql> source /pwd/shopping-table.sql |
测试SQL
1 | select ua.user_id, ua.contact, p.province, c.city, r.area , ua.address from tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id = r.areaid ; |
1 | SELECT order_id , payment ,receiver, province , city , area FROM tb_order_master o, tb_areas_provinces p , tb_areas_city c , tb_areas_region r WHERE o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid ; |
全局表
对于省、市、区/县表tb_areas_provinces , tb_areas_city , tb_areas_region，是属于数据字典表，在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作, 避免跨库查询操作失败。
schema.xml中的逻辑表的配置，修改 tb_areas_provinces、tb_areas_city、tb_areas_region 三个逻辑表，增加 type 属性，配置为global，就代表该表是全局表，就会在所涉及到的dataNode中创建给表。对于当前配置来说，也就意味着所有的节点中都有该表了。
水平拆分
日志表, 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分。
配置
1). schema.xml
1 | <schema name="logs" checkSQLschema="true" sqlMaxLimit="100"> |
tb_log表最终落在3个节点中，分别是 dn4、dn5、dn6 ，而具体的数据分别存储在 dhost1、dhost2、dhost3的itcast数据库中。
2). server.xml
1 | <user name="root" defaultAccount="true"> |
配置root用户既可以访问 SHOPPING 逻辑库，又可以访问ITCAST逻辑库。
重新启动服务
1 | bin/mycat restart |
表结构/数据导入
8066端口连接Mycat服务
1 | mysql> source /pwd/tb-log.sql |
分片规则
范围分片
auto-sharding-long , 根据指定的字段及其配置的范围与数据节点的对应情况， 来决定该数据属于哪一个分片。该分片规则，主要是针对于数字类型的字段。
配置:
1 | <!-- schema.xml --> |
分片规则配置属性含义:
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| mapFile | 对应的外部配置文件 |
| type | 默认值为0 ; 0 表示Integer , 1 表示String |
| defaultNode | 默认节点 默认节点的所用:枚举分片时,如果碰到不识别的枚举值, 就让它路由到默认节点 ; 如果没有默认值,碰到不识别的则报错 。 |
取模分片
mod-long, 根据指定的字段值与节点数量进行求模运算，根据运算结果， 来决定该数据属于哪一个分片。该分片规则，主要是针对于数字类型的字段。
配置:
1 | <!-- schema.xml --> |
分片规则配置属性含义:
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| count | 数据节点的数量 |
一致性hash分片
sharding-by-murmur, 所谓一致性哈希，相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置，有效的解决了分布式数据的拓容问题。该分片规则，可以针对于字符串类型的字段。
配置:
1 | <!-- schema.xml --> |
分片规则配置属性含义:
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| seed | 创建murmur_hash对象的种子，默认0 |
| count | 要分片的数据库节点数量，必须指定，否则没法分片 |
| virtualBucketTimes | 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍;virtualBucketTimes*count就是虚拟结点数量 ; |
| weightMapFile | 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 |
| bucketMapPath | 用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 |
枚举分片
sharding-by-intfile, 通过在配置文件中配置可能的枚举值, 指定数据分布到不同数据节点上, 本规则适用于按照省份、性别、状态拆分数据等业务 。
配置:
1 | <!-- schema.xml --> |
分片规则配置属性含义:
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| mapFile | 对应的外部配置文件 |
| type | 默认值为0 ; 0 表示Integer , 1 表示String |
| defaultNode | 默认节点 ; 小于0 标识不设置默认节点 , 大于等于0代表设置默认节点 ;默认节点的所用:枚举分片时,如果碰到不识别的枚举值, 就让它路由到默认节点 ; 如果没有默认值,碰到不识别的则报错 。 |
应用指定算法
sharding-by-substring, 运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串（必须是数字）计算分片号。
配置:
1 | <!-- schema.xml --> |
分片规则配置属性含义:
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| startIndex | 字符子串起始索引 |
| size | 字符长度 |
| partitionCount | 分区(分片)数量 |
| defaultPartition | 默认分片(在分片数量定义时, 字符标示的分片编号不在分片数量内时,使用默认分片) |
固定分片hash算
sharding-by-long-hash, 该算法类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位 (即与1111111111 进行位 & 运算)，位与运算最小值为 0000000000，最大值为1111111111，转换为十进制，也就是位于0-1023之间。分片字段必须为数字类型。
配置:
1 | <!-- schema.xml --> |
分片规则配置属性含义:
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段名 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| partitionCount | 分片个数列表 |
| partitionLength | 分片范围列表 |
约束 :
1). 分片长度 : 默认最大2^10 , 为 1024 ;
2). count, length的数组长度必须是一致的 ;
以上分为三个分区:0-255,256-511,512-1023
字符串hash解析算法
sharding-by-stringhash, 截取字符串中的指定位置的子字符串, 进行hash算法， 算出分片。
配置:
1 | <!-- schema.xml --> |
分片规则配置属性含义:
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| partitionLength | hash求模基数 ; length*count=1024 (出于性能考虑) |
| partitionCount | 分区数 |
| hashSlice | hash运算位 , 根据子字符串的hash运算 ; 0 代表 str.length(), -1 代表 str.length()-1 , 大于0只代表数字自身 ; 可以理解为substring（start，end），start为0则只表示0 |
按天分片算法
sharding-by-date, 按照日期及对应的时间周期来分片。
配置:
1 | <!-- schema.xml --> |
分片规则配置属性含义:
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| dateFormat | 日期格式 |
| sBeginDate | 开始日期 |
| sEndDate | 结束日期，如果配置了结束日期，则代码数据到达了这个日期的分片后，会重复从开始分片插入sPartionDay 分区天数，默认值 10 ，从开始日期算起，每个10天一个分区 |
自然月分片
sharding-by-month, 使用场景为按照月份来分片, 每个自然月为一个分片。
配置:
1 | <!-- schema.xml --> |
分片规则配置属性含义:
| 属性 | 描述 |
|---|---|
| columns | 标识将要分片的表字段 |
| algorithm | 指定分片函数与function的对应关系 |
| class | 指定该分片算法对应的类 |
| dateFormat | 日期格式 |
| sBeginDate | 开始日期 |
| sEndDate | 结束日期，如果配置了结束日期，则代码数据到达了这个日期的分片后，会重复从开始分片插入 |
MyCat管理监控
在MyCat中，当执行一条SQL语句时，MyCat需要进行SQL解析、分片分析、路由分析、读写分离分析等操作，最终经过一系列的分析决定将当前的SQL语句到底路由到那几个(或哪一个)节点数据库，数据库将数据执行完毕后，如果有返回的结果，则将结果返回给MyCat，最终还需要在MyCat中进行结果合并、聚合处理、排序处理、分页处理等操作，最终再将结果返回给客户端。
而在MyCat的使用过程中，MyCat官方也提供了一个管理监控平台MyCat-Web（MyCat-eye）。Mycat-web 是 Mycat 可视化运维的管理和监控平台，弥补了 Mycat 在监控上的空白。帮 Mycat分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心，可以管理多个节点。Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等，具备 IP 白名单、邮件告警等模块，还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。
MyCat命令行管理
Mycat默认开通2个端口，可以在server.xml中进行修改。
8066 数据访问端口，即进行 DML 和 DDL 操作。
9066 数据库管理端口，即 mycat 服务管理控制功能，用于管理mycat的整个集群状态
连接MyCat的管理控制台：
1 | mysql -h 192.168.253.134 -P 9066 -uroot -p123456 |
常用命令:
| 命令 | 含义 |
|---|---|
| show @@help | 查看Mycat管理工具帮助文档 |
| show @@version | 查看Mycat的版本 |
| reload @@config | 重新加载Mycat的配置文件 |
| show @@datasource | 查看Mycat的数据源信息 |
| show @@datanode | 查看MyCat现有的分片节点信息 |
| show @@threadpool | 查看Mycat的线程池信息 |
| show @@sql | 查看执行的SQL |
| show @@sql.sum | 查看执行的SQL统计 |
Mycat-web
Mycat-web(Mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysql监控，监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。
Mycat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper。
安装
1 | sudo tar -zxvf zookeeper-3.4.6.tar.gz -C /usr/local/ |
访问后台
1 | http://192.168.253.134:8082/mycat |
配置Mycat服务信息
配置完成后即可查看Mycat运行状态及节点信息
基于Mycat的读写分离
读写分离,把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。
MySQL的主从复制，是基于二进制日志（binlog）实现的。
一主一从
| 主机 | 角色 |
|---|---|
| 192.168.253.134:8066 | Mycat |
| 192.168.253.134 | master |
| 192.168.253.135 | slave |
参考【主从复制】内容, 完成两台服务器(134, 135)的MySQL主从搭建
MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制。
1). schema.xml配置
1 | <schema name="ms_rep" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"> |
writeHost代表的是写操作对应的数据库，readHost代表的是读操作对应的数据库。 要想实现读写分离，就得配置writeHost关联主库，readHost关联从库。
而仅仅配置好了writeHost以及readHost还不能完成读写分离，还需要配置一个非常重要的负责均衡的参数balance，取值有4种，具体含义如下：
| 参数值 | 含义 |
|---|---|
| 0 | 不开启读写分离机制 , 所有读操作都发送到当前可用的writeHost上 |
| 1 | 全部的readHost 与 备用的writeHost 都参与select 语句的负载均衡（主要针对于双主双从模式） |
| 2 | 所有的读写操作都随机在writeHost , readHost上分发 |
| 3 | 所有的读请求随机分发到writeHost对应的readHost上执行, writeHost不负担读压力 |
在一主一从模式的读写分离中，balance配置1或3都可以完成读写分离的。
2). server.xml配置
1 | <user name="root" defaultAccount="true"> |
配置完毕后，重新启动Mycat
1 | bin/mycat restart |
在执行增删改操作时，对应的主库及从库的数据均发生变化。 在执行查询操作时，仅从从库查询数据。
当主节点Master宕机之后，业务系统就只能够读，而不能写入数据了。
就得通过另外一种主从复制结构双主双从来解决了。
双主双从
一个主机 Master1 用于处理所有写请求，它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1 主机宕机后，Master2 主机负责写请求，Master1 、Master2 互为备机。架构图如下:
| 编号 | IP | 预装软件 | 角色 |
|---|---|---|---|
| 1 | 192.168.253.134 | MyCat | MyCat中间件服务器 |
| 2 | 192.168.253.134 | MySQL | M1 |
| 3 | 192.168.253.135 | MySQL | S1 |
| 4 | 192.168.253.136 | MySQL | M2 |
| 5 | 192.168.253.137 | MySQL | S2 |
搭建双主从复制
1). 主库配置(134,136)
1 | sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf |
2). 从库配置(135,137)
1 | sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf |
3). 两台主库相互复制(134,136)
1 | # 134服务器 |
配置双主双从读写分离
MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制，通过writeType及switchType来完成失败自动切换的。
1). schema.xml
1 | <schema name="ms_rep" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"> |
属性说明
| 属性 | 参数值 | 含义 |
|---|---|---|
| balance | 1 | 代表全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下, M2,S1,S2 都参与 select 语句的负载均衡 ; |
| writeType | 0 | 写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上 |
| 1 | 所有的写操作都随机地发送到配置的writeHost上 ; | |
| switchType | -1 | 不自动切换 |
| 1 | 自动切换 |
2). server.xml
1 | <user name="root" defaultAccount="true"> |
3).重新加载配置
1 | mysql -h192.168.253.134 -P9066 -uroot -p123456 |
此时, 当一个主库服务停止时, 可以自动切换到备用主库, 实现高可用