环境:
创新互联长期为近1000家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为安陆企业提供专业的做网站、成都网站设计,安陆网站改版等技术服务。拥有10多年丰富建站经验和众多成功案例,为您定制开发。
客户端1.13
↓
mycat中间件1.11
↙ ↘
master主机1.12 slave主机1.10
两台主机必须时间同步,可以部署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 | | | |
+------------------+----------+--------------+------------------+-------------------+
步骤:
①配置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 status\G;
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;
步骤:
①安装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-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[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
......
<user name="root">
<property name="password">123.com</property>
<property name="schemas">test</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">test</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
⑤配置schema.xml
[root@192 conf]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host01" database="test" />
<dataHost name="host01" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM1" url="192.168.1.12:3306" user="root" password="123.com">
<readHost host="hostS1" url="192.168.1.10:3306" user="root" password="123.com"/>
</writeHost>
</dataHost>
</mycat:schema>
⑥配置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))
步骤:
①登录到连接端口
[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;
+------+
| id |
+------+
| 1 |
+------+
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 | W | 0 | 10 | 1000 | 49 | 0 | 2 |
| dn1 | hostS1 | mysql | 192.168.1.10 | 3306 | R | 0 | 6 | 1000 | 44 | 2 | 0 |
+----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
从管理端口观察两主机负载变化,发现READ_LOAD在hostS1为2, WRITE_LOAD在hostM1为2,说明读写分离已经实现
参考:
https://segmentfault.com/a/1190000012054904
https://blog.csdn.net/kk185800961/article/details/51147029
环境:
客户端
↓
mycat中间件(1.11)
↓
主数据库(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
部署前安装略
步骤:
①配置server.xml
[root@192 ~]# cd /usr/local/mycat/conf/
[root@192 conf]# vim server.xml
......
<user name="root">
<property name="password">123.com</property>
<property name="schemas">TESTDB</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
②配置schema.xml
[root@192 conf]# vim 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="users" primaryKey="id" type="global" dataNode="node_db01" />
<table name="item" primaryKey="id" dataNode="node_db02,node_db03" rule="mod-long">
<childTable name="item_detail" primaryKey="id" joinKey="item_id" parentKey="id" />
</table>
</schema>
<dataNode name="node_db01" dataHost="dataHost01" database="db01" />
<dataNode name="node_db02" dataHost="dataHost01" database="db02" />
<dataNode name="node_db03" dataHost="dataHost01" database="db03" />
<dataHost name="dataHost01" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="server1" url="192.168.1.12:3306" user="root"
password="123.com"/>
</dataHost>
</mycat:schema>
③配置rule.xml,默认为分三片,需要修改
.......
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
........
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
④配置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;
+----+------+
| id | name |
+----+------+
| 1 | haha |
+----+------+
mysql> select * from item;
+----+-------+
| id | value |
+----+-------+
| 2 | 20 |
| 1 | 10 |
+----+-------+
mysql> select * from item_detail;
+----+-------+------+---------+
| id | value | name | item_id |
+----+-------+------+---------+
| 1 | 10 | wu | 1 |
| 3 | 30 | kk | 55 |
| 2 | 20 | kk | 2 |
| 4 | 40 | kk | 66 |
+----+-------+------+---------+
②登录主数据库查看数据表存储位置
[root@192 ~]# mysql -uroot -p123.com
mysql> select * from db01.users;
+----+------+
| id | name |
+----+------+
| 1 | 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;
+----+-------+
| id | value |
+----+-------+
| 2 | 20 |
+----+-------+
1 row in set (0.00 sec)
mysql> select * from db03.item;
+----+-------+
| id | value |
+----+-------+
| 1 | 10 |
+----+-------+
1 row in set (0.00 sec)
mysql> select * from db01.item_detail;
Empty set (0.01 sec)
mysql> select * from db02.item_detail;
+----+-------+------+---------+
| id | value | name | item_id |
+----+-------+------+---------+
| 2 | 20 | kk | 2 |
| 4 | 40 | kk | 66 |
+----+-------+------+---------+
2 rows in set (0.00 sec)
mysql> select * from db03.item_detail;
+----+-------+------+---------+
| id | value | name | item_id |
+----+-------+------+---------+
| 1 | 10 | wu | 1 |
| 3 | 30 | kk | 55 |
+----+-------+------+---------+
2 rows in set (0.00 sec)
通过使用mycat的hash分片规则,在主数据数据均衡存储,users定义在db01库,item与item_detail分布在db02,db03库,验证成功
标题名称:Mycat简单实现读写分离与分库分表
本文链接:https://www.cdcxhl.com/article26/pdcojg.html
成都网站建设公司_创新互联,为您提供做网站、网站改版、网站维护、定制开发、小程序开发、域名注册
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联