一、 MySQL+MyCat分庫(kù)分表
1 MyCat簡(jiǎn)介
java編寫的數(shù)據(jù)庫(kù)中間件
Mycat運(yùn)行環(huán)境需要JDK。
Mycat是中間件，運(yùn)行在代碼應(yīng)用和MySQL數(shù)據(jù)庫(kù)之間的應(yīng)用。
前身： corba，是阿里開發(fā)的數(shù)據(jù)庫(kù)中間件，實(shí)現(xiàn)MySQL數(shù)據(jù)庫(kù)分庫(kù)分表集群管理的中間件，曾經(jīng)出現(xiàn)過(guò)重大事故，二次開發(fā)，形成Mycat。
使用MyCat之后，編寫的所有的SQL語(yǔ)句，必須嚴(yán)格遵守SQL標(biāo)準(zhǔn)規(guī)范。
- insert into table_name(column_name) values(column_value);
使用MyCat中間件后的結(jié)構(gòu)圖如下：
2 MyCat術(shù)語(yǔ)簡(jiǎn)介
2.1 切分
邏輯上的切分. 在物理層面,是使用多庫(kù)[database],多表[table]實(shí)現(xiàn)的切分.
2.1.1 縱向切分
把一個(gè)數(shù)據(jù)庫(kù)切分成多個(gè)數(shù)據(jù)庫(kù),配置方便
只能實(shí)現(xiàn)兩張表的表連接查詢.
將一張表中的數(shù)據(jù)，分散到若干個(gè)database的同結(jié)構(gòu)表中。多個(gè)表的數(shù)據(jù)的集合是當(dāng)前表格的數(shù)據(jù)。
2.1.2 橫向切分
把一個(gè)表切分成多個(gè)表,相比縱向切分配置麻煩
無(wú)法實(shí)現(xiàn)表連接查詢.
將一張表的字段，分散到若干張表中，將若干表連接到一起，才是當(dāng)前表的完整數(shù)據(jù)。
2.2 邏輯庫(kù)
Mycat中定義的database.是邏輯上存在的.但是物理上未必存在.
主要是針對(duì)縱向切分提供的概念.
訪問(wèn)MyCat，就是將MyCat當(dāng)做MySQL使用。
Db數(shù)據(jù)庫(kù)是MyCat中定義的database。通過(guò)SQL訪問(wèn)MyCat中的db庫(kù)的時(shí)候，對(duì)應(yīng)的是MySQL中的db1，db2，db3三個(gè)庫(kù)。物理上的database是db1，db2，db3.邏輯上的database就是db。
2.3 邏輯表
Mycat中定義的table.是邏輯上存在,物理上未必存在.
主要是針對(duì)橫向切分提供的概念
MyCat中的表格table，其字段分散到MySQL數(shù)據(jù)庫(kù)的表格table1,table2,table3中。
2.4 默認(rèn)端口
Mycat默認(rèn)端口是8066
2.5 數(shù)據(jù)主機(jī) - dataHost
物理MySQL存放的主機(jī)地址.可以使用主機(jī)名,IP,域名定義.
2.6 數(shù)據(jù)節(jié)點(diǎn) - dataNode
物理的database是什么.數(shù)據(jù)保存的物理節(jié)點(diǎn).就是database.
2.7 分片規(guī)則
當(dāng)控制數(shù)據(jù)的時(shí)候,如何訪問(wèn)物理database和table.
就是訪問(wèn)dataHost和dataNode的算法.
在Mycat處理具體的數(shù)據(jù)CRUD的時(shí)候,如何訪問(wèn)dataHost和dataNode的算法.如:哈希算法,crc16算法等.
3 Mycat搭建
3.1 安裝JDK
略
3.2 主從備份搭建完成
3.3 安裝mycat
解壓縮: tar -zxf mycat-xxxx.tar.gz
3.4 Master提供可被Mycat訪問(wèn)的用戶
在Mycat中通過(guò)Master數(shù)據(jù)庫(kù)的root用戶訪問(wèn)Master數(shù)據(jù)庫(kù).
- 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所有的配置文件,都在應(yīng)用的conf目錄中.
3.7.1 rule.xml
用于定義分片規(guī)則的配置文件.
主要是查看.很少修改.
mycat默認(rèn)的分片規(guī)則: 以500萬(wàn)為單位,實(shí)現(xiàn)分片規(guī)則.
邏輯庫(kù)A對(duì)應(yīng)dataNode - db1和db2. 1-500萬(wàn)保存在db1中, 500萬(wàn)零1到1000萬(wàn)保存在db2中,1000萬(wàn)零1到1500萬(wàn)保存在db1中.依次類推.
3.7.2 schema.xml
用于定義邏輯庫(kù)和邏輯表的配置文件.在配置文件中可以定義讀寫分離,邏輯庫(kù),邏輯表,dataHost,dataNode等信息.
配置文件解釋:
3.7.2.1 標(biāo)簽schema
配置邏輯庫(kù)的標(biāo)簽
3.7.2.1.1 屬性name
邏輯庫(kù)名稱
3.7.2.1.2 屬性checkSQLschema
是否檢測(cè)SQL語(yǔ)法中的schema信息.
如: Mycat邏輯庫(kù)名稱 A, dataNode名稱B
SQL : select * from A.table;
checkSQLschema值是true, Mycat發(fā)送到數(shù)據(jù)庫(kù)的SQL是select * from table;
checkSQLschema值是false,Mycat發(fā)送的數(shù)據(jù)庫(kù)的SQL是select * from A.table;
3.7.2.1.3 sqlMaxLimit
Mycat在執(zhí)行SQL的時(shí)候,如果SQL語(yǔ)法中沒(méi)有l(wèi)imit子句.自動(dòng)增加limit子句. 避免一次性得到過(guò)多的數(shù)據(jù),影響效率. limit子句的限制數(shù)量默認(rèn)配置為100.如果SQL中有具體的limit子句,當(dāng)前屬性失效.
SQL : select * from table . mycat解析后: select * from table limit 100
SQL : select * from table limit 10 . mycat不做任何操作修改.
3.7.2.2 標(biāo)簽table
定義邏輯表的標(biāo)簽，如果需要定義多個(gè)邏輯表，編寫多個(gè)table標(biāo)簽。要求邏輯表的表名和物理表（MySQL數(shù)據(jù)庫(kù)中真實(shí)存在的表）的表名一致。
3.7.2.2.1 屬性name
邏輯表名
3.7.2.2.2 屬性dataNode
數(shù)據(jù)節(jié)點(diǎn)名稱. 配置文件中后續(xù)需要定義的標(biāo)簽（即物理數(shù)據(jù)庫(kù)中的database名稱）.多個(gè)名稱使用逗號(hào)分隔.
多個(gè)database定義后，代表分庫(kù)。
3.7.2.2.3 屬性rule
分片規(guī)則名稱.具體的規(guī)則名稱參考rule.xml配置文件.
SQL語(yǔ)句發(fā)送到Mycat中后，Mycat如何計(jì)算，應(yīng)該將當(dāng)期的SQL發(fā)送到哪一個(gè)物理數(shù)據(jù)庫(kù)管理系統(tǒng)或物理database中。
3.7.2.3 標(biāo)簽dataNode
定義數(shù)據(jù)節(jié)點(diǎn)的標(biāo)簽， 定義具體的物理database信息的。
3.7.2.3.1 屬性name
數(shù)據(jù)節(jié)點(diǎn)名稱, 是定義的邏輯名稱,對(duì)應(yīng)具體的物理數(shù)據(jù)庫(kù)database
3.7.2.3.2 屬性dataHost
引用dataHost標(biāo)簽的name值,代表使用的物理數(shù)據(jù)庫(kù)所在位置和配置信息.
3.7.2.3.3 屬性database
在dataHost物理機(jī)中,具體的物理數(shù)據(jù)庫(kù)database名稱.
3.7.2.4 dataHost標(biāo)簽
定義數(shù)據(jù)主機(jī)的標(biāo)簽， 就是物理MYSQL真實(shí)安裝的位置。
3.7.2.4.1 屬性name
定義邏輯上的數(shù)據(jù)主機(jī)名稱
3.7.2.4.2 屬性maxCon/minCon
***連接數(shù), max connections
最小連接數(shù), min connections
3.7.2.4.3 屬性dbType
數(shù)據(jù)庫(kù)類型 : mysql數(shù)據(jù)庫(kù)
3.7.2.4.4 屬性dbDriver
數(shù)據(jù)庫(kù)驅(qū)動(dòng)類型, native,使用mycat提供的本地驅(qū)動(dòng).
3.7.2.5 dataHost子標(biāo)簽writeHost
寫數(shù)據(jù)的數(shù)據(jù)庫(kù)定義標(biāo)簽. 實(shí)現(xiàn)讀寫分離操作.
3.7.2.5.1 屬性 host
數(shù)據(jù)庫(kù)命名
3.7.2.5.2 屬性u(píng)rl
數(shù)據(jù)庫(kù)訪問(wèn)路徑
3.7.2.5.3 屬性u(píng)ser
數(shù)據(jù)庫(kù)訪問(wèn)用戶名
3.7.2.5.4 屬性password
訪問(wèn)用戶密碼
3.7.2.6 測(cè)試配置文件
3.7.3 server.xml
配置Mycat服務(wù)信息的.
如: Mycat中的用戶,用戶可以訪問(wèn)的邏輯庫(kù),可以訪問(wèn)的邏輯表,服務(wù)的端口號(hào)等.
常見修改內(nèi)容:
3.7.4 啟動(dòng)Mycat命令
- bin/mycat start
3.7.5 停止命令
- bin/mycat stop
3.7.6 重啟命令
- bin/mycat restart
3.7.7 查看Mycat狀態(tài)
- bin/mycat status
3.7.8 訪問(wèn)方式
可以使用命令行訪問(wèn)或客戶端軟件訪問(wèn).
3.7.8.1 命令行訪問(wèn)方式
mysql -u用戶名 -p密碼 -hmycat主機(jī)IP -P8066
鏈接成功后,可以當(dāng)做MySQL數(shù)據(jù)庫(kù)使用.
訪問(wèn)成功后，不能直接使用。因?yàn)镸ycat只能訪問(wèn)MYSQL的schema（database），不能自動(dòng)創(chuàng)建邏輯庫(kù)對(duì)應(yīng)的物理庫(kù)。且不能自動(dòng)創(chuàng)建邏輯表對(duì)應(yīng)的物理表。
必須人工鏈接master數(shù)據(jù)庫(kù)，手動(dòng)創(chuàng)建database。
表格可以在mycat控制臺(tái)創(chuàng)建。注意：在mycat控制臺(tái)創(chuàng)建的表，必須是schema.xml配置文件中定義過(guò)的邏輯表。
啟動(dòng)后，經(jīng)過(guò)測(cè)試，crc32slot分片規(guī)則無(wú)效，執(zhí)行DML語(yǔ)句的時(shí)候只能識(shí)別db1和db2。
DDL語(yǔ)句，可以識(shí)別db3。
修改conf/rule.xml配置文件，找標(biāo)簽
修改count參數(shù)。修改為對(duì)應(yīng)的物理database數(shù)量。
3.7.9 訪問(wèn)約束
3.7.9.1 表約束
不能創(chuàng)建未在schema.xml中配置的邏輯表
3.7.9.2 DML約束
尤其是新增: 必須在insert into語(yǔ)法后攜帶所有的字段名稱.至少攜帶主鍵名稱.
因?yàn)榉制?guī)則,絕大多數(shù)都是通過(guò)主鍵字段計(jì)算數(shù)據(jù)分片規(guī)則的.
3.7.10 查看Mycat日志
logs/wrapper.log
日志中記錄的是所有的mycat操作. 查看的時(shí)候主要看異常信息caused by信息
二、 MyCat配置讀寫分離
1 MySQL主從備份
1.1 主從備份概念
什么是主從備份: 就是一種主備模式的數(shù)據(jù)庫(kù)應(yīng)用.
主庫(kù)(Master)數(shù)據(jù)與備庫(kù)(Slave)數(shù)據(jù)完全一致.
實(shí)現(xiàn)數(shù)據(jù)的多重備份, 保證數(shù)據(jù)的安全.
可以在Master[InnoDB]和Slave[MyISAM]中使用不同的數(shù)據(jù)庫(kù)引擎,實(shí)現(xiàn)讀寫的分離
1.1.1 MySQL5.5版本后本身支持主從備份
在老舊版本的MySQL數(shù)據(jù)庫(kù)系統(tǒng)中,不支持主從備份,需要安裝額外的RPM包.
如果需要安裝RPM,只能在一個(gè)位置節(jié)點(diǎn)安裝.
1.1.2 主從備份目的
1.1.2.1 實(shí)現(xiàn)主備模式
保證數(shù)據(jù)的安全. 盡量避免數(shù)據(jù)丟失的可能.
1.1.2.2 實(shí)現(xiàn)讀寫分離
使用不同的數(shù)據(jù)庫(kù)引擎,實(shí)現(xiàn)讀寫分離.提高所有的操作效率.
InnoDB使用DML語(yǔ)法操作. MyISAM使用DQL語(yǔ)法操作.
1.1.3 主從備份效果
1.1.3.1 主庫(kù)操作同步到備庫(kù)
所有對(duì)Master的操作,都會(huì)同步到Slave中.
如果Master和Salve天生上環(huán)境不同,那么對(duì)Master的操作,可能會(huì)在Slave中出現(xiàn)錯(cuò)誤
如: 在創(chuàng)建主從模式之前,Master有database : db1, db2, db3. Slave有database: db1, db2.
創(chuàng)建主從模式.現(xiàn)在的情況Master和Slave天生不同.
主從模式創(chuàng)建成功后,在Master中drop database db3. Slave中拋出數(shù)據(jù)庫(kù)SQL異常.后續(xù)所有的命令不能同步.
一旦出現(xiàn)錯(cuò)誤. 只能重新實(shí)現(xiàn)主從模式.
1.2 安裝MySQL
略過(guò).
1.3 主從備份配置
主要操作Master和Slave中的配置文件和DBMS的配置.
配置文件: 定義主從模式的基礎(chǔ)信息. 如: 日志, 命令等.
DBMS配置: 提供主從訪問(wèn)的用戶,基礎(chǔ)信息[Master和Slave的位置,用戶名,密碼,日志文件名等]等.
建議：建立主從備份的多個(gè)MySQL，***原始環(huán)境一致。Database，table，data完全一致。
1.3.1 Master[主庫(kù)]配置
1.3.1.1 修改Master配置文件
- /etc/my.cnf
需要修改. 在修改前建議復(fù)制一份備份文件.
修改后的my.cnf配置文件,參考資料中的my.cnf文件內(nèi)容.
1.3.1.1.1 server-id
本環(huán)境中server-id是1
MySQL服務(wù)唯一標(biāo)識(shí)
唯一標(biāo)識(shí)是數(shù)字. 自然數(shù)
配置的時(shí)候有要求
1.3.1.1.1.1 單機(jī)使用
server-id 任意配置,只要是數(shù)字即可
1.3.1.1.1.2 主從使用
server-id Master唯一標(biāo)識(shí)數(shù)字必須小于Slave唯一標(biāo)識(shí)數(shù)字.
1.3.1.1.2 log_bin
本環(huán)境中l(wèi)og_bin值 : master_log
日志文件命名, 開啟日志功能。此日志是命令日志。就是記錄主庫(kù)中執(zhí)行的所有的SQL命令的。
1.3.1.1.2.1 開啟日志
MySQL的log_bin不是執(zhí)行日志,狀態(tài)日志. 是操作日志.就是在DBMS中所有的SQL命令
log_bin日志不是必要的.只有配置主從備份時(shí)才必要。
1.3.1.1.2.2 日志文件配置
變量的值就是日志文件名稱.是日志文件名稱的主體.
MySQL數(shù)據(jù)庫(kù)自動(dòng)增加文件名后綴和文件類型.
1.3.1.2 重啟MySQL
- service mysqld restart
1.3.1.3 配置Master
1.3.1.3.1 訪問(wèn)MySQL
- mysql -uusername -ppassword
1.3.1.3.2 創(chuàng)建用戶
在MySQL數(shù)據(jù)庫(kù)中,為不存在的用戶授權(quán),就是同步創(chuàng)建用戶并授權(quán).
此用戶是從庫(kù)訪問(wèn)主庫(kù)使用的用戶
ip地址不能寫為%. 因?yàn)橹鲝膫浞葜?當(dāng)前創(chuàng)建的用戶,是給從庫(kù)Slave訪問(wèn)主庫(kù)Master使用的.用戶必須有指定的訪問(wèn)地址.不能是通用地址.
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[從庫(kù)]配置
1.3.2.1 修改Slave配置文件
- /etc/my.cnf
1.3.2.1.1 server_id
唯一標(biāo)識(shí), 本環(huán)境中配置為 : 2
1.3.2.1.2 log_bin
可以使用默認(rèn)配置, 也可以注釋.
1.3.2.2 可選: 修改uuid
主從模式要求多個(gè)MySQL物理名稱不能相同. 即按裝MySQL過(guò)程中Linux自動(dòng)生成的物理標(biāo)志. 唯一物理標(biāo)志命名為uuid. 保存位置是MySQL數(shù)據(jù)庫(kù)的數(shù)據(jù)存放位置. 默認(rèn)為/var/lib/mysql目錄中. 文件名是auto.cnf.
修改auto.cnf文件中的uuid數(shù)據(jù). 隨意修改,不建議改變數(shù)據(jù)長(zhǎng)度.建議改變數(shù)據(jù)內(nèi)容.
- /var/lib/mysql/auto.cnf
1.3.2.3 重啟MySQL服務(wù)
- service mysqld restart
1.3.2.4 配置Slave
1.3.2.4.1 訪問(wèn)mysql
- mysql -uusername -ppassword
1.3.2.4.2 停止Slave功能
- stop slave
1.3.2.4.3 配置主庫(kù)信息
需要修改的數(shù)據(jù)是依據(jù)Master信息修改的. ip是Master所在物理機(jī)IP. 用戶名和密碼是Master提供的Slave訪問(wèn)用戶名和密碼. 日志文件是在Master中查看的主庫(kù)信息提供的.在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 啟動(dòng)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 ***一次錯(cuò)誤的IO請(qǐng)求編號(hào)
- Last_IO_Error:
- Last_SQL_Errno: 0 ***一次錯(cuò)誤的執(zhí)行SQL命令編號(hào).
- 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 測(cè)試主從
1.4 主從模式下的邏輯圖
2 MyCat讀寫分離配置
修改conf/schema.xml配置文件，下述內(nèi)容中，紅色部分為重點(diǎn)內(nèi)容。
