介紹
為什麼要主從複製
-
做資料的熱備
-
如果主資料庫當機，可以快速將業務系統切換到從資料庫上，可避免資料丟失。
-
業務量越來越大，I/O訪問頻率過高，單機無法滿足，此時做多庫的儲存，降低磁碟I/O訪問的頻率，提高單個機器的I/O效能。如果對資料庫的讀和寫都在同一個資料庫伺服器中操作，業務系統效能會降低。
-
在業務複雜的系統中，有這麼一個情景，有一句sql語句需要鎖表，導致暫時不能使用讀的服務，那麼就很影響執行中的業務，使用主從複製，讓主庫負責寫，從庫負責讀，這樣，即使主庫出現了鎖表的情景，通過讀從庫也可以保證業務的正常運作。通過做主從複製（讀寫分離）來減輕主資料庫的負載。
原理
- 步驟一：主庫db的更新事件(update、insert、delete)被寫到binlog
- 步驟二：從庫發起連線，連線到主庫
- 步驟三：此時主庫建立一個binlog dump thread，把binlog的內容傳送到從庫
- 步驟四：從庫啟動之後，建立一個I/O執行緒，讀取主庫傳過來的binlog內容並寫入到relay log
- 步驟五：還會建立一個SQL執行緒，從relay log裡面讀取內容，從Exec_Master_Log_Pos位置開始執行讀取到的更新事件，將更新內容寫入到slave的db
注：上面的解釋是解釋每一步做了什麼，整個mysql主從複製是非同步的，不是按照上面的步驟執行的。
同步操作通過 3 個執行緒實現，其基本步驟如下：
主伺服器將資料的更新記錄到二進位制日誌中（記錄被稱作二進位制日誌事件）-- 主庫執行緒；
從庫將主庫的二進位制日誌複製到本地的中繼日誌（relay log）-- 從庫 I/O 執行緒；
從庫讀取中繼日誌中的事件，將其重放到資料中 -- 從庫 SQL 執行緒。
複製程式碼主從複製
準備工作
- 主從資料庫版本最好一致
- 主從資料庫內資料保持一致
- 主資料庫：10.168.1.248 從資料庫：10.168.1.249
配置主庫Master
修改MySQL配置
找到主資料庫的配置檔案my.cnf，我的在/etc/my.cnf
vi /etc/my.cnf
複製程式碼在[mysqld]部分插入
[mysqld]
log-bin=mysql-bin #開啟二進位制日誌
server-id=1 #設定server-id，必須唯一
複製程式碼**配置說明 **
- log-bin：設定二進位制日誌檔案的基本名；
- log-bin-index：設定二進位制日誌索引檔名；
- binlog_format：控制二進位制日誌格式，進而控制了複製型別，三個可選值
- -STATEMENT：語句複製
- -ROW：行復制
- -MIXED：混和複製，預設選項
- server-id：伺服器設定唯一ID，預設為1，推薦取IP最後部分；
- sync-binlog：預設為0，為保證不會丟失資料，需設定為1，用於強制每次提交事務時，同步二進位制日誌到磁碟上。
建立使用者
重啟服務，建立用於同步的使用者
開啟mysql會話shell
mysql -uroot -p
複製程式碼建立使用者並授權：使用者：repl密碼：repl'
# 建立使用者
create user 'repl'@'10.168.1.249' identified by 'repl';
# 授權，只授予複製和客戶端訪問許可權
grant replication slave on *.* to 'repl'@'10.168.1.249';#分配許可權
複製程式碼檢視master狀態
記錄二進位制檔名(mysql-bin.000001)和位置(154):
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1416 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
複製程式碼配置從庫Slave
修改MySQL配置
找到主資料庫的配置檔案my.cnf，我的在/etc/my.cnf
vi /etc/my.cnf
複製程式碼在[mysqld]部分插入
[mysqld]
server-id=2 #設定server-id，必須唯一
複製程式碼執行同步SQL語句
重啟mysql，開啟mysql會話，執行同步SQL語句(需要主伺服器主機名，登陸憑據，二進位制檔案的名稱和位置)：
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.168.1.248',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=1416;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
複製程式碼啟動slave同步程式
mysql>start slave;
複製程式碼檢視slave狀態
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.168.1.248
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1917
Relay_Log_File: sl249-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
......
複製程式碼當Slave_IO_Running和Slave_SQL_Running都為YES的時候就表示主從同步設定成功了。
驗證
-
主伺服器
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> create database mysql_test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mysql_test | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) 複製程式碼 -
從伺服器
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mysql_test | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) 複製程式碼