一、 MySQL+MyCat分庫分表 1 MyCat簡介 java編寫的資料庫中間件 Mycat運行環境需要JDK. Mycat是中間件.運行在代碼應用和MySQL資料庫之間的應用. 前身 : corba. 是阿裡開發的資料庫中間件.實現MySQL資料庫分庫分表集群管理的中間件.曾經出現過重大事故. ...
一、 MySQL+MyCat分庫分表
1 MyCat簡介
java編寫的資料庫中間件
Mycat運行環境需要JDK.
Mycat是中間件.運行在代碼應用和MySQL資料庫之間的應用.
前身 : corba. 是阿裡開發的資料庫中間件.實現MySQL資料庫分庫分表集群管理的中間件.曾經出現過重大事故. 二次開發,形成Mycat.
使用MyCat之後,編寫的所有的SQL語句,必須嚴格遵守SQL標準規範.
insert into table_name(column_name) values(column_value);
使用MyCat中間件後的結構圖如下:
2 MyCat術語簡介
2.1 切分
邏輯上的切分. 在物理層面,是使用多庫[database],多表[table]實現的切分.
2.1.1 縱向切分
把一個資料庫切分成多個資料庫,配置方便
只能實現兩張表的表連接查詢.
將一張表中的數據,分散到若幹個database的同結構表中。多個表的數據的集合是當前表格的數據。
2.1.2 橫向切分
把一個表切分成多個表,相比縱向切分配置麻煩
無法實現表連接查詢.
將一張表的欄位,分散到若幹張表中,將若幹表連接到一起,才是當前表的完整數據。
2.2 邏輯庫
Mycat中定義的database.是邏輯上存在的.但是物理上未必存在.
主要是針對縱向切分提供的概念.
訪問MyCat,就是將MyCat當做MySQL使用。
Db資料庫是MyCat中定義的database。通過SQL訪問MyCat中的db庫的時候,對應的是MySQL中的db1,db2,db3三個庫。物理上的database是db1,db2,db3.邏輯上的database就是db。
2.3 邏輯表
Mycat中定義的table.是邏輯上存在,物理上未必存在.
主要是針對橫向切分提供的概念
MyCat中的表格table,其欄位分散到MySQL資料庫的表格table1,table2,table3中。
2.4 預設埠
Mycat預設埠是8066
2.5 數據主機 - dataHost
物理MySQL存放的主機地址.可以使用主機名,IP,功能變數名稱定義.
2.6 數據節點 - dataNode
物理的database是什麼.數據保存的物理節點.就是database.
2.7 分片規則
當控制數據的時候,如何訪問物理database和table.
就是訪問dataHost和dataNode的演算法.
在Mycat處理具體的數據CRUD的時候,如何訪問dataHost和dataNode的演算法.如:哈希演算法,crc16演算法等.
3 Mycat搭建
3.1 安裝JDK
略
3.2 主從備份搭建完成
3.3 安裝mycat
解壓縮: tar -zxf mycat-xxxx.tar.gz
3.4 Master提供可被Mycat訪問的用戶
在Mycat中通過Master資料庫的root用戶訪問Master資料庫.
grant all privileges on *.* to ‘username’@’ip’ identified by ‘password’ with grant option;
grant all privileges on *.* to 'mycat'@'%' identified by 'mycat' with grant option;
3.5 上傳mycat
Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
3.6 解壓縮
tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
3.7 Mycat配置文件詳解
Mycat所有的配置文件,都在應用的conf目錄中.
3.7.1 rule.xml
用於定義分片規則的配置文件.
主要是查看.很少修改.
mycat預設的分片規則: 以500萬為單位,實現分片規則.
邏輯庫A對應dataNode - db1和db2. 1-500萬保存在db1中, 500萬零1到1000萬保存在db2中,1000萬零1到1500萬保存在db1中.依次類推.
3.7.2 schema.xml
用於定義邏輯庫和邏輯表的配置文件.在配置文件中可以定義讀寫分離,邏輯庫,邏輯表,dataHost,dataNode等信息.
配置文件解釋:
3.7.2.1 標簽schema
配置邏輯庫的標簽
3.7.2.1.1 屬性name
邏輯庫名稱
3.7.2.1.2 屬性checkSQLschema
是否檢測SQL語法中的schema信息.
如: Mycat邏輯庫名稱 A, dataNode名稱B
SQL : select * from A.table;
checkSQLschema值是true, Mycat發送到資料庫的SQL是select * from table;
checkSQLschema值是false,Mycat發送的資料庫的SQL是select * from A.table;
3.7.2.1.3 sqlMaxLimit
Mycat在執行SQL的時候,如果SQL語法中沒有limit子句.自動增加limit子句. 避免一次性得到過多的數據,影響效率. limit子句的限制數量預設配置為100.如果SQL中有具體的limit子句,當前屬性失效.
SQL : select * from table . mycat解析後: select * from table limit 100
SQL : select * from table limit 10 . mycat不做任何操作修改.
3.7.2.2 標簽table
定義邏輯表的標簽,如果需要定義多個邏輯表,編寫多個table標簽。要求邏輯表的表名和物理表(MySQL資料庫中真實存在的表)的表名一致。
3.7.2.2.1 屬性name
邏輯表名
3.7.2.2.2 屬性dataNode
數據節點名稱. 配置文件中後續需要定義的標簽(即物理資料庫中的database名稱).多個名稱使用逗號分隔.
多個database定義後,代表分庫。
3.7.2.2.3 屬性rule
分片規則名稱.具體的規則名稱參考rule.xml配置文件.
SQL語句發送到Mycat中後,Mycat如何計算,應該將當期的SQL發送到哪一個物理資料庫管理系統或物理database中。
3.7.2.3 標簽dataNode
定義數據節點的標簽, 定義具體的物理database信息的。
3.7.2.3.1 屬性name
數據節點名稱, 是定義的邏輯名稱,對應具體的物理資料庫database
3.7.2.3.2 屬性dataHost
引用dataHost標簽的name值,代表使用的物理資料庫所在位置和配置信息.
3.7.2.3.3 屬性database
在dataHost物理機中,具體的物理資料庫database名稱.
3.7.2.4 dataHost標簽
定義數據主機的標簽, 就是物理MYSQL真實安裝的位置。
3.7.2.4.1 屬性name
定義邏輯上的數據主機名稱
3.7.2.4.2 屬性maxCon/minCon
最大連接數, max connections
最小連接數, min connections
3.7.2.4.3 屬性dbType
資料庫類型 : mysql資料庫
3.7.2.4.4 屬性dbDriver
資料庫驅動類型, native,使用mycat提供的本地驅動.
3.7.2.5 dataHost子標簽writeHost
寫數據的資料庫定義標簽. 實現讀寫分離操作.
3.7.2.5.1 屬性 host
資料庫命名
3.7.2.5.2 屬性url
資料庫訪問路徑
3.7.2.5.3 屬性user
資料庫訪問用戶名
3.7.2.5.4 屬性password
訪問用戶密碼
3.7.2.6 測試配置文件
3.7.3 server.xml
配置Mycat服務信息的.
如: Mycat中的用戶,用戶可以訪問的邏輯庫,可以訪問的邏輯表,服務的埠號等.
常見修改內容:
3.7.4 啟動Mycat命令
bin/mycat start
3.7.5 停止命令
bin/mycat stop
3.7.6 重啟命令
bin/mycat restart
3.7.7 查看Mycat狀態
bin/mycat status
3.7.8 訪問方式
可以使用命令行訪問或客戶端軟體訪問.
3.7.8.1 命令行訪問方式
mysql -u用戶名 -p密碼 -hmycat主機IP -P8066
鏈接成功後,可以當做MySQL資料庫使用.
訪問成功後,不能直接使用。因為Mycat只能訪問MYSQL的schema(database),不能自動創建邏輯庫對應的物理庫。且不能自動創建邏輯表對應的物理表。
必須人工鏈接master資料庫,手動創建database。
表格可以在mycat控制台創建。註意:在mycat控制台創建的表,必須是schema.xml配置文件中定義過的邏輯表。
啟動後,經過測試,crc32slot分片規則無效,執行DML語句的時候只能識別db1和db2。
DDL語句,可以識別db3。
修改conf/rule.xml配置文件,找標簽
修改count參數。修改為對應的物理database數量。
3.7.9 訪問約束
3.7.9.1 表約束
不能創建未在schema.xml中配置的邏輯表
3.7.9.2 DML約束
尤其是新增: 必須在insert into語法後攜帶所有的欄位名稱.至少攜帶主鍵名稱.
因為分片規則,絕大多數都是通過主鍵欄位計算數據分片規則的.
3.7.10 查看Mycat日誌
logs/wrapper.log
日誌中記錄的是所有的mycat操作. 查看的時候主要看異常信息caused by信息
二、 MyCat配置讀寫分離
1 MySQL主從備份
1.1 主從備份概念
什麼是主從備份: 就是一種主備模式的資料庫應用.
主庫(Master)數據與備庫(Slave)數據完全一致.
實現數據的多重備份, 保證數據的安全.
可以在Master[InnoDB]和Slave[MyISAM]中使用不同的資料庫引擎,實現讀寫的分離
1.1.1 MySQL5.5版本後本身支持主從備份
在老舊版本的MySQL資料庫系統中,不支持主從備份,需要安裝額外的RPM包.
如果需要安裝RPM,只能在一個位置節點安裝.
1.1.2 主從備份目的
1.1.2.1 實現主備模式
保證數據的安全. 儘量避免數據丟失的可能.
1.1.2.2 實現讀寫分離
使用不同的資料庫引擎,實現讀寫分離.提高所有的操作效率.
InnoDB使用DML語法操作. MyISAM使用DQL語法操作.
1.1.3 主從備份效果
1.1.3.1 主庫操作同步到備庫
所有對Master的操作,都會同步到Slave中.
如果Master和Salve天生上環境不同,那麼對Master的操作,可能會在Slave中出現錯誤
如: 在創建主從模式之前,Master有database : db1, db2, db3. Slave有database: db1, db2.
創建主從模式.現在的情況Master和Slave天生不同.
主從模式創建成功後,在Master中drop database db3. Slave中拋出資料庫SQL異常.後續所有的命令不能同步.
一旦出現錯誤. 只能重新實現主從模式.
1.2 安裝MySQL
略過.
1.3 主從備份配置
主要操作Master和Slave中的配置文件和DBMS的配置.
配置文件: 定義主從模式的基礎信息. 如: 日誌, 命令等.
DBMS配置: 提供主從訪問的用戶,基礎信息[Master和Slave的位置,用戶名,密碼,日誌文件名等]等.
建議:建立主從備份的多個MySQL,最好原始環境一致。Database,table,data完全一致。
1.3.1 Master[主庫]配置
1.3.1.1 修改Master配置文件
/etc/my.cnf
需要修改. 在修改前建議複製一份備份文件.
修改後的my.cnf配置文件,參考資料中的my.cnf文件內容.
1.3.1.1.1 server-id
本環境中server-id是1
MySQL服務唯一標識
唯一標識是數字. 自然數
配置的時候有要求
1.3.1.1.1.1 單機使用
server-id 任意配置,只要是數字即可
1.3.1.1.1.2 主從使用
server-id Master唯一標識數字必須小於Slave唯一標識數字.
1.3.1.1.2 log_bin
本環境中log_bin值 : master_log
日誌文件命名, 開啟日誌功能。此日誌是命令日誌。就是記錄主庫中執行的所有的SQL命令的。
1.3.1.1.2.1 開啟日誌
MySQL的log_bin不是執行日誌,狀態日誌. 是操作日誌.就是在DBMS中所有的SQL命令
log_bin日誌不是必要的.只有配置主從備份時才必要。
1.3.1.1.2.2 日誌文件配置
變數的值就是日誌文件名稱.是日誌文件名稱的主體.
MySQL資料庫自動增加文件名尾碼和文件類型.
1.3.1.2 重啟MySQL
service mysqld restart
1.3.1.3 配置Master
1.3.1.3.1 訪問MySQL
mysql -uusername -ppassword
1.3.1.3.2 創建用戶
在MySQL資料庫中,為不存在的用戶授權,就是同步創建用戶並授權.
此用戶是從庫訪問主庫使用的用戶
ip地址不能寫為%. 因為主從備份中,當前創建的用戶,是給從庫Slave訪問主庫Master使用的.用戶必須有指定的訪問地址.不能是通用地址.
1.3.1.3.3 查看用戶
use mysql;
select host, name from user;
1.3.1.3.4 查看Master信息
show master status;
1.3.2 Slave[從庫]配置
1.3.2.1 修改Slave配置文件
/etc/my.cnf
1.3.2.1.1 server_id
唯一標識, 本環境中配置為 : 2
1.3.2.1.2 log_bin
可以使用預設配置, 也可以註釋.
1.3.2.2 可選: 修改uuid
主從模式要求多個MySQL物理名稱不能相同. 即按裝MySQL過程中Linux自動生成的物理標誌. 唯一物理標誌命名為uuid. 保存位置是MySQL資料庫的數據存放位置. 預設為/var/lib/mysql目錄中. 文件名是auto.cnf.
修改auto.cnf文件中的uuid數據. 隨意修改,不建議改變數據長度.建議改變數據內容.
/var/lib/mysql/auto.cnf
1.3.2.3 重啟MySQL服務
service mysqld restart
1.3.2.4 配置Slave
1.3.2.4.1 訪問mysql
mysql -uusername -ppassword
1.3.2.4.2 停止Slave功能
stop slave
1.3.2.4.3 配置主庫信息
需要修改的數據是依據Master信息修改的. ip是Master所在物理機IP. 用戶名和密碼是Master提供的Slave訪問用戶名和密碼. 日誌文件是在Master中查看的主庫信息提供的.在Master中使用命令show master status查看日誌文件名稱.
change master to master_host=’ip’, master_user=’username’, master_password=’password’, master_log_file=’log_file_name’;
change master to master_host='192.168.199.212', master_user='slave', master_password='slave', master_log_file='master_log.000001';
1.3.2.4.4 啟動Slave功能
start slave;
1.3.2.4.5 查看Slave配置
show slave status \G;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.120.139
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 427
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 591
Relay_Master_Log_File: master-log.000001
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: 427
Relay_Log_Space: 765
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 最後一次錯誤的IO請求編號
Last_IO_Error:
Last_SQL_Errno: 0 最後一次錯誤的執行SQL命令編號.
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9ee988ac-8751-11e7-8a95-000c2953ac06
Master_Info_File: /var/lib/mysql/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)
1.3.3 測試主從
1.4 主從模式下的邏輯圖
2 MyCat讀寫分離配置
修改conf/schema.xml配置文件,下述內容中,紅色部分為重點內容。