面試被問MySQL 主從復制，怎么破？

一、前言

隨著應用業務資料不斷的增大，應用的回應速度不斷下降，在檢測程序中我們不難發現大多數的請求都是查詢操作，

此時，我們可以將資料庫擴展成主從復制模式，將讀操作和寫操作分離開來，多臺資料庫分攤請求，從而減少單庫的訪問壓力，進而應用得到優化，整理了一份328頁MySQLPDF檔案

本次測驗使用兩個虛擬機：ip：192.168.2.21（主） ip：192.168.2.22（從）

二、主從復制原理

同步操作通過 3 個執行緒實作，其基本步驟如下：

主服務器將資料的更新記錄到二進制日志中（記錄被稱作二進制日志事件）-- 主庫執行緒；
從庫將主庫的二進制日志復制到本地的中繼日志（relay log）-- 從庫 I/O 執行緒；
從庫讀取中繼日志中的事件，將其重放到資料中 -- 從庫 SQL 執行緒，

三、配置主庫

# 3.1 創建用戶

為了安全起見，準備創建一個新用戶用于從庫連接主庫，

# 創建用戶
create user 'repl'@'%' identified by 'repl';

# 授權，只授予復制和客戶端訪問權限
grant replication slave,replication client on *.* to 'repl'@'%' identified by 'repl';

# 3.2 修改組態檔

1）vim /etc/my.cnf 在[mysqld]下添加：

log-bin         = mysql-bin
log-bin-index   = mysql-bin.index
binlog_format   = mixed
server-id       = 21
sync-binlog     = 1
character-set-server = utf8

2）保存檔案并重啟主庫：

service mysqld restart

配置說明：

log-bin：設定二進制日志檔案的基本名；
log-bin-index：設定二進制日志索引檔案名；
binlog_format：控制二進制日志格式，進而控制了復制型別，三個可選值
    -STATEMENT：陳述句復制
    -ROW：行復制
    -MIXED：混和復制，默認選項
server-id：服務器設定唯一ID，默認為1，推薦取IP最后部分；
sync-binlog：默認為0，為保證不會丟失資料，需設定為1，用于強制每次提交事務時，同步二進制日志到磁盤上，

# 3.3 備份主資料庫資料

若主從資料庫都是剛剛裝好且資料都是一致的，直接執行 show master status 查看日志坐標，

若主庫可以停機，則直接拷貝所有資料庫檔案，

若主庫是在線生產庫，可采用 mysqldump 備份資料，因為它對所有存盤引擎均可使用，

1）為了獲取一個一致性的快照，需對所有表設定讀鎖：

flush tables with read lock;

2）獲取二進制日志的坐標：

 show master status;

回傳結果：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3）備份資料：

# 針對事務性引擎
mysqldump -uroot -ptiger --all-database -e --single-transaction --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql

# 針對 MyISAM 引擎，或多引擎混合的資料庫
mysqldump -uroot --all-database -e -l --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql
1 row in set (0.00 sec)

4）恢復主庫的寫操作：

unlock tables;

四、配置從庫

# 4.1 修改組態檔

1）vim /etc/my.cnf 在[mysqld]下添加：

log-bin             = mysql-bin
binlog_format       = mixed
log-slave-updates   = 0
server-id           = 22
relay-log           = mysql-relay-bin
relay-log-index     = mysql-relay-bin.index
read-only           = 1
slave_net_timeout   = 10

2）保存檔案并重啟從庫：

service mysqld restart

配置說明：

log-slave-updates：控制 slave 上的更新是否寫入二進制日志，默認為0；若 slave 只作為從服務器，則不必啟用；若 slave 作為其他服務器的 master，則需啟用，啟用時需和 log-bin、binlog-format 一起使用，這樣 slave 從主庫讀取日志并重做，然后記錄到自己的二進制日志中；
relay-log：設定中繼日志檔案基本名；
relay-log-index：設定中繼日志索引檔案名；
read-only：設定 slave 為只讀，但具有super權限的用戶仍然可寫；
slave_net_timeout：設定網路超時時間，即多長時間測驗一下主從是否連接，默認為3600秒，即1小時，這個值在生產環境過大，我們將其修改為10秒，即若主從中斷10秒，則觸發重新連接動作，

# 4.2 匯入備份資料

如果 3.3 步驟中沒進行備份，忽略此步驟，

mysql -uroot -p < /data/all_db.sql

# 4.3 統一二進制日志的坐標

根據 3.3 步驟獲取的坐標，統一到從庫中：

change master to
master_host='192.168.2.21',
master_user='repl',
master_password='repl',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=120;

注意：此處使用的是新創建的賬戶，

# 4.4 啟動主從復制

1）啟動從庫 slave 執行緒：

start slave;

2）查看從服務器復制功能狀態：

show slave status\G;

回傳結果：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        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: 120

此處只張貼部分回傳結果，

結果說明：

Slave_IO_Running：此行程負責 slave 從 master 上讀取 binlog 日志，并寫入 slave 上的中繼日志，
Slave_SQL_Running：此行程負責讀取并執行中繼日志中的 binlog 日志，

這兩個行程的狀態需全部為 YES，只要有一個為 NO，則復制就會停止，

當 Relay_Master_Log_File = Master_Log_File 且 Read_Master_Log_Pos = Exec_Master_Log_Pos 時，則表明 slave 和 master 處于完全同步的狀態，

五、驗證

使用一個簡單的例子：

在主庫創建名為 mysql_test 的資料庫，如果同步成功，那么在從庫中也能查詢出名為 mysql_test 資料庫，

六、參考資料

• MySQL 官網整理了一份328頁MySQLPDF檔案
  
  dev.mysql.com/doc/refman/…

標籤：Java

上一篇：技術公司與非技術公司的區別，太真實了…

下一篇：HashMap詳解