高可用數(shù)據(jù)庫架構(gòu)設(shè)計(jì)

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

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

3、主服務(wù)器，而一個(gè)或多個(gè)其它服務(wù)器充當(dāng)從服務(wù)器。主服務(wù)器將更新寫入二進(jìn)制日志文件，并維護(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ù)制類型：（）：基于語句的復(fù)制：  在主服務(wù)器上執(zhí)行的SQL語句，在從服務(wù)器上執(zhí)行同樣

4、的語句。MySQL默認(rèn)采用基于語句的復(fù)制，效率比較高。              一旦發(fā)現(xiàn)沒法精確復(fù)制時(shí)，   會(huì)自動(dòng)選著基于行的復(fù)制。    （）：基于行的復(fù)制：把改變的內(nèi)容復(fù)制過去，而不是把命令在從服務(wù)器上執(zhí)行一遍. 從mysql5.0開始支持（）：混合類型的復(fù)制: 默認(rèn)采用基于語句的復(fù)制，一旦發(fā)現(xiàn)基于語句的無法精確的復(fù)制時(shí)，就會(huì)采用基于行的復(fù)制。 1.2 . 復(fù)制解決的問題 

5、60;       MySQL復(fù)制技術(shù)有以下一些特點(diǎn)：         (1)    數(shù)據(jù)分布 (Data distribution )         (2)    負(fù)載平衡(load balancing)         (3

6、)    備份(Backups)          (4)    高可用性和容錯(cuò)行 High availability and failover   1.3 復(fù)制如何工作         整體上來說，復(fù)制有3個(gè)步驟：          (1) 

7、60;  master將改變記錄到二進(jìn)制日志(binary log)中（這些記錄叫做二進(jìn)制日志事件，binary log events）；       (2)    slave將master的binary log events拷貝到它的中繼日志(relay log)；        (3)    slave重做中繼日志中的事件，將改變反映它自己的數(shù)據(jù)。下圖描述了復(fù)制的過程：  

8、                                          該過程的第一部分就是master記錄二進(jìn)制日志。在每個(gè)事務(wù)更新數(shù)據(jù)完成之前，master在二日志記錄這些改

9、變。MySQL將事務(wù)串行的寫入二進(jìn)制日志，即使事務(wù)中的語句都是交叉執(zhí)行的。在事件寫入二進(jìn)制日志完成后，master通知存儲(chǔ)引擎提交事務(wù)。       下一步就是slave將master的binary log拷貝到它自己的中繼日志。首先，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線程將這些事件寫入中繼日

10、志。       SQL slave thread（SQL從線程）處理該過程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新slave的數(shù)據(jù)，使其與master中的數(shù)據(jù)一致。只要該線程與I/O線程保持一致，中繼日志通常會(huì)位于OS的緩存中，所以中繼日志的開銷很小。        此外，在master中也有一個(gè)工作線程：和其它MySQL的連接一樣，slave在master中打開一個(gè)連接也會(huì)使得master開始一個(gè)線程。復(fù)制過程有一個(gè)很重要的限制復(fù)制在sl

11、ave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。 2 .復(fù)制配置有兩臺(tái)MySQL數(shù)據(jù)庫服務(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)作的媒介是主服務(wù)器的二進(jìn)制變更日志，這個(gè)日志記載著需要傳輸給從服務(wù)器的各種修改動(dòng)作。因此，主服務(wù)器必須激活二進(jìn)制日志功能。從服務(wù)器必須具備足以讓它連接主服務(wù)器并請(qǐng)求主服務(wù)器

12、把二進(jìn)制變更日志傳輸給它的權(quán)限。        環(huán)境：Master和slave的MySQL數(shù)據(jù)庫版本同為5.0.18操作系統(tǒng)：unbuntu 11.10IP地址：002.1、創(chuàng)建復(fù)制帳號(hào)1、在Master的數(shù)據(jù)庫中建立一個(gè)備份帳戶：每個(gè)slave使用標(biāo)準(zhǔn)的MySQL用戶名和密碼連接master。進(jìn)行復(fù)制操作的用戶會(huì)授予REPLICATION SLAVE權(quán)限。用戶名的密碼都會(huì)存儲(chǔ)在文本文件中命令如下：mysql > GRANT REPLICATION SLAVE,REL

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

14、務(wù)器，將Master中的數(shù)據(jù)拷貝到B服務(wù)器中，使得Master和slave中的數(shù)據(jù)同步，并且確保在全部設(shè)置操作結(jié)束前，禁止在Master和slave服務(wù)器中進(jìn)行寫操作，使得兩數(shù)據(jù)庫中的數(shù)據(jù)一定要相同！2.3、配置master 接下來對(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的配置與maste

15、r類似，你同樣需要重啟slave的MySQL。如下：log_bin           = mysql-binserver_id         = 2relay_log         = mysql-relay-binlog_slave_updates = 1read_only     

16、;    = 1server_id是必須的，而且唯一。slave沒有必要開啟二進(jìn)制日志，但是在一些情況下，必須設(shè)置，例如，如果slave為其它slave的master，必須設(shè)置bin_log。在這里，我們開啟了二進(jìn)制日志，而且顯示的命名(默認(rèn)名稱為hostname，但是，如果hostname改變則會(huì)出現(xiàn)問題)。relay_log配置中繼日志，log_slave_updates表示slave將復(fù)制事件寫進(jìn)自己的二進(jìn)制日志(后面會(huì)看到它的用處)。有些人開啟了slave的二進(jìn)制日志，卻沒有設(shè)置log_slave_updates，然后查看slave的數(shù)據(jù)是否改變，這是一種

17、錯(cuò)誤的配置。所以，盡量使用read_only，它防止改變數(shù)據(jù)(除了特殊的線程)。但是，read_only并是很實(shí)用，特別是那些需要在slave上創(chuàng)建表的應(yīng)用。  2.5、啟動(dòng)slave接下來就是讓slave連接master，并開始重做master二進(jìn)制日志中的事件。你不應(yīng)該用配置文件進(jìn)行該操作，而應(yīng)該使用CHANGE MASTER TO語句，該語句可以完全取代對(duì)配置文件的修改，而且它可以為slave指定不同的master，而不需要停止服務(wù)器。如下：mysql> CHANGE MASTER TO MASTER_HOST='server1', &

18、#160;  -> 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語句查看slave的設(shè)置是否正確：mysql&

19、gt; SHOW SLAVE STATUSG * 1. row *             Slave_IO_State:                Master_Host: server1         

20、0;      Master_User: repl                Master_Port: 3306              Connect_Retry: 60       

21、0;    Master_Log_File: mysql-bin.000001        Read_Master_Log_Pos: 4             Relay_Log_File: mysql-relay-bin.000001         &

22、#160;    Relay_Log_Pos: 4      Relay_Master_Log_File: mysql-bin.000001           Slave_IO_Running: No          Slave_SQL_Running: No   

23、                          .omitted.      Seconds_Behind_Master: NULL Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No表明slave還沒有開

24、始復(fù)制過程。日志的位置為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 

25、60;              Master_Host: server1                Master_User: repl               

26、 Master_Port: 3306              Connect_Retry: 60            Master_Log_File: mysql-bin.000001        Read_Master_Log_Pos: 164 

27、60;           Relay_Log_File: mysql-relay-bin.000001              Relay_Log_Pos: 164      Relay_Master_Log_File: mysql-bin.000001   

28、60;       Slave_IO_Running: Yes          Slave_SQL_Running: Yes                         &#

29、160;   .omitted.      Seconds_Behind_Master: 0在這里主要是看:                   Slave_IO_Running=Yes             

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

31、G* 1. row *     Id: 1   User: root   Host: localhost:2096     db: testCommand: Query   Time: 0 State: NULL   Info: show processlist* 2. row *     Id: 2 

32、60; User: repl   Host: localhost:2144     db: NULLCommand: Binlog Dump   Time: 1838 State: Has sent all binlog to slave; waiting for binlog to be updated   Info: NULL2 rows in set (0.00 sec) 行2為處理slave的I/O線程的連接。在sla

33、ve服務(wù)器上運(yùn)行該語句： mysql> show processlist G* 1. row *     Id: 1   User: system user   Host:     db: NULLCommand: Connect   Time: 2291 State: Waiting for master to send event   Info:

34、NULL* 2. row *     Id: 2   User: system user   Host:     db: NULLCommand: Connect   Time: 1852 State: Has read all relay log; waiting for the slave I/O thread to update it   Info: NULL*

35、 3. row *     Id: 5   User: root   Host: localhost:2152     db: testCommand: Query   Time: 0 State: NULL   Info: show processlist3 rows in set (0.00 sec)行1為I/O線程狀態(tài)，行2為SQL線程狀態(tài)。問題與挑戰(zhàn)之 主從數(shù)

36、據(jù)庫一致性問題MYSQL復(fù)制不同步的原因mysql replication（復(fù)制）采用binlog進(jìn)行網(wǎng)絡(luò)傳輸，所以網(wǎng)絡(luò)延時(shí)是產(chǎn)生mysql主從不同步的主要原因，這會(huì)給我們進(jìn)行主從復(fù)制讀寫分離帶來一定困難 為了避免這種情況，在配置服務(wù)器的時(shí)候推薦使用INNODB存儲(chǔ)引擎的表，在主機(jī)上可以設(shè)置sync_binlog下面內(nèi)容摘抄自MYSQL行調(diào)優(yōu)和架構(gòu)設(shè)計(jì)“sync_binlog”：這個(gè)參數(shù)是對(duì)于 MySQL 系統(tǒng)來說是至關(guān)重要的，他不僅影響到 Binlog 對(duì) MySQL 所帶來的性能損耗，而且還影響到 MySQL 中數(shù)據(jù)的完整性。對(duì)“sync_binlog”參數(shù)的各種設(shè)置的說明如下：

37、 sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL 不做 fsync 之類的磁盤同步指令刷新 binlog_cache 中的信息到磁盤，而讓 Filesystem 自行決定什么時(shí)候來做同步，或者 cache 滿了之后才同步到磁盤。 sync_binlog=n，當(dāng)每進(jìn)行 n 次事務(wù)提交之后，MySQL 將進(jìn)行一次 fsync 之類的磁盤同步指令來將 binlog_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

38、 中的所有 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ù)沒有任何實(shí)質(zhì)性影響。從以往經(jīng)驗(yàn)和相關(guān)測(cè)試來看，對(duì)于高并發(fā)事務(wù)的系統(tǒng)來說，“sync_binlog”設(shè)置為 0 和設(shè)置為 1 的系統(tǒng)寫入性能差距可能高達(dá) 5 倍甚至更多。如果master主機(jī)上的max_allowed_packet比較大，但是從機(jī)上沒有配置該值的話，該參數(shù)還是使用默認(rèn)值1MB此時(shí)很有可能導(dǎo)致同步失敗，建議主從兩臺(tái)機(jī)器都設(shè)為5MB比較合適1.配置優(yōu)化在MySQL中，一次事務(wù)

39、提交后，需要寫undo、寫redo、寫binlog，寫數(shù)據(jù)文件等等。在這個(gè)過程中，可能在某個(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_binlog = 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 = "TABLE"relay_log_info_repository = "TABLE"relay_log_recovery = 1上述前兩個(gè)選項(xiàng)的作用是：確保在slave上和復(fù)制相關(guān)的元數(shù)據(jù)表也采用InnoDB引擎，受到InnoDB事務(wù)安全的保護(hù)，而后一個(gè)選項(xiàng)的作用是開啟relay log自動(dòng)