mycat讀寫分離模式智能優(yōu)化方案

1、MyCAT 讀寫分離模式智能優(yōu)化方案修訂記錄日期描述 作者 郵箱 QQ 版本號 2015-03-30創(chuàng)建文檔楊超654839088V1.00目錄1. 前言31.1 編寫目的31.2 預(yù)期讀者32. MySQL 引擎淺談33. 取長補短，兼容并包44. MySQL 主從配置55. 主從配置的 MySQL 創(chuàng)建表方式66. 啟用 Mycat 的讀寫分離機制77. 兩種模式的執(zhí)行時間比照圖71.前言1.1 編寫目的通過對 mycat 的研究和使用，目前已經(jīng)正式應(yīng)用于我公司的項目中，為了感謝群里各位朋友的幫助，現(xiàn)對 mycat 讀寫分離模式的智能優(yōu)化方案分享給大家。本文

2、檔屬于進階篇，寫者建議大家在閱讀本文檔之前，請先閱讀mycat 的入門文檔，并能初步使用mycat 時再閱讀。本人技術(shù)泛泛，若有不對之處，望大能指出。1.2 預(yù)期讀者本文的預(yù)期讀者包括：技術(shù)管理人員、技術(shù)開發(fā)人員、運維人員2. MySQL 引擎淺談在進入正題前，我這里先與大家談?wù)?MySql 的引擎InnoDB 與 MyISAM，這里，本文只考慮這兩種引擎，因為這兩個是常見的。MyISAM 是 MySQL 中默認(rèn)的存儲引擎，一般來說不是有太多人關(guān)心這個東西。決定使用什么樣的存儲引擎是一個很 tricky 的事情，但是還是值我們?nèi)パ芯恳幌隆Ｏ旅嫦茸屛覀兓卮鹨恍﹩栴}：你需要事務(wù)支持嗎？ 你需要全文

3、索引嗎？你經(jīng)常使用什么樣的查詢模式?思考上面這些問題可以讓你找到合適的方向，但那并不是絕對的。如果你需要事務(wù)處理， 那么 InnoDB 可能是比較好的方式。如果你需要全文索引，那么通常來說 MyISAM 是好的選擇，因為這是系統(tǒng)內(nèi)建的。事務(wù)支持，是一個影響你選擇什么樣存儲引擎的重要因素，事務(wù)支持趨向于選擇 InnoDB 方式，因為其支持事務(wù)處理和故障恢復(fù)。另外 InnoDB 可以利用事務(wù)日志進行數(shù)據(jù)恢復(fù)，這會比 MyISAM 快很多。而 MyISAM 可能會需要幾個小時甚至幾天來干這些事，InnoDB 只需要幾分鐘。當(dāng)然操作數(shù)據(jù)庫表的習(xí)慣可能也會是一個對性能影響很大的因素。比如： COUNT(

4、) 在MyISAM 表中會非常快，而在 InnoDB 表下可能會很痛苦。另外 MyISAM 存儲引擎在篩選大量數(shù)據(jù)時非常迅速，這是它最突出的優(yōu)點。MyISAM 還提供了大量的特性，包括全文索引、引擎是合適的。所以，到底你檢使用哪一個呢？根據(jù)應(yīng)用場景來說：MyISAM 管理非事務(wù)表。它提供高速存儲和檢索，以及全文搜索能力。如果應(yīng)用中需要執(zhí)行大量的 SELECT 查詢，那么 MyISAM 是更好的選擇。InnoDB 用于事務(wù)處理應(yīng)用程序，具有眾多特性，包括 ACID 事務(wù)支持。如果應(yīng)用中需要執(zhí)行大量的 INSERT 或 UPDATE 操作，則應(yīng)該使用 InnoDB，這樣可以提高多用戶并發(fā)操作的性能

5、。3.取長補短，兼容并包如前文所述，MySQL 的引擎各有千秋，我設(shè)計方案的初衷就是希望能結(jié)合兩種引擎的優(yōu)勢，摒棄各自的劣勢，創(chuàng)造出一種接近“理想”狀態(tài)的使用方式，既可以做到 InnoDB 的事務(wù)處理，又可以使用到 MyISAM 特性。而這種方式簡單概括起來就是：利用 MyCat 的讀寫分離模式，寫節(jié)點使用 MySql 的 InnoDB 引擎，讀節(jié)點使用的是 MySql 的 MyISAM 引擎。如下圖所示：Mycat 的入門使用教程，這里我不再贅述，有需要請到/MyCATApache/Mycat-doc 下載。這里我詳細談?wù)勅绾卫?Mycat 的讀寫分離機制

6、來最大限度的利用 MySQL 強大的功能。4. MySQL 主從配置我們的方案設(shè)計基于 Mysql 的主從數(shù)據(jù)復(fù)制功能，以下是基于 mysql5.5 以上版本最新的主從配置。開啟 mysql 主從數(shù)據(jù)復(fù)制，主要在 mysql 的 my.ini 文件中設(shè)置：windows 系統(tǒng)一般在 c:ProgrameDataMySQL 中：Linux 系統(tǒng)一般在 /etc/f 中：1、主數(shù)據(jù)庫服務(wù)器配置：server-id=1log-bin=mysql-bin binlog-do-db=Industry/只同步 Industry 數(shù)據(jù)庫binlog-ignore-db=mysql/避免同步 mys

7、ql 用戶配置，以免不必要的麻煩binlog-ignore-db=performance_schemabinlog-ignore-db=information_schema2、創(chuàng)建一個允許從服務(wù)器來訪問的用戶(主服務(wù)器)：grant replication slave on *.* to root8 identified by root;說明：root：Slave 使用的賬號IDENTIFIED BY root ：Slave 使用的8：Slave 數(shù)據(jù)庫 IPmaster 端，通過 show master statusG 顯示信息。如圖：注意上圖中的

8、 File,和 Position。Slave 中會用到這些信息。3、從數(shù)據(jù)庫服務(wù)器配置：/服務(wù) Id，注意要和 master 的 server-id 不一樣server-id=11log-bin=mysql-binrelay-log-index=slave-relay-bin.indexrelay-log=slave-relay-bin sync_master_info=1 sync_relay_log=1 sync_relay_log_info=14、關(guān)聯(lián)上 Master 信息：change master tomaster_host=8,master_user=root

9、,master_port=3306,master_password=rootmaster_log_file=mysql-bin.000001,master_log_pos=120;命令說明：master_host：master 數(shù)據(jù)庫服務(wù)器 ipmaster_user: master 數(shù)據(jù)庫通過 GRANT 授權(quán)的賬號master_port：master 數(shù)據(jù)庫使用的端master_password：master 數(shù)據(jù)庫通過 GRANT 授權(quán)的master_log_file：master 數(shù)據(jù)庫中通過 show master status 顯示的 File 名稱master_log_pos：m

10、aster 數(shù)據(jù)庫中通過 show master status 顯示的 Position 數(shù)據(jù)重啟 Slave 的 MySql 服務(wù)在命令行中輸入 start slave.在命令行中輸入 show slave status,顯示如下：注意：只有 Slave_IO_Running 與 Slave_SQL_Running 都為 Yes 才配置成功。至此，配置全部完成。這樣，Master 服務(wù)器上 Industry 里的數(shù)據(jù)就能同步到 slave 的 Industry數(shù)據(jù)庫中。5. 主從配置的 MySQL 創(chuàng)建表方式由于 Mysql 已經(jīng)配置好了主從復(fù)制，在創(chuàng)建表時，我們要注意，在 Master 庫

11、中創(chuàng)建表后，Mysql 會自動在 Slave 庫中創(chuàng)建一張表。這里我們需要做的是，將從庫中自動創(chuàng)建的表刪除，然后手動創(chuàng)建一張我們符合我們需求的表。具體操作步驟如下：1. 在主庫中創(chuàng)建表 A，引擎選擇 Innodb；2. 刪除從庫中自動創(chuàng)建的表 A；3.在從庫中創(chuàng)建表 A，引擎選擇 MyISAM；6. 啟用 Mycat 的讀寫分離機制最后啟用 Mycat 的讀寫分離機制即可。7. 兩種模式的執(zhí)行時間比照圖再多的廢話也不如實際的測試來的有效，接下來，讓我們實際測試?yán)眠@種方案能給我們帶來多大的執(zhí)行效率。首先，在開啟 mycat 的讀寫分離機制后，如下圖：此時寫入庫的引擎為Innodb，讀庫的引擎為MyISAM，這時使用count 語句,執(zhí)行時間如下圖：使用 like 條件語句執(zhí)行時間如下圖：使用等于條件語句執(zhí)行時間如下圖：接下來，我們?nèi)∠x寫分離機制，見下圖：此時只有寫入庫，并且引擎為 Innodb，這時使用 count 語句,執(zhí)行時間如下圖:使用 like 條件語句執(zhí)行時間如