如何實現(xiàn)MySQL數(shù)據(jù)庫同步

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

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

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

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

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

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

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

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“如何實現(xiàn)MySQL數(shù)據(jù)庫同步”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!