使用Mycat实现Mysql分库分表、主从复制、读写分离

分库分表

1.1、配置Mycat

cd mycat/conf

1.1.1、配置服务参数server.xml

修改第82、97行的数据库名称为test-db;文件中的用户为连接mycat的账号和密码

vi server.xml

配置虚拟账号和虚拟数据库

1.1.2配置逻辑库约束schema.xml

vi schema.xml 

1.1.3、配置表分片规则rule.xml

vi rule.xml

修改rule1的内容，把name="rule1"修改为name=“auto-sharding-test-id”;

<algorithm>func1</algorithm> 
改为
<algorithm>func-test-id</algorithm>

配置分片算法；继续在文件的倒数第二行添加如下内容：

    134         <!--分片算法函数
    135                 name 为分片规则的名称，需要与tableRule中匹配
    136                 class 是切分规则对应的切分类(表达式切分类)
    137                 property  标签是切分规则对应的不同属性，不同的切分规则配置不同
    138         -->
    139         <function name="func-test-id" class="io.mycat.route.function.PartitionByPattern">
    140                 <!--表达式的值：切分字段id%512=0-511-->
    141                 <property name="patternValue">512</property>
    142                 <property name="mapFile">partition-test-id.txt</property>
    143         </function>

1.1.4、添加并编辑配置文件

vi partition-test-id.txt

文件内容
0-127=0
128-255=1
256-511=2

1.2、准备数据库

CREATE DATABASE test_db1 CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test_db1;
CREATE TABLE `tb_test` (
  `test_id` BIGINT(20) NOT NULL ,
  `title` VARCHAR(100) NOT NULL COMMENT '商品标题',
  `num` INT(10) NOT NULL COMMENT '库存数量',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `update_time` DATETIME NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`test_id`),
  KEY `updated` (`update_time`)
) ENGINE=INNODB AUTO_INCREMENT=1369462 DEFAULT CHARSET=utf8 COMMENT='测试表';


CREATE DATABASE test_db2 CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test_db2;
CREATE TABLE `tb_test` (
  `test_id` BIGINT(20) NOT NULL ,
  `title` VARCHAR(100) NOT NULL COMMENT '商品标题',
  `num` INT(10) NOT NULL COMMENT '库存数量',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `update_time` DATETIME NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`test_id`),
  KEY `updated` (`update_time`)
) ENGINE=INNODB AUTO_INCREMENT=1369462 DEFAULT CHARSET=utf8 COMMENT='测试表';

CREATE DATABASE test_db3 CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test_db3;
CREATE TABLE `tb_test` (
  `test_id` BIGINT(20) NOT NULL ,
  `title` VARCHAR(100) NOT NULL COMMENT '商品标题',
  `num` INT(10) NOT NULL COMMENT '库存数量',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `update_time` DATETIME NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`test_id`),
  KEY `updated` (`update_time`)
) ENGINE=INNODB AUTO_INCREMENT=1369462 DEFAULT CHARSET=utf8 COMMENT='测试表';

1.2.1、连接mycat

连接Mycat和连接MySQL是一样的，区别是，MyCat的端口号是8066，MyCat的账号密码在server.xml中进行配置

-- 在mycat中插入数据
INSERT  INTO `tb_test`(`test_id`,`title`,`num`,`create_time`,`update_time`) VALUES (1369089,'测试',1,'2018-03-17 22:43:04','2018-03-17 22:43:04');

再查看数据库

2、主从复制

2.1、mysql主从复制作用（主从复制是mysql自带的不是第三方的）

1. 读写分离(我们等一下用mycat实现)
2. 数据备份
3. 高可用
4. 集群

2.2、主从复制原理：

二进制sql执行文件
从服务器有一个长连接和主服务器连接在一起，我们往主服务器写入数据的时候，就会产生一个二进制sql执行文件(该文件已经记录了在主服务器执行的insert、update等写的操作)同步到从服务器，在从服务器执行一遍就可以
(主从数据之间的同步会存在延迟，但这种延迟可以忽略不计，如果从服务器没有拿到执行文件也会有重试机制确保同步一致情况)

2.3、实现主从复制

2.3.1、修改主服务器配置文件

因为我们还是使用刚才分库分表的那三个数据库所以先把数据库的表先清空

vi /etc/my.cnf

启动mysql

service mysqld start

-- 执行sql查看服务器idserver_id=130配置是否成功
SHOW VARIABLES LIKE 'server_id';

-- 查询服务器状态
SHOW MASTER STATUS;

下图是服务器状态 File字段代表的是二进制sql执行文件的文件名

2.3.2修改从服务器配置文件

vi /etc/my.cnf

这里修改和主服务器基本一样就是需要配置同步的数据库，我们现在就用刚才做分库分表的那三个数据库每个数据库之间有逗号隔开

启动mysql从服务器

service mysqld start

2.3.3设置用户权限

在主服务器mysql中执行

-- 主服务器给从服务器账号授权
-- 综合2.2主从复制原理图来看，因为从服务器连接主服务器使用的是jdbc连接,所以要给从服务器设置账号权限，然后该账户拿到主服务器的二进制sql执行文件
GRANT REPLICATION SLAVE ON *.* to 'test_mycat'@'%' identified by 'mycat';

-- 1、关闭同步
stop slave;
--2、设置主服务器地址
-- MASTER_HOST='主服务器地址'
-- MASERT_USER=上面sql创建的用户名
-- MASERT_PASSWORD=上面sql用户的密码
-- MASTER_LOG_FILE=二进制sql执行文件的文件名
-- MASTER_LOG_POS文件行数 就是SHOW MASTER STATUS;语句查询出来Position字段
CHANGE MASTER TO MASTER_HOST='192.168.211.130',MASTER_USER='test_mycat',MASTER_PASSWORD='mycat',
         MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=3116;
-- 3、启动同步
start slave;         

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

-- 执行从服务器sql
SHOW SLAVE STATUS; 

2.4测试

在主服务器执行sql

USE test_db1;
CREATE TABLE `tb_test` (
  `test_id` BIGINT(20) NOT NULL ,
  `title` VARCHAR(100) NOT NULL COMMENT '商品标题',
  `num` INT(10) NOT NULL COMMENT '库存数量',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `update_time` DATETIME NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`test_id`),
  KEY `updated` (`update_time`)
) ENGINE=INNODB AUTO_INCREMENT=1369462 DEFAULT CHARSET=utf8 COMMENT='测试表';



USE test_db2;
CREATE TABLE `tb_test` (
  `test_id` BIGINT(20) NOT NULL ,
  `title` VARCHAR(100) NOT NULL COMMENT '商品标题',
  `num` INT(10) NOT NULL COMMENT '库存数量',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `update_time` DATETIME NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`test_id`),
  KEY `updated` (`update_time`)
) ENGINE=INNODB AUTO_INCREMENT=1369462 DEFAULT CHARSET=utf8 COMMENT='测试表';


USE test_db3;
CREATE TABLE `tb_test` (
  `test_id` BIGINT(20) NOT NULL ,
  `title` VARCHAR(100) NOT NULL COMMENT '商品标题',
  `num` INT(10) NOT NULL COMMENT '库存数量',
  `create_time` DATETIME NOT NULL COMMENT '创建时间',
  `update_time` DATETIME NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`test_id`),
  KEY `updated` (`update_time`)
) ENGINE=INNODB AUTO_INCREMENT=1369462 DEFAULT CHARSET=utf8 COMMENT='测试表';

这时可以发现在主服务器创建的三个表会同步到从服务器
但这时候还有一个问题，从前面说的原理从服务器通过jdbc连接主服务获取到二进制sql执行文件，进行同步所以这时如果我们在从服务器进行写操作时不能同步到主服务器，我们可以通过设置账号权限不能写解决这个问题，但我们现在使用mycat实现读写分离来解决这个问题

3、读写分离

注意mysql本身没有自带读写分离，如果要做读写分离就要使用第三方的中间件或者自己做

3.1修改server.xml配置文件

配置server.xml文件的主账号和从账号的权限，权限在上面的分库分表已经配好，这里可以不用配

vi server.xml

3.2修改schema.xml配置文件

3.3启动mycat

./mycat start

3.4链接mycat进行测试(连接mycat可以参考前面的分库分表)

INSERT  INTO `tb_test`(`test_id`,`title`,`num`,`create_time`,`update_time`) VALUES (256,'测试',1,'2018-03-17 22:43:04','2018-03-17 22:43:04');

执行上面的sql,因为分片的原因数据会写入主服务器的tb_test3数据库然后同步到从服务器的tb_test3数据库