一、 MySQL+MyCat分庫分表

1 MyCat簡介

java編寫的數(shù)據(jù)庫中間件

Mycat運行環(huán)境需要JDK。

Mycat是中間件，運行在代碼應(yīng)用和MySQL數(shù)據(jù)庫之間的應(yīng)用。

前身： corba，是阿里開發(fā)的數(shù)據(jù)庫中間件，實現(xiàn)MySQL數(shù)據(jù)庫分庫分表集群管理的中間件，曾經(jīng)出現(xiàn)過重大事故，二次開發(fā)，形成Mycat。

使用MyCat之后，編寫的所有的SQL語句，必須嚴格遵守SQL標準規(guī)范。

 

insert into table_name(column_name) values(column_value); 

使用MyCat中間件后的結(jié)構(gòu)圖如下：

2 MyCat術(shù)語簡介

2.1 切分

邏輯上的切分. 在物理層面,是使用多庫[database],多表[table]實現(xiàn)的切分.

2.1.1 縱向切分

把一個數(shù)據(jù)庫切分成多個數(shù)據(jù)庫,配置方便

只能實現(xiàn)兩張表的表連接查詢.

將一張表中的數(shù)據(jù)，分散到若干個database的同結(jié)構(gòu)表中。多個表的數(shù)據(jù)的集合是當前表格的數(shù)據(jù)。

2.1.2 橫向切分

把一個表切分成多個表,相比縱向切分配置麻煩

無法實現(xiàn)表連接查詢.

將一張表的字段，分散到若干張表中，將若干表連接到一起，才是當前表的完整數(shù)據(jù)。

2.2 邏輯庫

Mycat中定義的database.是邏輯上存在的.但是物理上未必存在.

主要是針對縱向切分提供的概念.

訪問MyCat，就是將MyCat當做MySQL使用。

Db數(shù)據(jù)庫是MyCat中定義的database。通過SQL訪問MyCat中的db庫的時候，對應(yīng)的是MySQL中的db1，db2，db3三個庫。物理上的database是db1，db2，db3.邏輯上的database就是db。

2.3 邏輯表

Mycat中定義的table.是邏輯上存在,物理上未必存在.

主要是針對橫向切分提供的概念

MyCat中的表格table，其字段分散到MySQL數(shù)據(jù)庫的表格table1,table2,table3中。

2.4 默認端口

Mycat默認端口是8066

2.5 數(shù)據(jù)主機 - dataHost

物理MySQL存放的主機地址.可以使用主機名,IP,域名定義.

2.6 數(shù)據(jù)節(jié)點 - dataNode

物理的database是什么.數(shù)據(jù)保存的物理節(jié)點.就是database.

2.7 分片規(guī)則

當控制數(shù)據(jù)的時候,如何訪問物理database和table.

就是訪問dataHost和dataNode的算法.

在Mycat處理具體的數(shù)據(jù)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數(shù)據(jù)庫的root用戶訪問Master數(shù)據(jù)庫.

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默認的分片規(guī)則: 以500萬為單位,實現(xiàn)分片規(guī)則.

邏輯庫A對應(yīng)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發(fā)送到數(shù)據(jù)庫的SQL是select * from table;

checkSQLschema值是false,Mycat發(fā)送的數(shù)據(jù)庫的SQL是select * from A.table;

3.7.2.1.3 sqlMaxLimit

Mycat在執(zhí)行SQL的時候,如果SQL語法中沒有l(wèi)imit子句.自動增加limit子句. 避免一次性得到過多的數(shù)據(jù),影響效率. limit子句的限制數(shù)量默認配置為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數(shù)據(jù)庫中真實存在的表）的表名一致。

3.7.2.2.1 屬性name

邏輯表名

3.7.2.2.2 屬性dataNode

數(shù)據(jù)節(jié)點名稱. 配置文件中后續(xù)需要定義的標簽（即物理數(shù)據(jù)庫中的database名稱）.多個名稱使用逗號分隔.

多個database定義后，代表分庫。

3.7.2.2.3 屬性rule

分片規(guī)則名稱.具體的規(guī)則名稱參考rule.xml配置文件.

SQL語句發(fā)送到Mycat中后，Mycat如何計算，應(yīng)該將當期的SQL發(fā)送到哪一個物理數(shù)據(jù)庫管理系統(tǒng)或物理database中。

3.7.2.3 標簽dataNode

定義數(shù)據(jù)節(jié)點的標簽， 定義具體的物理database信息的。

3.7.2.3.1 屬性name

數(shù)據(jù)節(jié)點名稱, 是定義的邏輯名稱,對應(yīng)具體的物理數(shù)據(jù)庫database

3.7.2.3.2 屬性dataHost

引用dataHost標簽的name值,代表使用的物理數(shù)據(jù)庫所在位置和配置信息.

3.7.2.3.3 屬性database

在dataHost物理機中,具體的物理數(shù)據(jù)庫database名稱.

3.7.2.4 dataHost標簽

定義數(shù)據(jù)主機的標簽， 就是物理MYSQL真實安裝的位置。

3.7.2.4.1 屬性name

定義邏輯上的數(shù)據(jù)主機名稱

3.7.2.4.2 屬性maxCon/minCon

***連接數(shù), max connections

最小連接數(shù), min connections

3.7.2.4.3 屬性dbType

數(shù)據(jù)庫類型 : mysql數(shù)據(jù)庫

3.7.2.4.4 屬性dbDriver

數(shù)據(jù)庫驅(qū)動類型, native,使用mycat提供的本地驅(qū)動.

3.7.2.5 dataHost子標簽writeHost

寫數(shù)據(jù)的數(shù)據(jù)庫定義標簽. 實現(xiàn)讀寫分離操作.

3.7.2.5.1 屬性 host

數(shù)據(jù)庫命名

3.7.2.5.2 屬性url

數(shù)據(jù)庫訪問路徑

3.7.2.5.3 屬性user

數(shù)據(jù)庫訪問用戶名

3.7.2.5.4 屬性password

訪問用戶密碼

3.7.2.6 測試配置文件

3.7.3 server.xml

配置Mycat服務(wù)信息的.

如: Mycat中的用戶,用戶可以訪問的邏輯庫,可以訪問的邏輯表,服務(wù)的端口號等.

常見修改內(nèi)容:

3.7.4 啟動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 訪問方式

可以使用命令行訪問或客戶端軟件訪問.

3.7.8.1 命令行訪問方式

mysql -u用戶名 -p密碼 -hmycat主機IP -P8066

鏈接成功后,可以當做MySQL數(shù)據(jù)庫使用.

訪問成功后，不能直接使用。因為Mycat只能訪問MYSQL的schema（database），不能自動創(chuàng)建邏輯庫對應(yīng)的物理庫。且不能自動創(chuàng)建邏輯表對應(yīng)的物理表。

必須人工鏈接master數(shù)據(jù)庫，手動創(chuàng)建database。

表格可以在mycat控制臺創(chuàng)建。注意：在mycat控制臺創(chuàng)建的表，必須是schema.xml配置文件中定義過的邏輯表。

啟動后，經(jīng)過測試，crc32slot分片規(guī)則無效，執(zhí)行DML語句的時候只能識別db1和db2。

DDL語句，可以識別db3。

修改conf/rule.xml配置文件，找標簽

修改count參數(shù)。修改為對應(yīng)的物理database數(shù)量。

3.7.9 訪問約束

3.7.9.1 表約束

不能創(chuàng)建未在schema.xml中配置的邏輯表

3.7.9.2 DML約束

尤其是新增: 必須在insert into語法后攜帶所有的字段名稱.至少攜帶主鍵名稱.

因為分片規(guī)則,絕大多數(shù)都是通過主鍵字段計算數(shù)據(jù)分片規(guī)則的.

3.7.10 查看Mycat日志

logs/wrapper.log

日志中記錄的是所有的mycat操作. 查看的時候主要看異常信息caused by信息

二、 MyCat配置讀寫分離

1 MySQL主從備份

1.1 主從備份概念

什么是主從備份: 就是一種主備模式的數(shù)據(jù)庫應(yīng)用.

主庫(Master)數(shù)據(jù)與備庫(Slave)數(shù)據(jù)完全一致.

實現(xiàn)數(shù)據(jù)的多重備份, 保證數(shù)據(jù)的安全.

可以在Master[InnoDB]和Slave[MyISAM]中使用不同的數(shù)據(jù)庫引擎,實現(xiàn)讀寫的分離

1.1.1 MySQL5.5版本后本身支持主從備份

在老舊版本的MySQL數(shù)據(jù)庫系統(tǒng)中,不支持主從備份,需要安裝額外的RPM包.

如果需要安裝RPM,只能在一個位置節(jié)點安裝.

1.1.2 主從備份目的

1.1.2.1 實現(xiàn)主備模式

保證數(shù)據(jù)的安全. 盡量避免數(shù)據(jù)丟失的可能.

1.1.2.2 實現(xiàn)讀寫分離

使用不同的數(shù)據(jù)庫引擎,實現(xiàn)讀寫分離.提高所有的操作效率.

InnoDB使用DML語法操作. MyISAM使用DQL語法操作.

1.1.3 主從備份效果

1.1.3.1 主庫操作同步到備庫

所有對Master的操作,都會同步到Slave中.

如果Master和Salve天生上環(huán)境不同,那么對Master的操作,可能會在Slave中出現(xiàn)錯誤

如: 在創(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ù)庫SQL異常.后續(xù)所有的命令不能同步.

一旦出現(xiàn)錯誤. 只能重新實現(xiàn)主從模式.

1.2 安裝MySQL

略過.

1.3 主從備份配置

主要操作Master和Slave中的配置文件和DBMS的配置.

配置文件: 定義主從模式的基礎(chǔ)信息. 如: 日志, 命令等.

DBMS配置: 提供主從訪問的用戶,基礎(chǔ)信息[Master和Slave的位置,用戶名,密碼,日志文件名等]等.

建議：建立主從備份的多個MySQL，***原始環(huán)境一致。Database，table，data完全一致。

1.3.1 Master[主庫]配置

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ù)唯一標識

唯一標識是數(shù)字. 自然數(shù)

配置的時候有要求

1.3.1.1.1.1 單機使用

server-id 任意配置,只要是數(shù)字即可

1.3.1.1.1.2 主從使用

server-id Master唯一標識數(shù)字必須小于Slave唯一標識數(shù)字.

1.3.1.1.2 log_bin

本環(huán)境中l(wèi)og_bin值 : master_log

日志文件命名, 開啟日志功能。此日志是命令日志。就是記錄主庫中執(zhí)行的所有的SQL命令的。

1.3.1.1.2.1 開啟日志

MySQL的log_bin不是執(zhí)行日志,狀態(tài)日志. 是操作日志.就是在DBMS中所有的SQL命令

log_bin日志不是必要的.只有配置主從備份時才必要。

1.3.1.1.2.2 日志文件配置

變量的值就是日志文件名稱.是日志文件名稱的主體.

MySQL數(shù)據(jù)庫自動增加文件名后綴和文件類型.

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 創(chuàng)建用戶

在MySQL數(shù)據(jù)庫中,為不存在的用戶授權(quán),就是同步創(chuàng)建用戶并授權(quán).

此用戶是從庫訪問主庫使用的用戶

ip地址不能寫為%. 因為主從備份中,當前創(chuàng)建的用戶,是給從庫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

唯一標識, 本環(huán)境中配置為 : 2

1.3.2.1.2 log_bin

可以使用默認配置, 也可以注釋.

1.3.2.2 可選: 修改uuid

主從模式要求多個MySQL物理名稱不能相同. 即按裝MySQL過程中Linux自動生成的物理標志. 唯一物理標志命名為uuid. 保存位置是MySQL數(shù)據(jù)庫的數(shù)據(jù)存放位置. 默認為/var/lib/mysql目錄中. 文件名是auto.cnf.

修改auto.cnf文件中的uuid數(shù)據(jù). 隨意修改,不建議改變數(shù)據(jù)長度.建議改變數(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 訪問mysql

mysql -uusername -ppassword 

1.3.2.4.2 停止Slave功能

stop slave 

1.3.2.4.3 配置主庫信息

需要修改的數(shù)據(jù)是依據(jù)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 ***一次錯誤的執(zhí)行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配置文件，下述內(nèi)容中，紅色部分為重點內(nèi)容。