如何實現MySQL數據庫同步

這篇文章主要介紹了如何實現MySQL數據庫同步，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

MySQL 的數據同步，在 MySQL 官方網站文檔上，叫 Replication 字面是重作的意思，意譯就是同步了。其實，MySQL 的同步，并不是使用同步 sync 這個單詞而是用重作 replication，很準確表明了 MySQL 數據庫操作的實質，是作同樣的操作，或叫重作同樣的操作，以保持主數據庫服務器 master 與 從屬服務器 slave 之樣的數據保持一致。replication 就是有重復，重作的意思。
MySQL 為了實現 replication 必須打開 bin-log 項，也是打開二進制的 MySQL 日志記錄選項。
MySQL 的 bin log 二進制日志，可以記錄所有影響到數據庫表中存儲記錄內容的 sql 操作，如 insert / update / delete 操作，而不記錄 select 這樣的操作。因此，我們可以通過二進制日志把某一時間段內丟失的數據可以恢復到數據庫中（如果二進制日志中記錄的日志項，包涵數據庫表中所有數據，那么，就可以恢復本地數據庫的全部數據了）。 而這個二進制日志，如果用作遠程數據庫恢復，那就是 replication 了。這就是使用 replication 而不用 sync 的原因。這也是為什么要設置 bin-log = 這個選項的原因。
在同步過程中，最重要的同步參照物，就是同步使用那一個二進制日志文件，從那一條記錄開始同步。
下面，我們來講解最核心的過程：
首先，你應該有兩個或兩個以上的 MySQL 數據庫服務器，版本最好是在 3.3 以上 （當然，兩個服務器不一定是兩臺機器，一臺機器上安裝兩個 MySQL 服務是可以的，同時，如果你對 MySQL replication 原理十分精通的話，你甚至可以在一個 MySQL 服務的兩個不同數據庫 database 之間作同步，看有沒有需要了）說明： 這兩個服務器一般設置一個為主服務器，或叫源服務器， master mysql server, 另一臺或其他多臺就是 replication slave 同步從服務器了。一臺 slave 與多臺 slave 設置方法是一樣的，這樣你就可以作類似數據庫集群了。
設置可訪問 MySQL 帳號，操作以英文為準。
MySQL 帳號一般設置為限定 IP 訪問，以保障安全性
MySQL 帳號一般在 master 與 slave 設置為相同帳號，同時是遠程可訪問
特別注意，如果你用 Linux / Unix 操作系統，那一定要注意一下防火墻 firewall 有沒有限制 MySQL 遠程訪問，如果是，最好是打開遠程訪問端口，并作好訪問 IP 限制
由于 my.cnf 中要明文存儲 MySQL 帳號密碼，請注意保護 my.cnf 不讓其他用戶訪問到（看來要向 MySQL 說明下以后用密碼存 passwd）。
第三步，當然是設置兩個服務器要同步的數據庫為同樣的數據庫了。
這里有一些技巧，包括 mysql 的 sql 指令說明下。
方法之一，就是英文說明中說的，先鎖定數據庫讀寫功能 ( 其實最好是停止 mysqld 服務，再作備份) 然后用 tar 備份數據庫目錄，轉到 slave 服務器相同數據目錄中。
方法之二，使用 mysql studio 這樣的實用工具，直接使用 mysql studio 的 backup database 工具把數據庫同步
以上兩種方法都是 master 數據庫中有不少數據記錄，按上兩 種方法得到 master 與 slave 有相同數據庫與數據記錄。 而第三種方法，則是適合于新建數據庫的情況，特別適合于 master 與 slave 在 my.cnf 已經設置好 replication 關系 （但 未指定 database 同步數據庫 ) 的情況: 這就是使用　mysql 的 sql 語句 load table from master 與 load data from master;
load table from master 可以從 master 數據庫把表結構復制到 slave 數據庫中，這樣可以建立同步的表。
load data from master 是從 master 數據庫把數據導入到 slave 數據表中，條件是 master 從一開始安裝運行就使用了 bin-log 參數而保存有二進制日志
接下來就是配置 master 與 slave 的 my.cnf 文件，使得 replcation 能有合適的啟動參數以支持數據同步
技巧： 如果你使用 win2k 的 mysql 那么，你可以 mysql.com 出的官方 mysql administrator 實用程序，直接在 mysql administrator 中即可配置 master 與 slave ，同時也可以配置 query-cache 。
在 master 的 my.cnf（如果是 win32 那就是 my.ini）增加
[mysqld]
log-bin =
server-id=1
注意，上面的 log-bin = 中的 等號 = 是不可少的。
在 slave 的 my.cnf 修改
[mysqld]
server-id=2 # 如果有多個 slave 就改為不重復的 id 就好，在mysql 4.1 中，這個已經取消了
master-host=10.10.10.22
master-user=backup #同步用戶帳號
master-password=1234
master-port=3306
master-connect-retry=60 #預設重試間隔60秒
replicate-do-db=test # 告訴slave只做 test 數據庫的更新
bin-log =
 
 
 
檢查 master 與 slave 配置狀態，使用 show master status; 與 show slave status; 這兩個 SQL 指令即可在 master 與 slave 查看配置狀態。這里有兩個狀態變量十分重要
mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File　　 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73　　| test　　 | manual,mysql　 |
+---------------+----------+--------------+------------------+
注意 File 是 mysql-bin.003 而 position 是 73
這個文件是對 test 這個 數據庫的二進制日志記錄，記錄數據變化的當前記錄條數是 73
前面我們說過，二進制日志記錄著某個數據庫所有數據記錄變化的 sql 操作語句，如 insert / update / delete 等，正是因為如此，在 slave 同步 mysql 數據庫操作時，其實是讀取master 中這個 mysql-bin.003 二進制日志中的 sql 操作，同在 slave 中執行這些 sql 操作，所以，同步成功有以下幾個條件：
master 與 slave 有相同的數據庫表結構，最好 database name 也一樣（可以設置為不一樣的 database name）
master 與 slave 有相同的初始數據記錄，保證同步操作開始后兩者數據一致
master 必須使用 bin-log 二進制日志記錄（推薦 slave 也使用 bin-log）
slave 是從 master 的 bin-log 是讀取 sql 記錄來同步，所以，從哪一條 log 開始讀取很重要（下面的第 7 條操作，就是保證 slave 能與 master 保持相同的記錄讀取，并讀取正確的 bin-log 日志文件）
在 slave 執行下面 sql 操作
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,
->　 MASTER_LOG_POS=recorded_log_position;    
這里，把 上面的 redcorded_log_file_name 改為 mysql-bin.003 而 recorded_log_position 改為 73 （特別注意，這是關系關鍵）。
linux 下的 mysql 與 win2k 下的 MySQL 作 replication 會有什么技巧：
注意  下有大小寫區分，而 win2k 下沒有。所以最好使用 mysqldump 方法來讓兩個初始化同步，而不能使用 tar 方式直接 copy 數據庫表文件。使用  studio 來作 database backup 也不錯
注意 win2k 下的 mysql 配置文件是 c:my.ini。你可以使用 mysql administrator 0.9 這個圖型界面的實用工具來設置。
如果 win2k 下的 mysql 為 master ， 一定要注意 show master status 中顯示出來的結果。
win2k 下的 mysql 為 slave 時，注意不要把同步時間周期設置太長。
實戰
環境配置
操作系統：linux
MySQL：5.14
主服務器A IP：192.168.1.2（假設)
從服務器B IP：192.168.1.3（假設）
同步數據庫: test（用于測試，兩臺服務器初始數據一致） 
主服務器（master）設置
新建一個用于備份的用戶（直接用root用戶也可以）：
GRANT FILE ON *.* TO  IDENTIFIED BY ‘123456’；

編輯“/etc/my.cnf”（不同服務器可能路徑不同）文件，在該文件添加以下內容：
server-id=1
#設置服務器id，主從服務器要不同
log-bin=mysqllog
#啟用二進制變更日志（即把所有對數據進行操作的SQL命令以二進制格式記入日志）
#其中mysqllog是日志文件的名稱，日志的文件名是mysqllog.n，其中n是一個6位數字的整數。
binlog-do-db=test
#指定需要啟用二進制變更日志的數據庫"test"
binlog-ignore-db = mysql
#指定不需要啟用二進制變更日志的數據庫"mysql"

重啟mysqld服務，可以用mysql命令：SHOW MASTER STATUS;查看“啟用二進制變更日志”情況 
從服務器（slave）設置
編輯“/etc/my.cnf”（不同服務器可能路徑不同）文件，在該文件添加以下內容：

server-id=2
#設置服務器id，主從服務器要不同
replicate-do-db=test
#指定需要從master同步過來的數據庫"test"
replicate-ignore-db = mysql
#指定不需要從master同步過來的數據庫"mysql"

#設置master服務器的IP地址、登陸用戶、密碼、端口
master-host=192.168.1.2
master-user=backup
master-password=123456
master-port=3306

master-connect-retry=60
#連接master服務器失敗后重試的延遲時間
slave-skip-errors=all
#跳過所有錯誤繼續執行同步工作
log-slave-updates
#啟用從屬服務器上的日志同步功能 
注意：如果從服務器上存在master.info文件（如：/var/lib/mysql/master.info），要使以上配置選項生效，在重啟mysqld服務前必須刪除該文件。
重啟mysqld服務，可以用mysql命令：SHOW SLAVE STATUS;查看同步情況
從數據庫的相關命令：
slave st; slave start ; 開始停止從數據庫。
show slave statusG; 顯示從庫正讀取哪一個主數據庫二進制日志 
成功完成以上配置后，在主服務器A的test庫里添加數據或刪除數據，在從服務器B的test庫里馬上也能看到相應的變更。兩臺服務器的同步操作可以說是瞬間完成的。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“如何實現MySQL數據庫同步”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!