Mysql主從同步（複製）

標籤：

目錄：

mysql主從同步定義

     主從同步機制

配置主從同步

     配置主伺服器

     配置從伺服器

使用主從同步來備份

     使用mysqldump來備份

     備份原始檔案

主從同步的小技巧

排錯

     Slave_IO_Running: NO

     Slave_SQL_Running: No

  mysql主從同步定義

主從同步使得資料可以從一個資料庫伺服器複製到其他伺服器上，在複製資料時，一個伺服器充當主伺服器（master），其餘的伺服器充當從伺服器（slave）。因為複製是非同步進行的，所以從伺服器不需要一直串連著主伺服器，從伺服器甚至可以通過撥號斷斷續續地串連主伺服器。通過設定檔，可以指定複製所有的資料庫，某個資料庫，甚至是某個資料庫上的某個表。

使用主從同步的好處：

1. 通過增加從伺服器來提高資料庫的效能，在主伺服器上執行寫入和更新，在從伺服器上向外提供讀功能，可以動態地調整從伺服器的數量，從而調整整個資料庫的效能。
2. 提高資料安全-因為資料已複製到從伺服器，從伺服器可以終止複製進程，所以，可以在從伺服器上備份而不破壞主伺服器相應資料
3. 在主伺服器上產生即時資料，而在從伺服器上分析這些資料，從而提高主伺服器的效能

注意，mysql是非同步複製的，而MySQL Cluster是同步複製的。有很多種主從同步的方法，但核心的方法有兩種，Statement Based Replication（SBR）基於SQL語句的複製，另一種是Row Based Replication（RBR）基於行的複製，也可以使用Mixed Based Replication（MBR）。在mysql5.6中，預設使用的是SBR。而mysql 5.6.5和往後的版本是基於global transaction identifiers(GTIDs)來進行事務複製。當使用GTIDs時可以大大簡化複製過程，因為GTIDs完全基於事務，只要在主伺服器上提交了事務，那麼從伺服器就一定會執行該事務。

通過設定伺服器的系統變數binlog_format來指定要使用的格式：

1.SBR：當使用二進位日誌時，主伺服器會把SQL語句寫入到日誌中，然後從伺服器會執行該日誌，這就是SBR，在mysql5.1.4之前的版本都只能使用這種格式。使用SBR會有如下

長處：

1. 記錄檔更小
2. 記錄了所有的語句，可以用來日後審計

弊端：

1. 使用如下函數的語句不能被正確地複製：load_file(); uuid(), uuid_short(); user(); found_rows(); sysdate(); get_lock(); is_free_lock(); is_used_lock(); master_pos_wait(); rand(); release_lock(); sleep(); version();
2. 在日誌中出現如下警告資訊的不能正確地複製：[Warning] Statement is not safe to log in statement format.
3. 或者在用戶端中出現show warnings
4. Insert … select語句會執行大量的行級鎖表
5. Update語句會執行大量的行級鎖表來掃描整個表

2.RBR：主伺服器把表的行變化作為事件寫入到二進位日誌中，主伺服器把代表了行變化的事件複製到從服務中，使用RBR的

長處：

1. 所有的資料變化都是被複製，這是最安全的複製方式
2. 更少的行級鎖表

弊端：

1. 日誌會很大
2. 不能通過查看日誌來審計執行過的sql語句，不過可以通過使用mysqlbinlog  
3. --base64-output=decode-rows --verbose來查看資料的 變動

3.MBR：既使用SBR也使用RBR，預設使用SBR

  主從同步機制

Mysql伺服器之間的主從同步是基於二進位日誌機制，主伺服器使用二進位日誌來記錄資料庫的變動情況，從伺服器通過讀取和執行該記錄檔來保持和主伺服器的資料一致。

在使用二進位日誌時，主伺服器的所有操作都會被記錄下來，然後從伺服器會接收到該日誌的一個副本。從伺服器可以指定執行該日誌中的哪一類事件（譬如只插入資料或者只更新資料），預設會執行日誌中的所有語句。

每一個從伺服器會記錄關於二進位日誌的資訊：檔案名稱和已經處理過的語句，這樣意味著不同的從伺服器可以分別執行同一個二進位日誌的不同部分，並且從伺服器可以隨時串連或者中斷和伺服器的串連。

主伺服器和每一個從伺服器都必須配置一個唯一的ID號（在my.cnf檔案的[mysqld]模組下有一個server-id配置項），另外，每一個從伺服器還需要通過CHANGE MASTER TO語句來配置它要串連的主伺服器的ip地址，記錄檔名稱和該日誌裡面的位置（這些資訊儲存在主伺服器的資料庫裡）

  配置主從同步

有很多種配置主從同步的方法，可以總結為如下的步驟：

1．在主伺服器上，必須開啟二進位日誌機制和配置一個獨立的ID

2．在每一個從伺服器上，配置一個唯一的ID，建立一個用來專門複製主伺服器資料的帳號

3．在開始複製進程前，在主伺服器上記錄二進位檔案的位置資訊

4．如果在開始複製之前，資料庫中已經有資料，就必須先建立一個資料快照（可以使用mysqldump匯出資料庫，或者直接複製資料檔案）

5．配置從伺服器要串連的主伺服器的IP地址和登陸授權，二進位記錄檔名和位置

配置主伺服器

1.更改設定檔，首先檢查你的主伺服器上的my.cnf檔案中是否已經在[mysqld]模組下配置了log-bin和server-id

[mysqld]log-bin=mysql-binserver-id=1

注意上面的log-bin和server-id的值都是可以改為其他值的，如果沒有上面的配置，首先關閉mysql伺服器，然後添加上去，接著重啟伺服器

2.建立使用者，每一個從伺服器都需要用到一個賬戶名和密碼來串連主伺服器，可以為每一個從伺服器都建立一個賬戶，也可以讓全部伺服器使用同一個賬戶。下面就為同一個ip網段的所有從伺服器建立一個只能進行主從同步的賬戶。

首先登陸mysql，然後建立一個使用者名稱為rep，密碼為123456的賬戶，該賬戶可以被192.168.253網段下的所有ip地址使用，且該賬戶只能進行主從同步

mysql > grant replication slave on *.* to ‘rep’@‘192.168.253.%’ identified by ‘123456’;

3.擷取二進位日誌的資訊並匯出資料庫，步驟：

首先登陸資料庫，然後重新整理所有的表，同時給資料庫加上一把鎖，阻止對資料庫進行任何的寫操作

mysql > flush tables with read lock;

然後執行下面的語句擷取二進位日誌的資訊

mysql > show master status;

File的值是當前使用的二進位日誌的檔案名稱，Position是該日誌裡面的位置資訊（不需要糾結這個究竟代表什麼），記住這兩個值，會在下面配置從伺服器時用到。

注意：如果之前的伺服器並沒有配置使用二進位日誌，那麼使用上面的sql語句會顯示空，在鎖表之後，再匯出資料庫裡的資料（如果資料庫裡沒有資料，可以忽略這一步）

[[email protected] backup]# mysqldump -uroot -p‘123456‘ -S /data/3306/data/mysql.sock --all-databases > /server/backup/mysql_bak.$(date +%F).sql

如果資料量很大，可以在匯出時就壓縮為原來的大概三分之一

[[email protected] backup]# mysqldump -uroot -p‘123456‘ -S /data/3306/data/mysql.sock --all-databases | gzip > /server/backup/mysql_bak.$(date +%F).sql.gz

這時可以對資料庫解鎖，恢複對主要資料庫的操作

mysql > unlock tables;

 

配置從伺服器

首先檢查從伺服器上的my.cnf檔案中是否已經在[mysqld]模組下配置leserver-id

[mysqld]server-id=2

注意上面的server-id的值都是可以改為其他值的（建議更改為ip地址的最後一個欄位），如果沒有上面的配置，首先關閉mysql伺服器，然後添加上去，接著重啟伺服器

如果有多個從伺服器上，那麼每個伺服器上配置的server-id都必須不一致。從伺服器上沒必要配置log-bin，當然也可以配置log-bin選項，因為可以在從伺服器上進行資料備份和災難恢複，或者某一天讓這個從伺服器變成一個主伺服器

如果主伺服器匯出了資料，下面就匯入該檔案，如果主伺服器沒有資料，就忽略這一步

[[email protected] ~]# mysql -uroot -p‘123456‘ -S /data/3306/data/mysql.sock < /server/backup/mysql_bak.2015-07-01.sql

如果從主伺服器上拿過來的是壓縮檔，就先解壓再匯入

配置同步參數，登陸mysql，輸入如下資訊：

mysql> CHANGE MASTER TO-> MASTER_HOST=‘master_host_name‘,-> MASTER_USER=‘replication_user_name‘,-> MASTER_PASSWORD=‘replication_password‘,-> MASTER_LOG_FILE=‘recorded_log_file_name‘,

：

 

啟動主從同步進程

mysql > start slave;

檢查狀態

mysql > show slave status \G

上面的兩個進程都顯示YES則表示配置成功

 使用主從同步來備份

把主伺服器的資料複製到從伺服器上，然後備份從伺服器的資料，在資料量不是很大的時候使用mysqldump命令，對於很大的資料庫，就直接備份資料檔案。

使用mysqldump來備份

步驟：（以下的所有操作都在從伺服器上進行）

1.首先暫停從伺服器的複製進程

shell > mysqladmin stop-slave

或者只是暫停SQL進程（從伺服器仍然能接收二進位日誌的事件，但不會執行這些事件，這樣能在重啟SQL進程時加快複製進度）

shell > mysql -e ‘stop slave sql_thread;’

2.使用mysqldump匯出全部或部分的資料庫

shell > mysqldump --all-databases > fulldb.dump

3.在匯出資料庫後，重啟複製進程

shell > mysqladmin start-slave

 

備份原始檔案

為了保證資料檔案的完整性，在備份之前首先關閉從伺服器，步驟：

1.關閉從伺服器：

shell > mysqladmin shutdown

2.複製資料檔案，可以使用壓縮命令，假如目前的目錄就是資料庫的資料目錄（在my.cnf檔案中的配置項datadir的值就是該目錄的位置）

shell > tar cf /tmp/dbbackup.tar ./data

3.然後再啟動mysql伺服器

 主從同步的小技巧

主伺服器第一次匯入資料，如果你從其他地方拿來了要匯入到主伺服器中的資料，此時只要在主伺服器中匯入一次即可，因為這些資料會自動發送到從伺服器中，在主伺服器上使用命令

shell > mysql -h master < other_data.sql

增加從伺服器，本來已經至少有一個從伺服器時（暫時命名為slave1），決定再添加其餘的從伺服器（slave2），此時就不需要像上面那樣去操作主伺服器，只要複製一個已經存在的從伺服器就可以了

 排錯Slave_IO_Running: NO

這是一個很常見的錯誤（我也曾對這個錯誤咬牙切齒），總結起來就三個原因：

1. 主伺服器的網路不通，或者主伺服器的防火牆拒絕了外部串連3306連接埠
2. 在配置從伺服器時，輸錯了ip地址和密碼，或者主伺服器在建立使用者時寫錯了使用者名稱和密碼
3. 在配置從伺服器時，輸錯了主伺服器的二進位日誌資訊

排錯過程：（主伺服器ip：192.168.1.139，從伺服器ip：192.168.1.204）

第0步就是檢查錯誤記錄檔，如果不能快速排錯，可以按我的步驟試試：

1．首先在從伺服器上執行ping程式，確定能ping通主伺服器

在從伺服器上執行mysq的遠端連線

[[email protected] log]# mysql -urep -p -h 192.168.1.139 -P3306

如果顯示ERROR 1045 (28000): Access denied for user ‘test‘@‘192.168.1.204‘ (using password: YES)則跳轉到第3

2．登陸主伺服器的mysql，查看所有的使用者

mysql > select user,host from mysql.user;

就是我的錯誤根源，可以看到使用者名稱完全寫錯了，先刪除錯誤的使用者：

mysql > drop user “[email protected]%”@”%”;

再重新建立使用者：

mysql > grant replication slave on *.* to ‘rep’@‘192.168.1.%’ identified by ‘123456’;mysql > flush privileges;

3．假如使用者名稱沒有錯，那麼如何排除是否是輸入的密碼錯誤呢？

額，我也想知道方法。最好就是多輸入幾遍，或者重新建立使用者名稱和密碼來測試。問題還沒有解決，轉到4

4．在你的防火牆中添加3306連接埠

[[email protected] mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent[root@localhost mysql]# firewall-cmd --reload

再關閉selinux

[[email protected] log]# vi /etc/sysconfig/selinux

把SELINUX=enforcing改為SELINUX=disabled

[[email protected] log]# source /etc/sysconfig/selinux

登入主伺服器，查看伺服器狀態

mysql > show master status \G

然後重新設定一次從伺服器，在配置之前首先關閉主從同步進程

mysql > stop slave;

之外的方法，我也沒試過

 Slave_SQL_Running: No

把上面的Slave_IO_Running調試成YES後，就輪到這個小樣了。我根據這個部落格的內容來解決的：http://kerry.blog.51cto.com/172631/277414

Mysql主從同步（複製）