MySQL主從複製(Master-Slave)實踐

標籤：執行   完成   mysql配置   etc   height   ret   port   它的   資料庫的配置   

MySQL資料庫自身提供的主從複製功能可以方便的實現資料的多處自動備份，實現資料庫的拓展。多個資料備份不僅可以加強資料的安全性，通過實現讀寫分離還能進一步提升資料庫的負載效能。

就描述了一個多個資料庫間主從複製與讀寫分離的模型(來源網路)：

在一主多從的資料庫體系中，多個從伺服器採用非同步方式更新主要資料庫的變化，商務服務器在執行寫或者相關修改資料庫的操作是在主伺服器上進行的，讀操作則是在各從伺服器上進行。如果配置了多個從伺服器或者多個主伺服器又涉及到相應的負載平衡問題，關於負載平衡具體的技術細節還沒有研究過，今天就先簡單的實現一主一從的主從複製功能。

Mysql主從複製的實現原理圖大致如下(來源網路)：

MySQL之間資料複製的基礎是二進位記錄檔（binary log file）。一台MySQL資料庫一旦啟用二進位日誌後，其作為master，它的資料庫中所有操作都會以“事件”的方式記錄在二進位日誌中，其他資料庫作為slave通過一個I/O線程與主伺服器保持通訊，並監控master的二進位記錄檔的變化，如果發現master二進位記錄檔發生變化，則會把變化複製到自己的中繼日誌中，然後slave的一個SQL線程會把相關的“事件”執行到自己的資料庫中，以此實現從資料庫和主要資料庫的一致性，也就實現了主從複製。

實現MySQL主從複製需要進行的配置：

    主伺服器：

•         開啟二進位日誌
•         配置唯一的server-id
•         獲得master二進位記錄檔名及位置
•         建立一個用於slave和master通訊的使用者帳號

    從伺服器：

•         配置唯一的server-id
•         使用master指派的使用者帳號讀取master二進位日誌
•         啟用slave服務

具體實現過程如下：

一、準備工作：

1. 主從資料庫版本最好一致

2. 主從資料庫內資料保持一致

主要資料庫：182.92.172.80 /linux

從資料庫：123.57.44.85 /linux

二、主要資料庫master修改：

1. 修改mysql配置

找到主要資料庫的設定檔my.cnf(或者my.ini)，我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下兩行：

[mysqld]log-bin=mysql-bin #開啟二進位日誌server-id=1 #設定server-id

2. 重啟mysql，建立用於同步的使用者帳號

開啟mysql會話

shell>mysql -hlocalhost -uname -ppassword

建立使用者並授權：使用者：rel1 密碼：slavepass

mysql> CREATE USER ‘repl‘@‘123.57.44.85‘ IDENTIFIED BY ‘slavepass‘;    #建立使用者（IP為可訪問該master的IP，任意IP就寫‘%‘）mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘123.57.44.85‘;    #分配許可權（IP為可訪問該 master的IP，任意IP就寫‘%‘）mysql> flush privileges;    #重新整理許可權

3. 查看master狀態，記錄二進位檔案名(mysql-bin.000003)和位置(73)：

mysql > SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 | 73       | test         | manual,mysql     |+------------------+----------+--------------+------------------+

三、從伺服器slave修改：

1. 修改mysql配置

同樣找到my.cnf設定檔，添加server-id

[mysqld]server-id=2 #設定server-id，必須唯一

2. 重啟mysql，開啟mysql會話，執行同步SQL語句(需要主伺服器主機名稱，登陸憑據，二進位檔案的名稱和位置)：

mysql> CHANGE MASTER TO    ->     MASTER_HOST=‘182.92.172.80‘,    ->     MASTER_USER=‘rep1‘,    ->     MASTER_PASSWORD=‘slavepass‘,    ->     MASTER_LOG_FILE=‘mysql-bin.000003‘,    ->     MASTER_LOG_POS=73;

3. 啟動slave同步進程：

mysql>start slave;

4. 查看slave狀態：

mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 182.92.172.80                  Master_User: rep1                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000013          Read_Master_Log_Pos: 11662               Relay_Log_File: mysqld-relay-bin.000022                Relay_Log_Pos: 11765        Relay_Master_Log_File: mysql-bin.000013             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:         ...

當Slave_IO_Running和Slave_SQL_Running都為YES的時候就表示主從同步設定成功了。接下來就可以進行一些驗證了，比如在主master資料庫的test資料庫的一張表中插入一條資料，在slave的test庫的相同資料表中查看是否有新增的資料即可驗證主從複製功能是否有效，還可以關閉slave（mysql>stop slave;）,然後再修改master，看slave是否也相應修改（停止slave後，master的修改不會同步到slave），就可以完成主從複製功能的驗證了。

 

還可以用到的其他相關參數：

master開啟二進位日誌後預設記錄所有庫所有表的操作，可以通過配置來指定只記錄指定的資料庫甚至指定的表的操作，具體在mysql設定檔的[mysqld]可添加修改如下選項：

# 不同步哪些資料庫  binlog-ignore-db = mysql  binlog-ignore-db = test  binlog-ignore-db = information_schema    # 只同步哪些資料庫，除此之外，其他不同步  binlog-do-db = game  

如之前查看master狀態時就可以看到只記錄了test庫，忽略了manual和mysql庫。

MySQL主從複製(Master-Slave)實踐