Mycat 資料庫分庫分表中介軟體系列文章之三：Mycat分庫配置

摘要： 本文是Mycat 資料庫分庫分表中介軟體系列文章的第三篇，平時工作太忙，加上又忙著從PHP轉Java，平日的空閒時間都去研究Java了。什麼Spring MVC、Spring Boot、Spring Cloud、Dubbo，東西真尼瑪的多！正好國慶一人沒事幹，再次拾起來Mycat的研究。...

本文是Mycat 資料庫分庫分表中介軟體系列文章的第三篇，平時工作太忙，加上又忙著從PHP轉Java，平日的空閒時間都去研究Java了。什麼Spring MVC、Spring Boot、Spring Cloud、Dubbo，東西真尼瑪的多！正好國慶一人沒事幹，再次拾起來Mycat的研究。

首先，先按照實現SQL/">MySQL主從複製 上的步驟配置一個簡單的MySQL主從環境，先貼一下相關的配置資訊：

MySQL中Master的配置：

[client]
port=3306
[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir="C:/phpStudy/MySQL/"
datadir="C:/phpStudy/MySQL/data/"
character-set-server=utf8
collation-server=utf8_general_ci 
default-storage-engine=MyISAM
### add
server-id=1
log-bin =mysql-bin
binlog-format = mixed
binlog-do-db=db1 #此引數表示只記錄指定資料庫的二進位制日誌。
binlog-do-db=db2
binlog-do-db=db3
#binlog-ignore-db=api,mysql,performance_schema #此引數表示忽略指定的資料庫的二進位制日誌。
#其他配置省略

MySQL中slave的配置：

[client]
port=3307
[mysql]
default-character-set=utf8

[mysqld]
port=3307
basedir="C:/phpStudy/MySQL3307/"
datadir="C:/phpStudy/MySQL3307/data/"
character-set-server=utf8
collation-server=utf8_general_ci 
default-storage-engine=MyISAM

server-id=3307
log-bin = mysql-bin
binlog-format =mixed
relay-log=mysql-relay
slave-skip-errors=all #跳過所有錯誤
#其他配置省略

在master上建立用於同步資料的賬戶：

grant replication client,replication slave on *.* to slave3307@'%' identified by 'slave3307';
#重新整理許可權，立即生效
flush privileges;

在Master執行命令檢視log_pos和log_file

show master status;

+------------------+----------+--------------+------------------+
| File| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000062 |8233|||
+------------------+----------+--------------+------------------+

注意執行完此步驟後不要再操作主伺服器MYSQL，防止主伺服器狀態值變化

在slave上執行下面的命令，講master和slave關聯起來：

hange master to master_host='127.0.0.1',master_port=3306,master_user='slave3307',
master_password='slave3307',master_log_file='mysql-bin.000062',master_log_pos=8233;

start slave #啟動從伺服器複製功能
show slave status\G

其中 Slave_IO_Running 和 Slave_SQL_Running 兩列的值都為 "Yes"，表明 Slave 的 I/O 和 SQL 執行緒都在正常執行。正常的表現如下：

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave3307
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 24536
Relay_Log_File: mysql-relay.000005
Relay_Log_Pos: 24699
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 24536
Relay_Log_Space: 25318
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: fe1b230d-6ee9-11e8-b0c6-9c5c8e103115
Master_Info_File: C:\phpStudy\MySQL3307\data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

友情提示：如果是直接複製MySQL的安裝目錄配置的主從，注意MySQL5.6開始的UUID的問題。

下面開始Mycat的配置，Mycat中有三個重要的配置檔案：server.xml、schema.xml、rule.xml，下面就分別貼一下對應的配置資訊：

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為開啟實時統計、0為關閉 -->
<property name="useGlobleTableCheck">0</property><!-- 1為開啟全加班一致性檢測、0為關閉 -->

<property name="sequnceHandlerType">2</property>
 
<!--預設為type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
<property name="processorBufferPoolType">0</property>

<!--分散式事務開關，0為不過濾分散式事務，1為過濾分散式事務（如果分散式事務內只涉及全域性表，則不過濾），2為不過濾分散式事務,但是記錄分散式事務日誌-->
<property name="handleDistributedTransactions">0</property>

<!--off heap for merge/order/group/limit1開啟0關閉-->
<property name="useOffHeapForMerge">1</property>

<!--單位為m-->
<property name="memoryPageSize">1m</property>

<!--單位為k-->
<property name="spillsFileBufferSize">1k</property>

<property name="useStreamOutput">0</property>

<!--單位為m-->
<property name="systemReserveMemorySize">384m</property>
<!--是否採用zookeeper協調切換-->
<property name="useZKSwitch">true</property> 
</system> 
<user name="root">
<property name="password">mycat</property>
<!--多個數據庫逗號隔開 -->
<property name="schemas">testdb</property> 
</user>

<user name="mycatread">
<property name="password">mycatread</property>
<property name="schemas">testdb</property>
<property name="readOnly">true</property>
</user>

</mycat:server>

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="t_user" dataNode="dn1,dn2,dn3" rule="crc32slot">
<!-- 父表為t_user，子表為t_admin。t_admin表中的欄位user_id引用t_user表中的id主鍵欄位。 -->
<childTable name="t_admin" joinKey="user_id" parentKey="id" />
</table>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2"slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="123456">
<!--<readHosthost="hostS1" url="127.0.0.1:3307" user="root" password="123456"/>-->
</writeHost>
</dataHost>
</mycat:schema>

rule.xml中的配置資訊：

<function name="crc32slot"
class="io.mycat.route.function.PartitionByCRC32PreSlot">
<property name="count">3</property><!-- 要分片的資料庫節點數量，必須指定，否則沒法分片 -->
</function>

注意下面的地方：

<!--schema.xml中-->
<table name="t_user" dataNode="dn1,dn2,dn3" rule="crc32slot">

<!--rule.xml中-->
<function name="crc32slot"
class="io.mycat.route.function.PartitionByCRC32PreSlot">
<property name="count">3</property><!-- 要分片的資料庫節點數量，必須指定，否則沒法分片 -->
</function>

使用crc32lot進行分片，分片的資料庫節點的數量預設給了2，如果你修改了這裡，請一定要刪除conf/ruledata/crc32slot_T_USER.properties這個檔案，並重新啟動Mycat，不然你的資料是不會分到db3這個資料庫節點的。

到這裡Mycat分庫配置就完成了，你只需要在Mycat的管理上操作，就能在master、slave上看到效果：

mysql>mysql -uroot -pmycat -P8066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloun

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> insert into t_user(id, name) values(1, 'aaa');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_user(id, name) values(2, 'bbb');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id, name) values(3, 'ccc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id, name) values(4, 'ddd');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id, name) values(5, 'eee');
Query OK, 1 row affected (0.00 sec)

在master上看效果：

C:\Users\nick>mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 10628
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from db1.t_user;
+----+------+-------+
| id | name | _slot |
+----+------+-------+
|3 | ccc| 32411 |
|5 | eee| 27566 |
+----+------+-------+
2 rows in set (0.00 sec)


mysql> select * from db2.t_user;
+----+------+-------+
| id | name | _slot |
+----+------+-------+
|1 | aaa| 44983 |
|2 | bbb| 65037 |
+----+------+-------+
2 rows in set (0.00 sec)


mysql> select * from db3.t_user;
+----+------+-------+
| id | name | _slot |
+----+------+-------+
|4 | ddd| 68408 |
+----+------+-------+
1 row in set (0.00 sec)

在slave上檢視資料也是如此，此處就貼出部分資料：

C:\Users\nick>mysql -uroot -p -P3307
Enter password: ******
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 10412
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from db1.t_user;
+----+------+-------+
| id | name | _slot |
+----+------+-------+
|3 | ccc| 32411 |
|5 | eee| 27566 |
+----+------+-------+
2 rows in set (0.00 sec)

最後更新於2018-10-0321:49:01 並被新增「mycat mysql」標籤，已有 2 位童鞋閱讀過。