溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

Mycat簡(jiǎn)單實(shí)現(xiàn)讀寫(xiě)分離與分庫(kù)分表

發(fā)布時(shí)間：2020-06-30 22:16:29 來(lái)源：網(wǎng)絡(luò) 閱讀：1569 作者：汀云社 欄目：MySQL數(shù)據(jù)庫(kù)

Mycat數(shù)據(jù)庫(kù)讀寫(xiě)分離

環(huán)境：

            客戶(hù)端1.13
                 ↓
            mycat中間件1.11
           ↙            ↘
master主機(jī)1.12        slave主機(jī)1.10

一、master主機(jī)（1.12）配置

兩臺(tái)主機(jī)必須時(shí)間同步，可以部署ntp服務(wù)
步驟：
①配置my.cnf
[root@192 ~]# vim /etc/my.cnf

[mysqld]
...
server_id = 1
log_bin = mysql-bin

[root@192 ~]# systemctl restart mysqld
②配置復(fù)制用戶(hù)以及root用戶(hù)權(quán)限
[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主機(jī)（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權(quán)限
[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主機(jī)創(chuàng)建test庫(kù)
mysql> create database test;

三、mycat主機(jī)（1.11）配置

步驟：
①安裝jdk
選擇與操作系統(tǒng)位數(shù)匹配的版本
[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/
③加載環(huán)境變量
[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>

                <!-- 表級(jí) DML 權(quán)限設(shè)置 -->
                <!--            
                <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

⑦啟動(dòng)mycat服務(wù)器
[root@192 logs]# mycat start
[root@192 logs]# tailf wrapper.log #觀察啟動(dòng)日志，便于排錯(cuò)
[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))

四、客戶(hù)端（1.13）驗(yàn)證讀寫(xiě)分離

步驟：
①登錄到連接端口
[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 |
+----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+

從管理端口觀察兩主機(jī)負(fù)載變化，發(fā)現(xiàn)READ_LOAD在hostS1為2， WRITE_LOAD在hostM1為2，說(shuō)明讀寫(xiě)分離已經(jīng)實(shí)現(xiàn)

Mycat簡(jiǎn)單實(shí)踐分庫(kù)分表

參考：
https://segmentfault.com/a/1190000012054904
https://blog.csdn.net/kk185800961/article/details/51147029
環(huán)境：

            客戶(hù)端
               ↓
        mycat中間件(1.11)
               ↓
          主數(shù)據(jù)庫(kù)(1.12)

一、主數(shù)據(jù)庫(kù)配置(1.12)

步驟：
①在主數(shù)據(jù)庫(kù)創(chuàng)建庫(kù)表
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;

三張表各在三個(gè)庫(kù)，一共九表三庫(kù)

②分配root網(wǎng)段
mysql>grant all on . to root@’%’ identified by ‘123.com’;
③關(guān)閉防火墻或開(kāi)啟端口
[root@192 ~]# systemctl stop firewalld

二、mycat中間件配置(1.11)

部署前安裝略

步驟：
①配置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>

                <!-- 表級(jí) DML 權(quán)限設(shè)置 -->
                <!--            
                <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，默認(rèn)為分三片，需要修改

.......
<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=..

⑤關(guān)閉防火墻或開(kāi)啟端口
[root@192 ~]# systemctl stop firewalld
⑥啟動(dòng)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))

三、驗(yàn)證

步驟：
①?gòu)目蛻?hù)端登入并且插入數(shù)據(jù)
[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); #列出全列名，否則報(bào)錯(cuò)
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 |
+----+-------+------+---------+
②登錄主數(shù)據(jù)庫(kù)查看數(shù)據(jù)表存儲(chǔ)位置
[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)

通過(guò)使用mycat的hash分片規(guī)則，在主數(shù)據(jù)數(shù)據(jù)均衡存儲(chǔ)，users定義在db01庫(kù)，item與item_detail分布在db02，db03庫(kù)，驗(yàn)證成功

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手