1、MySQL數(shù)據(jù)庫(kù)高可用架構(gòu)設(shè)計(jì)目標(biāo)：MySQL 數(shù)據(jù)庫(kù)服務(wù)器不受單點(diǎn)宕機(jī)的影響，即時(shí) A 服務(wù)器掛掉或者磁盤損壞物理故障導(dǎo)致數(shù)據(jù)庫(kù)不可用也不會(huì)導(dǎo)致整個(gè)系統(tǒng)處于不可用狀態(tài)，因?yàn)檫€有另外一臺(tái)備用的數(shù)據(jù)庫(kù)服務(wù)器可以提供服務(wù)。派寶箱采取方案雙機(jī)主從熱備 (Mater Slave 模式)背景：雙機(jī)熱備的概念簡(jiǎn)單說(shuō)一下，就是要保持兩個(gè)數(shù)據(jù)庫(kù)的狀態(tài)自動(dòng)同步。對(duì)任何一個(gè)數(shù)據(jù)庫(kù)的操作都自動(dòng)應(yīng)用到另外一個(gè)數(shù)據(jù)庫(kù)，始終保持兩個(gè)數(shù)據(jù)庫(kù)數(shù)據(jù)一致。 這樣做的好處: 1. 可以做災(zāi)備，其中一個(gè)壞了可以切換到另一個(gè)。2. 可以做負(fù)載均衡，可以將請(qǐng)求分?jǐn)偟狡渲腥魏我慌_(tái)上，提高網(wǎng)站吞吐量。 對(duì)于異地?zé)醾?，尤其適合災(zāi)備。原理：My

2、SQL Replication雙機(jī)熱備 + 每天自動(dòng)sqldump出物理文件備份雙機(jī)主從自動(dòng)熱備實(shí)現(xiàn)數(shù)據(jù)庫(kù)服務(wù)的高可用加sqldump導(dǎo)出數(shù)據(jù)文件的方式備份。雙重保險(xiǎn)！可能遇到的問(wèn)題與挑戰(zhàn)：主從數(shù)據(jù)庫(kù)數(shù)據(jù)一致性問(wèn)題宕機(jī)后主從切換的問(wèn)題1 復(fù)制概述 Mysql內(nèi)建的復(fù)制功能(MySQL REPLICATION)是構(gòu)建大型，高性能應(yīng)用程序的基礎(chǔ)。將Mysql的數(shù)據(jù)分布到多個(gè)系統(tǒng)上去，這種分布的機(jī)制，是通過(guò)將Mysql的某一臺(tái)主機(jī)的數(shù)據(jù)復(fù)制到其它主機(jī)（slaves）上，并重新執(zhí)行一遍來(lái)實(shí)現(xiàn)的。復(fù)制過(guò)程中一個(gè)服務(wù)器充當(dāng)主服務(wù)器，而一個(gè)或多個(gè)其它服務(wù)器充當(dāng)從服務(wù)器。主服務(wù)器將更新寫入二進(jìn)制日志文件，并維

3、護(hù)文件的一個(gè)索引以跟蹤日志循環(huán)。這些日志可以記錄發(fā)送到從服務(wù)器的更新。當(dāng)一個(gè)從服務(wù)器連接主服務(wù)器時(shí)，它通知主服務(wù)器從服務(wù)器在日志中讀取的最后一次成功更新的位置。從服務(wù)器接收從那時(shí)起發(fā)生的任何更新，然后封鎖并等待主服務(wù)器通知新的更新。請(qǐng)注意當(dāng)你進(jìn)行復(fù)制時(shí)，所有對(duì)復(fù)制中的表的更新必須在主服務(wù)器上進(jìn)行。否則，你必須要小心，以避免用戶對(duì)主服務(wù)器上的表進(jìn)行的更新與對(duì)從服務(wù)器上的表所進(jìn)行的更新之間的沖突。1.1 mysql支持的復(fù)制類型：（）：基于語(yǔ)句的復(fù)制： 在主服務(wù)器上執(zhí)行的SQL語(yǔ)句，在從服務(wù)器上執(zhí)行同樣的語(yǔ)句。MySQL默認(rèn)采用基于語(yǔ)句的復(fù)制，效率比較高。 一旦發(fā)現(xiàn)沒(méi)法精確復(fù)制時(shí)， 會(huì)自動(dòng)選著基于

4、行的復(fù)制。（）：基于行的復(fù)制：把改變的內(nèi)容復(fù)制過(guò)去，而不是把命令在從服務(wù)器上執(zhí)行一遍. 從mysql5.0開始支持（）：混合類型的復(fù)制: 默認(rèn)采用基于語(yǔ)句的復(fù)制，一旦發(fā)現(xiàn)基于語(yǔ)句的無(wú)法精確的復(fù)制時(shí)，就會(huì)采用基于行的復(fù)制。1.2 . 復(fù)制解決的問(wèn)題 MySQL復(fù)制技術(shù)有以下一些特點(diǎn)： (1) 數(shù)據(jù)分布 (Data distribution )(2) 負(fù)載平衡(load balancing) (3) 備份(Backups) (4) 高可用性和容錯(cuò)行 High availability and failover 1.3 復(fù)制如何工作 整體上來(lái)說(shuō)，復(fù)制有3個(gè)步驟： (1) master將改變記錄到二進(jìn)

5、制日志(binary log)中（這些記錄叫做二進(jìn)制日志事件，binary log events）； (2) slave將master的binary log events拷貝到它的中繼日志(relay log)； (3) slave重做中繼日志中的事件，將改變反映它自己的數(shù)據(jù)。下圖描述了復(fù)制的過(guò)程： 該過(guò)程的第一部分就是master記錄二進(jìn)制日志。在每個(gè)事務(wù)更新數(shù)據(jù)完成之前，master在二日志記錄這些改變。MySQL將事務(wù)串行的寫入二進(jìn)制日志，即使事務(wù)中的語(yǔ)句都是交叉執(zhí)行的。在事件寫入二進(jìn)制日志完成后，master通知存儲(chǔ)引擎提交事務(wù)。 下一步就是slave將master的binary lo

6、g拷貝到它自己的中繼日志。首先，slave開始一個(gè)工作線程I/O線程。I/O線程在master上打開一個(gè)普通的連接，然后開始binlog dump process。Binlog dump process從master的二進(jìn)制日志中讀取事件，如果已經(jīng)跟上master，它會(huì)睡眠并等待master產(chǎn)生新的事件。I/O線程將這些事件寫入中繼日志。 SQL slave thread（SQL從線程）處理該過(guò)程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新slave的數(shù)據(jù)，使其與master中的數(shù)據(jù)一致。只要該線程與I/O線程保持一致，中繼日志通常會(huì)位于OS的緩存中，所以中繼日志的開銷很小

7、。 此外，在master中也有一個(gè)工作線程：和其它MySQL的連接一樣，slave在master中打開一個(gè)連接也會(huì)使得master開始一個(gè)線程。復(fù)制過(guò)程有一個(gè)很重要的限制復(fù)制在slave上是串行化的，也就是說(shuō)master上的并行更新操作不能在slave上并行操作。2 .復(fù)制配置有兩臺(tái)MySQL數(shù)據(jù)庫(kù)服務(wù)器Master和slave，Master為主服務(wù)器，slave為從服務(wù)器，初始狀態(tài)時(shí)，Master和slave中的數(shù)據(jù)信息相同，當(dāng)Master中的數(shù)據(jù)發(fā)生變化時(shí)，slave也跟著發(fā)生相應(yīng)的變化，使得master和slave的數(shù)據(jù)信息同步，達(dá)到備份的目的。要點(diǎn)：負(fù)責(zé)在主、從服務(wù)器傳輸各種修改動(dòng)作的

8、媒介是主服務(wù)器的二進(jìn)制變更日志，這個(gè)日志記載著需要傳輸給從服務(wù)器的各種修改動(dòng)作。因此，主服務(wù)器必須激活二進(jìn)制日志功能。從服務(wù)器必須具備足以讓它連接主服務(wù)器并請(qǐng)求主服務(wù)器把二進(jìn)制變更日志傳輸給它的權(quán)限。環(huán)境：Master和slave的MySQL數(shù)據(jù)庫(kù)版本同為5.0.18操作系統(tǒng)：unbuntu 11.10IP地址：002.1、創(chuàng)建復(fù)制帳號(hào)1、在Master的數(shù)據(jù)庫(kù)中建立一個(gè)備份帳戶：每個(gè)slave使用標(biāo)準(zhǔn)的MySQL用戶名和密碼連接master。進(jìn)行復(fù)制操作的用戶會(huì)授予REPLICATION SLAVE權(quán)限。用戶名的密碼都會(huì)存儲(chǔ)在文本文件中命令如下：my

9、sql GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*TO backup00IDENTIFIED BY 1234;建立一個(gè)帳戶backup，并且只能允許從00這個(gè)地址上來(lái)登陸，密碼是1234。(如果因?yàn)閙ysql版本新舊密碼算法不同，可以設(shè)置：set password for backup00=old_password(1234)）2.2、拷貝數(shù)據(jù)（假如是你完全新安裝mysql主從服務(wù)器，這個(gè)一步就不需要。因?yàn)樾掳惭b的master和slave有相同的數(shù)據(jù)）關(guān)停Master服務(wù)器，將Master中

10、的數(shù)據(jù)拷貝到B服務(wù)器中，使得Master和slave中的數(shù)據(jù)同步，并且確保在全部設(shè)置操作結(jié)束前，禁止在Master和slave服務(wù)器中進(jìn)行寫操作，使得兩數(shù)據(jù)庫(kù)中的數(shù)據(jù)一定要相同！2.3、配置master接下來(lái)對(duì)master進(jìn)行配置，包括打開二進(jìn)制日志，指定唯一的servr ID。例如，在配置文件加入如下值：server-id=1log-bin=mysql-binserver-id：為主服務(wù)器A的ID值log-bin：二進(jìn)制變更日值重啟master，運(yùn)行SHOW MASTER STATUS，輸出如下：2.4、配置slaveSlave的配置與master類似，你同樣需要重啟slave的MySQL。

11、如下：log_bin = mysql-binserver_id = 2relay_log = mysql-relay-binlog_slave_updates = 1read_only = 1server_id是必須的，而且唯一。slave沒(méi)有必要開啟二進(jìn)制日志，但是在一些情況下，必須設(shè)置，例如，如果slave為其它slave的master，必須設(shè)置bin_log。在這里，我們開啟了二進(jìn)制日志，而且顯示的命名(默認(rèn)名稱為hostname，但是，如果hostname改變則會(huì)出現(xiàn)問(wèn)題)。relay_log配置中繼日志，log_slave_updates表示slave將復(fù)制事件寫進(jìn)自己的二進(jìn)制日志(

12、后面會(huì)看到它的用處)。有些人開啟了slave的二進(jìn)制日志，卻沒(méi)有設(shè)置log_slave_updates，然后查看slave的數(shù)據(jù)是否改變，這是一種錯(cuò)誤的配置。所以，盡量使用read_only，它防止改變數(shù)據(jù)(除了特殊的線程)。但是，read_only并是很實(shí)用，特別是那些需要在slave上創(chuàng)建表的應(yīng)用。2.5、啟動(dòng)slave接下來(lái)就是讓slave連接master，并開始重做master二進(jìn)制日志中的事件。你不應(yīng)該用配置文件進(jìn)行該操作，而應(yīng)該使用CHANGE MASTER TO語(yǔ)句，該語(yǔ)句可以完全取代對(duì)配置文件的修改，而且它可以為slave指定不同的master，而不需要停止服務(wù)器。如下：mys

13、ql CHANGE MASTER TO MASTER_HOST=server1,- MASTER_USER=repl,- MASTER_PASSWORD=p4ssword,- MASTER_LOG_FILE=mysql-bin.000001, - MASTER_LOG_POS=0;MASTER_LOG_POS的值為0，因?yàn)樗侨罩镜拈_始位置。你可以用SHOW SLAVE STATUS語(yǔ)句查看slave的設(shè)置是否正確：mysql SHOW SLAVE STATUSG* 1. row *Slave_IO_State: Master_Host: server1 Master_User: repl M

14、aster_Port: 3306 Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 4Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: NoSlave_SQL_Running: No .omitted.Seconds_Behind_Master: NULLSlave_IO_State, Slave_IO_Running,和Slave

15、_SQL_Running是No表明slave還沒(méi)有開始復(fù)制過(guò)程。日志的位置為4而不是0，這是因?yàn)?只是日志文件的開始位置，并不是日志位置。實(shí)際上，MySQL知道的第一個(gè)事件的位置是4。為了開始復(fù)制，你可以運(yùn)行：mysql START SLAVE;運(yùn)行SHOW SLAVE STATUS查看輸出結(jié)果：mysql SHOW SLAVE STATUSG* 1. row *Slave_IO_State: Waiting for master to send event Master_Host: server1 Master_User: repl Master_Port: 3306 Connect_Ret

16、ry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 164Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 164Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes .omitted.Seconds_Behind_Master: 0在這里主要是看: Slave_IO_Running=Yes Slave_SQL_Running=Yesslave的I/O和SQL

17、線程都已經(jīng)開始運(yùn)行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味著一些事件被獲取并執(zhí)行了。如果你在master上進(jìn)行修改，你可以在slave上看到各種日志文件的位置的變化，同樣，你也可以看到數(shù)據(jù)庫(kù)中數(shù)據(jù)的變化。你可查看master和slave上線程的狀態(tài)。在master上，你可以看到slave的I/O線程創(chuàng)建的連接：在master上輸入show processlistG;mysql show processlist G* 1. row *Id: 1User: rootHost: localhost:2096db: testCommand: QueryTi

18、me: 0State: NULLInfo: show processlist* 2. row *Id: 2User: replHost: localhost:2144db: NULLCommand: Binlog DumpTime: 1838State: Has sent all binlog to slave; waiting for binlog to be updatedInfo: NULL2 rows in set (0.00 sec)行2為處理slave的I/O線程的連接。在slave服務(wù)器上運(yùn)行該語(yǔ)句：mysql show processlist G* 1. row *Id: 1U

19、ser: system userHost:db: NULLCommand: ConnectTime: 2291State: Waiting for master to send eventInfo: NULL* 2. row *Id: 2User: system userHost:db: NULLCommand: ConnectTime: 1852State: Has read all relay log; waiting for the slave I/O thread to update itInfo: NULL* 3. row *Id: 5User: rootHost: localhos

20、t:2152db: testCommand: QueryTime: 0State: NULLInfo: show processlist3 rows in set (0.00 sec)行1為I/O線程狀態(tài)，行2為SQL線程狀態(tài)。問(wèn)題與挑戰(zhàn)之 主從數(shù)據(jù)庫(kù)一致性問(wèn)題MYSQL復(fù)制不同步的原因mysql replication（復(fù)制）采用binlog進(jìn)行網(wǎng)絡(luò)傳輸，所以網(wǎng)絡(luò)延時(shí)是產(chǎn)生mysql主從不同步的主要原因，這會(huì)給我們進(jìn)行主從復(fù)制讀寫分離帶來(lái)一定困難為了避免這種情況，在配置服務(wù)器的時(shí)候推薦使用INNODB存儲(chǔ)引擎的表，在主機(jī)上可以設(shè)置sync_binlog下面內(nèi)容摘抄自MYSQL行調(diào)優(yōu)和架構(gòu)設(shè)計(jì)

21、“sync_binlog”：這個(gè)參數(shù)是對(duì)于 MySQL 系統(tǒng)來(lái)說(shuō)是至關(guān)重要的，他不僅影響到 Binlog 對(duì) MySQL 所帶來(lái)的性能損耗，而且還影響到 MySQL 中數(shù)據(jù)的完整性。對(duì)“sync_binlog”參數(shù)的各種設(shè)置的說(shuō)明如下： sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL 不做 fsync 之類的磁盤同步指令刷新 binlog_cache 中的信息到磁盤，而讓 Filesystem 自行決定什么時(shí)候來(lái)做同步，或者 cache 滿了之后才同步到磁盤。 sync_binlog=n，當(dāng)每進(jìn)行 n 次事務(wù)提交之后，MySQL 將進(jìn)行一次 fsync 之類的磁盤同步指令來(lái)將 binl

22、og_cache 中的數(shù)據(jù)強(qiáng)制寫入磁盤。在 MySQL 中系統(tǒng)默認(rèn)的設(shè)置是 sync_binlog=0，也就是不做任何強(qiáng)制性的磁盤刷新指令，這時(shí)候的性能是最好的，但是風(fēng)險(xiǎn)也是最大的。因?yàn)橐坏┫到y(tǒng) Crash，在 binlog_cache 中的所有 binlog 信息都會(huì)被丟失。而當(dāng)設(shè)置為“1”的時(shí)候，是最安全但是性能損耗最大的設(shè)置。因?yàn)楫?dāng)設(shè)置為 1 的時(shí)候，即使系統(tǒng)Crash，也最多丟失 binlog_cache 中未完成的一個(gè)事務(wù)，對(duì)實(shí)際數(shù)據(jù)沒(méi)有任何實(shí)質(zhì)性影響。從以往經(jīng)驗(yàn)和相關(guān)測(cè)試來(lái)看，對(duì)于高并發(fā)事務(wù)的系統(tǒng)來(lái)說(shuō)，“sync_binlog”設(shè)置為 0 和設(shè)置為 1 的系統(tǒng)寫入性能差距可能高達(dá)

23、5 倍甚至更多。如果master主機(jī)上的max_allowed_packet比較大，但是從機(jī)上沒(méi)有配置該值的話，該參數(shù)還是使用默認(rèn)值1MB此時(shí)很有可能導(dǎo)致同步失敗，建議主從兩臺(tái)機(jī)器都設(shè)為5MB比較合適1.配置優(yōu)化在MySQL中，一次事務(wù)提交后，需要寫undo、寫redo、寫binlog，寫數(shù)據(jù)文件等等。在這個(gè)過(guò)程中，可能在某個(gè)步驟發(fā)生crash，就有可能導(dǎo)致主從數(shù)據(jù)的不一致。為了避免這種情況，我們需要調(diào)整主從上面相關(guān)選項(xiàng)配置，確保即便發(fā)生crash了，也不能發(fā)生主從復(fù)制的數(shù)據(jù)丟失。1.1在master上修改配置innodb_flush_log_at_trx_commit = 1sync_bin

24、log = 1上述兩個(gè)選項(xiàng)的作用是：保證每次事務(wù)提交后，都能實(shí)時(shí)刷新到磁盤中，尤其是確保每次事務(wù)對(duì)應(yīng)的binlog都能及時(shí)刷新到磁盤中，只要有了binlog，InnoDB就有辦法做數(shù)據(jù)恢復(fù)，不至于導(dǎo)致主從復(fù)制的數(shù)據(jù)丟失。1.2 在slave上修改配置master_info_repository = TABLErelay_log_info_repository = TABLErelay_log_recovery = 1上述前兩個(gè)選項(xiàng)的作用是：確保在slave上和復(fù)制相關(guān)的元數(shù)據(jù)表也采用InnoDB引擎，受到InnoDB事務(wù)安全的保護(hù)，而后一個(gè)選項(xiàng)的作用是開啟relay log自動(dòng)修復(fù)機(jī)制，發(fā)生c

25、rash時(shí)，會(huì)自動(dòng)判斷哪些relay log需要重新從master上抓取回來(lái)再次應(yīng)用，以此避免部分?jǐn)?shù)據(jù)丟失的可能性。通過(guò)上面幾個(gè)選項(xiàng)的調(diào)整，就可以確保主從復(fù)制數(shù)據(jù)不會(huì)發(fā)生丟失了。但是，這并不能保證主從數(shù)據(jù)的絕對(duì)一致性，因?yàn)?，有可能設(shè)置了ignoredorewrite等replication規(guī)則，或者某些SQL本身存在不確定因素，或者人為在slave上修改數(shù)據(jù)，最終導(dǎo)致主從數(shù)據(jù)不一致。這種情況下，可以采用pt-table-checksum和pt-table-sync工具來(lái)進(jìn)行數(shù)據(jù)的校驗(yàn)和修復(fù)。2. 一致性檢測(cè)和修復(fù)工具pt-table-checksum 和 pt-table-sync問(wèn)題與挑戰(zhàn)之 主從切換1 正常切換1