mysql主从复制+mycat读写分离和分库分表

背景：使用mysql5.7做主从复制，配置mycat做读写分离和分库分表

资源部署：

系统：Centos7.2 ip: 192.168.117.152 ( 安装mysql - 主 )系统：Centos7.2 ip: 192.168.117.161 ( 安装mysql - 从)系统：Centos7.2 ip: 192.168.117.150 ( 安装jdk、mysql、mycat )

下载官方rpm包安装源

安装mysql，我这里是下载的mysql8.0版本

rpm -ivh mysql80-community-release-el7-3.noarch.rpm

选择发布系列

yum repolist all | grep mysql

关闭mysql8.0，开启mysql5.7

yum-config-manager --disable mysql80-communityyum-config-manager --enable mysql5.7-community

安装mysql服务，并启动

yum -y install mysql-community-serversystemctl start mysqld

登录mysql5.7的默认密码在 /var/log/mysqd.log

首次登录要修改密码，非生产环境可以改下密码限制修改全局密码策略：set global validate_password_policy=0;再修改密码的长度：set global validate_password_length=8;ALTER USER 'root'@'localhost' IDENTIFIED BY 'picc1234';

配置mysql主，修改 /etc/my.cnf 在文件末尾添加如下内容：

server-id=152 #主库的id 和从库不能一样log-bin=mysql-binlog-slave-updates=true #开启从日志

给从服务器授权

mysql> grant replication slave on . to 'myslave'@'192.168.2.%' identified by '123123';

刷新特权

flush privileges;

获取master信息

配置从服务器，vim /etc/my.cnf

relay-log=relay-log-binrelay-log-index=slave-relay-bin.indexserver-id = 12 // 主从服务器的 server-id 不能相同！

重启mysql服务

在slave上执行：mysql>change master tomaster_host='192.168.2.11',master_user='myslave',master_password='123123',master_log_file='mysql-bin.000002',master_log_pos=337;启动slavemysql> start slave;查看slave状态，io/sql线程是否为yesshow slave status \G;

MyCAT是mysql中间件，是用java开发的，所以需要在实验环境下安装java，官方建议jdk1.7及以上版本。

在官网下载mycat源码包：

解压源码包tar -xzvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz -C /usr/loca/

解压后完成后，目录如下：

目录	说明
bin	mycat命令，启动、重启、停止等
catlet	catlet为Mycat的一个扩展功能
conf	Mycat 配置信息,重点关注
lib	Mycat引用的jar包，Mycat是java开发的
logs	日志文件，包括Mycat启动的日志和运行的日志

Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：

文件	说明
server.xml	Mycat的配置文件，设置账号、参数等
schema.xml	Mycat对应的物理数据库和数据库表的配置
rule.xml	Mycat分片（分库分表）规则

    
          #user 用户配置节点，name 登录的用户名，也就是连接Mycat的用户名                
     
      123456
       #登录的密码，也就是连接Mycat的密码                
     
      TESTDB
       #数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如需要这个用户需要管理两个数据库db1,db2，则配置db1,dbs                
                     
             
            
                    
     
      user
                     
     
      TESTDB
                     
     
      true
             
    

这里配置了一个账号test_mycat 密码也是123456,针对数据库TESTDB,读写权限都有，没有针对表做任何特殊的权限。

schema.xml

schema.xml是最主要的配置项，首先看我的配置文件。

    
            
                     
                      
              
             
             
             
             
             
             
                     
      
       select user()
                      
                      
                              
                               
                       
                      
              
     
    

其中配置文件中的这部分是做了切片（也就是分表）

解释：在mycat中TESTDB逻辑库中可以看到两张student、grade，student表对应dn1、dn2、dn3三个数据节点，每个数据节点分别映射beijing、shanghai、guangzhou三个实例数据库，这三个库中都有一张student表，根据不同的rule模式（表的切分规则，共十种）

配置文件中的这一段是为了实现读写分离的，可以添加多主和多从

解释：url：是连接mysql的地址，user：是登录数据的用户，password：是密码

测试读写分离

登录mycat就想连接mysql一样，mycat有两个端口，8066是连接端口，9066是管理端口 如果连接mycat成功后，查看所有表时卡住，有可能是mycat没有权限连接mysql，需要再主从库上给mycat授权只要先关闭主从同步功能，在mycat上写一条数据在查一条数据即可，如果在select是看不到刚插入的数据则说明读写分离生效。

测试分库分表

创建实际数据库首先，我们肯定需要创建4个database：beijing、shanghai、guangzhou、basic，并生成对应的表。可以看出，student和grade表存在于不同的数据库，而且student表中的数据，分散存储在3个不同的数据库中：

create database beijing;use beijing;create table student(    id    int primary key,    name   varchar(8) not null,    grade  int  not null)ENGINE=InnoDB DEFAULT CHARSET=utf8;create database shanghai;use shanghai;create table student(    id    int primary key,    name   varchar(8) not null,    grade  int  not null)ENGINE=InnoDB DEFAULT CHARSET=utf8;create database guangzhou;use guangzhou;create table student(    id    int primary key,    name   varchar(8) not null,    grade  int  not null)ENGINE=InnoDB DEFAULT CHARSET=utf8;create database basic;use basic;create table grade(    id    int primary key,    name   varchar(8) not null)ENGINE=InnoDB DEFAULT CHARSET=utf8;

连接mycat写入测试数据

insert into grade (id,name) values (1,'一年级');insert into grade (id,name) values (2,'二年级');insert into grade (id,name) values (3,'三年级');insert into student (id,name,grade) values (1,'张三',2);insert into student (id,name,grade) values (2,'李四',1);insert into student (id,name,grade) values (3,'王五',3);insert into student (id,name,grade) values (4,'甲',3);insert into student (id,name,grade) values (5,'乙',2);insert into student (id,name,grade) values (6,'丙',1);

我们插入了6条student记录，这时应该根据不同的取模结果，存在不同的实际数据库的student表中，所以我们切换到实际数据库，分别查看实际数据库里的数据：

再回到mycat看逻辑数据库：

逻辑数据库，查询grade和student两张表，已经数据聚合，还可以进行排序；

如果我们需要将2张表进行关联，也是可以的：

/!mycat:catlet=io.mycat.catlets.ShareJoin /select * from student s,grade g on s.grade=g.id where g.name='一年级';