Mycat 學習筆記-有解無憂

概述

1. Mycat 是什么？

Mycat 是資料庫中間件，連接 Java 應用程式和資料庫，它的作用如下：

讀寫分離
資料分片：垂直拆分（分庫）、水平拆分（分表）、垂直+水平拆分（分庫分表）
多資料源整合

2. Mycat 原理

Mycat 攔截了用戶發送過來的 SQL 陳述句，首先對 SQL 陳述句進行特定的分析：如分片分析、路由分析、讀寫分離分析、快取分析等，然后將該 SQL 發送到真實的資料庫，并處理回傳的結果，再回傳給用戶

Mycat 安裝啟動

1. 安裝

在 Mycat官網下載壓縮包，拷貝到 Linux 并解壓

tar -vxzf Mycat-server-1.6.7.6-release-20220221174943-linux.tar.gz

Mycat 有三個組態檔：

schema.xml：定義邏輯庫，表、分片節點等內容
rule.xml：定義分片規則
server.xml：定義用戶以及系統相關變數，如埠等

修改組態檔server.xml，修改用戶資訊，與 MySQL 區分，如下：

…
<user name="mycat">
	<property name="password">123456</property>
	<property name="schemas">TESTDB</property>
</user>
…

修改組態檔 schema.xml，如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="MYCAT_TEST_DB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1"></schema>

        <dataNode name="dn1" dataHost="host1" database="mycat_test_db" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root" password="123">
                        <readHost host="hostS1" url="jdbc:mysql://localhost:3307" user="root" password="123"/>
                </writeHost>
        </dataHost>
</mycat:schema>

接下來啟動程式，有兩種方式：

控制臺啟動：mycat/bin 目錄下執行 ./mycat console
后臺啟動：mycat/bin 目錄下 ./mycat start

2. 登錄

登錄后臺管理視窗，此登錄方式用于管理維護 Mycat

mysql -umycat -p123456 -P 9066 -h localhost

登錄資料視窗，此登錄方式用于通過 Mycat 查詢資料

mysql -umycat -p123456 -P 8066 -h localhost

Mycat 搭建讀寫分離

通過 Mycat 和 MySQL 的主從復制配合搭建資料庫的讀寫分離，實作 MySQL 的高可用性

1. 主從復制原理

主庫的更新事件（update、insert、delete）被寫到 binlog
主庫創建一個 binlog dump thread，把 binlog 的內容發送到從庫
從庫啟動并發起連接，連接到主庫
從庫啟動之后，創建一個 I/O 執行緒，讀取主庫傳過來的 binlog 內容并寫入到 relay log
從庫啟動之后，創建一個 SQL 執行緒，從 relay log 里面讀取內容，從 Exec_Master_Log_Pos 位置開始執行讀取到的更新事件，將更新內容寫入到 slave 的 db

1. 一主一從

一個主機用于處理所有寫請求，一臺從機負責所有讀請求，架構圖如下：

設定主機配置，修改組態檔：vim /etc/my.cnf

# 主服務器唯一ID
server-id=1
# 啟用二進制日志
log-bin=mysql-bin
# 設定不要復制的資料庫(可設定多個)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 設定需要復制的資料庫
binlog-do-db=需要復制的主資料庫名字
# 設定logbin格式
binlog_format=STATEMENT

binlog 有三種格式：

Statement：Statement 模式只記錄執行的 SQL，不需要記錄每一行資料的變化，因此極大的減少了 binlog 的日志量，避免了大量的 IO 操作，提升了系統的性能，但是，正是由于 Statement 模式只記錄 SQL，而如果一些 SQL 中包含了函式，那么可能會出現執行結果不一致的情況，比如說 uuid() 函式，每次執行的時候都會生成一個隨機字串，在 master 中記錄了 uuid，當同步到 slave 之后，再次執行，就得到另外一個結果了
Row：Row 格式不記錄 SQL 陳述句背景關系相關資訊，僅僅只需要記錄某一條記錄被修改成什么樣子了，Row 格式的日志內容會非常清楚地記錄下每一行資料修改的細節，這樣就不會出現 Statement 中存在的那種資料無法被正常復制的情況，不過 Row 格式也有一個很大的問題，那就是日志量太大了，特別是批量 update、整表 delete、alter 表等操作，由于要記錄每一行資料的變化，此時會產生大量的日志，大量的日志也會帶來 IO 性能問題
Mixed：在 Mixed 模式下，系統會自動判斷該用 Statement 還是 Row，一般的陳述句修改使用 Statement 格式保存 binlog；對于一些 Statement 無法準確完成主從復制的操作，則采用 Row 格式保存 binlog

設定從機配置，修改組態檔：vim /etc/my.cnf

#從服務器唯一ID
server-id=2
#啟用中繼日志
relay-log=mysql-relay

主機、從機重啟 MySQL 服務，并關閉防火墻

在主機上建立帳戶并授權 slave

# 在主機 MySQL 里執行授權命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

查詢 master 的狀態，記錄下 File 和 Position 的值，執行完此步驟后不要再操作主服務器 MySQL，防止主服務器狀態值變化

在從機上配置需要復制的主機

# 復制主機的命令
CHANGE MASTER TO MASTER_HOST='主機的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具體數字',MASTER_LOG_POS=具體值;
# 啟動從服務器復制功能
start slave;
# 查看從服務器狀態
show slave status\G;
# 停止從服務復制功能
stop slave;
# 重啟主機
reset master;

下面兩個引數都是 Yes，則說明主從配置成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

2. 雙主雙從

一個主機 m1 用于處理所有寫請求，它的從機 s1 和另一臺主機 m2 還有它的從機 s2 負責所有讀請求，當 m1 主機宕機后，m2 主機負責寫請求，m1、m2 互為備機，架構圖如下：

設定雙主機配置：Master1 和 Master2

#主服務器唯一ID
server-id=1
#啟用二進制日志
log-bin=mysql-bin
# 設定不要復制的資料庫(可設定多個)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#設定需要復制的資料庫
binlog-do-db=需要復制的主資料庫名字
#設定logbin格式
binlog_format=STATEMENT
# 在作為從資料庫的時候，有寫入操作也要更新二進制日志檔案
log-slave-updates 
#表示自增長欄位每次遞增的量，指自增欄位的起始值，其默認值是1，取值范圍是1 .. 65535
auto-increment-increment=2 
# 表示自增長欄位從哪個數開始，指欄位一次遞增多少，他的取值范圍是1 .. 65535
auto-increment-offset=1

#主服務器唯一ID
server-id=3
#啟用二進制日志
log-bin=mysql-bin
# 設定不要復制的資料庫(可設定多個)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#設定需要復制的資料庫
binlog-do-db=需要復制的主資料庫名字
#設定logbin格式
binlog_format=STATEMENT
# 在作為從資料庫的時候，有寫入操作也要更新二進制日志檔案
log-slave-updates 
#表示自增長欄位每次遞增的量，指自增欄位的起始值，其默認值是1，取值范圍是1 .. 65535
auto-increment-increment=2 
# 表示自增長欄位從哪個數開始，指欄位一次遞增多少，他的取值范圍是1 .. 65535
auto-increment-offset=2

雙主機配置：Slave1 和 Slave2

#從服務器唯一ID
server-id=2
#啟用中繼日志
relay-log=mysql-relay

#從服務器唯一ID
server-id=4
#啟用中繼日志
relay-log=mysql-relay

雙主機、雙從機重啟 mysql 服務，并且關閉防火墻

在兩臺主機上建立帳戶并授權 slave

# 在主機MySQL里執行授權命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

查詢 master 的狀態，分別記錄下 File 和 Position 的值，執行完此步驟后不要再操作主服務器 MySQL，防止主服務器狀態值變化

在從機上配置需要復制的主機，Slava1 復制 Master1，Slava2 復制 Master2，以及主機 Master2 的復制 Master1

# 復制主機的命令
CHANGE MASTER TO MASTER_HOST='主機的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具體數字',MASTER_LOG_POS=具體值;

3. 讀寫分離

修改 Mycat 的組態檔 schema.xml，配置對應的讀寫機，修改的 balance 屬性，通過此屬性配置讀寫分離的負載均衡型別，目前的取值有四種：

balance="0"：不開啟讀寫分離機制，所有讀操作都發送到當前可用的 writeHost 上
balance="1"：全部的 readHost 與 stand by writeHost 參與 select 陳述句的負載均衡，簡單的說，當雙主雙從模式（M1->S1，M2->S2，并且 M1 與 M2 互為主備），正常情況下，M2,S1,S2 都參與 select 陳述句的負載均衡
balance="2"：所有讀操作都隨機的在 writeHost、readhost 上分發
balance="3"：所有讀請求隨機的分發到 readhost 執行，writerHost 不負擔讀壓力

其他配置引數如下：

writeType="0"：所有寫操作發送到配置的第一個writeHost，第一個掛了切到還生存的第二個
writeType="1"：所有寫操作都隨機的發送到配置的 writeHost，1.5 以后廢棄不推薦

writeHost 以重新啟動后以切換后的為準，切換記錄在組態檔 dnindex.properties 中，設定引數switchType="1"：

1 默認值，自動切換
-1 表示不自動切換
2 基于 MySQL 主從同步的狀態決定是否切換

垂直分庫

一個資料庫由很多表的構成，每個表對應著不同的業務，垂直切分是指按照業務將表進行分類，分布到不同的資料庫上面，這樣也就將資料或者說壓力分擔到不同的庫上面

1. 分庫原則

由于在兩臺主機上的兩個資料庫中的表不能關聯查詢，所以有緊密關聯關系的表應該在一個庫里，相互沒有關聯關系的表可以分到不同的庫里

2. 分庫實作

假設現有四張表

# 客戶表 rows:20w
CREATE TABLE customer(
 id INT AUTO_INCREMENT,
 NAME VARCHAR(200),
 PRIMARY KEY(id)
);

# 訂單表 rows:600w
CREATE TABLE orders(
 id INT AUTO_INCREMENT,
 order_type INT,
 customer_id INT,
 amount DECIMAL(10,2),
 PRIMARY KEY(id)
);

# 訂單詳細表 rows:600w
CREATE TABLE orders_detail(
 id INT AUTO_INCREMENT,
 detail VARCHAR(2000),
 order_id INT,
 PRIMARY KEY(id)
);

# 訂單狀態字典表 rows:20w
CREATE TABLE dict_order_type(
 id INT AUTO_INCREMENT,
 order_type VARCHAR(200),
 PRIMARY KEY(id)
);

客戶表分在一個資料庫，另外三張都需要關聯查詢，所有分在另外一個資料庫

修改 schema 組態檔

...
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="orders" />
<dataNode name="dn2" dataHost="host2" database="orders" />
<dataHost name="host1" 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="hostM1" url="192.168.140.128:3306" user="root" password="123123"></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.140.127:3306" user="root" password="123123"></writeHost>
</dataHost>

在資料節點 dn1、dn2 上分別創建資料庫 orders

訪問 Mycat 進行分庫，切換到 TESTDB，創建對應的四張表，在對應節點查看表資訊，可以看到成功分庫

水平分表

相對于垂直拆分，水平拆分不是將表做分類，而是按照某個欄位的某種規則來分散到多個庫之中，每個表中包含一部分資料，簡單來說，我們可以將資料的水平切分理解為是按照資料行的切分，就是將表中的某些行切分到一個資料庫，而另外的某些行又切分到其他的資料庫中

1. 分表實作

以 orders 表為例，可以根據不同的欄位進行分表：

id（主鍵、或創建時間）：查詢訂單注重時效，歷史訂單被查詢的次數少，如此分片會造成一個節點訪問多，一個訪問少，不平均
customer_id（客戶 id）：根據客戶 id 去分，兩個節點訪問平均，一個客戶的所有訂單都在同一個節點

修改組態檔 schema.xml，為 orders 表設定資料節點為 dn1、dn2，并指定分片規則為 mod_rule（自定義的名字）

...
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2" ></table>
    <table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
</schema>
...

修改組態檔 rule.xml，在 rule 組態檔里新增分片規則 mod_rule，并指定規則適用欄位為 customer_id，指定分片演算法 mod-long（對欄位求模運算），customer_id 對兩個節點求模，根據結果分片

<tableRule name="mod_rule">
    <rule>
        <columns>customer_id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>
...
<function name="mod-long" >
    <!-- 表示有兩個節點 -->
    <property name="count">2</property>
</function>

在資料節點 dn2 上建 orders 表，重啟 Mycat，讓配置生效，訪問 Mycat 實作分片

# 在 mycat 里向 orders 表插入資料，INSERT 欄位不能省略
INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);

2. 分片的關聯查詢

2.1 ER 表

Orders 訂單表已經進行分表操作了，和它關聯的 orders_detail 訂單詳情表如果需要進行 join 查詢，那也要對 orders_detail 也要進行分片操作，原理如下：

Mycat 借鑒了 NewSQL 領域的新秀 Foundation DB 的設計思路，Foundation DB 創新性的提出了 Table Group 的概念，其將子表的存盤位置依賴于主表，并且物理上緊鄰存放，因此徹底解決了 JION 的效率和性能問題，根據這一思路，提出了基于 E-R 關系的資料分片策略，子表的記錄與所關聯的父表記錄存放在同一個資料分片上

修改 schema.xml 組態檔

<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
    <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>

在 dn2 創建 orders_detail 表，重啟 Mycat，訪問 Mycat 向 orders_detail 表插入數

INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);
# 在mycat、dn1、dn2中運行兩個表join陳述句
Select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;

2.2 全域表

在分片的情況下，當業務表因為規模而進行分片以后，業務表與這些附屬的字典表之間的關聯，就成了比較棘手的問題，考慮到字典表具有以下幾個特性：

變動不頻繁
資料量總體變化不大
資料規模不大，很少有超過數十萬條記錄

鑒于此，Mycat 定義了一種特殊的表，稱之為全域表，全域表具有以下特性：

全域表的插入、更新操作會實時在所有節點上執行，保持各個分片的資料一致性
全域表的查詢操作，只從一個節點獲取
全域表可以跟任何一個表進行 JOIN 操作

將字典表或者符合字典表特性的一些表定義為全域表，可以很好的解決了資料 JOIN 的難題

修改 schema.xml 組態檔

...
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
    <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
...

在 dn2 創建 dict_order_type 表，重啟 Mycat，訪問 Mycat 向 dict_order_type 表插入資料

INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');

3. 常用的分片規則

3.1 取模

此規則為對分片欄位求摸運算，也是水平分表最常用規則，上述對 orders 表就采用了此規則

3.2 分片列舉

通過在組態檔中配置可能的列舉 id，自己配置分片，本規則適用于特定的場景，比如有些業務需要按照省份或區縣來做保存，而全國省份區縣固定的，這類業務使用本條規則

修改 schema.xml 組態檔

<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>

修改 rule.xml 組態檔

<tableRule name="sharding_by_intfile">
    <rule>
        <columns>areacode</columns>
        <algorithm>hash-int</algorithm>
    </rule>
</tableRule>
...
<function name="hash-int" >
    <property name="mapFile">partition-hash-int.txt</property>
    <property name="type">1</property>
    <property name="defaultNode">0</property>
</function>

引數說明如下：

columns：分片欄位
algorithm：分片函式
mapFile：標識組態檔名稱
type：0 為 int 型，非 0 為 String
defaultNode：默認節點，小于 0 表示不設定默認節點，大于等于 0 表示設定默認節點，設定默認節點如果碰到不識別的列舉值，就讓它路由到默認節點，如不設定不識別就報錯

修改 partition-hash-int.txt 組態檔

110=0
120=1

重啟 Mycat，訪問 Mycat 創建表

# 訂單歸屬區域資訊表
CREATE TABLE orders_ware_info
(
    `id` INT AUTO_INCREMENT comment '編號',
    `order_id` INT comment '訂單編號',
    `address` VARCHAR(200) comment '地址',
    `areacode` VARCHAR(20) comment '區域編號',
    PRIMARY KEY(id)
);
# 插入資料
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (1,1,'北京','110');
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (2,2,'天津','120');

3.3 范圍約定

此分片適用于，提前規劃好分片欄位某個范圍屬于哪個分片

修改 schema.xml 組態檔

<table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long" ></table>

修改 rule.xml 組態檔

<tableRule name="auto_sharding_long">
    <rule>
        <columns>order_id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>
...
<function name="rang-long" >
    <property name="mapFile">autopartition-long.txt</property>
    <property name="defaultNode">0</property>
</function>

引數說明如下：

columns：分片欄位
algorithm：分片函式
mapFile：標識組態檔名稱
defaultNode：默認節點，小于 0 表示不設定默認節點，大于等于 0 表示設定默認節點，設定默認節點如果碰到不識別的列舉值，就讓它路由到默認節點，如不設定不識別就報錯

修改 autopartition-long.txt 組態檔

0-102=0
103-200=1

重啟 Mycat，訪問 Mycat 創建表

# 支付資訊表 
CREATE TABLE payment_info
(
    `id` INT AUTO_INCREMENT comment '編號',
    `order_id` INT comment '訂單編號',
    `payment_status` INT comment '支付狀態',
    PRIMARY KEY(id)
);
# 插入資料
INSERT INTO payment_info (id,order_id,payment_status) VALUES (1,101,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (2,102,1);
INSERT INTO payment_info (id,order_id ,payment_status) VALUES (3,103,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (4,104,1);

3.4 按日期（天）分片

此規則為按天分片，設定時間格式、范圍

修改 schema.xml 組態檔

<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date" ></table>

修改 rule.xml 組態檔

<tableRule name="sharding_by_date">
    <rule>
        <columns>login_date</columns>
        <algorithm>shardingByDate</algorithm>
    </rule>
</tableRule>
...
<function name="shardingByDate" >
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="sBeginDate">2019-01-01</property>
    <property name="sEndDate">2019-01-04</property>
    <property name="sPartionDay">2</property>
</function>

引數說明如下：

columns：分片欄位
algorithm：分片函式
dateFormat：日期格式
sBeginDate：開始日期
sEndDate：結束日期，代表資料達到了這個日期的分片后回圈從開始分片插入
sPartionDay：磁區天數，即默認從開始日期算起，分隔兩天一個磁區

重啟 Mycat，訪問 Mycat 創建表

# 用戶資訊表 
CREATE TABLE login_info
(
    `id` INT AUTO_INCREMENT comment '編號',
    `user_id` INT comment '用戶編號',
    `login_date` date comment '登錄日期',
    PRIMARY KEY(id)
); 
# 插入資料
INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,'2019-01-01');
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,'2019-01-02');
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,'2019-01-03');
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,'2019-01-04');
INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,'2019-01-05');
INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,'2019-01-06');

4. 全域序列

在實作分庫分表的情況下，資料庫自增主鍵已無法保證自增主鍵的全域唯一，為此，Mycat 提供了全域 sequence，并且提供了包含本地配置和資料庫配置等多種實作方式

4.1 本地檔案

此方式 Mycat 將 sequence 配置到檔案中，當使用到 sequence 中的配置后，Mycat 會取 classpath 中的 sequence_conf.properties 檔案中 sequence 當前的值

優點：本地加載，讀取速度較快
缺點：抗風險能力差，Mycat 所在主機宕機后，無法讀取本地檔案

4.2 資料庫方式

利用資料庫一個表來進行計數累加，但是并不是每次生成序列都讀寫資料庫，這樣效率太低，Mycat 會預加載一部分號段到 Mycat 的記憶體中，這樣大部分讀寫序列都是在記憶體中完成的，如果記憶體中的號段用完了 Mycat 會再向資料庫要一次

建庫序列腳本如下：

#在 dn1 上創建全域序串列
CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT
NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB;
# 創建全域序列所需函式
DELIMITER $$ 
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC 
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS 
VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = https://www.cnblogs.com/Yee-Q/archive/2022/03/28/VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) 
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;
# 初始化序串列記錄
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,
100);

修改 sequence_db_conf.properties 的配置：vim sequence_db_conf.properties

# 意思是 ORDERS 這個序列在 dn1 這個節點上
ORDERS=dn1

修改 server.xml

<property name="sequnceHandlerType">1</property>

全域序列型別：

0：本地檔案
1：資料庫方式
2：時間戳方式

重啟 Mycat，登錄 Mycat，插入資料

insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);

4.3 時間戳方式

全域序列 ID = 64 位二進制(42(毫秒) + 5(機器 ID) + 5(業務編碼) + 12(重復累加) 換算成十進制為 18 位數的 long 型別，每毫秒可以并發 12 位二進制的累加

優點：配置簡單
缺點：18 位 ID 過長

4.4 自主生成全域序列

在 Java 專案里自己生成全域序列，可以利用 redis 的單執行緒原子性 incr 來生成序列

Mycat 高可用

在實際專案中，Mycat 服務也需要考慮高可用性，如果 Mycat 所在服務器出現宕機，或 Mycat 服務故障，需要有備機提供服務，需要考慮 Mycat 集群

1. 高可用方案

我們可以使用 HAProxy + Keepalived 配合兩臺 Mycat 搭起 Mycat 集群，實作高可用性，HAProxy 實作了 MyCat 多節點的集群高可用和負載均衡，而 HAProxy 自身的高可用則可以通過 Keepalived 來實作

編號	角色	IP 地址	機器名
1	Mycat1	192.168.140.128	host79
2	Mycat2	192.168.140.127	host80
3	HAProxy（master）	192.168.140.126	host81
4	Keepalived（master）	192.168.140.125	host82
5	HAProxy（backup）	192.168.140.124	host83
6	Keepalived（backup）	192.168.140.123	host84

2. HAProxy 安裝配置

下載 HAProxy 安裝包并解壓，進入解壓后的目錄，查看內核版本，進行編譯

# 查看內核版本
uname -r
# 進行編譯
# ARGET=linux310 內核版本,如:3.10.0-514.el7,此時該引數就為linux310
# ARCH=x86_64，系統位數；
# PREFIX=/usr/local/haprpxy 為 haprpxy 安裝路徑
make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64
# 編譯完成后進行安裝
make install PREFIX=/usr/local/haproxy
# 安裝完成后，創建目錄和 HAProxy 組態檔
mkdir -p /usr/data/haproxy/
vim /usr/local/haproxy/haproxy.conf

向組態檔中插入以下配置資訊并保存

global
    log 127.0.0.1 local0
    #log 127.0.0.1 local1 notice
    #log loghost local0 info
    maxconn 4096
    chroot /usr/local/haproxy
    pidfile /usr/data/haproxy/haproxy.pid
    uid 99
    gid 99
    daemon
    #debug
    #quiet
defaults
    log global
    mode tcp
    option abortonclose
    option redispatch
    retries 3
    maxconn 2000
    timeout connect 5000
    timeout client 50000
    timeout server 50000
listen proxy_status 
	bind :48066
		mode tcp
        balance roundrobin
        server mycat_1 192.168.140.128:8066 check inter 10s
        server mycat_2 192.168.140.127:8066 check inter 10s
frontend admin_stats 
	bind :7777
        mode http
        stats enable
        option httplog
        maxconn 10
        stats refresh 30s
        stats uri /admin
        stats auth admin:123123
        stats hide-version
        stats admin if TRUE

啟動驗證

# 啟動HAProxy
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
# 查看 HAProxy 行程
ps -ef|grep haproxy

打開瀏覽器訪問 http://192.168.140.125:7777/admin，在彈出框輸入用戶名：admin，密碼：123123

通過 HAProxy 訪問 Mycat

mysql -umycat -p123456 -h 192.168.140.126 -P 48066

3. KeepAlive 安裝配置

下載 KeepAlive 安裝包并解壓，進入解壓后的目錄

# 安裝依賴插件
yum install -y gcc openssl-devel popt-devel
# 進入解壓后的目錄進行配置和編譯
cd /usr/local/src/keepalived-1.4.2
./configure --prefix=/usr/local/keepalived
# 編譯完成后進行安裝
make && make install
# 運行前配置
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

修改組態檔：vim /etc/keepalived/keepalived.conf

# 修改內容如下
! Configuration File for keepalived

global_defs {
	notification_email {
		[email protected]
    }
    notification_email_from [email protected]
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id LVS_DEVEL
    vrrp_skip_check_adv_addr
    vrrp_garp_interval 0
    vrrp_gna_interval 0
}

vrrp_instance VI_1 {
	# 主機配 MASTER,備機配 BACKUP
	state MASTER
	# 所在機器網卡
	interface ens33
	virtual_router_id 51
	# 數值越大優先級越高
    priority 100
    advert_int 1
    authentication {
    	auth_type PASS
    	auth_pass 1111
 	}
	virtual_ipaddress {
	# 虛擬IP
 		192.168.140.200
 	}
}

virtual_server 192.168.140.200 48066 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP
    real_server 192.168.140.125 48066 {
    	weight 1
     	TCP_CHECK {
     		connect_timeout 3
     		retry 3
     		delay_before_retry 3
     	}
    }
    real_server 192.168.140.126 48600 {
 		weight 1
 		TCP_CHECK {
 			connect_timeout 3
 			nb_get_retry 3
 			delay_before_retry 3
 		}
 	}
}

啟動驗證

service keepalived start

登錄驗證

mysql -umycat -p123456 -h 192.168.140.200 -P 48066

Mycat 安全設定

1. 權限配置

1.1 user 標簽權限控制

Mycat 對于中間件的連接控制并沒有做太復雜的控制，只做了中間件邏輯庫級別的讀寫權限控制，通過 server.xml 的 user 標簽進行配置

# server.xml組態檔user部分
<user name="mycat">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
</user>
<user name="user">
     <property name="password">user</property>
     <property name="schemas">TESTDB</property>
     <property name="readOnly">true</property>
</user>

配置說明：

name：應用連接中間件邏輯庫的用戶名
password：該用戶對應的密碼
TESTDB：應用當前連接的邏輯庫中所對應的邏輯表，schemas 中可以配置一個或多個
readOnly：應用連接中間件邏輯庫所具有的權限，true 為只讀，false 為讀寫都有，默認為 false

1.2 privileges 標簽權限控制

在 user 標簽下的 privileges 標簽可以對邏輯庫（schema）、表（table）進行精細化的 DML 權限控制

privileges 標簽下的 check 屬性，如為 true 開啟權限檢查，為 false 不開啟，默認為 false，由于 Mycat 一個用戶的 schemas 屬性可配置多個邏輯庫（schema），所以 privileges 的下級節點 schema 節點同樣可配置多個，對多庫多表進行細粒度的 DML 權限控制

# server.xml組態檔privileges部分
# 配置orders表沒有增刪改查權限
<user name="mycat">
    <property name="password">123456</property>
    <property name="schemas">TESTDB</property>
 	<!-- 表級 DML 權限設定 -->
	<privileges check="true">
		<schema name="TESTDB" dml="1111" >
			<table name="orders" dml="0000"></table>
			<!--<table name="tb02" dml="1111"></table>-->
		</schema>
	</privileges>
</user>

配置說明：

DML 權限	增加（insert）	更新（update）	查詢（select）	洗掉（select）
0000	禁止	禁止	禁止	禁止
0010	禁止	禁止	可以	禁止
1110	可以	禁止	禁止	禁止
1111	可以	可以	可以	可以

2. SQL 攔截

firewall 標簽用來定義防火墻，firewall 下 whitehost 標簽用來定義 IP 白名單，blacklist 用來定義 SQL 黑名單

2.1 白名單

# 配置只有 192.168.140.128 主機可以通過 mycat 用戶訪問
<firewall>
    <whitehost>
        <host host="192.168.140.128" user="mycat"/>
    </whitehost>
</firewall>

2.2 黑名單

可以通過設定黑名單，實作 Mycat 對具體 SQL 操作的攔截，如增刪改查等操作的攔截

# 配置禁止mycat用戶進行洗掉操作
<firewall>
    <whitehost>
        <host host="192.168.140.128" user="mycat"/>
    </whitehost>
    <blacklist check="true">
        <property name="deleteAllow">false</property>
    </blacklist>
</firewall>

可以設定的黑名單 SQL 攔截功能串列

配置項	預設值	描述
selelctAllow	true	是否允許執行 SELECT 陳述句
deleteAllow	true	是否允許執行 DELETE 陳述句
updateAllow	true	是否允許執行 UPDATE 陳述句
insertAllow	true	是否允許執行 INSERT 陳述句
createTableAllow	true	是否允許創建表
setAllow	true	是否允許使用 SET 語法
alterTableAllow	true	是否允許執行 Alter Table 陳述句
dropTableAllow	true	是否允許修改表
commitAllow	true	是否允許執行 commit 操作
rollbackAllow	true	是否允許執行 roll back 操作

Mycat 監控工具

1. Mycat-web 簡介

Mycat-web 是 Mycat 可視化運維的管理和監控平臺，彌補了 Mycat 在監控上的空白，幫 Mycat 分擔統計任務和配置管理任務，Mycat-web 引入了 ZooKeeper 作為配置中心，可以管理多個節點， Mycat-web 主要管理和監控 Mycat 的流量、連接、活動執行緒和記憶體等，具備 IP 白名單、郵件告警等模塊，還可以統計 SQL 并分析慢 SQL 和高頻 SQL 等，為優化 SQL 提供依據，

2. Mycat-web 配置使用

首先安裝 Zookeeper，下載安裝包并解壓，進入 ZooKeeper 解壓后的配置目錄（conf），復制組態檔并改名

cp zoo_sample.cfg zoo.cfg

進入 ZooKeeper 的命令目錄（bin），運行啟動命令

./zkServer.sh start

ZooKeeper 服務埠為 2181，查看服務已經啟動

netstat -ant | grep 2181

3. Mycat-web 安裝

下載安裝包并解壓，進入解壓目錄下運行啟動命令

./start.sh &

Mycat-web 服務埠為 8082，查看服務已經啟動

netstat -ant | grep 8082

通過地址訪問服務：http://192.168.140.127:8082/mycat/

先在注冊中心配置 ZooKeeper 地址，配置后重繪頁面，可以看到配置頁面

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/450422.html

標籤：其他

上一篇：MYSQL8.0版本安裝失敗!

下一篇：GoogleAppEngine標準環境是否與next/image兼容？