主庫意外宕機

如果沒有設置主庫的sync_binlog選項，就可能在奔潰前沒有將最后的幾個二進制日志事件刷新到磁盤中。備庫I/O線程因此也可一直處于讀不到尚未寫入磁盤的事件的狀態中。當主庫從新啟動時，備庫將重連到主庫并再次嘗試去讀該事件，但主庫會告訴備庫沒有這個二進制日志偏移量。解決這個問題的方法是指定備庫從下一個二進制日志的開頭讀日志。但是一些事件將永久丟失。可以使用前面文章提到的工具來檢查主從數據一致以及修復pt-table-checksum。即使開啟了sync_binlog，myisam表的數據仍然可能在奔潰的時候損壞。對于innodb表，如果innodb_flush_log_at_trx_commit沒有設置為1，也可能丟失數據，但是數據不會損壞。

因此主庫的參數建議開啟

sync_binlog=1
innodb-flush-log-at-trx-commit=1

MySQL 5.6版本之前存在一個bug，即當啟用上述兩個參數時，會使得InnoDB存儲引擎的group commit失效，從而導致在寫密集的環境中性能的急劇下降。group commit是什么？這是一個知識點，那為什么sync_binlog=1,innodb-flush-log-at-trx-commit=1

會導致組提交失敗？這又是一個知識點，大家可以查閱相關資料。

因此，我們常常在性能和數據一致性中做了妥協，通常將參數innodb-flush-log-at-trx-commit設置為2，而這就導致了master不再是crash safe的，主從數據可能會不一致。關于innodb_flush_log_at_trx_commit的有效值為0,1,2。我這里簡單提一下，因為很多知識點是有連貫性的，往往提到這個問題而又涉及到另外的問題^_^

0代表當提交事務時，并不將事務的重做日志寫入磁盤上的日志文件，而是等待主線程每秒的刷新。當宕機時，丟失1秒的事務。

1和2有點相同，但是不同的地方在于：1表示在執行commit時將重做日志緩沖同步寫到磁盤，即伴有fsync的調用。2表示將重做日志異步寫到磁盤，即寫到文件系統的緩存中。由操作系統控制刷新。因此不能完全保證在執行commit時肯定會寫入重做日志文件，只是有這個動作的發生。

因此為了保證事務的ACID中的持久性，必須將innodb_flush_log_at_trx_commit設置為1，也就是每當有事務提交時，就必須確保事務都已經寫入重做日志文件。那么當數據庫因為意外發生宕機時，可以通過重做日志文件恢復，并保證可以恢復已經提交的事務。而將該參數設置為0或者2，都有可能發生恢復時部分事務的丟失。不同之處在于，設置為2時，當mysql數據庫發生宕機而操作系統及服務器并沒有發生宕機時，由于此時未寫入磁盤的事務日志保存在文件系統緩存中，當恢復時同樣能保證數據不丟失。

對于性能與安全我們都要的情況下，我們肯定會使用RAID，并且開啟Write Back功能，而且RAID卡提供電池備份單元（BBU,Battery Backup Unit），關于這塊的知識，童鞋們可以自行查閱相關資料。

備庫意外宕機：

當備庫在一次非計劃的關閉后重啟時，會去讀master.info文件以找到上次停止復制的位置。不幸的是，該文件可能并沒有同步寫到磁盤，因為該信息是在緩存中，可能并沒有刷新到磁盤文件master.info。文件中存儲的信息可能是錯誤的，備庫可能會嘗試重新執行一些二進制日志事件，這可能導致主鍵沖突，就是我們常常看見的1062錯誤。除非能確定備庫在哪里停止（很難），否則唯一的辦法就是忽略那些錯誤。

在從庫導致復制中斷有兩方面的原因，即replication中的SQL thread和IO thread。首先來看SQL thread，其主要完成兩個操作：

1.運行relay log中對應的事務信息
2.更新relay-info.log文件

更新relay-info.log文件是為了記錄已經執行relay log中的位置，當slave重啟后可以根據這個位置繼續同步relay log。但是，這里用戶會發現這兩個操作不是在一個事務中，一個是數據庫操作，一個是文件操作，因此不能達到原子的效果。此外，MySQL數據庫默認對于文件relay-info.log是寫入到操作系統緩存，因此在發生宕機時可能導致大量的已更新位置的丟失，從而導致重復執行SQL語句，最終的現象就是主從數據不一致。MySQL 5.5新增了參數sync_relay_log_info,可以控制每次事務更新relay-info.log后就進行一次fdatasync操作，這加重了系統負擔，而且即使這樣也可能存在最后一個事務丟失的情況。

IO thread用于同步master上的二進制日志，但是其在crash時依然會導致數據不一致的情況發生。IO thread將收到的二進制日志寫入到relay log，每個二進制日志由多個log event組成，所以每接受到一個log event就需要更新master-info.log。和relay-info.log一樣，其也是寫入操作系統緩存，參數sync_master_info可以控制fdatasync的時間。由于IO thread的更新不能像SQL thread一樣進行放到一個事務進行原子操作，因此其是對數據一致性會產生影響，設想一個log event傳送到了relay log中兩次的情形。

不過好在從MySQL 5.5版本開始提供了參數relay_log_recovery，當發生crash導致重連master時，其不根據master-info.log的信息進行重連，而是根據relay-info中執行到master的位置信息重新開始拉master上的日志數據（不過需要確保日志依然存在于master上，否則就。。。）

so，mysql 5.5版本的從庫推薦配置參數：

sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
read_only          #從庫只讀，但是有super權限的依然可以寫入
relay_log_recovery = 1
skip_slave_start   # 默認啟動從庫就開啟了同步，io線程和sql線程都運行了，該參數是需要手動執行start slave方可啟動同步

復制過濾選項

常常看見很多同學在主庫進行過濾選項設置，當然這也有好處，減少了帶寬，但是在主庫設置過濾選項是非常危險的操作，因為無論是顯示要過濾的或者要同步的，二進制日志只記錄你設置的，其他的是不會記錄的。當主庫有數據需要用到binlog恢復時，你就準備哭吧。所以通常在備庫進行過濾選項設置。比如忽略某個庫，同步所有庫，或者同步某一個庫，當然這會浪費帶寬，但是和安全比起來，這點浪費不算什么。有時候安全與性能往往需要我們自己平衡。

還有就是跨庫更新，如果我們在備庫是這樣設置的，比如同步yayun這個庫

replicate_do_db=yayun

主庫記錄如下：

mysql> select *  from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)mysql> 

備庫記錄如下：

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)mysql> 

現在我們在主庫插入一條記錄

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> insert into yayun.t1 (name) values ('good yayun');
Query OK, 1 row affected (0.01 sec)mysql> select  * from yayun.t1;  
+----+------------+
| id | name       |
+----+------------+
|  1 | yayun      |
|  2 | atlas      |
|  3 | mysql      |
|  5 | good yayun |
+----+------------+
4 rows in set (0.00 sec)mysql> 

查看備庫：

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yayun |
|  2 | atlas |
|  3 | mysql |
+----+-------+
3 rows in set (0.00 sec)mysql> 

怎么回事？怎么沒有同步？這就是跨庫更新帶來的問題，比如下面的更新：

use test
insert into yayun.t1 (name) values ('good yayun')

當然你會說哪個2B會這么干啊，呵呵，有時2B還是有的。所以我們還有另外2個過濾復制參數

replicate_wild_do_table
replicate_wild_ignore_table

一個是要同步的表，一個是不同步的表，通常我們可以這樣寫

replicate_wild_do_table=yayun.%

表示同步yayun庫下面的所有表，這樣就解決的跨庫更新的問題。

復制格式的問題

通常推薦使用ROW格式，為什么使用？看看我前面文章MySQL數據恢復和復制對InnoDB鎖機制的影響

不要用Seconds_Behind_Master來衡量MySQL主備的延遲時間?