MySql进阶(六)——主从复制&分表分库

主从复制

概述

主从复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而 使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。
MySQL 复制的主要特点包含以下三个方面：

1. 主库出现问题，可以快速切换到从库提供服务
2. 实现读写分离，降低主库的访问压力
3. 可以在从库中执行备份，以避免备份期间影响主库服务
   

原理

MySQL主从复制原理：

从上图来看，复制分成三步：

1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog（记录原始 SQL 语句 ）中
2. 从库的IO线程读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log
3. slave的SQL线程重做中继日志中的事件，即读取中继日志中的 SQL 语句并执行，将改变反映它自己的数据

搭建实现

主库配置

1. 修改配置文件 /etc/my.cnf

#mysql服务1D，保证整个集群环境中唯一，取值范围：1-232-1，默认为1
server-id=1
#是否只读,1代表只读,0代表读写
read-only=0
#忽略的数据，指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

2. 重启MySQL服务器

systemctl restart mysqld

3. 登录mysql，创建远程连接的账号，并授予主从复制权限

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY Root@123456；
#为 'itcast'@'%’ 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

4. 通过指令，查看二进制日志坐标

show master status;

字段含义说明：
file：从哪个日志文件开始推送日志文件
position 从哪个位置开始推送日志
binlog_ignore_db：指定不需要同步的数据库
binlog.000003

从库配置

1.修改配置文件 /etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1-232-1，和主库不一样即可
server-id=2
#是否只读,1代表只读，0代表读写
read-only=1

2. 重新启动MySQL服务

systemctl restart mysqld

设置read_only=1后，超级管理员不是只读，如果想要让超级管理员也是只读，就需要额外配置super_read_only=1

3. 登录mysql，设置主库配置

CHANGE REPLICATION SOURCE TO SOURCE_HOST= 'xxx.xxx', SOURCE USER= 'xxx', SOURCE_PASSWORD= 'xxx', SOURCE_LOG_FILE='xxx', MASTER_LOG_POS='xxx';

上述是8.0.23中的语法。如果mysql是8.0.23 之前的版本，执行如下SQL：

CHANGE MASTER TO MASTER_HOST= 'xxx.xxx.xxx.xxx', MASTER_ USER='xxx', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS='xxx';

参数含义：

4. 开启同步操作

start replica; #8.0.22之后
start slave #8.0.22之前

5. 查看主从同步状态

showreplica status； #8.0.22之后
show slave status; #8.0.22之前

• 当Replica_IO_Running和Replica_SQL_Running都显示Yes时才表示配置正常，否则检查重新配置

测试

1. 在主库上创建数据库、表，并插入数据

create database db01;
use db01;
create table tb_use(
        id int(11) primary key not null auto_increment,
        name varchar(50) not null,
        sex varchar(1)
)engine=innodb default charset=utf8mb4;
insert into tb_user(id, name, sex) valurs (null, 'Tom', '1'), (null, 'Trigger', '0'), (null, 'Dawn', '1');

2. 在从库中查询数据，验证主从是否同步

分库分表

1.介绍

实现技术：

1. ShardingIDBC： 基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
2. MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

2.Mycat概述

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat，对于开发人员来说根本感觉 不到mycat的存在。

优势：

• 性能可靠稳定
• 强大的技术团队
• 体系完善
• 社区活跃

Mycat安装

Mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，下面介绍MyCat的Linux中的环境搭建。我们需要在 准备好的服务器中安装如下软件：

1. MySQL
2. JDK
3. Mycat
   

安装步骤：下载Mycat上传到服务器，解压即可

目录结构

• bin : 存放可执行文件，用于启动停止mycat
• conf：存放mycat的配置文件
• lib：存放mycat的项目依赖包（jar）
• logs：存放mycat的日志文件

概念介绍

- Mycat本身不直接存储数据，只负责管理数据请求的路由和分发，而实际的数据存储由后端数据库完成

Mycat入门

需求
以 tb_order 表为例：由于 tb_order 表中数据量很大，磁盘IO及容量都到达了瓶颈，现在需要对 tb_order 表进行数据分片，分为三个数据节点，每一个节点主机位于不同的服务器上。

环境准备

1. 关闭三台服务器的防火墙
2. 分别在三台服务器里创建数据库（分库、库名要一致）
   

分片配置
schema.xml

server.xml
配置mycat的用户及用户的权限信息：

启动服务
切换到Mycat的安装目录，执行如下指令，启动Mycat：

## 启动
bin/mycat start
## 停止
bin/mycat stop

Mycat启动之后，占用端口号 8066
启动完毕之后，可以查看logs目录下的启动日志，查看Mycat是否启动完成

分片测试
通过如下指令，就可以连接并登陆MyCat：

mysql -h 192.168.200.210 -P 8066 -uroot -p123456

然后就可以在MyCat中来创建表，并往表结构中插入数据，查看数据在MySQL中的分布情况

CREATE TABLE TB ORDER (
id BIGINT(20) NOT NULL,
title VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO TB ORDER(id,title) VALUES(1,'goods1);
INSERT INTO TB_ORDER(id,title) VALUES(2, goods2');
INSERT INTO TB_ ORDER(id,title) VALUES(3,'goods3);
INSERT INTO TB_ ORDER(id,title) VALUES(1000000,'goods1000000");
INSERT INTO TB_ ORDER(id,title) VALUES(10000000,'goods10000000");

会发现goods3在node1服务器，goods1000000在node2服务器，goods10000000在node3服务器
原因是分片规则默认如下：

3.Mycat配置

3.1 schema.xml

schema.xml 作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置
主要包含以下三组标签：

• schema标签
• datanode标签
• datahost标签
  

schema标签
schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念，等同于MySQL中的database概念 , 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。
核心属性：

• name：指定自定义的逻辑库库名
• checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
• sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录
  

table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义 。
核心属性：

• name：定义逻辑表表名，在该逻辑库下唯一
• dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
• rule：分片规则的名字，分片规则名字是在rule.xml中定义的
• primaryKey：逻辑表对应真实表的主键
• type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为 global
  

dataNode标签
dataNode标签中定义了MyCat中的数据节点, 也就是我们通常说的数据分片。一个dataNode标签就是一个独立的数据分片。
核心属性：

• name：定义数据节点名
• dataHost：数据库实例主机名称，引用自 dataHost 标签中name属性
• database：定义分片所属数据库
  

dataHost标签
该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
核心属性：

• name：唯一标识，供上层标签使用
• maxCon/minCon：最大连接数/最小连接数
• balance：负载均衡策略，取值 0,1,2,3
• writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
• dbDriver：数据库驱动，支持 native、jdbc
  

3.2 rule.xml

rule.xml中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配 置化。
主要包含两类标签：

• tableRule
• Function
  

3.3 server.xml

server.xml配置文件包含了MyCat的系统配置信息。
主要有两个重要的标签：

• system
• user
  
  

system标签
对应的系统配置项及其含义，参考server系统配置信息含义：

Mycat分片

垂直拆分

在业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据 库表进行拆分, 原有的数据库表如下：

准备

分别在三台MySQL中创建数据库 shopping。

配置

测试

全局表配置：对于第二个查询语句，省、市、区/县表tb_areas_provinces , tb_areas_city , tb_areas_region，是属于数据字典表，只能在本子数据库中可以使用，但是在多个业务模块中都可能会遇到，可以将其设置为全局表，利于业务操作：

水平拆分

在业务系统中, 有一张表(日志表), 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存储及处理能力是有限的, 可以对数据库表 进行拆分。

准备

分别在三台MySQL中创建数据库 itcast。

配置

测试

• 在Mycat中创建表、插入数据后分表会自动创建和插入相应的数据，不需要像垂直分表那样手动创建表

分片规则

1.分片规则-范围

根据指定的字段及其配置的范围与数据节点的对应情况， 来决定该数据属于哪一个分片。

2.分片规则-取模

根据指定的字段值与节点数量进行求模运算，根据运算结果， 来决定该数据属于哪一个分片。

• 范围分片和取模分片只适用于数字，其他如字符串就不适用了

3.分片规则-一致性hash

所谓一致性哈希， 相同的哈希因子计算值总是被划分到相同的分区表中，不会因为分区节点的增加而改变原来数据的分区位置。

4.分片规则-枚举

通过在配置文件中配置可能的枚举值, 指定数据分布到不同数据节点上, 本规则适用于按照身份、性别、状态拆分数据等业务 。

5.分片规则-应用指定

运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串（必须是数字）计算分片号。

6.分片规则-固定分片hash算法

该算法类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位 与 1111111111 进行位 & 运算。

7.分片规则-字符串hash解析

截取字符串中的指定位置的子字符串, 进行hash算法， 算出分片。

8.分片规则-按（天）日期分片

9.分片规则-自然月

使用场景为按照月份来分片, 每个自然月为一个分片。

Mycat管理及监控

1.Mycat原理

Mycat从客户端接收到信息后，会先进行SQL语句解析，进行分片分析和路由分析，根据分片规则将 SQL 路由到相应的数据库节点，Mycat 支持读写分离，将写操作发往主库，读操作发往从库，服务器处理运行Mycat路由过来的SQL后，将运行结果返回给Mycat，Mycat从各节点获取数据后，在中间件层进行结果合并和聚合处理，如果SQL语句有排序操作和分页操作，会对聚合后的数据进行排序处理和分页处理，最后把最终结果返回给客户端。

2.Mycat管理

Mycat默认开通2个端口，可以在server.xml中进行修改：

• 8066 数据访问端口，即进行 DML 和 DDL 操作
• 9066 数据库管理端口，即 mycat 服务管理控制功能，用于管理mycat的整个集群状态

mysql -h 192.168.200.210 -P 9066 -uroot -p123456

3.Mycat-eye

Mycat-web（Mycat-eye）是对mycat-server提供监控服务，功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysql监控，监控远程服务器（目前仅限于linux系统）的cpu、内存、网络、磁盘。
Mycat-eye运行过程中需要依赖zookeeper，因此需要先安装zookeeper。

安装：Zookeeper安装和MyCat-web安装

安装Zookeeper

A. 上传安装包 zookeeper-3.4.6.tar.gz

B. 解压
tar -zxvf zookeeper-3.4.6.tar.gz -C /usr/local/

C. 创建数据存放目录
cd /usr/local/zookeeper-3.4.6/
mkdir data

D. 修改配置文件名称并配置
cd config
mv zoo_sample.cfg zoo.cfg

E. 配置数据存放录
dataDir=/usr/local/zookeeper-3.4.6/data

F. 启动Zookeeper
bin/zkServer.sh start
bin/zkServer.sh status

安装MyCat-web

A. 上传安装包Mycat-web.tar.gz

B. 解压tar -zxvf Mycat-web.tar.gz -C /usr/local/

C. 目录介绍    
etc         ----> jetty配置文件    
lib         ----> 依赖jar包    
mycat-web   ----> mycat-web项目    
readme.txt    
start.jar   ----> 启动jar    
start.sh    ----> linux启动脚本

D. 启动sh start.sh

E. 访问：http://192.168.200.210:8082/mycat

配置：

读写分离

1.介绍

主从复制在进行数据库操作时，客户端服务器会先判断目前操作是读还是写，再决定是分配到哪个服务器，压力较大。
读写分离,简单地说就是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。
通过MyCat即可轻易实现上述功能，不仅可以支持MySQL，也可以支持Oracle和SQL Server。

2.一主一从

MySQL的主从复制，是基于二进制日志（binlog）实现的。

环境准备：

主从复制的搭建，可以参考：主从复制

3.一主一从读写分离

配置

MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制。

测试

连接Mycat，并在Mycat中执行DML、DQL查看是否能够进行读写分离。

• 主节点Master宕机之后，业务系统就只能够读，而不能写入数据了，可以通过双主双从

4.双主双从

一个主机 Master1 用于处理所有写请求，它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1 主机宕机后，Master2 主机负责写请求，Master1 、Master2 互为备机：

4.1.准备工作

我们需要准备5台服务器，具体的服务器及软件安装情况如下：

关闭以上所有服务器的防火墙：

• systemctl stop firewalld
• systemctl disable firewalld

4.2.主库配置（Master1）

4.3.主库配置（Master2）

4.4.两台主库创建账户并授权

4.5.从库配置（Slave1）

4.6.从库配置（Slave2）

4.7.两台从库配置关联的主库

• MASTER_HOST跟从库所关联的主库IP，MASTER_USER跟用户名，MASTER_PASSWORD跟用户密码，MASTER_LOG_FILE和MASTER_LOG_POS可以在对应主库中通过show master status获得
  两台从库 2 和 4 都要配置

4.8.两台主库相互复制

4.9.测试

分别在两台主库Master1、Master2上执行DDL、DML语句，查看涉及到的数据库服务器的数据同步情况

<code class="language-SQL">create database db01;
use db01;
create table tb_user(
        id in(11)not null primary key,
        name varchar(50) not null,
        sex varcahr(1)
)engine=innodb default charset=utf8mb4

insert into tb user(id,name,sex) values(l,'Tom','1');
insert into tb user(id,name,sex) values(2,'Trigger','0');
insert into tb user(id,name,sex) values(3,'Dawn','1');
insert into tb user(id,name,sex) values(4,"ack Ma','1');
insertinto tb user(id,name,sex) values(5,'Coco','0');
insert into tb user(id,name,sex) values(6,'erry','1');

5.双主双从读写分离

5.1 配置

MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制，通过writeType及switchType来完成失败自动切换。

5.2 测试

登录MyCat，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。

• 查询时，从哪个服务器查询时随机的，插入时，默认插入到master1当中，然后同步到其他三台服务器
  当主库挂掉一个之后，是否能够自动切换。

小技巧

1. 在SQL语句之后加上\G会将结果的表格形式转换成行文本形式
2. 查看Mysql数据库占用空间：

SELECT table_schema "Database Name"
     , SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema;