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 時，注意不要把同步時間周期設置太長。

同步實戰：

環境：

環境配置

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

MySQL：5.14

主服務器A IP：10.224.194.239

從服務器B IP：10.224.194.237

同步數據庫: test（用于測試，兩臺服務器初始數據一致） 

主服務器（master）設置

新建一個用于備份的用戶（直接用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”（不同服務器可能路徑不同）文件，在該文件添加以下內容：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=10.224.194.239 master-user=backup master-password=pass 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; 顯示從庫正讀取哪一個主數據庫二進制日志 

驗證：

在主/次 服務器上創建一張表名為test01,最好設置主鍵：讓后在主服務器上插入一條數據，此時會同步到次服務器上，如果沒有成功，查看log，號配置項是否正確

create table test01 

(   name_id varchar(10) not null,

    primary key(name_id)  

)

insert into test01(name_id) value(1);

成功完成以上配置后，在主服務器A的test庫里添加數據或刪除數據，在從服務器B的test庫里馬上也能看到相應的變更。兩臺服務器的同步操作可以說是瞬間完成的。

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