MySQL主從複製、基於SSL的主從複製、主主複製、半同步複製

MySQL複製分為主從複製，主主複製。 什麼是主從複製主從複製指有一台器做為主伺服器，一台或多台伺服器做為從伺服器。從伺服器不寫入任何資料，資料的來源是主伺服器。複製的目的是為了資料同步，如果從伺服器也寫入資料的話，在主從架構裡，主伺服器是不會複製從伺服器的資料的，所以會造成資料的不同步，所以，從伺服器不能寫入資料。MySQL複製的基礎，就是二進位日誌，因為二進志日誌裡面，記錄了所有能改變或者能潛在改變資料庫資料的SQL語句，當從伺服器把這些SQL語句複製過來之後，再執行一遍，資料就會跟主伺服器相同了。所以，從伺服器的MySQL版本，一定要比主伺服器高或才跟主伺服器相同，最好是主從版本一樣，這樣就不會因為不同版本，SQL語句不同，而造成錯誤。什麼是主主複製主主複製是指，兩台或多台MySQL伺服器，都為主伺服器，大家相互複製，這樣的好處是，每台伺服器，都可以進行資料的寫入操作，而主從裡面，從伺服器是不能進行寫操作的。主主複製的原理，跟主從複製差不多，只不過是資料之間，是相互複製，相互同步的。不管是主從複製，還是主主複製，其複製過程，都是非同步。可以加快資料的查詢，而對資料的寫入，沒有任何提高。做主從或者主主複製，也可以實現對資料的冗餘，當從伺服器DOWN機的時候，主伺服器可以正常工作而不受任何影響。當主伺服器DOWN機的時候，只需要將從伺服器暫時切換為主伺服器即可。也方便資料的備份，備份的時候，可以停止從伺服器，然後對從伺服器上的資料進行備份，備份完成後，再啟用。 主從複製的實現START SLAVE時，從伺服器建立一個I/O線程，以串連主伺服器並讓主伺服器發送二進位日誌。主伺服器建立一個Binlog Dump線程將二進位日誌中的內容發送到從伺服器。從伺服器I/O 線程讀取主伺服器Binlog Dump線程發送的內容並將該資料拷貝到從伺服器資料目錄中的本地檔案中，即中繼日誌。第3個線程是SQL 線程，從伺服器使用此線程讀取中繼日誌並執行日誌中包含的更新。SHOW PROCESSLIST語句可以查詢在主伺服器上和從伺服器上發生的關於複製的資訊。 從伺服器在資料目錄中另外建立兩個狀態檔案master.info 和relay-log.info。狀態檔案儲存在硬碟上，從伺服器關閉時不會丟失。下次從伺服器啟動時，讀取這些檔案以確定它已經從主伺服器讀取了多少二進位日誌，以及處理自己的中繼日誌的程度。 從伺服器flush tables with read lock;之後，資料暫時就不能同步了，只有解鎖之後，複製過來的語句，才會被執行。從伺服器可以不用關閉二進位日誌，也可以不用開戶中繼日誌，因為伺服器會自動幫你開戶或關閉。不關閉二進位日誌，從主伺服器複製過來的SQL語句，執行的時候，也不會被記錄到二進位日誌，只有在本機執行的SQL語句，才會被記錄到二進位日誌。中繼日誌預設存放在資料目錄下，檔案名稱為 主機名稱.relay-00000N。如果想儲存到其它位置，可以在設定檔裡面進行指定。 1、安裝MySQL資料庫（略）2、配置主從伺服器主伺服器操作 

 

  
# vim /etc/my.cnf 
  
server-id = 1   //一定不能跟從伺服器一樣，這裡使用預設即可 
  
log-bin = mysql-bin //開戶指定MySQL的二進位日誌及儲存位置，預設開戶，儲存在資料目錄中 
  
sync-binlog=1       //立即同步到磁碟，建議開戶 
  
 
  
mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO repl@'1.1.1.2' IDENTIFIED BY '123456'; 
  
mysql> FLUSH PRIVILEGES; 
  
    REPLICATION CLIENT允許在複製主機（Master）和從機（Slave）上使用SHOW STATUS  
  
    REPLICATION SLAVE允許複製從伺服器串連到主伺服器  
 

 從伺服器操作 

 

  
# vi /etc/my.cnf 
  
#log-bin = mysql-bin    //加#號表示關閉，也可以不關閉 
  
server-id = 11          //ID號，一定不能跟主伺服器相同 
  
relay-log = relay-bin   //定義中繼日誌儲存的位置及檔案名稱，也可以不用定義 
  
relay-log-index = relay-bin-index   //中繼日誌索引檔案的名字及儲存位置 
  
 
  
mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1',MASTER_USER='repl',MASTER_PASSWORD='123456'; 
  
mysql> START SLAVE; 
  
mysql> SHOW SLAVE STATUS; 
 

 能看到以下兩項為YES,說明從伺服器工作正常 

 

  
Slave_IO_Running: Yes 
  
Slave_SQL_Running: Yes 
 

  

 

  
mysql> show processlist \G 
  
*************************** 1. row *************************** 
  
     Id: 6 
  
   User: system user 
  
   Host:  
  
     db: NULL 
  
Command: Connect 
  
   Time: 1159 
  
  State: Waiting for master to send event 
  
   Info: NULL 
  
*************************** 2. row *************************** 
  
     Id: 7 
  
   User: system user 
  
   Host:  
  
     db: NULL 
  
Command: Connect 
  
   Time: -153412 
  
  State: Slave has read all relay log; waiting for the slave I/O thread to update it 
  
   Info: NULL 
 

   可以看到有兩個線程，一個等待主伺服器發送資料的線程，叫I/O,一個讀取中繼日誌並執行，然後等待slave上的I/O線程更新中日誌的，叫SQL線程 然後在主伺服器上建立一個資料庫，在從伺服器上查看一下，可以看到資料庫，說明是OK的。如果從伺服器沒有關閉二進位日誌，主伺服器上建立資料庫，在從伺服器同步完成後，可以在中從伺服器的中繼日誌裡面，看到建立資料庫的SQL語句，但是在二進位日誌裡面，是看不到的。從伺服器的二進位日誌，只記錄直接在從伺服器上執行SQL語句，而不記錄從主伺服器上複製SQL語句，然後執行的語句。在從伺服器上直接建立資料庫，可以看到，從伺服器的二進位日誌記錄語句的。但是主伺服器，不會同步從伺服器的資料，這就是主從複製。 也可以把以下配置寫到從伺服器裡面，而不用再CHANGE MASTER TO這麼麻煩了，另外還有一些其它的定義。 

 

  
// 配置slave伺服器/etc/my.cnf 檔案，添加以下內容： 
  
server-id=2 # 從伺服器ID號，不要和主ID相同 
  
master-host=1.1.1.3 # 指定主伺服器IP地址 
  
master-user=repl # 指定在主伺服器上可以進行同步的使用者名稱 
  
master-password=123456 # 密碼 
  
master-port=3306 # 同步所用連接埠 
  
master-connect-retry=60 # 斷點從新連線時間 
  
replicate-ignore-db=mysql # 屏蔽對mysql庫的同步 
  
replicate-do-db=test1 # 同步的資料庫的名稱 
 

 常用排錯命令及其解釋： 

 

  
flush master;   //清空二進位日誌 
  
flush slave;    //清空二進位日誌 
 

以上命令，在主從複製出錯的時候，特別是報欄位重複的時候，清空一下主伺服器和從伺服器的二進位日誌，然後再用CHANGE MASTER TO命令指向，然後再開戶從伺服器，應該就不會報錯了。注意：以上兩個命令會清空二進位日誌，在生產環境請確保已經備份了日誌，並且沒有資料寫入。  

 

  
show master status;     //查看主伺服器當前使用的二進位檔案，及二進位檔案的位置 
 

說明：如果不想清空二進位日誌，但是錯誤又不容易排查，可以在指定主伺服器的時候，指定主伺服器使用的二進位檔案和二進位檔案的位置，表示從當前二進位檔案的位置，開始往後複製。不複製之前的內容。在此之前，可以通過mysqldump命令，先將資料匯出，然後再匯入到從伺服器。來實現資料一致，然後再使用命令指定二進位檔案及位置，以確保主從資料一致。 以上，到此，主從複製就結束了。