海量数据存储与查询 MyCat（未完成）

一、MyCat概述

 1.1 什么是Mycat

Mycat是数据库中间件，所谓中间件数据库中间件是连接Java应用程序和数据库中间的软件。

 1.2 为什么要用Mycat

       我们现在普遍的Java应用程序都是直接连接了MySQL软件进行读写操作，也就是我们在Java中的配置文件等定义了mysql的数据源，直接连接到了我们的mysql软件，但是当某些情况下我们可能需要用到了多个数据库，这个时候我们可能就需要配多个数据源去连接我们的多个数据库，这个时候我们进行sql操作的时候就会很麻烦，因为Java与数据库有了一个紧密的耦合度，但是如果我们在Java应用程序与mysql中间使用了mycat，我们只需要访问mycat就可以了，至于数据源等问题，mycat会直接帮我们搞定。

        再来说一下高访问量高并发，我们都知道mysql数据库实际上在数据查询上是有一个瓶颈的，当我们的数据太多的时候，已经互联网上有高并发的请求的时候，这个时候对我们mysql的压力是非常大的，当访问量一大，就可能会出现查不出数据，响应的时间太长等，这个时候我们可能需要有多个服务器对数据库进行读写分离，以及对数据库进行集群，这个时候我们的sql语句要进行分类，哪个sql语句要访问哪个数据库，这个时候只要交给mycat就可以了。

遇到问题：

• Java与数据库的紧耦合
• 高访问量高并发对数据库的压力
• 读写请求数据不一致

 1.3 Mycat与其他数据库中间件的区别

 目前的数据库中间件有很多，并从各个维度将其与 Mycat进行对比。

Mycat优势

性能可靠稳定

基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、优秀的架构和性能以及众多成熟的使用 案例使得MYCAT一开始就拥有一个很好的起点，站在巨人的肩膀上，我们能看到更远。

强大的技术团队

MyCat现在由一支强大的技术团队维护 , 吸引和聚集了一大批业内大数据和云计算方面的资深工程师、架构师、DBA，优秀的团队保障了MyCat的稳定高效运行。而且MyCat不依托于任何商业公司，而 且得到大批开源爱好者的支持。

体系完善

MyCat已经形成了一系列的周边产品,比较有名的是 Mycat-web、Mycat-NIO、Mycat-Balance 等,已经形成了一个比较完整的解决方案,而不仅仅是一个中间件。

1.4 应用场景

Mycat还形成了一系列周边产品，比较有名的是 Mycat-web、Mycat-NIO、Mycat-Balance等，已成为一个比较完整的数据处理解决方案，而不仅仅是中间件。

高可用性与MySQL读写分离

• 高可用：利用MyCat可以轻松实现热备份，当一台服务器停机时，可以由集群中的另一台服务器自动接 管业务，无需人工干预，从而保证高可用。
• 读写分离：通过MySQL数据库的binlog日志完成主从复制，并可以通过MyCat轻松实现读写分离，实 现insert、update、delete走主库，而在select时走从库，从而缓解单台服务器的访问压力。

注意：

Mycat 的读写分离及自动切换都依赖于数据库产品的主从数据同步功能。主从复制不是mycat的功能，是mysql自己的功能。

百亿大表水平分表、集群并行计算

数据切分是Mycat的核心功能，是指通过某种特定的条件，将存放在同一个数据库中的数据分散存放在多个数据库（主机）中，以达到分散单台设备负载的效果。

数据切分有两种切分模式

• 按照不同的表将数据切分到不同的数据库中，这种切分可以叫作数据的垂直切分。
• 根据表中数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多个数据库中，这种切分叫作数据的水平切分。当数据量超过800万行且需要做分片时，可以利用Mycat实现数据切分。

数据库路由器

Mycat基于MySQL 实例的连接池复用机制，可以让每个应用最大程度地共享一个MySQL实例的所有连接池，让数据库的并发访问能力大大提升。

整合多种数据源

当一个项目需要用到多种数据源如Oracle、MySQL、SQL Server、PostgreSQL时，可以利用Mycat进行整合，只需访问Mycat 这一个数据源就行。

 1.5 核心概念

分片

 简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库 （主机）上面，以达到分散单台设备负载的效果。 数据的切分（Sharding）根据其切分规则的类 型，可以分为两种切分模式。 1). 一种是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，这种切分可以称之为 数据的垂直（纵向）切分。

逻辑库schema

业务开发人员通常在实际应用中并不需要知道中间件的存在，只需要关注数据库，所以数据库中间件可以被当作一个或多个数据库集群构成的逻辑库。

注意：

逻辑库，与MySQL中的Database（数据库）对应，⼀个逻辑库中定义了所包括的Table。

逻辑表table

既然有逻辑库，就会有逻辑表。在分布式数据库中，对于应用来说，读写数据的表就是逻辑表。逻辑表可以分布在一个或多个分片库中，也可以不分片。

注意：

Table：表，即物理数据库中存储的某⼀张表，与传统数据库不同，这⾥的表格需要声明其所存储的逻辑数据节点DataNode。

分片节点 DataNode

逻辑概念，将数据切分后，每个分片节点不一定会独占一台机器，同一台机器上可以有多个分片数据库，这样一个或多个分片节点所在的机器就是节点主机。为了规避单节点主机并发数量的限制,尽量将读写压力高的分片节点均匀地放在不同的节点主机上。

 DN1、DN2、DN3分别是MySQL的连接实例

数据库主机（节点主机）DataHost

数据切分后，每个分片节点(dataNode)不一定都会独占一台机器，同一机器上面可以有多个分片数据库，这样一个或多个分片节点(dataNode)所在的机器就是节点主机(dataHost),为了规避单节点主机并发数限制，尽量将读写压力高的分片节点(dataNode)均衡的放在不同的节点主机(dataHost)。

分片规则 

用户

MyCat的用户(类似于MySQL的用户，支持多用户）

1.6 Mycat原理

 Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL 语句。

流程：

首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离 分析、缓存分析等，然后将此 SQL 发 往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

1. 解析SQL语句涉及的表。
2. 查看表的定义，如果表存在分片规则，则获取SQL语句的分片字段。
3. 将SQL语句发送到相应的分片去执行。
4. 最后处理所有分片返回的数据并返回给客户端。

 二、Mycat部署安装

2.1 MySQL主从复制概述

 为什么要主从复制

MySQL是现在普遍使用的数据库，但是如果宕机了必然会造成数据丢失。为了保证MySQL数据库的可靠性。就要会一些提高可靠性的技术，即主从复制。

如何解决性能问题

生活中有很形象的例子，比如你在自助咖啡厅买咖啡（如果只有一台咖啡机)

 如果有多台咖啡机，很明显大家买咖啡的效率就上去了：

 MySQL主从复制原理

 主从复制的原理则是采用binlog文件进行复制，我们都知道MySQL的数据库会产生一个二进制日志，就是binlog，该日志文件记录了数据的任何修改（增删改）,所以我们的从机Slave会启动一个Thread从主机读取二进制的binlog日志到本机的I/O thread中,然后写入一个Relay log文件中,从机开启一个SQL thread 定时检查Realy log 文件,如果发现有更新立即把更新的内容在本地的数据库上执行。

biglog 日志的三种格式

• Statement：每一条会修改数据的sql语句都会记录在binlog中
• Row: 仅保存哪条记录被修改
• Mixed: 以上两种的混合使用，一般的语句修改用statement，全表更新使用Row，但是无法使用@@host name。

 查看二进制日志位置

 查看二进制日志格式

mysql> show variables like '%format%';

 查看当前使用的二进制日志文件

mysql> show master status;

 2.2 Linux上搭建MySQL主从复制_安装日志带时间

 环境准备

准备两台mysql服务器分别为master和slave

#下载mysql8的YUM源
wget  http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm


#安装MySQL的YUM源库
rpm -ivh mysql57-community-release-el7-10.noarch.rpm


#安装MySQL
yum -y install mysql-community-server

#启动MySQL
[root@localhost /]# systemctl restart mysqld

#日志文件中找出密码
grep "password" /var/log/mysqld.log

#登录MySQL
mysql -uroot -p

#修改密码策略
set global validate_password_policy=0;
set global validate_password_policy=LOW;
set global validate_password_length=6;


#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

#开启远程连接
use mysql;
update user set host ="%" where user = "root";

#刷新配置信息
flush privileges;

开启主节点和从节点的bin-log日志

#进入主节点配置文件
vim /etc/my.cnf

#配置主节点的id，不重复即可
server-id=1

#启用二进制日志
log-bin=mysql-bin

#进入从节点配置文件
vim /etc/my.cnf

#从服务器唯一ID
server-id=2

#启用中继日志
log-bin=mysql-bin

重启两台服务器的mysql

systemctl restart mysqld.service

在主节点服务器上建立帐户并授权slave

#修改密码策略
set global validate_password_policy=0;
set global validate_password_policy=LOW;
set global validate_password_length=6;

#授权
GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' identified by '123456';

注意：

一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。

 查询Master的状态

mysql>show master status;

注意：

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

从服务器Slave中配置主节点的信息

mysql>change master to master_host='192.168.66.100',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=430; 

 启动从服务器复制功能

mysql>start slave;

检查从服务器复制功能状态

mysql> show slave status \G;

注意：

Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。看看防火墙是否关闭。

主从服务器测试

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据

#主节点创建数据库
mysql> create database hi_db;

#选择该数据库
mysql> use hi_db;

#创建表
create table hi_tb(id int(3),name char(10));

#插入数据
insert into hi_tb values(001,'zj');

 从服务器Mysql查询

mysql>show databases;
mysql>use hi_db
mysql>select * from hi_tb;   

2.3 基于Docker搭建MySQL主从复制

首先拉取Docker镜像

docker pull mysql:5.7

 启动两个容器

#启动Master
docker run -p 3350:3306 --name master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
#启动Slave
docker run -p 3340:3306 --name slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

注意：

• --name：为容器指定名称，这里是master
• -p：将容器的指定端口映射到主机的指定端口，这里是将容器的3306端口映射到主机的3306端口
• -e：设置环境变量，这里是指定root账号的密码为root
• -d：后台运行容器，并返回容器ID

查看已经创建好的docker容器 

docker ps

注意：

是UP状态，表示正在运行中。

进入docker容器

#进入master容器
[root@localhost /]# docker exec -it master /bin/bash

#进入slave容器
[root@localhost /]# docker exec -it slave /bin/bash

 在xshell中可以复制会话来分别操作matser和slave较为方便。

安装vim

注意：

vi my.cnf对my.cnf进行编辑。此时会报出bash: vi: command not found，需要我们在docker容器内部自行安装vim。使用apt-get install vim命令安装vim

如果报错：

Reading package lists... Done Building dependency tree
Reading state information... Done E: Unable to locate package vim

更新apt-get

apt-get update

安装vim

apt-get install vim

进入master容器 

#进入配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf

#在配置文件的外面的[mysqld]后面添加如下配置
# 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-bin
## 同一局域网内注意要唯一
server-id=100 

注意：slave容器也是相同的操作，只是server-id变了。配置完成后也需要重启mysql服务和docker容器，操作和配置Master(主)一致。

 重启mysql服务

#重启MySQL服务
service mysql restart

#启动master容器
docker start master

#启动slave容器
docker start slave

在Master数据库创建数据同步用户slave 

#进入master容器
[root@localhost /]# docker exec -it master  /bin/bash

#进入mysql
root@b8974d3aa0f2:/# mysql -uroot -p123456

#创建slave账户
mysql>  GRANT replication SLAVE ON *.* TO 'slave' @'%' IDENTIFIED BY '123456';

连接接Master(主)和Slave(从)

在Master进入mysql

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      438 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在Slave 中进入 mysql 配置主节点的信息

mysql>change master to master_host='172.17.0.3',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=438; 

参数：

• master_host: Master 的IP地址（可以先退出容器使用docker inspect master查看master节点的IPAddress）
• master_user: 在 Master 中授权的用于数据同步的用户
• master_password: 同步数据的用户的密码
• master_port: Master 的数据库的端口号
• master_log_file: 指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
• master_log_pos: 从哪个 Position 开始读，即上文中提到的 Position 字段的值
• master_connect_retry: 当重新建立主从连接时，如果连接失败，重试的时间间隔，单位是秒，默认是60秒。

在Slave中开启主从复制 

mysql> start slave;

在 Slave 的 MySQL 终端执行查看主从同步状态

mysql> show slave status \G;

2.4 Mycat安装以及管理命令详解

下载解压Mycat安装包

MyCAT 有提供编译好的安装包，支持 windows、Linux、Mac、Solaris 等系统上安装与运行。

下载地址： Index of /1.6.7.6/20201104174609/ (mycat.org.cn)

tar -zxvf  Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local

 将MyCat配置到环境变量中

#进入环境变量
vim /etc/profile

#添加如下配置
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin

#使环境变量生效
source /etc/profile

注意，mycat是Java语言编写的因此需要jdk环境。配置完环境变量后相关命令可以直接执行不需要到特定的文件夹下执行。

MyCat常用操作命令

mycat start      #启动MyCat
mycat stop       #停止MyCat
mycat console     #前台运行MyCat带控制台输出
mycat restart     #重启MyCat
mycat pause      #暂停MyCat
mycat status      #查看启动状态

 使用MySQL命令进行MyCat的连接

Mycat 提供类似数据库的管理监控方式，可以通过MySQL命令行登录管理端口(9066）执行相应的SQL语句进行管理，也可以通过JDBC方式进行远程连接管理。

管理端口

9066用于查看MyCat的运行状态

mysql -uroot -p123456 -h 192.168.1.125 -P9066 -DTESTDB

参数：

• -h：后面是主机。 即当前Mycat安装的主机IP地址。
• -u：Mycat server.xml中配置逻辑库的用户。
• -p：Mycat server.xml中配置逻辑库的密码。不是MySQL的用户名和密码
• -P：后面是管理端口号。注意P是大写。
• -d：Mycat server.xml中配置逻辑库。

数据端口

8066用于进行数据的CRUD操作

mysql -uroot -p123456 -h 192.168.1.125 -P8066 -DTESTDB

MyCat的连接用户名相关信息在配置文件/usr/local/mycat/conf/server.xml中查看编辑

<user name="root">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
</user>

Show命令

-- 查看所有的可用命令
show @@help; 
-- 查看所有的逻辑数据库
show @@database;
-- 查看所有的数据节点
show @@datanode;
-- 查看所有的数据源
show @@datasource;

2.5 Mycat目录和配置文件

目录结构 

说明：

• bin：可执行命令
• conf：配置文件
• lib：存放jar包
• logs：日志信息
• tmlogs：临时日志
• version.txt：版本信息

三个配置文件

• schema.xml：定义逻辑库，表、分片节点等内容
• rule.xml：定义分片规则
• server.xml：定义用户以及系统相关变量，如端口等

三、Mycat高级特性 

3.1 Mycat读写分离概述

什么是读写分离

读写分离，基本的原理是让主数据库处理事务性增、改、删操作，而从数据库处理查询操作。

为什么使用读写分离

从集中到分布，最基本的一个需求不是数据存储的瓶颈，而是在于 计算的瓶颈，即 SQL 查询的瓶颈，我们知道，正常情况下，Insert SQL 就是几十个毫秒的时间内写入完成，而系 统中的大多数 Select SQL 则要几秒到几分钟才能有结果，很多复杂的 SQL，其消耗服务器 CPU 的能力超强，不亚于死循环的威力。高峰期，复杂的查询可能导致服务器瘫痪。

读写分离方案

MyCat的读写分离是建立在MySQL主从复制基础之上实现的，所以必须先搭建MySQL的主从复制。数据库读写分离对于⼤型系统或者访问量很⾼的互联网应用来说，是必不可少的⼀个重要功能。

注意：

Mycat实现的读写分离和自动切换机制，需要MySQL的主从复制机制配合。

Mysql 主从复制的常用拓扑结构

一主一从

注意：

最基础的复制结构，用来分担之前单台数据库服务器的压力，可以进行读写分离。

 一主多从

注意：

一台 Slave 承受不住读请求压力时，可以添加多台，进行负载均衡，分散读压力。

双主复制 

注意：

双主结构就是用来解决这个问题的，互相将对方作为自己的 Master，自己作为对方的 Slave 来进行复制，但对外来讲，还是一个主和一个从。

级联复制 

 注意： 级联结构就是通过减少直接从属于 Master 的 Slave 数量，减轻 Master 的压力，分散复制请求，从而提高整体的复制效率。

双主级联

注意：

Mysql 的复制结构有很多种方式，复制的最大问题是数据延时，选择复制结构时需要根据自己的具体情况，并评估好目标结构的延时对系统的影响。

 3.2 搭建读写分离

检查主从复制是否开启

mysql> show slave status \G;

 修改mycat配置文件schema.xml,添加dataNode属性

<schema name="TESTDB" checkSQLschema="false" dataNode="db_node" sqlMaxLimit="100">
</schema>

参数：

• schema：逻辑库 name ：逻辑库名称
• sqlMaxLimit：一次取多少条数据
• table：逻辑表
• dataNode：数据节点 对应 datanode标签
• rule：分片规则 对应 rule.xml
• primaryKey: 分片主键 可缓存

 分片配置

<dataNode name="db_node" dataHost="db_host" database="hi_db" />

参数：

• name：分片名字
• dataHost：分片主机
• database：分片数据库,该数据库是真实存在的数据库

配置读写分离

<dataHost name="db_host" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" 
          dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
            #写库(主节点)
            <writeHost host="hostM1" url="192.168.66.100:3306" user="root" password="123456">
            #读库（从节点）
            <readHost host="hostS2" url="192.168.66.102:3306" user="root" password="123456" />
                </writeHost>
</dataHost>

参数：

• dataHost：数据主机（节点主机）
• dbType：数据库驱动native:MySQL JDBC: oracle SQLServer
• switchType： 是否主动读 1

Balance参数设置：

1. balance=“0”, 所有读操作都发送到当前可⽤的writeHost上。
2. balance=“1”，所有读操作都随机的发送到readHost。
3. balance=“2”，所有读操作都随机的在writeHost、readhost上分发

WriteType参数设置：

1. writeType=“0”, 所有写操作都发送到可⽤的writeHost上。
2. writeType=“1”，所有写操作都随机的发送到readHost。
3. writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。

witchType参数设置：

1. switchType="1", 主从自动切换
2. switchType="2"，从机延时超过slaveThreshold值时切换为主读

这里的hi_db实际上在主库和从库中都会存在，因为主从复制的原因，当我们在master中创建数据库或者表的时候，从库中就会将主库中的数据库和数据表复制到从库中。

启动Mycat 

 mycat restart

 连接到mycat

mysql -uroot -p123456 -h 192.168.66.100 -P 8066

注意：这里的-p密码是mycat的密码，-P 8066是mycatCRUD的端口。

 查看逻辑数据库TESTDB

mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

 查看逻辑数据库中的表

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_hi_db |
+-----------------+
| hi_tb           |
+-----------------+
1 row in set (0.00 sec

mysql> select * from hi_tb;
+------+------+
| id   | name |
+------+------+
|    1 | zj   |
+------+------+
1 row in set (0.02 sec)

验证读写分离

#查看主机的名称（写库）
[root@localhost /]# uname -n
localhost.localdomain

#mycat插入数据
mysql> insert into hi_tb values(2,@@hostname);
Query OK, 1 row affected (0.01 sec)

mysql> select * from hi_tb;
+------+-----------------------+
| id   | name                  |
+------+-----------------------+
|    1 | zj                    |
|    2 | localhost.localdomain |
+------+-----------------------+
2 rows in set (0.00 sec)

3.3 MySQL双主双从原理

 一主一从

 注意： 是最基础的复制结构，用来分担之前单台数据库服务器的压 力，可以进行读写分离。

一主多从 

 问题： 一台 Slave 承受不住读请求压力时，可以添加多台，进行负载 均衡，分散读压力。

 如何解决

 介绍： 互相将对方作为自己的 Master，自己作为对方的 Slave 来进行 复制，但对外来讲，还是一个主和一个从。

3.4 通过Docker搭建双主双从

环境准备

编号	角色	IP地址	端口	机器名
1	Master1	192.168.66.100	3350	node-1
2	Slave1	192.168.66.100	3360	node-2
3	Master2	192.168.66.100	3370	node-3
4	Slave2	192.168.66.100	3380	node-4

创建docker容器

#启动第一台
docker run -d  -p 3350:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=master1 mysql:5.7

#启动第二台
docker run -d  -p 3360:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=slave1 mysql:5.7

#启动第三台
docker run -d  -p 3370:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=master2 mysql:5.7

#启动第四台
docker run -d  -p 3380:3306 -e MYSQL_ROOT_PASSWORD=123456  --name=slave2 mysql:5.7

docker容器安装vim

#软件库更新
apt-get update

#安装vim
apt-get install -y vim

 修改容器内MySQL配置文件

1、修改master1配置文件开启binlog日志

#进入master1配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema


#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 
#指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 
# 指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

2、修改master2配置文件

[mysqld]
#主服务器唯一ID
server-id=3 #启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 
#指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 
#指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2

3、修改slave1配置文件

[mysqld]
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay

4、修改slave2配置文件

[mysqld]
#从服务器唯一ID
server-id=4 
#启用中继日志
relay-log=mysql-relay

双主双从重启服务

systemctl restart mysql

配置数据库

1、分别在两个主库中执行创建从库连接账号命令

GRANT replication SLAVE ON *.* TO 'slave' @'%' IDENTIFIED BY '123456';

2、查看两个主库的master状态

mysql> show master status;

两个从库连接到主库

1、slave1连接到master1

mysql> change master to master_host='192.168.66.100',
       master_user='slave', 
       master_port=3350, 
       master_password='123456', 
       master_log_file='mysql-bin.000001', 
       master_log_pos=438; 

参数：

• master_host：这里的ip就是主机mysql所在服务器对应的ip
• master_user：就是在第一步配置的账号
• master_port：mysql的端口
• master_password：配置的密码
• master_log_file：file参数
• master_log_pos：Position参数

2、slave2连接到master2

mysql> change master to master_host='192.168.66.100',
       master_user='slave', 
       master_port=3370, 
       master_password='123456', 
       master_log_file='mysql-bin.000001', 
       master_log_pos=438; 

两个从库启动复制功能

mysql>start slave;

查看连接状态

mysql>show slave status \G;

两个主库再互相成为对方的从库

# 在master1上执行
mysql> change master to master_host='192.168.66.100',
       master_user='slave', 
       master_port=3370, 
       master_password='123456', 
       master_log_file='mysql-bin.000001', 
       master_log_pos=438;

#开启主从复制
mysql> start slave;

#查看链接信息
show slave status \G;



#在master2上执行
mysql> change master to master_host='192.168.66.100',
       master_user='slave', 
       master_port=3350, 
       master_password='123456', 
       master_log_file='mysql-bin.000001', 
       master_log_pos=438;  

#开启主从复制
mysql> start slave;

#查看连接信息
show slave status \G;

双主双从配置MyCat

vim schema.xml

<dataNode name="db_node" dataHost="db_host" database="test" />
 <dataHost name="db_host" maxCon="1000" minCon="10" balance="1"
 writeType="0" dbType="mysql" dbDriver="native" switchType="1" 
slaveThreshold="100" >
 <heartbeat>select user()</heartbeat>
 <!-- can have multi write hosts -->
 <writeHost host="hostM1" url="192.168.140.128:3306" user="root"
 password="123456">
 <!-- can have multi read hosts -->
 <readHost host="hostS1" url="192.168.140.127:3306" user="root" 
password="123456" />
 </writeHost>
 <writeHost host="hostM2" url="192.168.140.126:3306" user="root"
 password="123456">
 <!-- can have multi read hosts -->
 <readHost host="hostS2" url="192.168.140.125:3306" user="root" 
password="123456" />
 </writeHost>
 </dataHost>

重新启动mycat

mycat restart 

注意：

• writeType="0"：所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个riteHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties.
• writeType="1"：所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。
• switchType="-1" ：表示不自动切换 mysql 实例
• switchType="1" ：默认值，自动切换

四、Mycat分片技术

4.1 垂直拆分-分库

前沿

目前很多互联网系统都存在单表数据量过大的问题，这就降低了查询速度，影响了客户体验。为了提高查询速度，我们可以优化sql语句，优化表结构和索引，不过对那些百万级千万级的数据库表，即便是优化过后，查询速度还是满足不了要求。

垂直分库

就是将不同业务的数据表存放到不同的数据库服务器中。

 实现分库

我们选择master1和slave1作为存放不同表的服务器。但是需要注意的是这两个服务器是存在主从复制的，我们需要将这两个服务器的主从复制关掉。

mysql> stop slave;


#查看主从复制是否关闭
show slave status \G;

修改mycat配置文件schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

  <!-- 数据库配置 与server.xml 中的数据库对应 -->
    <schema name="db_test" checkSQLschema="false" dataNode="dn1" sqlMaxLimit="100">
      <!--如果是customer表就存放在dn2节点上,其他的表存放在dn1数据节点上-->
      <table name="customer" dataNode="dn2"/>
    </schema>
    
  <!-- 两个数据节点 dn1,2 上都要创建 orders 数据库 -->
    <dataNode name="dn1" dataHost="host1" database="orders" />
    <dataNode name="dn2" dataHost="host2" database="orders" />
    
  <!-- 两个数据库 肯定两个 dataHost -->
    <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
             writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.66.100:3350" user="root"
                  password="123456">
            
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM2" url="192.168.66.100:3360" user="root" password="123456">
        </writeHost>
    </dataHost>


    
</mycat:schema>

启动/重新启动mycat

mycat start/restart

开启3350和3360两个docker容器master1和slave1(此时两者不具备主从复制关系).

docker start master1
docker start slave1

#分别进入到容器
docker exec -it master1 /bin/bash
docker exec -it slave1/bin/bash

#分别进入两个数据库
mysql -uroot -p123456

#分别创建orders表
mysql> create database orders;

进入mycat,并创建表验证垂直分库

mysql -uroot -p123456 -h192.168.66.100 -P8066

create table customer(
  id int auto_increment,
  name varchar(200),
 primary key(id)
);

create table orders(
  id int auto_increment,
  order_type int,
  customer_id int,
  amount decimal(10,2),
 primary key(id)
);

create table orders_detail(
  id int auto_increment,
  order_id int,
  detail varchar(200),
 primary key(id)
);

create table dict_order_type(
  id int auto_increment,
  order_type varchar(200),
 primary key(id)
);

master1节点中的数据表

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| dict_order_type  |
| orders           |
| orders_detail    |
+------------------+
3 rows in set (0.00 sec)

slave1节点中的数据表

mysql> show tables;
+------------------+
| Tables_in_orders |
+------------------+
| customer         |
+------------------+
1 row in set (0.00 sec)

综上，垂直分库成功！

4.2 水平拆分-分表

前言

上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题。

水平拆分

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。

理解：

我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

 实现分表

1、 选择要拆分的表

MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。

2、分表字段

以 orders 表为例，可以根据不同字段进行分表

编号	分表字段	效果
1	id	查询订单注重时效，历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均。
2	customer_id	根据客户 id 去分，两个节点访问平均。

3、 修改配置文件 schema.xml

为 orders 表设置数据节点为 dn1、dn2，并指定分片规则为 mod_rule（自定义的名字）

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" dataNode="db_node1"  sqlMaxLimit="100">

       <table name="orders" dataNode="db_node1,db_node2" rule="mod_rule"/>

        </schema>
        <dataNode name="db_node1" dataHost="db_host1" database="orders" />
        <dataNode name="db_node2" dataHost="db_host2" database="orders" />

        <dataHost name="db_host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.66.100:3350" user="root" password="123456">   
                </writeHost>
        </dataHost>

        <dataHost name="db_host2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.66.100:3360" user="root" password="123456">   
                </writeHost>
        </dataHost>
</mycat:schema>

 4、修改配置文件rule.xml

分片规则：

在 rule 配置文件里新增分片规则 mod_rule，并指定规则适用字段为 customer_id， 还有选择分片算法 mod-long（对字段求模运算），customer_id 对两个节点求模，根据结果分片.配置算法 mod-long 参数 count 为 2，两个节点。

<tableRule name="mod_rule">
 <rule>
  <columns>customer_id</columns>
  <algorithm>mod-long</algorithm>
 </rule>
</tableRule>
<function name="mod-long"   class="io.mycat.route.function.PartitionByMod">
 <!-- 物理主机的节点数 -->
 <property name="count">2</property>
</function>

5、 重启Mycat，让配置生效 

 6、 在数据节点dn1,dn2上建orders表

create table orders(
  id int auto_increment,
  order_type int,
  customer_id int,
  amount decimal(10,2),
 primary key(id)
);

7、 连接mycat并添加数据

mysql -uroot -p123456 -h192.168.66.100 -P8066

INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);

在mycat中可以看到全部的6条数据，但是在两个节点中只能看到3条数据。

4.3 ER表

分片 join

 在上一小结主要是实现了数据表的水平拆分，但是在做表和表之间的连接查询的时候会出问题，假设我们用orders表和orders_details表做关联查询，sql发送到mycat的时候发现orders表做水平拆分后出现在好几个数据库服务器中，于是就将sql发送给这些服务器，但是有些服务器不存在orders_detilas表，执行sql会返回错误。这就是分片join错误。

 ER 表

 将子表的存储位置依赖于主表，并且物理上紧邻存放因此彻底解决了JION的效率和性能问题，根据这一思路，提出了基于E-R关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。说人话就是我们将表和表之间基于某种关系或指定分为子表和父表，mycat会将子表的数据存放在含有父表的数据库服务器上。

修改配置文件schema.xml配置父子关系

 <schema name="TESTDB" checkSQLschema="false" dataNode="db_node1"  sqlMaxLimit="100">

       <table name="orders" dataNode="db_node1,db_node2" rule="mod_rule">
             #childTable：子表
            <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
       </table>
 </schema>

重启Mycat服务

mycat restart

分别在两个数据库中创建orders数据库，其中一个数据库不含orders_detail表

向mycat中添加数据