其中Amoeba for MySQL也是實現(xiàn)讀寫分離

環(huán)境描述:

操作系統(tǒng)：CentOS6.5 32位

主服務(wù)器Master：192.168.179.146

從服務(wù)器Slave：192.168.179.147

調(diào)度服務(wù)器MySQL-Proxy：192.168.179.142

由于電腦配置不行辩尊，安裝了三臺虛擬機，就卡死了齿诞，只能將就一下,由于是一主

一從歇父，所以碘勉，導(dǎo)致讀寫都在master上币励，有機會递礼，再弄兩臺slave來測試

一.mysql主從復(fù)制岖圈，參考：http://www.cnblogs.com/lin3615/p/5679828.html

二鹦牛、mysql-proxy實現(xiàn)讀寫分離

1搞糕、安裝mysql-proxy

實現(xiàn)讀寫分離是有l(wèi)ua腳本實現(xiàn)的，現(xiàn)在mysql-proxy里面已經(jīng)集成曼追，無需再安裝

下載：http://dev.mysql.com/downloads/mysql-proxy/ 一定要下載對應(yīng)的版本

tarzxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gzmvmysql-proxy-0.8.5-linux-glibc2.3-x86-32bit /usr/local/mysql-proxy

2窍仰、配置mysql-proxy，創(chuàng)建主配置文件

cd /usr/local/mysql-proxymkdir lua #創(chuàng)建腳本存放目錄mkdir logs #創(chuàng)建日志目錄cpshare/doc/mysql-proxy/rw-splitting.lua ./lua #復(fù)制讀寫分離配置文件cpshare/doc/mysql-proxy/admin-sql.lua ./lua #復(fù)制管理腳本vi/etc/mysql-proxy.cnf? #創(chuàng)建配置文件

[mysql-proxy]

user=root #運行mysql-proxy用戶

admin-username=lin3615 #主從mysql共有的用戶

admin-password=123456 #用戶的密碼

proxy-address=192.168.179.142:4040#mysql-proxy運行ip和端口礼殊，不加端口驹吮，默認4040

proxy-read-only-backend-addresses=192.168.179.147 #指定后端從slave讀取數(shù)據(jù)

proxy-backend-addresses=192.168.179.146 #指定后端主master寫入數(shù)據(jù)

proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定讀寫分離配置文件位置

admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理腳本

log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置

log-level=info#定義log日志級別，由高到低分別有(error|warning|info|message|debug)

daemon=true? ? #以守護進程方式運行

keepalive=true#mysql-proxy崩潰時晶伦，嘗試重啟

#保存退出碟狞！chmod660/etc/mysql-porxy.cnf

3、修改讀寫分離配置文件

vim /usr/local/mysql-proxy/lua/rw-splitting.luaifnot proxy.global.config.rwsplitthen proxy.global.config.rwsplit = {

? min_idle_connections =1, #默認超過4個連接數(shù)時婚陪，才開始讀寫分離族沃，改為1

? max_idle_connections =1, #默認8，改為1

? is_debug =false }

end

4泌参、啟動mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

netstat -tupln |grep4000 #已經(jīng)啟動killall-9mysql-proxy #關(guān)閉mysql-proxy使用

5脆淹、測試讀寫分離

(1).在主服務(wù)器創(chuàng)建proxy用戶用于mysql-proxy使用，從服務(wù)器也會同步這個操作

mysql> grant all on *.* to'lin3615'@'192.168.179.142'identified by'123456';

(2).使用客戶端連接mysql-proxy

mysql -u lin3615 -h192.168.179.142-P4040-p123456

接下來就按基本的 curd執(zhí)行即可,由于只有一臺slave沽一，測試時盖溺，每次讀寫都是從master，電腦性能無法開四臺虛擬機铣缠，所以有機會烘嘱，再測試多臺 slave，看下是否ＯＫ

讀寫分離攘残，延遲是個大問題

在slave服務(wù)器上執(zhí)行 show slave status,

可以看到很多同步的參數(shù),要注意的參數(shù)有：

Master_Log_File:slave中的I/O線程當前正在讀取的master服務(wù)器二進制式日志文件名.

Read_Master_Log_Pos:在當前的 master服務(wù)器二進制日志中拙友，slave中的I/O線程已經(jīng)讀取的位置

Relay_Log_File:SQL線程當前正在讀取與執(zhí)行中繼日志文件的名稱

Relay_Log_Pos:在當前的中繼日志中，SQL線程已讀取和執(zhí)行的位置

Relay_Master_Log_File:由SQL線程執(zhí)行的包含多數(shù)近期事件的master二進制日志文件的名稱

Slave_IO_Running:I/O線程是否被啟動并成功連接到master

Slave_SQL_Running:SQL線程是否被啟動

Seconds_Behind_Master:slave服務(wù)器SQL線程和從服務(wù)器I/O線程之間的差距歼郭，單位為秒計

slave同步延遲情況出現(xiàn)：

1.Seconds_Behind_Master不為了遗契，這個值可能會很大

2.Relay_Master_Log_File和Master_Log_File顯示bin-log的編號相差很大，說明bin-log在slave上沒有及時同步病曾，所以近期執(zhí)行的 bin-log和當前I/O線程所讀的 bin-log相差很大

3.mysql的 slave數(shù)據(jù)庫目錄下存在大量的 mysql-relay-log日志牍蜂，該日志同步完成之后就會被系統(tǒng)自動刪除漾根，存在大量日志，說明主從同步延遲很厲害

mysql主從同步延遲原理

mysql主從同步原理

主庫針對讀寫操作鲫竞，順序?qū)?binlog辐怕，從庫單線程去主庫讀"寫操作的binlog",從庫取到 binlog在本地原樣執(zhí)行(隨機寫),來保證主從數(shù)據(jù)邏輯上一致.

mysql的主從復(fù)制都是單線程的操作，主庫對所有DDL和DML產(chǎn)生 binlog从绘，binlog是順序?qū)懠氖瑁孕屎芨撸瑂lave的Slave_IO_Running線程到主庫取日志僵井，效率比較高陕截，下一步問題來了，slave的 slave_sql_running線程將主庫的 DDL和DML操作在 slave實施批什。DML农曲，DDL的IO操作是隨即的，不能順序的驻债，成本高很多乳规，還有可能slave上的其他查詢產(chǎn)生 lock，由于 slave_sql_running也是單線程的合呐，所以 一個 DDL卡住了暮的，需求需求執(zhí)行一段時間，那么所有之后的DDL會等待這個 DDL執(zhí)行完才會繼續(xù)執(zhí)行合砂，這就導(dǎo)致了延遲.由于master可以并發(fā)青扔，Slave_sql_running線程卻不可以，所以主庫執(zhí)行 DDL需求一段時間翩伪，在slave執(zhí)行相同的DDL時，就產(chǎn)生了延遲.

主從同步延遲產(chǎn)生原因

當主庫的TPS并發(fā)較高時谈息，產(chǎn)生的DDL數(shù)量超過Slave一個 sql線程所能承受的范圍缘屹，那么延遲就產(chǎn)生了，當然還有就是可能與 slave的大型 query語句產(chǎn)生了鎖等待

首要原因：數(shù)據(jù)庫在業(yè)務(wù)上讀寫壓力太大侠仇，CPU計算負荷大轻姿，網(wǎng)卡負荷大，硬盤隨機IO太高

次要原因：讀寫 binlog帶來的性能影響逻炊，網(wǎng)絡(luò)傳輸延遲

主從同步延遲解決方案

架構(gòu)方面

1.業(yè)務(wù)的持久化層的實現(xiàn)采用分庫架構(gòu)互亮，mysql服務(wù)可平行擴展分散壓力

2.單個庫讀寫分離，一主多從余素，主寫從讀豹休，分散壓力。

3.服務(wù)的基礎(chǔ)架構(gòu)在業(yè)務(wù)和mysql之間加放 cache層

4.不同業(yè)務(wù)的mysql放在不同的機器

5.使用比主加更了的硬件設(shè)備作slave

反正就是mysql壓力變小桨吊，延遲自然會變小

硬件方面：

采用好的服務(wù)器

mysql主從同步加速

1威根、sync_binlog在slave端設(shè)置為0

2凤巨、–logs-slave-updates 從服務(wù)器從主服務(wù)器接收到的更新不記入它的二進制日志。

3洛搀、直接禁用slave端的binlog

4敢茁、slave端，如果使用的存儲引擎是innodb留美，innodb_flush_log_at_trx_commit =2

從文件系統(tǒng)本身屬性角度優(yōu)化

master端

修改linux彰檬、Unix文件系統(tǒng)中文件的etime屬性， 由于每當讀文件時OS都會將讀取操作發(fā)生的時間回寫到磁盤上谎砾，對于讀操作頻繁的數(shù)據(jù)庫文件來說這是沒必要的逢倍，只會增加磁盤系統(tǒng)的負擔影響I/O性能」桌疲可以通過設(shè)置文件系統(tǒng)的mount屬性瓶堕，組織操作系統(tǒng)寫atime信息，在linux上的操作為：

打開/etc/fstab症歇，加上noatime參數(shù)

/dev/sdb1 /data reiserfs noatime 1 2

然后重新mount文件系統(tǒng)

#mount -oremount /data

主庫是寫郎笆，對數(shù)據(jù)安全性較高，比如sync_binlog=1忘晤，innodb_flush_log_at_trx_commit = 1 之類的設(shè)置是需要的

而slave則不需要這么高的數(shù)據(jù)安全宛蚓，完全可以講sync_binlog設(shè)置為0或者關(guān)閉binlog，innodb_flushlog也可以設(shè)置為0來提高sql的執(zhí)行效率

1设塔、sync_binlog=1 o

MySQL提供一個sync_binlog參數(shù)來控制數(shù)據(jù)庫的binlog刷到磁盤上去凄吏。

默認，sync_binlog=0闰蛔，表示MySQL不控制binlog的刷新痕钢，由文件系統(tǒng)自己控制它的緩存的刷新。這時候的性能是最好的序六，但是風險也是最大的任连。一旦系統(tǒng)Crash，在binlog_cache中的所有binlog信息都會被丟失例诀。

如果sync_binlog>0随抠，表示每sync_binlog次事務(wù)提交，MySQL調(diào)用文件系統(tǒng)的刷新操作將緩存刷下去繁涂。最安全的就是sync_binlog=1了拱她，表示每次事務(wù)提交，MySQL都會把binlog刷下去扔罪，是最安全但是性能損耗最大的設(shè)置秉沼。這樣的話，在數(shù)據(jù)庫所在的主機操作系統(tǒng)損壞或者突然掉電的情況下，系統(tǒng)才有可能丟失1個事務(wù)的數(shù)據(jù)氧猬。

但是binlog雖然是順序IO背犯，但是設(shè)置sync_binlog=1，多個事務(wù)同時提交盅抚，同樣很大的影響MySQL和IO性能漠魏。

雖然可以通過group commit的補丁緩解，但是刷新的頻率過高對IO的影響也非常大妄均。對于高并發(fā)事務(wù)的系統(tǒng)來說柱锹，

“sync_binlog”設(shè)置為0和設(shè)置為1的系統(tǒng)寫入性能差距可能高達5倍甚至更多。

所以很多MySQL DBA設(shè)置的sync_binlog并不是最安全的1丰包，而是2或者是0禁熏。這樣犧牲一定的一致性，可以獲得更高的并發(fā)和性能邑彪。

默認情況下瞧毙，并不是每次寫入時都將binlog與硬盤同步。因此如果操作系統(tǒng)或機器(不僅僅是MySQL服務(wù)器)崩潰寄症，有可能binlog中最后的語句丟失了宙彪。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值有巧，但也是最慢的)释漆，使binlog在每N次binlog寫入后與硬盤同步。即使sync_binlog設(shè)置為1,出現(xiàn)崩潰時篮迎，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性男图。

2、innodb_flush_log_at_trx_commit （這個很管用）

抱怨Innodb比MyISAM慢 100倍甜橱？那么你大概是忘了調(diào)整這個值逊笆。默認值1的意思是每一次事務(wù)提交或事務(wù)外的指令都需要把日志寫入（flush）硬盤，這是很費時的岂傲。特別是使用電池供電緩存（Battery backed up cache）時览露。設(shè)成2對于很多運用，特別是從MyISAM表轉(zhuǎn)過來的是可以的譬胎，它的意思是不寫入硬盤而是寫入系統(tǒng)緩存。

日志仍然會每秒flush到硬 盤命锄，所以你一般不會丟失超過1-2秒的更新堰乔。設(shè)成0會更快一點，但安全方面比較差脐恩，即使MySQL掛了也可能會丟失事務(wù)的數(shù)據(jù)镐侯。而值2只會在整個操作系統(tǒng) 掛了時才可能丟數(shù)據(jù)。

3、ls(1) 命令可用來列出文件的 atime苟翻、ctime 和 mtime韵卤。

atime 文件的access time 在讀取文件或者執(zhí)行文件時更改的

ctime 文件的create time 在寫入文件，更改所有者崇猫，權(quán)限或鏈接設(shè)置時隨inode的內(nèi)容更改而更改

mtime 文件的modified time 在寫入文件時隨文件內(nèi)容的更改而更改

ls -lc filename 列出文件的 ctime

ls -lu filename 列出文件的 atime

ls -l filename 列出文件的 mtime

stat filename 列出atime沈条，mtime，ctime

atime不一定在訪問文件之后被修改

因為：使用ext3文件系統(tǒng)的時候诅炉，如果在mount的時候使用了noatime參數(shù)那么就不會更新atime信息蜡歹。

這三個time stamp都放在 inode 中.如果mtime,atime 修改,inode 就一定會改, 既然 inode 改了,那ctime也就跟著改了.

之所以在 mount option 中使用 noatime, 就是不想file system 做太多的修改, 而改善讀取效能

4.進行分庫分表處理，這樣減少數(shù)據(jù)量的復(fù)制同步操作