Mysql資料同步給第三方系統的方案探索

來自: http://xiaofengtoo.iteye.com/category/57504?show_full=true

 

1.   
問題提出

在需給第三方提供某些表的資料同步更新時，例如做平台的系統，需要給上層的CRM系統提供某些表的資料時，大家一般如何採取如何方案呢？另外，如果上層的系統有多家，分別用於多種用途時，應該提供何種方式讓別人的資料得到同步？ 

2.   
解決方案 2.1 採用資料庫的同步功能

2.1.1 方案描述 
   該方案利用了各種資料庫的資料同步功能，例如MySql和Sql Server等資料庫都具有資料庫或表同步的功能，這種情況需要在需要同步的機器上建立需要同步的表的結構。該功能也用在當我們的系統有主從資料庫的情況下的資料同步問題。使用該方案的優點是：

1）不需要自己編寫程式；

2）可以多個從機過來同步某些表。

使用該方案的缺點是：

1）比較依賴於資料庫的功能，若兩者的資料庫不一樣時，可能不能成功配置；

2）需要改變主要資料庫的伺服器的配置；

3）要同步的表的結構必須完全一致。

該種方案的參考如下：

 

 

 

2.1.2 方案舉例
       MySql資料庫支援資料同步，可同步其中的某些表，不過需要在第三方的資料庫和需要進行同步的資料庫中進行配置。

假設有兩台MySQL伺服器192.168.2.89和192.168.2.170，兩台伺服器都是Linux環境下的，兩台伺服器的配置一樣，將192.168.2.89作為Master（主）資料庫，將192.168.2.170作為slave（從）資料庫，此時採用單步同步的方式即可，即是Slave資料庫伺服器主動去Master同步資料。

首先在Master資料庫（192.168.2.89）中找到my.cnf設定檔的目錄（筆者這裡為/etc/my.cnf），修改該檔案的內容，在其後添加如下兩行（第一行表示同步方式，第二行表示要同步的資料庫為smp）：

Sql代碼

1. sql-bin-update-same   
2. binlog-do-db=smp  

sql-bin-update-samebinlog-do-db=smp

 

為了使得配置生效，需要將Master資料庫伺服器重啟一下，參考命令如下：

 

 

 

Sql代碼

1. mysql.server restart  

mysql.server restart

 Master資料庫配置完畢以後，接著開始配置Slave資料庫伺服器（192.168.2.170），首先也是需要編輯/etc/my.cnf檔案，找到如下行：

Sql代碼

1. server-id       = 1  

server-id       = 1

 

 

 修改該行，並添加如下一些行，新增內容如下：

Sql代碼

1. server-id                   = 2          //本MySQL是slave伺服器   
2. master-host             = 192.168.2.89    //master伺服器的IP   
3. master-user             = mysql           //串連master伺服器的使用者   
4. master-password   = mysql123             //串連master伺服器的密碼   
5. master-port              = 3306            //串連連接埠   
6. master-connect-retry    = 10              //重試次數   
7. replicate-do-db        = smp:test_mysql //同步資料庫表為smp庫的test_mysql表   
8. log-slave-updates                          //同步的形式  

server-id                   = 2          //本MySQL是slave伺服器master-host             = 192.168.2.89    //master伺服器的IPmaster-user             = mysql           //串連master伺服器的使用者master-password   = mysql123             //串連master伺服器的密碼master-port              = 3306            //串連連接埠master-connect-retry    = 10              //重試次數replicate-do-db        = smp:test_mysql //同步資料庫表為smp庫的test_mysql表log-slave-updates                          //同步的形式

 

修改完畢後，使用同樣的方法重啟一下從機的資料庫。但是筆者發現重啟以後，從機的MySql的/home/mysql/var/ localhost.localdomain.err報出了如下的錯誤，並沒有重啟成功：

Sql代碼

1. 101223 20:37:01 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet o   
2. n master ( server_errno=1236)   
3. 101223 20:37:01 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max  
4. _allowed_packet; Increase max_allowed_packet on master', Error_code: 1236  

101223 20:37:01 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236)101223 20:37:01 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master', Error_code: 1236

 

查網上資料才知，原來是兩個機器的my.cnf的max_allowed_packet
配置都是1M，太小，可將其都改成16M，接著按如下順序執行重啟主從伺服器：注意：我實踐時發現還有點問題，請大家先不用使用該方案嘗試。

注意：因為同步的是smp庫的test_mysql表，所以首先需要在兩個主機smp資料庫中都有名為test_mysql的表，筆者用了建立了個簡單的test_mysql表，參考語句如下：

Sql代碼

1. create table test_mysql    
2. (    
3.     id   integer not null auto_increment,   
4.     name varchar(32) not null,   
5.     primary key (id)   
6. );  

create table test_mysql (     id   integer not null auto_increment,    name varchar(32) not null,    primary key (id));

 

若要測試是否成功配置，最簡單的辦法是在Master資料庫（192.168.2.89）的該表中插入一條資料，看看是否從機的該表（192.168.2.170）也插入了對應的資料。

2.2 同步寫入FTP目錄檔案

 2.2.1 方案描述
    在筆者的開發過程中，也遇到過這種情況：第三方接了多家平台，有些資訊（例如話單資訊）的寫入格式都已經由這個第三方統一定義好了，需要在即時寫入到指定的FTP目錄下，由他們那邊的程式每隔一小時或半小時等自行抓取和刪除。參考如下所示：

 2.2.2 方案實現建議 
    可以讓平台在本地寫入同步資訊（例如話單資訊）時，即時觸發一個或多個程式，按照第三方定義的格式寫入到指定的FTP目錄下。或者每隔一段時間（比對方取檔案的間隔時間一樣或者更短）觸發程式，將話單資訊寫入到FTP目錄下。

         對於資料量很大的情況，一般採用分小時寫入檔案，並有對應的小時匯總檔案，告知該小時需要讀取的是哪幾個檔案。刪除檔案操作一般讓第三方平台取完後自行刪除。

         為了以後核對資料的方便或應對異常的情況，在寫入FTP目錄下的檔案時，最好在本地目錄寫入與同步給第三方的檔案同樣的檔案內容。

2.3 自編程接收更新語句更新資料表

    2.3.1 方案描述
     該方案需要自己編寫程式來來對一個或多個第三方系統的資料進行更新或插入操作。

    2.3.2 方案實現建議   

    程式設計之初，需要考慮多個問題，例如：

 1）同步表採用設定檔配置

配置哪些表觸發第三方系統進行同步，需要能夠靈活配置在設定檔中。

 2）觸發動作可配置

為了備份方便，應該能進行多種操作。例如唯寫第三方庫、寫入第三方庫時還要寫入本地的備份檔案中。

 3）觸發程式需要靈活配置

為了應對本平台的表結構與第三方平台不一致，或需要查詢一些資訊後再插入或更新的情況，還需要支援在某個表的觸發事件發生時，觸發哪個程式能夠配置指定，預設時可採用預設的程式（一般是運行語句直接插入或更新第三方系統）。

 4）第三方系統的資料庫配置可配性

需要同步資料的第三方系統可以為多個，這些資料庫配置資訊應該可以在設定檔中靈活配置的。

 5）觸發時機可配

       是每半小時、一小時還是即時觸發，應該是可配置的。

3.   
參考文章

1）《Mysql兩個資料庫之間的同步以及指定表之間的同步》：

 http://www.360doc.com/content/06/1215/18/3500_295248.shtml

2）《教你怎樣在兩台MySql資料庫之間實現同步》

http://www.ltesting.net/html/51/n-131551.html