Mycat是數據庫中介軟體,連線java應用程式和數據庫。
底層:由java語言編寫。
功能:讀寫分離、數據分片(分庫分表)、多數據源整合。
原理:攔截。
1、解壓後即可使用,解壓縮檔案拷貝到linux下/usr/local/
三個組態檔:
①schema.xml:定義邏輯庫,表、分片節點等內容。
②rule.xml:定義分片規則。
③server.xml:定義使用者以及系統相關變數,如埠等·
2、編輯組態檔
修改組態檔 server.xml
修改組態檔 schema.xml
驗證數據庫存取情況
mysql -umycat -p123123 -h ip -P 3306
3、啓動程式
1、登錄後臺管理視窗
mysql -umycat -p123123 -h ip -P 9066
常用命令:
show database
show @@help
2、登錄數據視窗
mysql -umycat -p123123 -h ip -P 8066
第一步:主機設定
修改組態檔: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
#日誌的三種格式:
statement:會產生數據不一致問題。
row:記錄改變的值,若是全部更新,記錄量太大。
mixed:自動匹配,解決了一部分數據不一致問題,但是無法識別系統變數。
第二步:從機設定
修改組態檔:vim /etc/my.cnf
#從伺服器唯一ID
server-id=2
#啓用中繼日誌
relay-log=mysql-relay
第三步:建立賬號授權
#在主機MySQL裡執行授權命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
#查詢master的狀態,記錄下File和Position的值。
show master status;
第四步:在從機上設定需要複製的主機
#複製主機的命令
CHANGE MASTER TO MASTER_HOST='主機的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具體數位',MASTER_LOG_POS=具體值;
#啓動從伺服器複製功能
start slave;
#檢視從伺服器狀態,顯示兩個YES即設定成功
show slave status\G;
第五步:重新設定主從
#停止主從複製功能
stop slave;
#重新設定主從
stop slave;
reset master;
第六步:設定讀寫分離
#驗證讀寫分離
在寫主機插入:insert into mytbl values (1,@@hostname);
在Mycat裡查詢:select * from mytbl;
#修改<dataHost>的balance屬性,通過此屬性設定讀寫分離的型別
(1)balance="0", 不開啓讀寫分離機制 機製,所有讀操作都發送到當前可用的 writeHost 上。
(2)balance="1",全部的 readHost 與 stand by writeHost 參與 select 語句的負載均衡。
(3)balance="2",所有讀操作都隨機的在 writeHost、readhost 上分發。
(4)balance="3",所有讀請求隨機的分發到 readhost 執行,writerHost 不負擔讀壓力
主機設定(額外加入):
# 在作爲從數據庫的時候,有寫入操作也要更新二進制日誌檔案
log-slave-updates
#表示自增長欄位每次遞增的量,指自增欄位的起始值,其預設值是1,取值範圍是1 .. 65535
auto-increment-increment=2
# 表示自增長欄位從哪個數開始,指欄位一次遞增多少,他的取值範圍是1 .. 65535
auto-increment-offset=1
注意:兩臺主機也需要設定互相複製。
balance="1": 全部的readHost與stand by writeHost參與select語句的負載均衡。
writeType="0": 所有寫操作發送到設定的第一個writeHost,第一個掛了切到還生存的第二個。
writeType="1",所有寫操作都隨機的發送到設定的writeHost。
switchType="1": 1 預設值,自動切換。
-1 表示不自動切換。
2 基於 MySQL 主從同步的狀態決定是否切換。
分庫原則:有緊密關聯關係的表應該在一個庫裡,相互沒有關聯關係的表可以分到不同的庫裡。
修改schema 組態檔:
分庫原則:按照某個欄位的某種規則來分散到多個庫之中,每個表中 包含一部分數據。
#修改schema 組態檔
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
#修改組態檔 rule.xml
<tableRule name="mod_rule">
<rule>
<columns>customer_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>
#修改 schema.xml 組態檔
…
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
…
#修改 schema.xml 組態檔
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
分片列舉:
#修改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"class="io.mycat.route.function.PartitionByFileMap">
<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
範圍約定:
#修改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" class="io.mycat.route.function.AutoPartitionByLong">
<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
日期分片:
#修改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" class="io.mycat.route.function.PartitionByDate">
<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 :分割區天數,即預設從開始日期算起,分隔 2 天一個分割區
1、本地檔案(sequence_conf.properties)
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 = 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);
#修改 Mycat 設定(修改sequence_db_conf.properties,指定序列數據庫是哪個)
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
#修改server.xml(全域性序列型別:0-本地檔案,1-數據庫方式,2-時間戳方式)
<property name="sequnceHandlerType">1<property>
#驗證全域性序列(注意id不是自己填的,而是序列庫裡自動分配的)
insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);
3、時間戳方式
方案:HAProxy實現mycat多節點的叢集高可用和負載均衡,Keepalived實現HAProxy自身的高可用。
1、安裝HAProxy
#準備HAProxy安裝包,傳到/opt目錄下
#解壓到/usr/local/src
tar -zxvf haproxy-1.5.18.tar.gz -C /usr/local/src
cd /usr/local/src/haproxy-1.5.18
#ARGET=linux310,內核版本,使用uname -r檢視內核,如:3.10.0-514.el7,此時該參數就爲linux310; #ARCH=x86_64,系統位數;
uname -r
make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64
make install PREFIX=/usr/local/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 ip1:8066 check inter 10s
server mycat_2 ip2: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
2、啓動HAProxy
#啓動HAProxy
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
#檢視HAProxy進程
ps -ef|grep haproxy
#開啓瀏覽器存取
http://ip:7777/admin
#驗證負載均衡,通過HAProxy存取Mycat
mysql -umycat -p123456 -h ip -P 48066
3、安裝 Keepalived
#準備Keepalived安裝包,傳到/opt目錄下
#解壓到/usr/local/src
tar -zxvf keepalived-1.4.2.tar.gz -C /usr/local/src
#安裝依賴外掛
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 {
xlcocoon@foxmail.com
}
notification_email_from
keepalived@showjoy.com
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 ip1 48066 {
weight 1
TCP_CHECK {
connect_timeout 3
retry 3
delay_before_retry 3
}
}
real_server ip2 48600 {
weight 1
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
4、啓動Keepalived
#啓動Keepalived
service keepalived start
#登錄
mysql -umycat -p123456 -h ip -P 48066
1、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
2、 privileges 標籤許可權控制
<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許可權爲四位數位(0000、0010、1110、1111),分別對應增加、更新、查詢、刪除。
3、SQL攔截
<firewall>
#白名單
<whitehost>
<host host="192.168.140.128" user="mycat"/>
</whitehost>
#黑名單
<blacklist check="true">
<property name="deleteAllow">false</property>
</blacklist>
</firewall>
Mycat-web 是 Mycat 視覺化運維的管理和監控平臺,彌補了 Mycat 在監控上的空白。幫 Mycat 分擔統計任務和設定管理任務。Mycat-web 引入了 ZooKeeper 作爲設定中心,可以管理多個節點。Mycat-web 主要管理和監控 Mycat 的流量、連線、活動執行緒和記憶體等,具備 IP 白名單、郵件告警等模組,還可以統計 SQL 並分析慢 SQL 和高頻 SQL 等。爲優化 SQL 提供依據。
1、ZooKeeper 安裝
#下載安裝包
官網:http://zookeeper.apache.org/
#安裝包拷貝到Linux系統/opt目錄下,並解壓
tar -zxvf zookeeper-3.4.11.tar.gz
cp zoo_sample.cfg zoo.cfg
#啓動
./zkServer.sh start
#ZooKeeper伺服器端口爲2181,檢視服務是否已經啓動
netstat -ant | grep 2181
2、Mycat-web 安裝
#下載安裝包
官網:http://www.mycat.io/
#安裝包拷貝到Linux系統/opt目錄下,並解壓
tar -zxvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
#拷貝mycat-web資料夾到/usr/local目錄下
cp -r mycat-web /usr/local
#4 啓動
cd /usr/local/mycat-web/
./start.sh &
#Mycat-web伺服器端口爲8082,檢視服務是否已經啓動
netstat -ant | grep 8082
#6 通過地址存取服務
http://ip:8082/mycat/