您好,登錄后才能下訂單哦!
環(huán)境如下:
系統(tǒng) | IP | 主機名 | 服務(wù) |
---|---|---|---|
Centos 7.5 | 192.168.20.2 | mysql01 | MySQL 5.7.24 |
Centos 7.5 | 192.168.20.3 | mysql02 | MySQL 5.7.24 |
Centos 7.5 | 192.168.20.3 | mysql03 | MySQL 5.7.24 |
Centos 7.5 | 192.168.20.4 | mycat | Mycat |
注:主機mysql01和mysql02為主從復(fù)制關(guān)系,可以參考博文MySQL高可用方案——雙主(注:只需要參考博文做出主從效果即可,并不需要雙主,也不需要keepalived來做高可用)來搭建。mycat服務(wù)請參考博文MyCat部署安裝來搭建,這里新增的主機mysql03為一個剛剛搭建好的mysql服務(wù)器,可以參考博文Centos部署MySQL 5.7來搭建。
這里沒有用到mysql03主機,讀寫分離的實現(xiàn)比較簡單,只需要修改mycat的下面兩個配置文件即可。如下:
[root@mycat mycat]# pwd
/usr/local/mycat <!--此處為mycat的家目錄-->
[root@mycat mycat]# vim conf/server.xml
<!--指定client連接mycat的用戶名及密碼,此處的賬號密碼與MySQL數(shù)據(jù)庫無關(guān)-->
[root@mycat mycat]# vim conf/server.xml <!--定位到80行左右,修改如下-->
<user name="mycat" defaultAccount="true"> <!--mycat為用戶名-->
<property name="password">pwd@123</property> <!--此處為用戶mycat的登錄密碼-->
<property name="schemas">test_mycat</property> <!--此處為邏輯庫名--
<!-- 表級 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>
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--下面的schema必須和第一個文件中的schema name(邏輯庫名)一致。-->
<schema name="test_mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="test" /> <!--這里的database是指定要連接后端的哪個數(shù)據(jù)庫,這里連接的是test庫-->
<!--下面是指定后端真實的MySQL主機,關(guān)于下面的balance值,有三個可選值,將在下面寫下來-->
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<!--指定后端哪臺主機用來寫數(shù)據(jù)-->
<writeHost host="hostM1" url="192.168.20.2:3306" user="root"
password="123.com">
<!-- can have multi read hosts -->
<!--指定后端哪臺主機用來讀數(shù)據(jù)-->
<readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat mycat]# vim conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">0</property> <!-- 1為開啟實時統(tǒng)計、0為關(guān)閉 -->
<property name="useGlobleTableCheck">0</property> <!-- 1為開啟全加班一致性檢測、0為關(guān)閉 -->
<property name="sequnceHandlerType">2</property>
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">1</property>
<property name="memoryPageSize">1m</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
<property name="useZKSwitch">true</property>
</system>
<user name="mycat" >
<property name="password">pwd@123</property>
<property name="schemas">test_mycat</property>
</user>
</mycat:server>
[root@mycat mycat]# vim conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="test_mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="test" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.20.2:3306" user="root"
password="123.com">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
</writeHost>
</dataHost>
</mycat:schema>
注:至此,后端數(shù)據(jù)庫上自行創(chuàng)建有相關(guān)權(quán)限的用戶,比如我在上面的datahost字段中指定的writehost和readhost字段的user值為root,則數(shù)據(jù)庫的root賬號要有對應(yīng)的登錄權(quán)限(默認(rèn)root用戶只允許在localhost登錄,所以要自行修改root權(quán)限或換成其他有權(quán)限的用戶)。
[root@mycat mycat]# mycat start #啟動mycat
[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066 #登錄到mycat
#192.168.20.4為mycat主機IP,mycat監(jiān)聽端口為8066,使用的登錄用戶為server.xml文件中創(chuàng)建的用戶
#創(chuàng)建表并插入一些測試數(shù)據(jù)
mysql> use test_mycat;
mysql> create table t1 (id int,name varchar(4));
mysql> insert into t1 values(1,'張三'),(2,'李四'),(3,'王五');
#此時,去后端數(shù)據(jù)庫上查看,應(yīng)該是有上面這些數(shù)據(jù)的。
#現(xiàn)在可以暫時關(guān)閉后端數(shù)據(jù)庫的主從復(fù)制,然后再mycat主機上繼續(xù)插入數(shù)據(jù),測試是否可以讀到
#關(guān)閉主從復(fù)制命令:stop slave;
#以下操作還是在mycat主機進行
mysql> insert into t1 values(4,'趙六'); #插入一條數(shù)據(jù)
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1; #查詢不到剛插入的數(shù)據(jù),說明讀寫操作沒有在同一臺主機上進行
+------+--------+
| id | name |
+------+--------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
+------+--------+
#可以去后端兩個數(shù)據(jù)庫分別進行查看數(shù)據(jù)
#mysql01主機的數(shù)據(jù)如下(說明寫操作是在mysql01):
mysql> select * from t1;
+------+--------+
| id | name |
+------+--------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 趙六 |
+------+--------+
4 rows in set (0.00 sec)
#mysql02主機的數(shù)據(jù)如下:
mysql> select * from t1(說明讀操作是在mysql02);
+------+--------+
| id | name |
+------+--------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
+------+--------+
3 rows in set (0.00 sec)
讀寫分離效果至此結(jié)束。(為不影響后面的測試進行,最好恢復(fù)mysql01和mysql02主機的主從復(fù)制)。
一個數(shù)據(jù)庫由很多表組成,每個表對應(yīng)著不同的業(yè)務(wù),所謂分庫,就是按照業(yè)務(wù)將表進行分類,分不到不同的數(shù)據(jù)庫上面,這樣也就將數(shù)據(jù)或者說壓力分擔(dān)到不同的庫上面,分庫的原則:有緊密關(guān)聯(lián)關(guān)系的表應(yīng)該在一個庫里,相互沒有或者關(guān)聯(lián)關(guān)系不大的表可以分到不同的庫里。
分庫舉例:
假設(shè)現(xiàn)在有四張表: customer, orders, orders_detail, dict_order_type,每張表都有數(shù)百萬條數(shù)據(jù),那么這四張表如若要實現(xiàn)分庫,則可以將customer表單獨分離到一個數(shù)據(jù)庫,另外三張表單獨在另一個數(shù)據(jù)庫。
[root@mycat mycat]# cat conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">0</property> <!-- 1為開啟實時統(tǒng)計、0為關(guān)閉 -->
<property name="useGlobleTableCheck">0</property> <!-- 1為開啟全加班一致性檢測、0為關(guān)閉 -->
<property name="sequnceHandlerType">2</property>
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">1</property>
<property name="memoryPageSize">1m</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
<property name="useZKSwitch">true</property>
</system>
<user name="mycat" >
<property name="password">pwd@123</property>
<property name="schemas">test_db</property>
</user>
</mycat:server>
[root@mycat mycat]# cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="test_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"/> <!--這里指定customer在dn2節(jié)點上-->
</schema>
<dataNode name="dn1" dataHost="host1" database="test" />
<dataNode name="dn2" dataHost="host2" database="test" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.20.2:3306" user="root"
password="123.com">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
</writeHost>
</dataHost>
<!--下面添加host2主機字段,指定的主機就是mysql03-->
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="192.168.20.21:3306" user="root"
password="123.com">
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat mycat]# mycat restart
注:后端指定的真實數(shù)據(jù)庫必須要有test數(shù)據(jù)庫,因為在上面schema.xml文件中的dataNode字段指定的是連接后端的test數(shù)據(jù)庫。
#登錄到mycat
[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066
#創(chuàng)建相應(yīng)的表
mysql> use test_db;
mysql> CREATE TABLE customer(
-> id_a INT AUTO_INCREMENT,
-> NAME VARCHAR(200),
-> PRIMARY KEY(id_a)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE orders(
-> id_b INT AUTO_INCREMENT,
-> order_type INT,
-> customer_id INT,
-> amount DECIMAL(10,2),
-> PRIMARY KEY(id_b)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE orders_detail(
-> id_c INT AUTO_INCREMENT,
-> detail VARCHAR(2000),
-> order_id INT,
-> PRIMARY KEY(id_c)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE dict_order_type(
-> id_d INT AUTO_INCREMENT,
-> order_type VARCHAR(200),
-> PRIMARY KEY(id_d)
-> );
Query OK, 0 rows affected (0.01 sec)
#登錄到后端數(shù)據(jù)庫上,查看表是否按照預(yù)計的效果拆分
[root@mycat mycat]# mysql -uroot -p123.com -h 192.168.20.2
mysql> use test;
mysql> show tables; #可以看到主機mysql01這里有三個表
+-----------------+
| Tables_in_test |
+-----------------+
| dict_order_type |
| orders |
| orders_detail |
+-----------------+
3 rows in set (0.00 sec)
#登錄到mysql03上查看
[root@mycat mycat]# mysql -uroot -p123.com -h 192.168.20.21
mysql> use test;
mysql> show tables; #可以看到這里只有customer表
+----------------+
| Tables_in_test |
+----------------+
| customer |
+----------------+
1 row in set (0.00 sec)
至此,已經(jīng)實現(xiàn)了分庫,多個表放在了不同的庫中(在上面的栗子中,實現(xiàn)的是多個表放在了不同的主機上),但對于通過mycat登錄的客戶來說,還是一個庫,庫中有四張表。
這里對一個新表emp進行拆分。
[root@mycat mycat]# cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="test_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"/>
<!--只需要在原來的配置文件中增加下面一行即可,表示將emp表分在了dn1和dn2這兩個節(jié)點上-->
<table name="emp" dataNode="dn1,dn2" rule="mod_rule"/>
</schema>
<dataNode name="dn1" dataHost="host1" database="test" />
<dataNode name="dn2" dataHost="host2" database="test" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.20.2:3306" user="root"
password="123.com">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="192.168.20.21:3306" user="root"
password="123.com">
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat mycat]# vim conf/rule.xml #修改rule.xml文件
<tableRule name="mod_rule"> <!--這里指定的name必須和schema.xml中指定的name一致-->
<rule>
<columns>id</columns> <!--這里指定的是以哪里列進行分表的-->
<algorithm>mod_long</algorithm> <!--這里指定的是分片方法-->
</rule>
</tableRule>
...................
<!--下面的name必須和上面的分片方法一致-->
<function name="mod_long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property> <!--這里指定的是node節(jié)點數(shù)量-->
</function>
[root@mycat mycat]# mycat restart
[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066
mysql> use test_db;
#創(chuàng)建emp表(后端兩個數(shù)據(jù)庫節(jié)點上都會有這張表)
mysql> create table emp (
-> id int,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.41 sec)
#插入多條數(shù)據(jù)(對于這種分表,插入數(shù)據(jù)需要指定列名,否則會報錯)
mysql> insert into emp(id,name) values(1,'張三'),(2,'李四'),(3,'王五'),(4,'趙六');
#查詢新插入的數(shù)據(jù)
mysql> select * from emp;
+------+--------+
| id | name |
+------+--------+
| 2 | 李四 |
| 4 | 趙六 |
| 1 | 張三 |
| 3 | 王五 |
+------+--------+
4 rows in set (0.04 sec)
mysql> select * from emp order by id; #對結(jié)果進行排序
+------+--------+
| id | name |
+------+--------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 趙六 |
+------+--------+
4 rows in set (0.01 sec)
#后端mysql01主機查詢到該表的數(shù)據(jù)如下:
mysql> select * from emp;
+------+--------+
| id | name |
+------+--------+
| 2 | 李四 |
| 4 | 趙六 |
+------+--------+
2 rows in set (0.00 sec)
#mysql03主機查詢到的數(shù)據(jù)如下:
mysql> select * from emp;
+------+--------+
| id | name |
+------+--------+
| 1 | 張三 |
| 3 | 王五 |
+------+--------+
2 rows in set (0.00 sec)
可以看到數(shù)據(jù)是平均分散在不同節(jié)點上保存的。
但是經(jīng)過分片的普通表,是無法直接和其他表進行join的。
要想解決多表join的問題,還需要修改schema.xml配置文件,在分表的table字段下添加childTable字段,具體怎么實現(xiàn),還在研究中。但是可以將分表的類型設(shè)置為全局表,這是一種比較簡單的實現(xiàn)方式。
分表后,與其他表如何join就成了比較棘手的問題,我們可以選擇將分表設(shè)置為全局表,這是一個解決join的方法(但不是唯一的方法),但全局表有一定的局限性,如下:
若想創(chuàng)建全局表,只需在table字段增加type="global"即可。如下:
[root@mycat conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="test_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"/>
<!--在下面的table字段增加global屬性即可-->
<table name="emp" dataNode="dn1,dn2" type="global">
</table>
</schema>
<dataNode name="dn1" dataHost="host1" database="test" />
<dataNode name="dn2" dataHost="host2" database="test" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.20.2:3306" user="root"
password="123.com">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="192.168.20.21:3306" user="root"
password="123.com">
</writeHost>
</dataHost>
</mycat:schema>
mysql> drop table emp; #需要刪除之前創(chuàng)建的emp表
Query OK, 0 rows affected (0.03 sec)
#重新創(chuàng)建emp表
mysql> create table emp(
-> id int,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.38 sec)
#插入數(shù)據(jù)測試
mysql> insert into emp(id,name) values(1,'張三'),(2,'李四'),(3,'王五'),(4,'趙六');
#在mycat上查詢插入的數(shù)據(jù)如下:
mysql> select * from emp;
+------+--------+
| id | name |
+------+--------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 趙六 |
+------+--------+
4 rows in set (0.02 sec)
#在后端主機上分別查看emp表中的數(shù)據(jù):
#mysql01主機上數(shù)據(jù)如下:
mysql> select * from emp;
+------+--------+
| id | name |
+------+--------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 趙六 |
+------+--------+
4 rows in set (0.00 sec)
#mysql03主機上數(shù)據(jù)如下:
mysql> select * from emp;
+------+--------+
| id | name |
+------+--------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 趙六 |
+------+--------+
4 rows in set (0.00 sec)
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。