MYSQL主從模式

標籤：版本   sync   ignore   資料庫的配置   tab   變數   ima   name   第一個   

MYSQL的主從介紹

1、在主伺服器將用戶端對資料操作的指令傳到master的服務端，服務端會根據事務的提交順序寫到二進位日誌。

2、在有新的操作後，master會通知slave有新的資料變化。slave通過IO線程將master的二進位日誌傳到slave的中繼日誌中。

3、slave會將中繼日誌中的事件通過SQL線程在備庫上執行。從而實現資料更新，直到追趕上master的資料。

4、在master上並存執行的sql語句，在儲存到備庫的中繼日誌後，只有一個SQL語句對中繼日誌中的事務進行串列執行。

5、在master與slave中的資料達到一致後，slave就會進入休眠狀態，等待下一次的資料同步，下一次的資料同步有master通知slave。

MYSQL的主從配置一、主從資料庫的基本配置

 我在配置主從伺服器時，使用了兩台伺服器：10.19.34.126和10.19.34.91

1、首先需要在這兩台上搭建單獨的mysql服務。

2、在主要資料庫上建立帳號，用於備庫通過這個帳號進行同步主庫的二進位日誌到備庫的中繼日誌。

　　

3、配置主庫，要對每一個資料庫服務配置唯一標示，參數名為server-id，可以配置主庫為server-id=1，也可以通過ip來指定。

　　server-id=10.19.34.92

　　log-bin=mysql-bin        //設定主庫的執行操作儲存到二進位日誌中，確保備庫能夠同步二進位日誌到中繼日誌。

4、配置從庫

　　server-id=10.19.34.126

　　log-bin=mysql-bin    //設定備庫在執行中繼日誌中的事件時將事件重新儲存到二進位日誌中

　　replay_log=mysql-replay-bin   //設定從庫的中繼日誌

　　log_slave_updates=1     //該設定是允許中繼日誌中的事件在二進位日誌中進行重放

　　read-only=1     //設定該從庫為唯讀庫

5、啟動主從服務

　　正常執行主庫啟動指令

　　在備庫上執行指令：change master to master_host=‘server1‘,master_user=‘repl‘ ,master_password=‘123456‘,mater_log_file=‘mysql-bin.000001‘,master_log_pos=0;

　　在備庫上執行指令：start slave;

二、主從資料庫的配置最佳化

　　　　sync_binlog=1      //所有的事務在提交前，將二進位日誌儲存到磁碟。保證在系統崩潰時，不至於資料丟失。該參數預設設定為0。但是該設定只對二進位日誌起作用，而非中繼日誌。並且會帶來一定的開銷。

　　　　innodb_flush_log_at_trx_commit = 2    //如果設定為1 ,InnoDB會在每次提交後重新整理(fsync)交易記錄到磁碟上。

MYSQL的主從原理

 　　　　　1、基於語句的複製

　　　　　　優點：基於語句的複製會在主庫上執行的資料操作在備庫上重新執行一遍。由於基於語句的複製只是簡單的執行sql語句，實現比較簡單。並且在二進位日誌裡事件更加緊湊。

　　　　　　缺點：基於語句的複製在備庫上執行時會帶來資料的不一致性，例如在擷取伺服器的目前時間時，在同步到備庫上時，可能與主庫有一定的偏差。

　　　　　2、基於行的複製

　　　　　　優點：基於行的複製將資料記錄在二進位日誌中，能夠正確的複製每一行。資料的一致性很強。

　　　　　　缺點：佔用的頻寬較大，可能一句簡單的語句會帶來較大的頻寬。

　　　　　3、複製檔案

　　　　　　mysql-bin.index：該檔案中儲存著二進位的所有檔案名稱。

　　　　　　mysql-replay-bin-index：該檔案中儲存著中繼日誌的檔案名稱。

　　　　　　master.info：該檔案儲存在備程式庫伺服器上，該檔案中儲存著主庫的資訊，例如主庫的連接埠，密碼等。

　　　　　　replay-log.info：該檔案儲存在備庫上，用來記錄同步主庫的座標位置。可以在備庫重啟時，通過該檔案來判斷從何處同步主庫。

　　　　　4、發送複製事件到其他備庫

　　　　　　

　　　　　　在將第一個備庫作為其他備庫的主庫時，需要設定第一備庫的二進位檔案和log_slave_upodates為1，第二備庫設定主庫為第一備庫即可。

　　　　　5、複製過濾器

　　　　　　在設定過濾器時，可以在主庫上設定，也可以在備庫上設定。

　　　　　　binlog-do-db=dbname     //該參數會設定將該資料庫的變化儲存到二進位日誌中，將該資料庫的變化同步到備庫。

　　　　　　binlog-ignore-db=dbname   //該參數會忽略該資料庫的變化不會儲存到二進位日誌中，由於該資料庫的二進位日誌沒有儲存，所以在系統崩潰後，重新啟動時，無法恢複該資料庫。

　　　　　　binlog-do-table=tablename   //該參數會設定將該資料庫表的變化儲存到二進位日誌中，將該資料庫表的變化同步到備庫。

　　　　　　binlog-ignore-table=tablename   //該參數會忽略該資料庫表的變化不會儲存到二進位日誌中，由於該資料庫表的二進位日誌沒有儲存，所以在系統崩潰後，重新啟動時，無法恢複該資料庫表。

　　　　　　replicat-do-db=dbname     //該參數會設定將該資料庫的變化同步到中繼日誌中，將該資料庫的變化在備庫上執行。

　　　　　　replicate-ignore-db=dbname   //該參數會忽略該資料庫的變化不會儲存到中繼日誌中。

　　　　　　replicate-do-table=tablename   //該參數會設定將該資料庫表的變化儲存到中繼日誌中，將該資料庫表的變化在備庫上執行。

　　　　　　replicate-ignore-table=tablename   //該參數會忽略該資料庫表的變化不會儲存到中繼日誌中。

　　　　　　注意：1、binlog-ignore-db和binlog-ignore-table參數會過濾對該資料庫和表的操作儲存到二進位日誌中，所以在資料恢複時，導致資料丟失，一般不建議在主庫上設定該參數，

　　　　　　　　　　可以在備庫上進行過濾。　　　　

　　　　　　　　　2、在同時使用binlog-do-db和binlog-ignore-db時，只對binlog-do-db參數有效。

二進位日誌一、二進位日誌基本介紹

　　　　二進位記錄檔只是儲存了對資料庫的變更，對沒有變更的資料操作不會記錄到二進位日誌。

　　　　二進位日誌的內容如所示：

　　　　

　　　　二進位日誌中的欄位如下：

　　　　Event_Type：用來設定資料的操作類型。如Query、Update。

　　　　Server_id：伺服器的id

　　　　Log_name：用來表明該事件儲存的二進位記錄檔名稱

‘　　　　Pos：事件的開始位置

　　　　End_log_pos：事件的結束位置

　　　　info：事件資訊的可讀文本

　　　　二進位檔案的結構：

　　　　

　　　　主要包含二進位檔案和二進位檔案的索引檔案，二進位記錄檔以格式描述事件開始，以日誌輪換事件結束。

　　　　格式描述事件主要包括伺服器版本號碼、伺服器資訊和二進位檔案資訊等。

　　　　日誌輪換事件’主要包括事件在記錄到的下一個記錄檔名稱。

　　　　查看二進位日誌的指令：

　　　　show  binlog  events    //用以查看第一個記錄檔的第一條事件的資訊。

　　　　show  binlog  events  in "檔案名稱"  //用以查看指定二進位日誌的事件資訊

　　　　show  master  status   //通過主伺服器查看當前正在寫入的二進位日誌

二、二進位日誌詳解

二進位日誌結構

記錄語句

記錄事務

二進位日誌管理

二進位日誌選項和變數

建立新的slave

　　　　change  master  to  通過兩個參數master_log_file和master_log_pos來設定從主伺服器同步的位置，而不是從開始同步。如果在建立立slave時，需要以下幾步：

　　　　1、master已經運行了很長一段時間了，可以先備份一次master的資料。

　　　　2、記下master資料庫的binlog的位置。

　　　　3、到新的slave上恢複。

　　　　4、同時設定slave伺服器上的備份位置。

複製master複製slave常見的複製任務一主多從

　　在使用一主多從時一般有一下幾個用途：

　　1、每個從庫只過濾一部分資料操作到中繼日誌中，對資料進行儲存。這樣可以分擔資料庫的壓力。

　　2、將一台資料庫作為隨時變為主庫的可能。

　　

MYSQL主從模式