MySQL 5.7 資料庫主從複制方案與配置實作
由 小馬克 建立于1年前, 最後更新于 1年前
版本号 #3
1545 views
0 likes
3 collects
主從複制方案
主從複制的好處
MySQL 的 Replication(英文為複制)是一個多 MySQL 資料庫做主從同步的方案，特點是異步複制，廣泛用在各種對 MySQL有更高性能、更高可靠性要求的場合。主從複制有以下四方面的好處：
資料備份 (Data Backup) 隻是簡單的對資料庫進行備份，降低資料丢失的風險，有時也用于報表等對資料時效 性要求不高的場合。
負載均衡 (Load Balance) 主要用在 MySQL 叢集，解決單點故障或做故障切換；以降低單台伺服器的負載和風險，如實作讀寫分離，可以使得伺服器通路負荷比較均衡。
資料分發 (Data Distribution) 主要用于多資料中心或異地備份，實作資料分發與同步。
高可用和資料容錯 (High Availability and Failover) MySQL 自帶的健康監控和檢測，根據配置的時間間隔，可以檢測主庫是否正常工作， 一旦發現主庫當機或無法正常工作，就會選擇到最好的一個備庫上。
主從複制類型
MySQL主從複制有下面三種類型：
1、基于語句的複制(Statement-Based)： 在主伺服器上執行的 SQL語句，然後将語句寫入二進制日志檔案，在從伺服器上執行同樣的語句。MySQL預設采用基于語句的複制，效率比較高。
2、基于行的複制(Row-Based)：把改變的内容(資料本身)複制過去，而不是把指令在從伺服器上執行一遍，從 mysql 5.0 開始支援。
3、混合類型的複制: 預設采用基于語句的複制，一旦發現基于語句的無法精确的複制時，就會采用基于行的複制。
主從複制常用拓撲結構
一主一從
這是最基礎的複制結構，用來分擔之前單台資料庫伺服器的壓力，可以進行讀寫分離。
一主多從
一台 Slave 承受不住讀請求壓力時，可以添加多台，進行負載均衡，分散讀壓力。還可以 對多台 Slave 進行分工，服務于不同的系統，例如一部分 Slave 負責網站前台的讀請求，另 一部分 Slave 負責背景統計系統的請求。因為不同系統的查詢需求不同，對 Slave 分工後，可以建立不同的索引，使其更好的服務于目标系統。
Mysql 的複制結構有很多種方式，複制的最大問題是資料延時，選擇複制結構時需要根據 自己的具體情況，并評估好目标結構的延時對系統的影響。
主從複制實施配置執行個體
配置環境準備
這裡以一主一從的拓撲結構為例來進行配置。一主多從拓撲結構的配置類似。以下配置在 MySQL5.5.x 和 MySQ 5.7.x 版本上配置測試通過，主從資料庫版本必須一樣。
安裝環境：
1、作業系統：Window7 x64(跟作業系統關系不大，隻是指令和操作上有差别)；
2、資料庫版本：MySQL 5.5.16(Master主庫)、MySQL5.5.29(Slave從庫)；最好是同一大版本系列，小版本差别不大。
3、Master 主庫 IP 位址： 192.168.1.10；
4、Slave 從庫 IP 位址： 192.168.1.20；
這裡強調資料庫版本，是因為不同 MySQL 版本的性能有差别；目前 MySQL 5.7.x 版本系列的性能是最好的，實際系統使用時最好用此系列的資料庫版本。
MySQL 5.7 号稱性能是 MySQL 5.6 的 3 倍，而 MySQL 5.6 号稱性能是 MySQL 5.5 的 2 倍，你信嗎？這個問題還是要辯證的了解，首先性能肯定是改進了，但為什麼大部份人更新後也沒有感覺？這就要從測試方法說起，大部份廠商釋出新版本都會說性能提升了多少多少， 因為這是最能吸引客戶的資料。從官方的測試資料來看，首先是 32 個并發以上的簡單查詢，并且超過 10 萬 QPS 才有差別，如果你的系統沒有達到這個并發度那肯定沒有體會的。另外是純記憶體與 CPU 計算，如果你的系統瓶頸在磁盤或網絡 IO 那也不會有明顯的效率提升。當然，我認為 MySQL 5.6 的 ICP 這種特性其實對于業務來說更有意義，說不定剛好你的慢 SQL可以解決掉。
主從複制參數配置
主從複制參數配置内容及步驟順序如下：
1、在 Master 主庫上建立複制用的賬号，并授權：
mysql> GRANT REPLICATION SLAVE ON *.* TO 'copyer'@'192.168%' IDENTIFIED BY 'copyer password';
mysql> FLUSH PRIVILEGES;
2、對 Master 主庫的 my.ini 或 my.cnf 配置檔案做更改：
##必須在 mysqld 節點上進行配置
[mysqld]
##設定伺服器 ID，必須保持唯一性
server-id=10
##設定需要寫 bin 日志的資料庫，多個資料庫則要多行分别設定
binlog-do-db=test
#binlog-do-db=xxxx
##設定不需要寫 bin 日志的資料庫，多個資料庫則要多行分别設定
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
##注：binlog-do-db, binlog-ignore-db 為互斥關系，隻需設定其中一項即可；
##更要注意 binlog-do-db 庫的 SQL 不能有操作 binlog-ignore-db 庫的語句或動作，否則報錯
##日志模式 ROW|STATEMENT|MIXED，預設是語句模式，現設定為行模式
binlog-format=row
##二進制日志檔案存放位置，可以設定路徑；也可以不設路徑，此時存放到 datadir 目錄下。
log-bin=E:/MySQL5/Data/test-binlog
3、對 Slave 從庫的 my.ini 或 my.cnf 配置檔案做更改：
##必須在 mysqld 節點上進行配置
[mysqld]
##設定伺服器 ID，必須保持唯一性
server-id=20
##設定需要複制的資料庫，多個資料庫則要多行分别設定
replicate-do-db=test
replicate-do-db=xxxx
##設定不需要複制的資料庫，多個資料庫則要多行分别設定
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
4、在完成 Master 主庫和 Slave 從庫的配置後，分别重新開機主從 MySQL 資料庫，然後在 Master 主庫裡用 root 賬号登入、執行以下指令，這些參數在 Slave從庫啟動複制時需要用到。
然後在 Slave 從庫(記住是 Slave 從庫)也用 root 賬号登入、在 MySQL 指令行執行以下指令：
mysql> change master to
-> master_host='192.168.1.10',
-> master_user='copyer',
-> master_password='copyer password',
-> master_log_file='test-binlog.000005',
-> master_log_pos=107;
Query OK, 0 rows affected (0.10 sec)
注意：裡面的 host/user/password/log_file/log_pos 要根據實際參數做更改。如果 MySQL Server 不是預設的 3306 端口，那麼必須加上 master_port=xxxx 參數，設定具體端口數字。如果有經過防火牆的話，在防火牆上必須開放對應端口。
然後在 Slave 從庫裡啟動 Slave 複制，這樣主從複制配置完成。
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
如果複制過程中有問題，可以先 mysql> stop slave; 然後再 mysql> start slave;
主從複制狀态監測
在 Master 主庫的 MySQL 指令行裡可以執行以下指令來監測主從複制執行情況：
mysql> show master status; ##顯示 master 複制狀态
mysql> show master logs; ##顯示 Master 庫 bin log 情況，跟 show binary logs; 指令一樣。
mysql> show processlist; ##檢視 MySQL程序狀态，包括複制程序，看 state 字段說明
在 Slave 主庫的 MySQL 指令行裡，可以執行以下指令來監測主從複制執行情況：
mysql> show processlist; ##檢視 MySQL程序狀态，包括複制程序，看 state 字段說明
mysql> show slave status; ##顯示 Slave複制狀态，狀态很多，具體要檢視手冊另外是直接檢視主從複制的庫和表裡的資料，是否有及時正常複制過來。
在 Slave主庫通過 show slave status 指令主要監測以下四個參數：
1、Slave_IO_State IO 程序處理狀态
2、Slave_IO_Running IO 線程是否打開：YES/NO/NULL三種狀态
3、Slave_SQL_Running SQL 線程是否運作：YES/NO/NULL三種狀态
4、Seconds_Behind_Master 落後主庫的時間(秒)
可能導緻主從複制延時的因素：
1、 主從複制的伺服器時鐘是否一緻；
2、 網絡通信是否存在延時；
3、 是否和日志類型、資料量過大有關；
4、 從庫性能，有沒有開啟 binlog
5、 從庫查詢是否優化(比如存在查詢慢，導緻從庫性能差，處理不過來)