6. 【熟悉】MySQL主从搭建

因为MyCAT只能路由、分发，不能把多个数据库里面的数据进行同步，所以要数据同步必须还要使用MySQL的读写分离，主从复制。

6.1 概述主从介绍

MySQL主从又叫Replication、AB复制。简单讲就是A与B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，实现数据实时同步。有这样几个关键点：

1）MySQL主从是基于binlog，主上需开启binlog才能进行主从；

2）主从过程大概有3个步骤；

3）主将更改操作记录到binlog里；

4）从将主的binlog事件（SQL语句）同步本机上并记录在relaylog里；

5）从根据relaylog里面的SQL语句按顺序执行。

6.2 主从作用

主从作用有：实时灾备，用于故障切换；读写分离，提供查询服务；备份，避免影响业务。

主从形式有：

1）一主一从

2）主主复制

3）一主多从：扩展系统读取的性能，因为读是在从库读取的。

4）多主一从：5.7版本开始支持

5）联级复制

6.3 主从复制原理

6.4 主从复制步骤

主库将所有的写操作记录在binlog日志中，并生成log dump线程，将binlog日志传给从库的I/O线程

从库生成两个线程，一个是I/O线程，另一个是SQL线程I/O线程去请求主库的binlog日志，并将binlog日志中的文件写入relay log（中继日志）中SQL线程会读取relay loy中的内容，并解析成具体的操作，来实现主从的操作一致，达到最终数据一致的目的。

6.5 主从复制配置步骤

1）确保从数据库与主数据库里的数据一致

2）在主数据库里创建一个同步账户授权给从数据库使用

3）配置主数据库（修改配置文件）

4）配置从数据库（修改配置文件）

5）需求

6）搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

6.6 环境说明

名称	Ip	Port
M1	192.168.8.100	3306
M1S1	192.168.8.101	3306

6.7 修改配置文件

6.7.1 主机的配置my.cnf

主机里面要记录SQL 语句，以后从机会把该SQL 语句偷过去

server-id=1
log-bin=master.bin

6.7.2 从机的配置my.cnf

server-id=2

6.7.3 测试连接

测试连接：

6.8 执行SQL语句

6.8.1 进入主机里面执行相关配置

mysql -uroot -proot123

创建用户：

create user 'rep'@'%' identified by 'root123';

给该用户授予权限：

grant replication slave on *.* to 'rep'@'%';

刷新权限：

flush privileges;

至此，M1 里面已经创建了一个用户：rep root123 拥有所有库，所有表replication slave。

接下来，我们尝试使用M1 里面的rep 用户登录：

6.8.2 进入从机里面执行相关配置

mysql -u root -proot123

change master to master_host="192.168.8.100",master_port=3306,master_user="rep",master_password="root123",master_log_file="master.000001",master_log_pos=745;

其中，master_log_file：该文件具体叫什么名称，需要从主机里面去看看。进入M1 里面使用root 用户登录M1，执行下面的SQL：show master status;

修改上面的SQL执行：

启动主从：（在M1S1里面执行）：start slave ;

查询主从的状态（M1S1）：show slave status \G;

成功的标志：

6.9 搭建失败的原因

6.9.1 第一个不是yes，是connecting

是因为从机使用你配置的主机信息没有登陆到主机里面！修改(从机里面)

bash stop slave; change master to master_host="192.168.8.100",master_port=3306,master_user="rep",master_password="root123",master_log_file="master.000001",master_log_pos=745; start slave;

6.9.2 第二个不是yes，是no

原因是主机和从机里的数据不一致：

从机会复制主机里面的SQL语句，来自己执行！实验时先把从机里面的db3 删除—>再把主机里面的db3 删除->从机里面复制该删除的命令->从机执行删除的命令（db3），事务无法提交，将一直阻塞！

现在从机里面要删除db3 ，但是没有db3，导致一直阻塞，以后的主从复制不会进行了。解决：在从机新建一个db3，然后停止主从，启动主从：

6.9.3 第一个不是yes，是no

就是你的server-id 没有配置成功的原因，需要重新修改配置文件，复制配置文件到容器里面，然后重启就ok

6.9.4 MySQL主从的操作规范

1）只能在主机里面执行DML 语句，不能在从机里面执行DML语句（会破坏主从）

2）在从机里面可以执行查询语句

3）主机只有一台，但是从机可以有多台

6.10 测试

在M1 里面创建数据库，看M1S1 有没有复制过去

7. 【掌握】MyCAT2的主从配置

前提，先搭建好MySQL的主从配置，登录MyCAT 2在MyCAT2里面操作，也就是连接8066这个端口。

7.1 创建数据源

-- 重置配置
/*+ mycat:resetConfig{} */

-- 添加读写的数据源 
/*+ mycat:createDataSource
{
    "dbType": "mysql",
    "idleTimeout": 60000,
    "initSqls": [],
    "initSqlsGetConnection": true,
    "instanceType": "READ_WRITE",
    "maxCon": 1000,
    "maxConnectTimeout": 3000,
    "maxRetryCount": 5,
    "minCon": 1,
    "name": "m1",
    "password": "root123",
    "type": "JDBC",
    "url": "jdbc:mysql://192.168.8.100:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
    "user": "root",
    "weight": 0
}
*/

-- 更多操作添加读的数据源

/*+ mycat:createDataSource
{
    "dbType": "mysql",
    "idleTimeout": 60000,
    "initSqls": [],
    "initSqlsGetConnection": true,
    "instanceType": "READ",
    "maxCon": 1000,
    "maxConnectTimeout": 3000,
    "maxRetryCount": 5,
    "minCon": 1,
    "name": "m1s1",
    "password": "root123",
    "type": "JDBC",
    "url": "jdbc:mysql://192.168.8.101:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
    "user": "root",
    "weight": 0
}
*/

7.2 查询数据源

/*+ mycat:showDataSources{} */

7.3 创建集群

/*! mycat:createCluster{
  "clusterType":"MASTER_SLAVE",
  "heartbeat":{
    "heartbeatTimeout":1000,
    "maxRetry":3,
    "minSwitchTimeInterval":300,
    "slaveThreshold":0
  },
  "masters":[
    "m1"
  ],
  "maxCon":2000,
  "name":"prototype",
  "readBalanceType":"BALANCE_ALL",
  "replicas":[
    "m1s1"
  ],
  "switchType":"SWITCH"
} */

7.4 查询集群

/*! mycat:showClusters{} */

7.5 创建逻辑库

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

7.6 修改逻辑库的数据源

修改 conf/schemas/db1.schema.json

vim /usr/local/mycat/conf/schemas/db1.schema.json
## 在里面添加集群的名称，作用是让该集群生成并管理这个库的物理库
"targetName":"prototype",

7.7 查看集群的配置文件

我们查看集群配置的结果，发现文件里面自动帮我们添加了。

7.8 测试读写分离是否成功(在MyCAT里面测试)

重启MyCAT：

1）在MyCAT里面创建一个sys_user表：

CREATE TABLE SYS_USER(  ID BIGINT PRIMARY KEY,  USERNAME VARCHAR(200) NOT NULL,  ADDRESS  VARCHAR(500))

2）通过注释生成物理库和物理表：

如果物理表不存在，在 MyCAT2 能正常启动的情况下，根据当前配置自动创建分片表，全局表和物理表：

/*+ mycat:repairPhysicalTable{} */;

3）查看后端物理库：发现物理库和物理表都生成了。

4）在MyCAT里面向sys_user表添加一条数据：

INSERT INTO SYS_USER(ID,USERNAME,ADDRESS) VALUES(1,"XIAOMING","WUHAN");

5）修改MySQL里面的让数据不一样：

6）在MyCAT里面查询数据，会发现每次查询的结果不一样：

到此，我们使用MyCAT2主从搭建就完成了。

8. 【掌握】MySQL的集群搭建（双主双从）

8.1 集群搭建概述

8.1.1 是什么

集群（Cluster）是一种较新的技术，通过集群技术，可以在付出较低成本的情况下，获得在性能、可靠性、灵活性方面的相对较高的收益，其任务调度则是集群系统中的核心技术。

MySQL集群技术在分布式系统中为MySQL数据提供了冗余特性，增强了安全性，使得单个MySQL服务器故障不会对系统产生巨大的负面效应，系统的稳定性得到保障。

8.1.2 优点

主要有以下两个优点。

1）高可伸缩性：服务器集群具有很强的可伸缩性，随着需求和负荷的增长，可以向集群系统添加更多的服务器，在这样的配置中，可以有多台服务器执行相同的应用和数据库操作。

2）高可用性：在不需要操作者干预的情况下，防止系统发生故障或从故障中自动恢复的能力。通过把故障服务器上的应用程序转移到备份服务器上运行，集群系统能够把正常运行时间提高到大于99.9%，大大减少服务器和应用程序的停机时间。

8.1.3 缺点

我们知道，集群中的应用只在一台服务器上运行，如果这个应用出现故障，其他的某台服务器会重新启动这个应用，接管位于共享磁盘柜上的数据区，进而使应用重新正常运转。

而整个应用的接管过程大体需要三个步骤：侦测并确认故障、后备服务器重新启动该应用、接管共享的数据区。因此在切换的过程中需要花费一定的时间，原则上根据应用的大小不同切换的时间也会不同，越大的应用切换的时间越长。

8.2 搭建思路图

8.3 搭建准备工作

8.3.1 启动5台MySQL（至少要5台）

名称	Ip	Port
M1	192.168.8.100	3306
M1S1	192.168.8.101	3306
M1S2	192.168.8.102	3306
M2	192.168.8.103	3306
M2S1	192.168.8.104	3306

前面主从已启动了两个，所以只用启动三个主机就够了

8.3.2 配置文件的修改

先准备5 个配置文件，分别修改。

1）因为在上面的主从里面已有两个配置文件，所以只用创建三个就够了：m1s2.cnf、m2.cnf、m2s1.cnf

2）M2 需要添加一个配置项(具体配置看下面截图)

3）M2 会从M1 复制数据，但是M2 从M1复制的数据，不会记录下来，则M2S1 里面没有数据！

4）我们需要打开M2的级联复制功能，让M2 也能记录从M1 里面复制的数据。

最后结果如下：

配置文件修改好了后，复制到主机里面，并且重启服务！

8.3.3 测试连接

8.4 进入执行SQL

在搭建MySQL的集群时，先清空所有机器里面的数据。

现在有数据的是：M1 M1S1，只需要清空M1的数据，就都没有了。

将之前新建的db1删除就ok。

4.1，M1修改

因为前面搭建好了，所有不用修改

4.2，M1S1修改

同理上面的4.1

4.3，M1S2修改（从机）

给它设置一个主机就ok

mysql -uroot -proot123

关联主机：

change master to master_host="192.168.8.100",master_port=3306,master_user="rep",master_password="root123",master_log_file="master.000001",master_log_pos=3574;

启动主从：start slave ;

查看状态：show slave status \G;

4.4，M2修改

进入M2使用Root登录

mysql -uroot -proot123

1）新建用户：create user 'rep1'@'%' identified by 'root123';

2）给用户授权：grant replication slave on . to 'rep1'@'%';

3）刷新权限：flush privileges;

4）使用新的用户尝试登录：

M2 作为M1的从机需要执行的SQL：（登录M2时要使用root用户）

change master to master_host="192.168.8.100",master_port=3306,master_user="rep",master_password="root123",master_log_file="master.000001",master_log_pos=3574;

start slave ;
show slave status \G;

4.5，M2S1修改

mysql -uroot -proot123

M2S1 是M2的从机，执行的SQL

进入M2查看pos：show master status;

在M2S1里面执行下面的命令：

change master to master_host="192.168.8.103",master_port=3306,master_user="rep1",master_password="root123",master_log_file="master.000001",master_log_pos=747;

启动主从：start slave ;

show slave status \G;

8.5 证集群是否成功

使用工具连接上所有的MySQL

1）往M1 写数据，看 M2S1

2）在M1 新建数据库

3）观察M2S1

4）往M1 写数据，看M1S1

9. 【掌握】MyCAT2操作双主双从

目地：在MyCAT中集群配置实现双主双从。

9.1 角色说明

M1 主机

M1S1 是M1的从机

M1S2 是M1的从机

M2 主机

M2S1 是M2的从机

9.2 添加数据源

-- 添加M1S2读的数据源
/*+ mycat:createDataSource
{
    "dbType": "mysql",
    "idleTimeout": 60000,
    "initSqls": [],
    "initSqlsGetConnection": true,
    "instanceType": "READ",
    "maxCon": 1000,
    "maxConnectTimeout": 3000,
    "maxRetryCount": 5,
    "minCon": 1,
    "name": "m1s2",
    "password": "root123",
    "type": "JDBC",
    "url": "jdbc:mysql://192.168.8.102:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
    "user": "root",
    "weight": 0
}*/;

-- 添加M2的数据源
/*+ mycat:createDataSource
{
    "dbType": "mysql",
    "idleTimeout": 60000,
    "initSqls": [],
    "initSqlsGetConnection": true,
    "instanceType": "READ_WRITE",
    "maxCon": 1000,
    "maxConnectTimeout": 3000,
    "maxRetryCount": 5,
    "minCon": 1,
    "name": "m2",
    "password": "root123",
    "type": "JDBC",
    "url": "jdbc:mysql://192.168.8.103:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
    "user": "root",
    "weight": 0
}*/;

-- 添加M2S1的数据源
/*+ mycat:createDataSource
{
    "dbType": "mysql",
    "idleTimeout": 60000,
    "initSqls": [],
    "initSqlsGetConnection": true,
    "instanceType": "READ",
    "maxCon": 1000,
    "maxConnectTimeout": 3000,
    "maxRetryCount": 5,
    "minCon": 1,
    "name": "m2s1",
    "password": "root123",
    "type": "JDBC",
    "url": "jdbc:mysql://192.168.8.104:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
    "user": "root",
    "weight": 0
}
*/;

9.3 修改配置集群

因为我们现在后端的MySQL集群结构发生了变化，现在修改集群的配置【只是修改】

{
    "clusterType": "MASTER_SLAVE",
    "heartbeat": {
        "heartbeatTimeout": 1000,
        "maxRetryCount": 0,
        "minSwitchTimeInterval": 300,
        "showLog": true,
        "slaveThreshold": 0.0
    },
    "masters": [
        "m1",
        "m2"
    ],
    "maxCon": 2000,
    "name": "prototype",
    "readBalanceType": "BALANCE_ALL",
    "replicas": [
        "m1s1",
        "m1s2",
        "m2s1"
    ],
    "switchType": "SWITCH",
    "timer":{
        "initialDelay":5,
        "period":3,
        "timeUnit":"SECONDS"
    }
}

### 设置主备切换
"switchType": "SWITCH", // 表示允切换

initialDelay: 设置为5秒，表示定时器启动后会等待5秒才执行第一次检查任务。
period: 设置为3秒，表示每3秒执行一次检查任务。
timeUnit: 设置为"SECONDS"，表示时间单位为秒。

9.4 重启MyCAT测试

9.4.1 重启MyCAT

cd /usr/local/mycat/bin
./mycat restart

重启成功如下：

9.4.2 开始测试

1）在MyCAT创建db1库：

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2）在MyCAT创建表：

create table sys_user(  id bigint primary key,  username varchar(200) not null,  address  varchar(500))

3）刷新逻辑表到物理库

/*+ mycat:repairPhysicalTable{} */;

刷新完成之后我们可以在后台的MySQL里面看到数据表已创建完成。

4）在MyCAT里面添加数据：

insert INTO sys_user(id,username,address) values(1,"xiaofang","wuhan");

完成之后刷新后端MySQL物理库，我们发现有数据了。

5）在MyCAT里查询测试：

修改上面5个MySQL数据库中db1库中sys_user表里面的数据，让它不一样，再在MyCAT里面执行查询结果如下: