高可用數(shù)據(jù)庫架構設計說明

1、. .PAGE13 / NUMPAGES13MySQL數(shù)據(jù)庫高可用架構設計目標：MySQL 數(shù)據(jù)庫服務器不受單點宕機的影響，即時 A 服務器掛掉或者磁盤損壞物理故障導致數(shù)據(jù)庫不可用也不會導致整個系統(tǒng)處于不可用狀態(tài)，因為還有另外一臺備用的數(shù)據(jù)庫服務器可以提供服務。派寶箱采取方案雙機主從熱備 (Mater Slave 模式)背景：雙機熱備的概念簡單說一下，就是要保持兩個數(shù)據(jù)庫的狀態(tài)自動同步。對任何一個數(shù)據(jù)庫的操作都自動應用到另外一個數(shù)據(jù)庫，始終保持兩個數(shù)據(jù)庫數(shù)據(jù)一致。 這樣做的好處:1. 可以做災備，其中一個壞了可以切換到另一個。2. 可以做負載均衡，可以將請求分攤到其中任何一臺上，提高吞吐量。

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

3、服務器將更新寫入二進制日志文件，并維護文件的一個索引以跟蹤日志循環(huán)。這些日志可以記錄發(fā)送到從服務器的更新。當一個從服務器連接主服務器時，它通知主服務器從服務器在日志中讀取的最后一次成功更新的位置。從服務器接收從那時起發(fā)生的任何更新，然后封鎖并等待主服務器通知新的更新。請注意當你進行復制時，所有對復制中的表的更新必須在主服務器上進行。否則，你必須要小心，以避免用戶對主服務器上的表進行的更新與對從服務器上的表所進行的更新之間的沖突。1.1 mysql支持的復制類型：（）：基于語句的復制： 在主服務器上執(zhí)行的SQL語句，在從服務器上執(zhí)行同樣的語句。MySQL默認采用基于語句的復制，效率比較高。 一旦

4、發(fā)現(xiàn)沒法精確復制時， 會自動選著基于行的復制。（）：基于行的復制：把改變的容復制過去，而不是把命令在從服務器上執(zhí)行一遍. 從mysql5.0開始支持（）：混合類型的復制: 默認采用基于語句的復制，一旦發(fā)現(xiàn)基于語句的無法精確的復制時，就會采用基于行的復制。1.2 . 復制解決的問題 MySQL復制技術有以下一些特點： (1) 數(shù)據(jù)分布 (Data distribution )(2) 負載平衡(load balancing) (3) 備份(Backups) (4) 高可用性和容錯行 High availability and failover 1.3 復制如何工作 整體上來說，復制有3個步驟： (

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

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

7、S的緩存中，所以中繼日志的開銷很小。 此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。復制過程有一個很重要的限制復制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。2 .復制配置有兩臺MySQL數(shù)據(jù)庫服務器Master和slave，Master為主服務器，slave為從服務器，初始狀態(tài)時，Master和slave中的數(shù)據(jù)信息一樣，當Master中的數(shù)據(jù)發(fā)生變化時，slave也跟著發(fā)生相應的變化，使得master和slave的數(shù)據(jù)信息同步，達到備份的目的。要點：負

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

9、.info中命令如下：mysql GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*TO backup00IDENTIFIED BY 1234;建立一個backup，并且只能允許從00這個地址上來登陸，密碼是1234。(如果因為mysql版本新舊密碼算法不同，可以設置：set password for backup00=old_password(1234)）2.2、拷貝數(shù)據(jù)（假如是你完全新安裝mysql主從服務器，這個一步就不需要。因為新安裝的master和slave有一樣的數(shù)據(jù)）關停Master服

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

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

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

13、止服務器。如下：mysql 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，因為它是日志的開始位置。你可以用SHOW SLAVE STATUS語句查看slave的設置是否正確：mysql SHOW SLAVE STATUSG* 1. row *Slave_IO_State: Master_Host: server1 Master_U

14、ser: repl Master_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_Run

15、ning,和Slave_SQL_Running是No表明slave還沒有開始復制過程。日志的位置為4而不是0，這是因為0只是日志文件的開始位置，并不是日志位置。實際上，MySQL知道的第一個事件的位置是4。為了開始復制，你可以運行：mysql START SLAVE;運行SHOW SLAVE STATUS查看輸出結果：mysql SHOW SLAVE STATUSG* 1. row *Slave_IO_State: Waiting for master to send event Master_Host: server1 Master_User: repl Master_Port: 3306

16、Connect_Retry: 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=Yessl

17、ave的I/O和SQL線程都已經開始運行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味著一些事件被獲取并執(zhí)行了。如果你在master上進行修改，你可以在slave上看到各種日志文件的位置的變化，同樣，你也可以看到數(shù)據(jù)庫中數(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: testComma

18、nd: QueryTime: 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服務器上運行該語句：mysql show processlist G* 1.

19、row *Id: 1User: 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: rootHos

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

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

22、步指令來將 binlog_cache 中的數(shù)據(jù)強制寫入磁盤。在 MySQL 中系統(tǒng)默認的設置是 sync_binlog=0，也就是不做任何強制性的磁盤刷新指令，這時候的性能是最好的，但是風險也是最大的。因為一旦系統(tǒng) Crash，在 binlog_cache 中的所有 binlog 信息都會被丟失。而當設置為“1”的時候，是最安全但是性能損耗最大的設置。因為當設置為 1 的時候，即使系統(tǒng)Crash，也最多丟失 binlog_cache 中未完成的一個事務，對實際數(shù)據(jù)沒有任何實質性影響。從以往經驗和相關測試來看，對于高并發(fā)事務的系統(tǒng)來說，“sync_binlog”設置為 0 和設置為 1 的系統(tǒng)寫

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

24、 1sync_binlog = 1上述兩個選項的作用是：保證每次事務提交后，都能實時刷新到磁盤中，尤其是確保每次事務對應的binlog都能與時刷新到磁盤中，只要有了binlog，InnoDB就有辦法做數(shù)據(jù)恢復，不至于導致主從復制的數(shù)據(jù)丟失。1.2 在slave上修改配置master_info_repository = TABLErelay_log_info_repository = TABLErelay_log_recovery = 1上述前兩個選項的作用是：確保在slave上和復制相關的元數(shù)據(jù)表也采用InnoDB引擎，受到InnoDB事務安全的保護，而后一個選項的作用是開啟relay log

25、自動修復機制，發(fā)生crash時，會自動判斷哪些relay log需要重新從master上抓取回來再次應用，以此避免部分數(shù)據(jù)丟失的可能性。通過上面幾個選項的調整，就可以確保主從復制數(shù)據(jù)不會發(fā)生丟失了。但是，這并不能保證主從數(shù)據(jù)的絕對一致性，因為，有可能設置了ignoredorewrite等replication規(guī)則，或者某些SQL本身存在不確定因素，或者人為在slave上修改數(shù)據(jù)，最終導致主從數(shù)據(jù)不一致。這種情況下，可以采用pt-table-checksum和pt-table-sync工具來進行數(shù)據(jù)的校驗和修復。2. 一致性檢測和修復工具pt-table-checksum 和 pt-table-sync問題與挑戰(zhàn)之 主從切換1 正常切換1）從服務器檢查SHOW PROCESSLIST語句的輸出，直到你看到Has r