mycat分库分表

主库192.168.1.121 mysql版本.0
从库192.168.1.165 mysql版本5.7
mycat 192.168.1.111 版本1.65

安装jdk1.8

  yum install java-1.8.0-openjdk* -y

查看版本号

java -version

image.png

主库my.conf配置

[client]
port=3306
[mysql]
default-character-set=utf8 

[mysqld]
#skip-grant-tables
port=3306
server-id=200
sync_binlog=1
log-bin=mysql-bin-200
binlog-do-db=shop

character-set-server=utf8
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password 

查看主库
mysql> show master status

image.png

主库给从库授权

//创建用户
CREATE USER 'leven'@'%' IDENTIFIED BY '123456';
//授权
GRANT REPLICATION SLAVE ON *.* TO 'leven'@'%';
//授权任意ip登陆（方便其他地方查看）
grant all privileges on *.* to 'leven'@'%';
flush privileges;
stop slave;
change master to  master_host='192.168.1.121', master_user='leven' ,master_password='123456', master_log_file='mysql-bin-200.000011' ,master_log_pos=2996222;
start slave;  

其中master_password等于Position，'mysql-bin等于File

查看从库
mysql> show slave status\G;

image.png

Slave_IO_Running: Yes， Slave_SQL_Running: Yes表示主从配置成功

mycat1.65配置
纸修改了schema.xml文件配置，其他文件默认没改

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <table name="orders" primaryKey="id" dataNode="dn" subTables="orders$1-3"  rule="mod-long"/>
        </schema>
        <dataNode name="dn" dataHost="localhost1" database="shop" />
        <dataHost name="localhost1" maxCon="100" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.1.121:3306" user="root"
                                   password="123456">
                        <readHost host="hostS2" url="192.168.1.165:3306" user="root" password="123456" />
                </writeHost>
                 <writeHost host="hostS1" url="192.168.1.165:3316" user="root"
                                   password="123456" />
        </dataHost>
</mycat:schema>

注意server.xml里的schemas要与schema.xml里的schema name="TESTDB"相同

image.png
image.png

测试：连接mycat

mysql -h192.168.1.111 -uroot -p123456 -P8066

在shop库里新建oder1,orders1,orders2,oreder3三张表

//使用mycat:schema 里定义的数据库名
 use TESTDB;
//创建库和表
CREATE DATABASE `shop`;
CREATE TABLE `shop`.`orders1` ( `id` INT NOT NULL , `username` VARCHAR(255) NOT NULL , `password` VARCHAR(255) NOT NULL , `balance` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `shop`.`orders2` ( `id` INT NOT NULL , `username` VARCHAR(255) NOT NULL , `password` VARCHAR(255) NOT NULL , `balance` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `shop`.`orders3` ( `id` INT NOT NULL , `username` VARCHAR(255) NOT NULL , `password` VARCHAR(255) NOT NULL , `balance` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
//插入数据
insert into orders(`id`,`username`,`password`,`balance`) values(1,30,84,5);
insert into orders(`id`,`username`,`password`,`balance`) values(2,30,84,5);
insert into orders(`id`,`username`,`password`,`balance`) values(3,30,84,5);

查看orders1,oreders2,orders3三张表

image.png
image.png
image.png

查询orders记录

image.png
分表成功(分表需要同库)

分库

192.168.1.121数据操作

CREATE DATABASE `shop1`;
CREATE TABLE `shop`.`orders` ( `id` INT NOT NULL , `username` VARCHAR(255) NOT NULL , `password` VARCHAR(255) NOT NULL , `balance` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `shop1`.`orders` ( `id` INT NOT NULL , `username` VARCHAR(255) NOT NULL , `password` VARCHAR(255) NOT NULL , `balance` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

192.168.1.111数据库操作

CREATE DATABASE `shop`;
CREATE TABLE `shop`.`orders` ( `id` INT NOT NULL , `username` VARCHAR(255) NOT NULL , `password` VARCHAR(255) NOT NULL , `balance` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

修改schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
                <table name="orders"  dataNode="dn,dn1,dn2"  rule="mod-long"/>
        </schema>
        <dataNode name="dn" dataHost="localhost1" database="shop" />
        <dataNode name="dn1" dataHost="localhost1" database="shop1" />
        <dataNode name="dn2" dataHost="localhost2" database="shop" />
        <dataHost name="localhost1" maxCon="100" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.1.121:3306" user="root"
                                   password="123456">
                        <readHost host="hostS2" url="192.168.1.165:3306" user="root" password="123456" />
                </writeHost>
                 <writeHost host="hostS1" url="192.168.1.165:3316" user="root"
                                   password="123456" />
        </dataHost>
        <dataHost name="localhost2" maxCon="100" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.1.111:3306" user="root"
                                   password="123456">
                        <readHost host="hostS2" url="192.168.1.111:3306" user="root" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

插入测试数据
mysql -h192.168.1.111 -uroot -p123456 -P8066

use TESTDB;
insert into orders(`id`,`username`,`password`,`balance`) values(1,30,84,5);
insert into orders(`id`,`username`,`password`,`balance`) values(2,30,84,5);
insert into orders(`id`,`username`,`password`,`balance`) values(3,30,84,5);

查看是否插入成功
在192.168.1.121的shop与shop1中看是否插入成功

image.png
image.png

然后在192.168.1.111的shop库中看是否插入成功

image.png
最后看mycat连接能否查询到数据
image.png
都能查到数据分库成功