一、相關概念：
日志（二進制日志、事務日志、錯誤日志、一般查詢日志、中繼日志、慢查詢日志）
二進制日志（記錄引起或潛在引起資料庫database發生改變的操作；用于做即時點恢複（即時點還原）；位置（資料目錄下）；滾動（達到最大上限；執行>flush logs；重新開機服務）；删除>purge to（不可用#rm指令直接删除）；格式（statement、row、mixed）；相關指令（>show master status;、show binlog eventsin FILE;、show binary logs;）
二進制日志并不能保證重放後的資料與原資料庫資料完全一緻，它替代不了備份（即時點還原的資料可能會與原資料不完全一緻，主機有多顆CPU，DB的storage engine支援事務，多個事務在執行時是并行的，但目前往二進制日志檔案中寫的僅一個，隻要往裡寫就是串形的，同時每個事務執行時若隔離級别低的話會交叉執行（事務隔離級别低會産生影響），是以導緻結果很可能與原資料不完全一緻，若要完全一緻，将原始資料複制一份，這樣産生的影響最小）
隔離級别（read uncommitted；read committed；repeatable read（mysql預設此項）；serializable
（repeatable read+statement）或（{readuncommitted,read committed}+mixed）
不建議使用以上這幾種方式，二進制日志記錄的事件再跑一遍極大可能資料不一緻，而要使用（repeatable read+{row,mixed}）或（{readuncommitted,read committed}+row）
注：statement（mysql官方已不建議使用），mysql5.X建議使用row
在某些場景下，資料庫不一緻會導緻異常，是以最好将資料庫保持一緻
master-->slave（在master上，前端使用者或程式app每執行一個引起變化的語句都會寫入目前DB并記錄到二進制日志中儲存為事件，然後将儲存的事件（二進制日志）通過3306端口發給slave（slave不接受前端使用者或app的更改操作），slave接收下來儲存在本地的中繼日志中relay-log，從relay-log中每次讀一個事件并執行，将結果儲存至本地的DB中
mysql中一個查詢隻能在一個CPU上運作，若主機是四核的，事務在每個CPU上運作這是并行的，事務運作是若是寫操作，往DB中寫這很快，但往二進制日志中記錄時，要先在記憶體的緩沖區中，過一會才會同步到二進制日志檔案（同步時是一條一條儲存的），隻有同步到二進制日志檔案中才能一條一條地傳給slave，slave再開始一條一條地儲存至relay-log進而一條一條地執行，是以master要比slave快的多，有時slave要比master慢半小時以上
預設mysql複制replication是異步的（隻要寫入DB就傳回成功，假如是同步的，前端使用者或app可能要等很長時間）
mysql的半同步（master僅保證将二進制日志傳給最近的一個slave，同步完成即傳回成功；mysql允許一主多從，與DNS類似）與drbd的半同步（傳至slave的TCP/IP協定棧即傳回成功）是兩碼事
mysql5.5之前版本不支援半同步semi-sync（semi-sync是由google提供的插件）
slave上本身可執行寫操作，但在master-slave架構中，slave上若寫了資料，它不能同步到master上，這會導緻兩端DB不一緻，是以一般禁止slave上寫
master上的二進制日志與slave上的relay-log在大小和個數上會不一樣（bin log和relay log的格式是一樣的），因為master上的mysql服務若重新開機則會自動滾動記錄下個日志
若不是多級複制，slave上可不要二進制日志（例如master-->slave1-->slave2，slave2是不需要bin log，中間的slave1上bin log和relay log都要有）
master-master（雙主模型，mysql支援但在生産中不建議使用，兩個mysql都可寫，要解決同時讀寫帶來DB不一緻狀況，類似高可用的CFS，但這對于關系型DB太複雜了，在某一node執行的事務有很多，要将狀态通知給另一node，事務執行時将一部分語句發給node1一點，再将一部分語句發給node2一點，兩端還要通知，事務還要保證原子性atomicity（要麼都執行要麼都不執行），是以很混亂）
server-id（至關重要，解決循環複制，每個node的server-id要唯一，接收另一node發來的二進制日志與本地的比較，相同的不接收或接收下來不應用）
雙主模型可以分攤讀操作，但無法減輕寫操作（每個node對于寫操作絕不能少，否則兩端資料不一緻（這是緻命的））
雙主的缺陷：舉例（對于一個表中name,age兩個字段，在兩個node上分别執行如下語句，一合并，DB會混亂）
兩條記錄：
tom 10
jerry 30
>UPDATE tutors SET name=’jerry’ WHERE age=10;
>UPDATE tutors SET age=30 WHERE name=’tom’;
replication的作用：
作冷備份（master若故障，将slave端停止服務并做備份，将資料拿到master上恢複即可，注意要記錄master上二進制日志的最後位置用于即時點還原）
提供高可用功能（master若故障，将slave的寫開啟并将落後的二進制日志執行完，提升為主即可）
分攤負載/讀寫分離（讓master隻負責寫，slave負責讀，一般都是讀多寫少，除非線上事務處理，是以多加幾台slave-server）
<a href="http://s5.51cto.com/wyfs02/M01/78/3D/wKiom1Z4ubeBjOdTAABTL6-EQJo982.jpg" target="_blank"></a>
一主多從模型中，主寫、從讀，前端使用者或app要連接配接mysql要找哪一個，中間要安裝mysql proxy（它工作在應用層，要能精确了解某種協定，在這裡它要了解每個SQL語句），通過mysql proxy可将前端user或app所要執行的操作定向至特定的server，這叫mysql的讀寫分離rw-splitting
若要在每個slave上實作負載均衡，在其前端加上director（LVS或haproxy）
對于master、mysql-proxy、director的高可用，可各自提供一台備用，也可隻提供一台備用server，這三個node若某一故障使用這個備用server（高可用叢集中N-m，N個server運作m個服務）
memcached（旁路緩存伺服器，緩存每個slave上查詢的資料，目前端user或app首次發起查詢請求，memcached中若沒有讓user或app自己連接配接mysql-proxy到後端slave查詢，查詢出的資料先儲存一份到memcached中，再傳回給前端user或app，當下次查詢同樣内容直接從memcached中傳回即可）
mysql自身有緩存，但在一叢集中，每次請求可能會在不同的mysql-slave上，這時mysql自身緩存就沒意義了，兩種方式：持久連接配接；共享式緩存（memcached，它本身是個程式設計API，緩存功能靠程式自身來實作）
一主多從模型（按預設使用異步方式，若有10個從，那在master上就要啟動10個複制線程dump用來同步二進制日志，這将導緻master壓力過大，解決方案：多級複制，單獨拿出一台server（node1）作為架構中master的從，node1是其它10個從的主（主隻有一個從，是node1，其它10個從是node1的從），注意在node1上必須要有relay log和bin log，其它10個從可僅有relay log）
注：一主可以有多從，但一個從隻能屬一個主
node1-server（隻負責發送二進制日志，用于減輕master複制的壓力，并不負責讀和寫，但寫操作相關語句的執行結果隻有儲存至DB中才能寫入bin log，隻要往DB中寫就要增加系統性能用于磁盤IO，關鍵是它儲存到DB中我們也不用它，解決方案：将node1的storage engine改為black hole（/dev/null））
一主多從架構中完成rw-splitting，必須要配置mysql-proxy（對于一主多從模型，若前端僅管理者使用，寫時連接配接到master，讀時斷開之前連接配接再次重連至slave，這比較麻煩；但是在LAMP架構中，php開發出的程式（php本身與mysql并無關，是使用php語言開發出的程式，如discuz要用到資料時，通過驅動連接配接至DB才産生互動）要通路DB，若不使用mysql-proxy，如何讓主的寫從的讀？解決方法：再次開發這個程式讓其自身解決rw-splitting和LB
半同步semi-sync，master隻確定離master最近的一台slave（一堆從中的一個）同步成功，若時間太長沒有slave響應，則降級為異步模式繼續工作
mysql5.6引入GTID（global transaction identifer），使得mysql複制更安全、多事務執行時不會産生混亂、引入多線程複制（multi thread replication）
特殊情形，如mysql伺服器已運作很長時間，現在要做主從複制，将master的DB備份，記錄二進制日志檔案名及事件位置，然後在slave上将備份還原，slave與master連接配接時指定說明master上哪個檔案名及事件位置
複制線程（master（dump），slave（io_thread、sql_thread））
multi thread replication（是在從端啟動多個sql_thread來完成多線程複制，一個庫隻能使用1個線程，多個庫多線程并發執行這才有意義，若僅1個庫就算開啟多線程也僅使用1個線程複制）
每個從分别對應主的dump（若10個從，則在主端要啟動10個dump，dump在有複制時會自動啟動），io_thread會到master上查詢二進制日志，若發現有資料要同步，則dump會發給io_thread，io_thread接收下來儲存至relay log中，sql_thread每次從relay log中讀一個事件，進行重放redo執行并在本地應用，若master上的dump不線上，則slave上的io_thread連不上dump就停了，而sql_thread仍能正常工作（io_thread不影響sql_thread，sql_thread隻監控relay log中有無更新資料）
若複制時跨越網際網路，要使用ssl（支援雙向認證，client<-->server，dump<-->io_thread）
1、複制的作用：輔助實作備份；高可用HA；異地容災；分攤負載(scaleout)；rw-spliting(mysql proxy工作在應用層)。
2、master有多個CPU允許事務并行執行，但往二進制日志檔案隻能一條條寫；slave比master要慢；master-slave預設異步方式傳送。
3、半同步：僅負責最近一台slave同步成功，其它的slave不管，5.5之前不支援半同步，半同步應指定timeout間隔，若逾時則降級為異步模式繼續工作。
4、slave-server本身可以寫操作，但在master-slave架構中不允許slave寫，因為它無法同步至其它server。
5、slave可向master做冷備份。
6、master的二進制日志檔案和slave上的中繼日志檔案在檔案大小和個數上都不會一樣，還原隻能用master的二進制日志檔案，不能使用中繼日志檔案。
7、本地一定要有中繼日志和二進制日志；slave-side隻要不做多級複制，可不要二進制日志檔案；多級複制可減輕master複制壓力；slave-side存儲引擎可用blackhole。
8、master-side若宕掉，可将slave-side提升為master（執行二進制日志），進而實作高可用。
slave-side，IO_thread用于從主端接收dump_thread發來的二進制日志語句然後儲存為本地的中繼日志，SQL_thread讀中繼日志轉為資料檔案成功後再儲存二進制日志。
9、server-id避免循環複制。
10、雙主無法減輕寫操作。
11、主從架構中，不使用mysql-proxy，如何讓master寫，slave讀：讓程式（PHP開發出的程式）自身具有讀寫分離的功能；雙主模型。
12、生産環境下不建議使用雙主模型。
13、雙主模型産生的問題：兩人同時更改同一表的不同字段或插入資料，送出後可能會導緻資料庫崩潰或出現非正常錯誤。
14、資料庫server壓力大時，兩種方案：scaleout；scale on
15、scale out：根據業務分庫，每個業務涉及到的庫放到一個實體伺服器上（垂直拆分），但資料有熱區，例如，100G的資料，僅1個G很BUSY，其它很閑，而這1G的資料在一個表裡；拆表（水準拆分），rid(row id)。
注：能不拆則不拆否則後續問題很難排查。
16、一個從隻能有一個主，一主可以有多從。
17、讀寫分離：mysql-proxy、amoeba(java),配置檔案xml格式
18、coba(amoeba)：資料拆分。
19、複制線程：master-side(dump)、slave-side(IO_thread、SQL_thread)
20、預設情況下MySQL的複制是異步的，Master上所有的更新操作寫入Binlog之後并不確定所有的更新都被複制到Slave之上。異步操作雖然效率高，但是在Master/Slave出現問題的時候，存在很高資料不同步的風險，甚至可能丢失資料。
21、MySQL5.5引入半同步複制功能的目的是為了保證在master出問題的時候，至少有一台Slave的資料是完整的。在逾時的情況下也可以臨時轉入異步複制，保障業務的正常使用，直到一台salve追趕上之後，繼續切換到半同步模式。
<a href="http://s2.51cto.com/wyfs02/M00/85/DE/wKiom1etGAbQpn_PAACyGA9Di68568.jpg" target="_blank"></a>
注：主從同步中，是主庫主動push推（而不是從庫從主庫上pull拉）
slave possible role：
failover server;
used for performancing backups;
read load balancing;
additional slaves allow scale-out;
二、MySQL5.5.45主從複制、半同步複制、資料庫複制過濾，具體操作：
1、主從複制配置步驟：
master-side:
#vi /etc/my.cnf
[mysqld]
log-bin = master-bin （開啟二進制日志）
log-bin-index = master-bin.index （定義二進制日志索引檔案）
server-id = 1 （與slave-side不能一樣，避免循環複制）
sync_binlog = 1 （此項用于事務安全，設定事務一送出就寫入二進制日志檔案）
innodb_flush_logs_at_trx_commit = 1 （每事務同步）
innodb_file_per_table = 1 （隻要支援事務的此項必開，每表一個表空間）
datadir = /mydata/data
log_format = mixed
>GRANT REPLICATION SLAVE ON *.* TO ‘repluser’@’192.168.1.%’ IDENTIFIED BY ‘repluser’;（建立使用者具有複制權限，權限有replication slave（具有從master二進制日志複制的權限）、replication client（具有連接配接master并擷取相關資訊的權限））
>FLUSH PRIVILEGES;
slave-side:
relay-log = relay-log
relay-log-index = relay-log.index
server-id = 11
read_only = 1 （slave-side不允許寫資料，僅讀，但此項對有SUPER權限的使用者不生效，可使用1|true|on|yes都可）
skip_slave_start = 1 （服務啟動不執行同步，待手動開啟IO_THREAD和SQL_THREAD，用于master-side資料出錯暫不往slave-side同步，等master-side資料正常，再手動開始同步）
>CHANGE MASTER TO MASTER_USER=’repluser’,MASTER_PASSWORD=’repluser’,MASTER_HOST=’192.168.1.222’,MASTER_LOG_FILE=’master_bin.000010’,MASTER_LOG_POS=107;
>START SLAVE; （也可分開執行>START SLAVE IO_THREAD;>START SLAVE SQL_THREAD;）若此步出現錯誤could notinitialization master info structure...執行>RESET SLAVE;再重新執行>CHANGE MASTER TO那條語句即可。
>SHOW SLAVE STATUS\G （檢視IO_THREAD和SQL_THREAD是否為ON狀态，Exec_Master_Log_Pos:為目前執行的位置，Seconds_Behind_Master:從比主慢的時間，Master_SSL_Allowed:是否啟用用ssl）
>STOP SLAVE IO_THREAD; （master上資料若有問題時，可将slave-side的IO_THREAD停掉）
slave-side重新開機mysqld，IO_THREAD和SQL_THREAD會自動啟動，資料目錄下/mydata/data/{relay-log.info,master.info}這兩個檔案是replication的基礎和前提，決定服務啟動時從哪個地方開始讀取，若不想讓服務一啟動就自動執行複制，可将這兩個檔案剪切至其它地方，再重新配置slave-side
[root@node2 ~]# cd /mydata/data
[root@node2 data]# file master.info
master.info: ASCII text
[root@node2 data]# file relay-log.info
relay-log.info: ASCII text
2、配置半同步複制：
#ll /usr/local/mysql/lib/
semisync_master.so semisync_slave.so （插件由google提供）
MySQL在加載并開啟semi-sync插件後，每一個事務需等待備庫接收日志後才傳回給用戶端。如果做的是小事務，兩台主機的延遲又較小，則Semi-sync可以實作在性能很小損失的情況下的零資料丢失。
>INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
>SHOW GLOBAL VARIABLES LIKE ‘%rpl_semi%’;
rpl_semi_sync_master_enabled 設為1
rpl_semi_sync_master_timeout 預設為10S （如果主備網絡故障或者備庫挂了，主庫在事務送出後等待10秒，無響應則自動轉為異步狀态）
>SET GLOBAL rpl_semi_sync_master_enabled=1; （僅目前生效，可寫入配置檔案）
slave_side:
>INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
>SET GLOBAL rpl_semi_sync_slave_enabled=1;
>STOP SLAVE;
>START SLAVE;
>SHOW GLOBAL STATUS LIKE ‘%rpl%’;
Rpl_semi_sync_master_clients和Rpl_semi_sync_master_status要為打開狀态，半同步複制才配置成功
>SHOW SLAVE STATUS\G
檢視Seconds_Behind_Master
3、資料庫複制過濾：
binlog-do-db = DB_NAME（白名單，僅将指定某資料庫的操作記入二進制日志）
binlog-ignore-db = DB_NAME（黑名單，不記錄某資料庫的操作到二進制日志）
注：不建議在master-side操作，否則二進制日志不完整。
replicate-do-db = DB_NAME
replicate-ignore-db = DB_NAME
replicate-do-table = TABLE_NAME
replicate-ignore-table = TABLE_NAME
replicate-wild-do-table = TABLE_NAME （支援通配符%和_）
replicate-wild-ignore-table = TABLE_NAME
舉例：在slave-side:
#vim /etc/my.cnf
添加replicate-do-db = test1
replicate-ignore-db = test2
#service mysqld restart
>SHOW SLAVE STATUS\G （檢視如下兩項）
Replicate_Do_DB: test1
Replicate_Ignore_DB: test2
[root@node1 ~]# yum -y --nogpgchecklocalinstall percona-toolkit-2.2.16-1.noarch.rpm
或使用源碼包安裝，安裝方法在安裝目錄下README檔案中有說明：
perl Makefile.PL
make
make test
make install
[root@node1 ~]# pt<TAB>
pt-align pt-duplicate-key-checker pt-ioprofile pt-show-grants pt-table-checksum
ptar pt-fifo-split pt-kill pt-sift pt-table-sync
pt-archiver pt-find pt-mext pt-slave-delay pt-table-usage
ptardiff pt-fingerprint pt-mysql-summary pt-slave-find pt-upgrade
pt-config-diff pt-fk-error-logger pt-online-schema-change pt-slave-restart pt-variable-advisor
pt-deadlock-logger pt-heartbeat pt-pmp pt-stalk pt-visual-explain
pt-diskstats pt-index-usage pt-query-digest pt-summary ptx
例如：
#pt-ioprofile（Watch process IO andprint a table of file and I/O activity詳細評估目前主機IO能力）
#pt-slave-delay（Make a MySQLslave server lag behind its master有意讓slave比master慢）
#pt-slave-find（Find andprint replication hierarchy tree of MySQL slaves）
#pt-slave-restart（Watch andrestart MySQL replication after errors）
#pt-show-grants（Canonicalizeand print MySQL grants so you can effectively replicate, compare andversion-control them）
#pt-summary（Summarize systeminformation nicely收集伺服器資訊）
#pt-diskstats（An interactive I/Omonitoring tool for GNU/Linux磁盤相關統計資料）
#pt-index-usage（Read queriesfrom a log and analyze how they use indexes目前索引表使用情況）
#pt-visual-explain（FormatEXPLAIN output as a tree可視化分析查詢）
#pt-table-checksum（Verify MySQLreplication integrity檢查slave資料是否與master資料一緻）
本文轉自 chaijowin 51CTO部落格，原文連結：http://blog.51cto.com/jowin/1690086，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)