一、 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配置文件，下述內容中，紅色部分為重點內容。
