mycat mysql 分表分库_Mycat简单实现读写分离与分库分表

桃扇骨 2023-01-12 01:56 209阅读 0赞

Mycat数据库读写分离

环境：

客户端1.13

↓

mycat中间件1.11

↙ ↘

master主机1.12 slave主机1.10

一、master主机(1.12)配置

两台主机必须时间同步，可以部署ntp服务

步骤：

①配置my.cnf

\[root@192 ~\]\# vim /etc/my.cnf

\[mysqld\]

...

server\_id = 1

log\_bin = mysql-bin

\[root@192 ~\]\# systemctl restart mysqld

②配置复制用户以及root用户权限

\[root@192 ~\]\# mysql

mysql> grant replication slave on . to 'myslave'@'192.168.1.%' identified by '123.com';

mysql> grant all on . to 'root'@'%' identified by '123.com';

mysql> flush privileges ;

mysql> show master status ;

File

Position

Binlog\_Do\_DB

Binlog\_Ignore\_DB

Executed\_Gtid\_Set

mysql-bin.000001

1334

二、slave主机(1.10)配置

步骤：

①配置my.cnf

\[root@192 ~\]\# vim /etc/my.cnf

\[mysqld\]

...

server\_id = 2

relay-log = relay-log-bin

relay-log-index = slave-relay-bin.index

\[root@192 ~\]\# systemctl restart mysqld

②配置同步以及root权限

\[root@192 ~\]\# mysql

mysql> change master to master\_host='192.168.1.12',master\_user='myslave',master\_password='123.com',master\_log\_file='mysql-bin.000001',master\_log\_pos=1334;

mysql> start slave ;

mysql> show slave statusG;

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

....

mysql> grant all on . to root@'%' identified by '123.com';

mysql> flush privileges;

③回到master主机创建test库

mysql> create database test;

三、mycat主机(1.11)配置

步骤：

①安装jdk

选择与操作系统位数匹配的版本

\[root@192 ~\]\# systemctl stop firewalld

\[root@192 ~\]\# tar xf jdk-7u65-linux-x64.gz -C /usr/src

\[root@192 ~\]\# cd /usr/src

\[root@192 src\]\# mv jdk1.7.0\_65/ /usr/local/java

②安装mycat

\[root@192 src\]\# wget http://dl.mycat.io/1.6-RELEAS...

\[root@192 ~\]\# tar zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/src

\[root@192 ~\]\# cd /usr/src

\[root@192 src\]\# mv mycat/ /usr/local/

③加载环境变量

\[root@192 ~\]\# vi /etc/profile

.....

export PATH=$PATH:/usr/local/java/bin

export JAVA\_HOME=/usr/local/java

export MYCAT\_HOME=/usr/local/mycat

export PATH=$PATH:/usr/local/mycat/bin

\[root@192 ~\]\# source /etc/profile

④配置server.xml

\[root@192 ~\]\# cd /usr/local/mycat/conf/

\[root@192 conf\]\# vim server.xml

......

123.com

test

user

test

true

⑤配置schema.xml

\[root@192 conf\]\# vim schema.xml

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

show slave status

⑥配置wrapper.conf

\[root@192 conf\]\# vim wrapper.conf

wrapper.java.command=/usr/local/java/bin/java

⑦启动mycat服务器

\[root@192 logs\]\# mycat start

\[root@192 logs\]\# tailf wrapper.log \#观察启动日志，便于排错

\[root@192 logs\]\# ss -anpt | grep java

LISTEN 0 1 127.0.0.1:32000 : users:(("java",pid=40133,fd=4))

LISTEN 0 50 :::50632 :::\* users:(("java",pid=40133,fd=51))

LISTEN 0 100 :::9066 :::\* users:(("java",pid=40133,fd=69))

LISTEN 0 50 :::33782 :::\* users:(("java",pid=40133,fd=53))

LISTEN 0 50 :::1984 :::\* users:(("java",pid=40133,fd=52))

LISTEN 0 100 :::8066 :::\* users:(("java",pid=40133,fd=73))

四、客户端(1.13)验证读写分离

步骤：

①登录到连接端口

\[root@192 ~\]\# mysql -h 192.168.1.11 -P 8066 -uroot -p123.com

mysql> show databases;

DATABASE

test

1 row in set (0.01 sec)

mysql> use test

Database changed

mysql> create table tb (id int);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into tb values (1);

Query OK, 1 row affected (0.05 sec)

mysql> select \* from tb;

1 row in set (0.02 sec)

②登录管理端口

\[root@192 ~\]\# mysql -P9066 -uroot -p123.com -h 192.168.1.11

mysql> show @@datasource ;

DATANODE

NAME

TYPE

HOST

PORT

W/R

ACTIVE

IDLE

SIZE

EXECUTE

READ\_LOAD

WRITE\_LOAD

dn1

hostM1

mysql

192.168.1.12

3306

1000

dn1

hostS1

mysql

192.168.1.10

3306

1000

从管理端口观察两主机负载变化，发现READ\_LOAD在hostS1为2， WRITE\_LOAD在hostM1为2，说明读写分离已经实现

Mycat简单实践分库分表

客户端

↓

mycat中间件(1.11)

↓

主数据库(1.12)

一、主数据库配置(1.12)

步骤：

①在主数据库创建库表

mysql>create database db01;

mysql>create database db02;

mysql>create database db03;

mysql>CREATE TABLE users (

id INT NOT NULL AUTO\_INCREMENT,

name varchar(50) NOT NULL default '',

PRIMARY KEY (id)

)AUTO\_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql>CREATE TABLE item (

id INT NOT NULL AUTO\_INCREMENT,

value INT NOT NULL default 0,

PRIMARY KEY (id)

)AUTO\_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql>CREATE TABLE item\_detail (

id INT NOT NULL AUTO\_INCREMENT,

value INT NOT NULL default 0,

name varchar(50) NOT NULL default '',

item\_id INT NOT NULL,

PRIMARY KEY (id),

key (item\_id)

)AUTO\_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;

三张表各在三个库，一共九表三库

②分配root网段

mysql>grant all on . to root@’%’ identified by ‘123.com’;

③关闭防火墙或开启端口

\[root@192 ~\]\# systemctl stop firewalld

二、mycat中间件配置(1.11)

部署前安装略

步骤：

①配置server.xml

\[root@192 ~\]\# cd /usr/local/mycat/conf/

\[root@192 conf\]\# vim server.xml

......

123.com

TESTDB

user

TESTDB

true

②配置schema.xml

\[root@192 conf\]\# vim schema.xml

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

password="123.com"/>

③配置rule.xml，默认为分三片，需要修改

.......

mod-long

........

④配置wrapper.xml

\[root@192 conf\]\# vim wrapper.conf

wrapper.java.command=/usr/local/java/bin/java

wrapper.working.dir=..

⑤关闭防火墙或开启端口

\[root@192 ~\]\# systemctl stop firewalld

⑥启动mycat

\[root@192 conf\]\# mycat console

\[root@192 ~\]\# ss -anpt | grep java

LISTEN 0 100 :::9066 :::\* users:(("java",pid=39691,fd=69))

LISTEN 0 50 :::1984 :::\* users:(("java",pid=39691,fd=52))

LISTEN 0 100 :::8066 :::\* users:(("java",pid=39691,fd=73))

LISTEN 0 50 :::58818 :::\* users:(("java",pid=39691,fd=53))

LISTEN 0 50 :::46407 :::\* users:(("java",pid=39691,fd=51))

三、验证

步骤：

①从客户端登入并且插入数据

\[root@192 ~\]\# mysql -h 192.168.1.11 -P8066 -DTESTDB -uroot -p123.com

mysql> show databases;

DATABASE

TESTDB

mysql>use TESTDB

mysql> show tables;

Tables in TESTDB

item

item\_detail

users

mysql>insert into users(name) values('haha');

mysql>insert into item(id,value) values (1,10);

mysql>insert into item(id,value) values (2,20);

mysql>insert into item\_detail(id,value,name,item\_id) values(1,10,'wu',1); \#列出全列名，否则报错

mysql>insert into item\_detail(id,value,name,item\_id) values(2,20,'kk',2);

mysql>insert into item\_detail(id,value,name,item\_id) values(3,30,'kk',55);

mysql>insert into item\_detail(id,value,name,item\_id) values(4,40,'kk',66);

mysql> select \* from users;

name

haha

mysql> select \* from item;

value

mysql> select \* from item\_detail;

value

name

item\_id

②登录主数据库查看数据表存储位置

\[root@192 ~\]\# mysql -uroot -p123.com

mysql> select \* from db01.users;

name

haha

1 row in set (0.00 sec)

mysql> select \* from db02.users;

Empty set (0.01 sec)

mysql> select \* from db03.users;

Empty set (0.01 sec)

mysql> select \* from db01.item\_detail;

Empty set (0.01 sec)

mysql> select \* from db02.item;

value

1 row in set (0.00 sec)

mysql> select \* from db03.item;

value

1 row in set (0.00 sec)

mysql> select \* from db01.item\_detail;

Empty set (0.01 sec)

mysql> select \* from db02.item\_detail;

value

name

item\_id

2 rows in set (0.00 sec)

mysql> select \* from db03.item\_detail;

value

name

item\_id

2 rows in set (0.00 sec)

通过使用mycat的hash分片规则，在主数据数据均衡存储，users定义在db01库，item与item\_detail分布在db02，db03库，验证成功

mycat mysql 分表分库_Mycat简单实现读写分离与分库分表

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MyCat实现分库分表

相关 MyCat实现读写分离+分库分表+全局表

相关 mycat配置分库分表+读写分离

相关 mycat mysql 分表分库_Mycat简单实现读写分离与分库分表

相关 mycat+mysql集群:实现读写分离，分库分表

相关 Mycat分库分表

相关 Mycat分库分表

相关数据库读写分离、分表分库——Mycat篇

相关 Mycat数据库读写分离、分表分库

相关使用Mycat进行分库分表，读写分离