MySQL主從複製實現

標籤：null   ges   參數   ica   com   mysql資料庫   oca   update   執行   

上回提到了用ThinkPHP架構來實現資料庫的讀寫分離，現在就來簡單說說MySQL的主從複製。

形式

• 一主一從（也就是這裡要實現的形式）
• 主主複製
• 一主多從
• 多主一從（MySQL5.7開始支援）
• 聯級複製

圖來自互連網

條件

• 主庫開啟binlog日誌（設定log-bin參數）
• 主從server-id不同（這個要小心）
• 從程式庫伺服器能連通主庫

原理

• 從庫產生兩個線程，一個I/O線程，一個SQL線程；
  
• i/o線程去請求主庫 的binlog，並將得到的binlog日誌寫到relay log（中繼日誌） 檔案中；
• 主庫會產生一個 log dump 線程，用來給從庫 i/o線程傳binlog；
• SQL 線程，會讀取relay log檔案中的日誌，並解析成具體操作，來實現主從的操作一致，而最終資料一致；

步驟

 

• 在主要資料庫設定檔中加入以下代碼
• [[email protected] ~]# vim /etc/my.cnf

log-bin=mysql-bin // 將mysql二進位日誌取名為mysql-binbinlog_format=mixed // 二進位日誌的格式，有三種：statement/row/mixed,具體分別不多做解釋，這裡使用mixedserver-id=111 // 為伺服器設定一個獨一無二的id便於區分，這裡使用ip地址的最後一位充當server-id

• 配置完成後，儲存並重啟主MySQL資料庫
• 在從資料庫重複同樣的操作，不一樣的是server-id要寫主要資料庫的，這裡寫的是server-id=110；
• 儲存並重啟從MySQL資料庫
• 登入主MySQL資料庫，在主要資料庫為從資料庫分配一個帳號，用於從資料庫共用主要資料庫的記錄檔

GRANT replication slave ON *.* TO ‘slave‘@‘%‘ IDENTIFIED BY ‘123456‘; 

• mysql> GRANT replication slave ON *.* TO ‘slave‘@‘%‘ IDENTIFIED BY ‘123456‘;
  Query OK, 0 rows affected (0.00 sec)

• 查看主伺服器BIN日誌的資訊（執行完之後記錄下這兩值，然後在配置完從伺服器之前不要對主伺服器進行任何操作，因為每次操作資料庫時這兩值會發生改變）；

• mysql> show master status;
  +------------------+----------+--------------+------------------+-------------------+
  | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  +------------------+----------+--------------+------------------+-------------------+
  | mysql-bin.000001 | 315 | | | |
  +------------------+----------+--------------+------------------+-------------------+
  1 row in set (0.00 sec)

• 進入從資料庫
• 如果之前有配置過主從複製，一定要先關閉slave
• 關閉命令為：stop slave
• 配置開始：change master to master_host=‘192.168.33.110‘,master_user=‘slave‘,master_password=‘123456‘,
           master_log_file=‘mysql-bin.000001‘,master_log_pos=315;
• 參數解釋：

 　　     master_host=‘192.168.33.110‘ // 設定要串連的主伺服器的ip地址

　　　　master_user=‘slave‘ // 設定要串連的主伺服器的使用者名稱

　　　　master_password=‘123456‘ // 設定要串連的主伺服器的密碼

　　　　master_log_file=‘mysql-bin.000001‘ // 設定要串連的主伺服器的bin日誌的日誌名稱，即show slave status命令得到的資訊File列名稱

　　　　master_log_pos=315 // 設定要串連的主伺服器的bin日誌的記錄位置，即show slave status命令得到的資訊Position列名稱，（這裡注意，最後一項不需要加引號。否則配置失敗）

　　　　從資料庫配置完成，重啟MySQL服務

• mysql> start slave;
  Query OK, 0 rows affected, 1 warning (0.00 sec)

• mysql> show slave status \G
  *************************** 1. row ***************************
  Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.33.110
  Master_User: slave
  Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: mysql-bin.000001
  Read_Master_Log_Pos: 1036
  Relay_Log_File: localhost-relay-bin.000002
  Relay_Log_Pos: 1004
  Relay_Master_Log_File: mysql-bin.000001
  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:
  Last_Errno: 0
  Last_Error:
  Skip_Counter: 0
  Exec_Master_Log_Pos: 1036
  Relay_Log_Space: 1181
  Until_Condition: None
  Until_Log_File:
  Until_Log_Pos: 0
  Master_SSL_Allowed: No
  Master_SSL_CA_File:
  Master_SSL_CA_Path:
  Master_SSL_Cert:
  Master_SSL_Cipher:
  Master_SSL_Key:
  Seconds_Behind_Master: 0
  Master_SSL_Verify_Server_Cert: No
  Last_IO_Errno: 0
  Last_IO_Error:
  Last_SQL_Errno: 0
  Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
  Master_Server_Id: 111
  Master_UUID: 39a19e0a-7957-11e6-aa19-0800272020f4
  Master_Info_File: /Data/data/mysql/data/master.info
  SQL_Delay: 0
  SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
  Master_Retry_Count: 86400
  Master_Bind:
  Last_IO_Error_Timestamp:
  Last_SQL_Error_Timestamp:
  Master_SSL_Crl:
  Master_SSL_Crlpath:
  Retrieved_Gtid_Set:
  Executed_Gtid_Set:
  Auto_Position: 0
  1 row in set (0.00 sec)

• 如果這兩個全部是yes，才表示成功，否則失敗；Slave_IO_Running: Yes；Slave_SQL_Running: Yes

最後就可以測試是否可以了~~~

 

MySQL主從複製實現