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

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

同步介紹：

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

同步原理：

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

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

同步過程：

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

設(shè)置可訪問MySQL 帳號，操作以英文為準(zhǔn)。

MySQL 帳號一般設(shè)置為限定IP 訪問，以保障安全性

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

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

由于my.cnf 中要明文存儲(chǔ)MySQL 帳號密碼，請注意保護(hù)my.cnf 不讓其他用戶訪問到（看來要向MySQL 說明下以后用密碼存passwd）。

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

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

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

方法之二，使用mysql studio 這樣的實(shí)用工具，直接使用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 從一開始安裝運(yùn)行就使用了bin-log 參數(shù)而保存有二進(jìn)制日志

接下來就是配置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 = 中的 等號= 是不可少的。

在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 #同步用戶帳號

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; 這兩個(gè)SQL 指令即可在master 與slave 查看配置狀態(tài)。這里有兩個(gè)狀態(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

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

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

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 會(huì)有什么技巧：

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

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

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

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

同步實(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ù)庫: test（用于測試，兩臺(tái)服務(wù)器初始數(shù)據(jù)一致） 

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

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

GRANT FILE ON *.* TO backup@10.224.194.237 IDENTIFIED BY ‘pass’；

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)制變更日志（即把所有對數(shù)據(jù)進(jìn)行操作的SQL命令以二進(jìn)制格式記入日志）#其中mysqllog是日志文件的名稱，日志的文件名是mysqllog.n，其中n是一個(gè)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=10.224.194.239 master-user=backup master-password=pass master-port=3306  master-connect-retry=60 #連接master服務(wù)器失敗后重試的延遲時(shí)間slave-skip-errors=all #跳過所有錯(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ù)庫的相關(guān)命令：

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

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

驗(yàn)證：

在主/次 服務(wù)器上創(chuàng)建一張表名為test01,最好設(shè)置主鍵：讓后在主服務(wù)器上插入一條數(shù)據(jù)，此時(shí)會(huì)同步到次服務(wù)器上，如果沒有成功，查看log，號配置項(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庫里添加數(shù)據(jù)或刪除數(shù)據(jù)，在從服務(wù)器B的test庫里馬上也能看到相應(yīng)的變更。兩臺(tái)服務(wù)器的同步操作可以說是瞬間完成的。

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