mysql數(shù)據(jù)庫(kù)同步的示例分析

144次閱讀

共計(jì) 5259 個(gè)字符，預(yù)計(jì)需要花費(fèi) 14 分鐘才能閱讀完成。

這篇文章主要介紹了 mysql 數(shù)據(jù)庫(kù)同步的示例分析，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

同步介紹：

MySQL 的數(shù)據(jù)同步，在 MySQL 官方網(wǎng)站文檔上，叫 Replication 字面是重作的意思，意譯就是同步了。其實(shí)，MySQL 的同步，并不是使用同步 sync 這個(gè)單詞而是用重作 replication，很準(zhǔn)確表明了 MySQL 數(shù)據(jù)庫(kù)操作的實(shí)質(zhì)，是作同樣的操作，或叫重作同樣的操作，以保持主數(shù)據(jù)庫(kù)服務(wù)器 master 與從屬服務(wù)器 slave 之樣的數(shù)據(jù)保持一致。replication 就是有重復(fù)，重作的意思。

同步原理：

MySQL 為了實(shí)現(xiàn) replication 必須打開(kāi) bin-log 項(xiàng)，也是打開(kāi)二進(jìn)制的 MySQL 日志記錄選項(xiàng)。MySQL 的 bin log 二進(jìn)制日志，可以記錄所有影響到數(shù)據(jù)庫(kù)表中存儲(chǔ)記錄內(nèi)容的 sql 操作，如 insert / update / delete 操作，而不記錄 select 這樣的操作。因此，我們可以通過(guò)二進(jìn)制日志把某一時(shí)間段內(nèi)丟失的數(shù)據(jù)可以恢復(fù)到數(shù)據(jù)庫(kù)中（如果二進(jìn)制日志中記錄的日志項(xiàng)，包涵數(shù)據(jù)庫(kù)表中所有數(shù)據(jù)，那么，就可以恢復(fù)本地?cái)?shù)據(jù)庫(kù)的全部數(shù)據(jù)了）。而這個(gè)二進(jìn)制日志，如果用作遠(yuǎn)程數(shù)據(jù)庫(kù)恢復(fù)，那就是 replication 了。這就是使用 replication 而不用 sync 的原因。這也是為什么要設(shè)置 bin-log = 這個(gè)選項(xiàng)的原因。

在同步過(guò)程中，最重要的同步參照物，就是同步使用那一個(gè)二進(jìn)制日志文件，從那一條記錄開(kāi)始同步。

同步過(guò)程：

首先，你應(yīng)該有兩個(gè)或兩個(gè)以上的 MySQL 數(shù)據(jù)庫(kù)服務(wù)器，版本最好是在 3.3 以上（當(dāng)然，兩個(gè)服務(wù)器不一定是兩臺(tái)機(jī)器，一臺(tái)機(jī)器上安裝兩個(gè) MySQL 服務(wù)是可以的，同時(shí)，如果你對(duì) MySQL replication 原理十分精通的話(huà)，你甚至可以在一個(gè) MySQL 服務(wù)的兩個(gè)不同數(shù)據(jù)庫(kù) database 之間作同步，看有沒(méi)有需要了）說(shuō)明：這兩個(gè)服務(wù)器一般設(shè)置一個(gè)為主服務(wù)器，或叫源服務(wù)器，master mysql server, 另一臺(tái)或其他多臺(tái)就是 replication slave 同步從服務(wù)器了。一臺(tái) slave 與多臺(tái) slave 設(shè)置方法是一樣的，這樣你就可以作類(lèi)似數(shù)據(jù)庫(kù)集群了。

設(shè)置可訪(fǎng)問(wèn) MySQL 帳號(hào)，操作以英文為準(zhǔn)。

MySQL 帳號(hào)一般設(shè)置為限定 IP 訪(fǎng)問(wèn)，以保障安全性

MySQL 帳號(hào)一般在 master 與 slave 設(shè)置為相同帳號(hào)，同時(shí)是遠(yuǎn)程可訪(fǎng)問(wèn)

特別注意，如果你用 Linux / Unix 操作系統(tǒng)，那一定要注意一下防火墻 firewall 有沒(méi)有限制 MySQL 遠(yuǎn)程訪(fǎng)問(wèn)，如果是，最好是打開(kāi)遠(yuǎn)程訪(fǎng)問(wèn)端口，并作好訪(fǎng)問(wèn) IP 限制

由于 my.cnf 中要明文存儲(chǔ) MySQL 帳號(hào)密碼，請(qǐng)注意保護(hù) my.cnf 不讓其他用戶(hù)訪(fǎng)問(wèn)到（看來(lái)要向 MySQL 說(shuō)明下以后用密碼存 passwd）。

第三步，當(dāng)然是設(shè)置兩個(gè)服務(wù)器要同步的數(shù)據(jù)庫(kù)為同樣的數(shù)據(jù)庫(kù)了。

這里有一些技巧，包括 mysql 的 sql 指令說(shuō)明下。

方法之一，就是英文說(shuō)明中說(shuō)的，先鎖定數(shù)據(jù)庫(kù)讀寫(xiě)功能 (其實(shí)最好是停止 mysqld 服務(wù)，再作備份) 然后用 tar 備份數(shù)據(jù)庫(kù)目錄，轉(zhuǎn)到 slave 服務(wù)器相同數(shù)據(jù)目錄中。

方法之二，使用 mysql studio 這樣的實(shí)用工具，直接使用 mysql studio 的 backup database 工具把數(shù)據(jù)庫(kù)同步

以上兩種方法都是 master 數(shù)據(jù)庫(kù)中有不少數(shù)據(jù)記錄，按上兩種方法得到 master 與 slave 有相同數(shù)據(jù)庫(kù)與數(shù)據(jù)記錄。而第三種方法，則是適合于新建數(shù)據(jù)庫(kù)的情況，特別適合于 master 與 slave 在 my.cnf 已經(jīng)設(shè)置好 replication 關(guān)系（但未指定 database 同步數(shù)據(jù)庫(kù) ) 的情況: 這就是使用 mysql 的 sql 語(yǔ)句 load table from master 與 load data from master;

load table from master 可以從 master 數(shù)據(jù)庫(kù)把表結(jié)構(gòu)復(fù)制到 slave 數(shù)據(jù)庫(kù)中，這樣可以建立同步的表。load data from master 是從 master 數(shù)據(jù)庫(kù)把數(shù)據(jù)導(dǎo)入到 slave 數(shù)據(jù)表中，條件是 master 從一開(kāi)始安裝運(yùn)行就使用了 bin-log 參數(shù)而保存有二進(jìn)制日志

接下來(lái)就是配置 master 與 slave 的 my.cnf 文件，使得 replcation 能有合適的啟動(dòng)參數(shù)以支持?jǐn)?shù)據(jù)同步

技巧：如果你使用 win2k 的 mysql 那么，你可以 mysql.com 出的官方 mysql administrator 實(shí)用程序，直接在 mysql administrator 中即可配置 master 與 slave，同時(shí)也可以配置 query-cache。

在 master 的 my.cnf（如果是 win32 那就是 my.ini）增加

[mysqld]

log-bin =

server-id=1

注意，上面的 log-bin = 中的等號(hào) = 是不可少的。

在 slave 的 my.cnf 修改

[mysqld]

server-id=2 # 如果有多個(gè) slave 就改為不重復(fù)的 id 就好，在 mysql 4.1 中，這個(gè)已經(jīng)取消了

master-host=10.10.10.22

master-user=backup #同步用戶(hù)帳號(hào)

master-password=1234

master-port=3306

master-connect-retry=60 #預(yù)設(shè)重試間隔 60 秒

replicate-do-db=test # 告訴 slave 只做 test 數(shù)據(jù)庫(kù)的更新

bin-log =

檢查 master 與 slave 配置狀態(tài)，使用 show master status; 與 show slave status; 這兩個(gè) SQL 指令即可在 master 與 slave 查看配置狀態(tài)。這里有兩個(gè)狀態(tài)變量十分重要

mysql SHOW MASTER STATUS;

+—————+———-+————–+——————+

+—————+———-+————–+——————+

+—————+———-+————–+——————+

注意 File 是 mysql-bin.003 而 position 是 73

這個(gè)文件是對(duì) test 這個(gè) 數(shù)據(jù)庫(kù)的二進(jìn)制日志記錄，記錄數(shù)據(jù)變化的當(dāng)前記錄條數(shù)是 73

前面我們說(shuō)過(guò)，二進(jìn)制日志記錄著某個(gè)數(shù)據(jù)庫(kù)所有數(shù)據(jù)記錄變化的 sql 操作語(yǔ)句，如 insert / update / delete 等，正是因?yàn)槿绱耍?slave 同步 mysql 數(shù)據(jù)庫(kù)操作時(shí)，其實(shí)是讀取 master 中這個(gè) mysql-bin.003 二進(jìn)制日志中的 sql 操作，同在 slave 中執(zhí)行這些 sql 操作，所以，同步成功有以下幾個(gè)條件：

master 與 slave 有相同的數(shù)據(jù)庫(kù)表結(jié)構(gòu)，最好 database name 也一樣（可以設(shè)置為不一樣的 database name）

master 與 slave 有相同的初始數(shù)據(jù)記錄，保證同步操作開(kāi)始后兩者數(shù)據(jù)一致

master 必須使用 bin-log 二進(jìn)制日志記錄（推薦 slave 也使用 bin-log）

slave 是從 master 的 bin-log 是讀取 sql 記錄來(lái)同步，所以，從哪一條 log 開(kāi)始讀取很重要（下面的第 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 會(huì)有什么技巧：

注意下有大小寫(xiě)區(qū)分，而 win2k 下沒(méi)有。所以最好使用 mysqldump 方法來(lái)讓兩個(gè)初始化同步，而不能使用 tar 方式直接 copy 數(shù)據(jù)庫(kù)表文件。使用 studio 來(lái)作 database backup 也不錯(cuò)

注意 win2k 下的 mysql 配置文件是 c:\my.ini。你可以使用 mysql administrator 0.9 這個(gè)圖型界面的實(shí)用工具來(lái)設(shè)置。

如果 win2k 下的 mysql 為 master，一定要注意 show master status 中顯示出來(lái)的結(jié)果。

win2k 下的 mysql 為 slave 時(shí)，注意不要把同步時(shí)間周期設(shè)置太長(zhǎng)。

同步實(shí)戰(zhàn)：

環(huán)境：

環(huán)境配置

操作系統(tǒng)：CentOS release 5.3 (Final) 64bit

MySQL：5.14

主服務(wù)器 A IP：10.224.194.239

從服務(wù)器 B IP：10.224.194.237

同步數(shù)據(jù)庫(kù): test（用于測(cè)試，兩臺(tái)服務(wù)器初始數(shù)據(jù)一致）

主服務(wù)器（master）設(shè)置

新建一個(gè)用于備份的用戶(hù)（直接用 root 用戶(hù)也可以）：

GRANT FILE ON *.* TO backup@10.224.194.237 IDENTIFIED BY lsquo;pass rsquo;；

GRANT REPLICATION SLAVE ON *.* TO backup@10.224.194.237 IDENTIFIED BY pass

編輯“/etc/my.cnf”（不同服務(wù)器可能路徑不同）文件，在該文件添加以下內(nèi)容：server-id=1 #設(shè)置服務(wù)器 id，主從服務(wù)器要不同 log-bin=mysqllog #啟用二進(jìn)制變更日志（即把所有對(duì)數(shù)據(jù)進(jìn)行操作的 SQL 命令以二進(jìn)制格式記入日志）# 其中 mysqllog 是日志文件的名稱(chēng)，日志的文件名是 mysqllog.n，其中 n 是一個(gè) 6 位數(shù)字的整數(shù)。binlog-do-db=test #指定需要啟用二進(jìn)制變更日志的數(shù)據(jù)庫(kù) test binlog-ignore-db = mysql #指定不需要啟用二進(jìn)制變更日志的數(shù)據(jù)庫(kù) 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 同步過(guò)來(lái)的數(shù)據(jù)庫(kù) test replicate-ignore-db = mysql #指定不需要從 master 同步過(guò)來(lái)的數(shù)據(jù)庫(kù) mysql #設(shè)置 master 服務(wù)器的 IP 地址、登陸用戶(hù)、密碼、端口 master-host=10.224.194.239 master-user=backup master-password=pass master-port=3306 master-connect-retry=60 #連接 master 服務(wù)器失敗后重試的延遲時(shí)間 slave-skip-errors=all #跳過(guò)所有錯(cuò)誤繼續(xù)執(zhí)行同步工作 log-slave-updates #啟用從屬服務(wù)器上的日志同步功能

注意：如果從服務(wù)器上存在 master.info 文件（如：/var/lib/mysql/master.info），要使以上配置選項(xiàng)生效，在重啟 mysqld 服務(wù)前必須刪除該文件。

重啟 mysqld 服務(wù)，可以用 mysql 命令：SHOW SLAVE STATUS; 查看同步情況

從數(shù)據(jù)庫(kù)的相關(guān)命令：

slave st; slave start ; 開(kāi)始停止從數(shù)據(jù)庫(kù)。

show slave statusG; 顯示從庫(kù)正讀取哪一個(gè)主數(shù)據(jù)庫(kù)二進(jìn)制日志

驗(yàn)證：

在主 / 次服務(wù)器上創(chuàng)建一張表名為 test01, 最好設(shè)置主鍵：讓后在主服務(wù)器上插入一條數(shù)據(jù)，此時(shí)會(huì)同步到次服務(wù)器上，如果沒(méi)有成功，查看 log，號(hào)配置項(xiàng)是否正確

create table test01

( name_id varchar(10) not null,

primary key(name_id)

)

insert into test01(name_id) value(1);

成功完成以上配置后，在主服務(wù)器 A 的 test 庫(kù)里添加數(shù)據(jù)或刪除數(shù)據(jù)，在從服務(wù)器 B 的 test 庫(kù)里馬上也能看到相應(yīng)的變更。兩臺(tái)服務(wù)器的同步操作可以說(shuō)是瞬間完成的。

感謝你能夠認(rèn)真閱讀完這篇文章，希望丸趣 TV 小編分享的“mysql 數(shù)據(jù)庫(kù)同步的示例分析”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持丸趣 TV，關(guān)注丸趣 TV 行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!

正文完