mysql主從複製原理及配置

⒈關於主從複製的原理：
簡單的來講就是slave上一個io執行緒去取binlog事件，一個sql執行緒將binlog事件重放至slave上

⒉關於複製賬號許可權
mysql的主伺服器需要有replication slave的許可權
當然在實際環境中，我們需要監控和管理複製，所以需要有replication client許可權
另外如果需要主備切換的，就需要主從都具有replication slave與replication client的許可權
實際操作：GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.101.%' IDENTIFIED BY 'tianqu';

⒊關於複製的方式（binlog_format)
基於語句的複製：通俗一點就是sql語句複製，好處：不會使用大量頻寬（為什麼?一條更新好幾M的資料，binlog中就幾十位元組)  缺點：有存在無法複製的sql語句,儲存過程、觸發器的複製也可能存在問題。
基於行的複製：通俗一點就是物理複製，表中資料基於行復制。優點:能解決語句複製的缺點 缺點：複製資料多，不好檢測複製中問題。
混合複製:結合兩者的優點，能用語句複製的採用語句，其他則基於行的複製
具體的引數設定請看核心三處


⒋關於複製的引數
⑴核心引數(主庫)
#指定log_bin,開啟二進位制,預設在data目錄下
log_bin = /var/lib/data/mysql-bin
#指定sever_id的一個作用是標識sql產生的源主機，防止重複複製，所以兩邊的server_id必須不一致
server_id=10
#複製方式
binlog_format='MIXED'
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)

⑵核心引數（備庫）
#指定log_bin,開啟二進位制,預設在data目錄下
log_bin = /var/lib/data/mysql_bin
#指定sever_id的一個作用是標識sql產生的源主機，防止重複複製，所以兩邊的server_id必須不一致
server_id=10
#複製方式
binlog_format='MIXED'
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)

#指定中繼日誌的位置
relay_log=/var/lib/mysql-relay-bin

⑶主庫複製其他引數
#該引數控制binlog在事務提交前將二進位制日誌同步到磁碟，該引數最好設定，防止丟失事務
sync_binlog=1
#過濾主庫上面的二進位制，這兩個引數在環境中強烈建議不使用，有可能導致某個時間的資料恢復失敗。
binlog_do_db與binlog_ignore_db

⑷備庫的其他引數
#防止備庫在崩潰後,啟動自動複製
skip_slave_start
#允許備份重放的事件重放到二進位制日誌中，從庫帶從庫是必須的選項
log-slave-updates = 1
#其他重新整理到磁碟的檔案,如果對效能不是很看重，可以配置如下引數，預設是不呼叫fsync的
sync_master_info =1
sync_relay_log =1
sync_relay_log_info=1
#中繼日誌中關於mysql庫操作的資料不進行重放
 replicate-wild-ignore-table=mysql%

⒌關於主備配置檔案
主備庫的一個基本配置
主庫：
log_bin = /var/lib/data/mysql-bin
server_id=10
binlog_format='MIXED'
sync_binlog=1

備庫（從庫帶從庫情況下）

log_bin = /var/lib/data/mysql-bin
server_id=8
binlog_format='MIXED'
relay_log=/var/lib/mysql_relay_bin
sync_binlog=1
skip_slave_start
Replicate_Wild_Ignore_Table=mysql.%


⒍配置主從複製
主備庫建立複製賬號
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO repl@'192.168.101.%' IDENTIFIED BY 'tianqu';
備庫上：
CHANGE MASTER TO MASTER_HOST='192.168.101.11',MASTER_USER='repl',MASTER_PASSWORD='tianqu',MASTER_LOG_FILE='mysql-bin.00001',MASTER_LOG_POS=0;

然後start salve


⒎關於複製的檢查
在從庫下：
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.101.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000119
          Read_Master_Log_Pos: 120
               Relay_Log_File: relaylog.000123
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000119
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: mysql.%

如果Slave_IO_Running，Slave_SQL_Running為yes說明覆制連線沒問題


⒏關於主主複製
主主複製就是互為主備的關係
在配置時，可以參照主從複製，但為了防止主鍵衝突，必須加入如下引數
主庫A
auto_increment_increment=1,即主鍵從1開始，可以自定義
auto_increment_offset=1,及主鍵的偏移量為1，可以自定義
主庫B
auto_increment_increment=2,即主鍵從2開始
auto_increment_offset=1,及主鍵的偏移量為1
所以主庫A上的主鍵的插入順序為：1 3 5 7 9 ...
而主庫B上的主鍵則為：2 4 6 8 ...
注意主主複製不能保證資料的一致性，如果update同一個語句，可能會導致兩臺的結果不一致