主库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
都能查到数据分库成功
网友评论