Llinux中mysql資料單向&雙向同步筆記

文章分類:資料庫

關鍵字: mysql master slave

最近因項目要求，在linux伺服器上設定mysql的資料同步，幸好網路資源多啊，google一下全有了，不過實際操作起來，就麻煩
了，遇到很多問題，不過多google一下也解決了，

同時學習了很多相關方面的知識，現將總結於此，以備後用，希望對有此需要的JE們有所協助；

一、準備環境
    自己做練習硬體資源少，只能在虛擬器上操作了哈哈！
   
     linux 版本：Red Hat Enterprise Linux Server release 5.1
     mysql 版本：MySQL-server-community-5.0.67-0.rhel5.i386.rpm
                 MySQL-client-community-5.0.67-0.rhel5.i386.rpm
    
（PS:mysql的同步機制是基於二進位日誌binlog，不同的mysql版本會導致其格式的不同，從而導致不能實現資料同步，因此最好的搭配組合是
Master的MySQL版本和Slave的

版本相同或者Slave的版本高於Master的版本，因為mysql是向下相容的，為此Master的版本不能高於Slave版本，否則有可能不能實現
功能；如果要實現雙向同步，最好mysql是同

一版本）

     虛擬機器版本：VMware6.0

     在虛擬機器上安裝兩個linux系統，並分別安裝上mysql（mysql安裝在俺部落格的mysql安裝有詳細說明）

   mysql預設安裝的檔案分別為：
   
　　/var/lib/mysql/ （資料庫目錄，其所建立的資料庫檔案都在該目錄下）

　　/usr/share/mysql （mysql相關設定檔）

　　/usr/bin （mysql相關命令，如mysql mysqladmin等）

   為了區分系統，給系統命名一下，便於後面的說名：
   
    LinuxA 系統： Ip：192.168.59.123 （Master）

    LinuxB 系統： Ip：192.168.59.124 （Slave）

    關閉LinuxA 和 LinuxB 的防火牆 #service iptables stop （保證系統互相可以ping的通）
   
二、設定單向mysql資料同步（LinuxA(Master)->LinuxB(Slave)）

    1、 LinuxA 為 master

        LinuxB 為 slave （同步LinuxA 上mysql上指定的test資料庫中的資料）；

    由於剛安裝的mysql，因此分別在LinuxA 和 LinuxB 的 mysql 上建立相同的資料庫如 test（表有 stu ，
class，teacher），保證兩個mysql上的資料庫名一致其表名及表的

結構一致，若有一個不同，將導致無法實現同步；
    （PS: 要使資料同步，必須保證資料庫名一致其表名及表的結構一致）

    2、在作為master（即LinuxA）的mysql上建立一個賬戶專門用於slave（即LinuxB）來進行資料同步

       # mysql （預設使用者，如設定了root密碼，使用mysql -u root -p 登入mysql）
       mysql>grant file on *.* to backa@'192.168.59.124' identified
by 'backa'
       mysql>flush privileges;

      （PS：此backa賬戶只能通過ip為192.168.59.124的訪問）
 
      在LinuxB上測試賬戶backa是否可以訪問LinxuA上的mysql：
      #mysql -u backa -p -h 192.168.59.123（輸入密碼backa，可以訪問說明設定正確）
   
    3、修改 my.cnf 檔案
      
     由於剛安裝的mysql
，在/etc目錄下可能無my.cnf檔案，從/user/share/mysql目錄中拷貝my-medium.cnf
到/etc並修改成my.cnf （LinuxA 和LinuxB 上一樣）
     如 # cp /user/share/mysql/my-medium.cnf  /etc/my.cnf

     修改在LinuxA(Master)上的/etc/my.cnf
    
      log-bin=mysql-bin （記錄檔，預設存在/var/lib/mysql下記錄檔以mysql-bin為開頭）
      #log_bin= /var/log/mysql/mysql-bin.log（也可以自己指定，在此用# 表示注釋掉了）
      binlog-do-db =test（需要同步的資料庫名，如需同步多個資料庫，可以另起行如binlog-do-db =test1
）     
      server-id= 1 （mysql標示）

     修改完後儲存退出，並重啟mysql 如#service mysql restart （重啟正常，表示配置沒錯誤）

     修改在LinxuB(Slave)上的/etc/my.cnf

      server-id= 2 （mysql標示，唯一否則啟動會出錯誤）
      master-host=192.168.59.123 （同步Master的ip地址）
      master-user=backa （同步所需的帳號）
      master-password=backa （同步帳號的密碼）
      master-port=3306 （LinuxA 中mysql的訪問連接埠）
      replicate-do-db=test （所需同步的資料庫名，如果是同步多個資料庫，可以另起一行如
replicate-do-db=test1）
      #replicate-do-table=test.stu （如果只需同步test資料庫中的stu表）
      #replicate-do-table=test.teacher（如果只需同步test資料庫中的stu表）
      master-connect-retry=60
（主伺服器（Master）宕機或串連丟失的情況下，從伺服器（slave）線程重新嘗試串連主伺服器之前睡眠的秒數）

     修改完後儲存退出，並重啟mysql 如#service mysql restart （重啟正常，表示配置沒錯誤）

    4.啟動同步

     進到LinuxA（Master）的mysql上：

     mysql>show master status/G (查看master目前狀態)
     運行完以上命令顯示結果如下：
 
    
            File: mysql-bin.000001 （當前binlog記錄檔）
        Position: 98 （但前binlog記錄檔位置）
    Binlog_Do_DB: test （同步資料庫）
Binlog_Ignore_DB:     （不需同步的資料，當前為設定）

-------------------------------------------------------------
     進到LinuxB（Slave）的mysql上：
     
     mysql>show slave status/G (查看slave同步狀態)

     運行完以上命令顯示結果如下：

             Slave_IO_State: Waiting for master to send event （slave
的io線程，擷取master的binlog的日誌內容，該為其目前的狀態）
                Master_Host: 192.168.59.123 （所需同步Master的ip地址）
                Master_User: backa（同步所需的帳號）
                Master_Port: 3306（同步所需的mysql連接埠號碼）
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
（master中的binlong的記錄檔，如與master的記錄檔名不同則同步失效）
        Read_Master_Log_Pos: 98
（master中的binlong的記錄檔的位置，即mysql-bin.000001檔案中的第98位置開始同步，若與master上的位置不同則同
步失效）
             Relay_Log_File: localhost-relay-bin.000001
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes  （Slave的io線程是否運行）
          Slave_SQL_Running: Yes （slave的sql線程是否運行，需要io線程和sql線程同為yes才有效）
            Replicate_Do_DB: test
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0

   
     如果Slave_IO_State：不為 Waiting for master to send event 表示未現實同步

    
     原因可能很多先列舉自己遇到：
        1、 Master_Log_File: mysql-bin.000001
            Read_Master_Log_Pos:98
            記錄檔和位置與LinuxA（Master）的目前狀態不一致
        解決方式：（在LinuxB的mysql操作）
        mysql>slave stop;(修改前先關閉slave)
        mysql>change master to
             >master_log_file='mysql-bin.000001' 
(ps:與LinuxA中的master的記錄檔名相同)
             >master_log_pso=98 (ps:與LinuxA中的master的記錄檔當前位置相同)
             >master_user='backa'  (如果同步的賬戶不一致，也可在此修改)
             >master_password='backa'  (如果同步的賬戶密碼不一致，也可在此修改)
             >;

        (PS:如果重新修改/etc/my.cnf 中相關slave的配置資訊，重新啟動查看
slave的狀態還是顯示未修改的資料，應為第一次是讀取my.cnf，之後就會在/var/lib/mysql/下

產生一個master.info
的檔案，因此第二次就不會讀取my.cnf的內容，而是讀取master.info中的內容，為此要想使重新修改的my.cnf生效的話，刪除
master.info檔案，重起mysql

即可)
        mysql>slave start;(重新啟動slave)

        mysql>show slave status/G (查看slave狀態)

        如果顯示的 Slave_IO_State: Waiting for master to send event
                 Slave_IO_Running: Yes 
                Slave_SQL_Running: Yes
        祝賀成功實現單向同步即 linuxA --> linuxB 的同步；
        在LinuxA的mysql上對test的 insert ， delete ，update及對錶結構的修改等操作 都會同步到
LinuxB的mysql上的test資料庫上
        （ps：如果在master上執行如 insert into to stu (uuid(),'yushan') 則同步到
slave上 ，id不同，因為其id是用uuid產生的，所以會不一致，之後對這條資料的

delete 和 update則不會實現同步）

三、雙向同步（LinuxA<--->LinuxB）
       
   
雙向同步最好使用的mysql版本要一致，保證二進位日誌binlog的格式相同；雙向同步即對任何一方mysql的
insert，update，delete，alter等操作都會影響到另一方mysql中

的資料；由於前面已成功設定單向同步，在此基礎上，把LinuxB（Slave）的配置 添加到 LinuxA(Master)
上，把LinuxA（Master）的配置添加到LinuxB（Slave）上，然後啟動設

置同步的binlong檔案及當前位置即可,具體操作如下：

    1、 LinuxA 在原有Master的基礎上增加Slave （同步LinuxB 上mysql上指定的test資料庫中的資料）

        LinuxB 在原有Slave的基礎上增加Mmaster（同步LinuxA 上mysql上指定的test資料庫中的資料）；
   
    2、在作為master（即LinuxB）的mysql上建立一個賬戶專門用於slave（即LinuxA）來進行資料同步

       # mysql （預設使用者，如設定了root密碼，使用mysql -u root -p 登入mysql）
       mysql>grant file on *.* to backb@'192.168.59.123' identified
by 'backb'
       mysql>flush privileges;

      （PS：此backb賬戶只能通過ip為192.168.59.123的訪問）
 
      在LinuxA上測試賬戶backb是否可以訪問LinxuB上的mysql：
      #mysql -u backb -p -h 192.168.59.124（輸入密碼backb，可以訪問說明設定正確）

   
   3、修改 my.cnf 檔案
 
    在LinuxA 上的/etc/my.cnf 檔案上添加Slave的配置

      #server-id= 2
（mysql標示，唯一否則啟動會出錯誤，因為原先已有標示為1，在此就可以注釋掉，一個mysql只需一個標示即可）
      master-host=192.168.59.124 （同步LinuxB (Master)的ip地址）
      master-user=backb （同步所需的帳號）
      master-password=backb （同步帳號的密碼）
      master-port=3306 （LinuxB 中mysql的訪問連接埠）
      replicate-do-db=test （所需同步的資料庫名，如果是同步多個資料庫，可以另起一行如
replicate-do-db=test1）
      #replicate-do-table=test.stu （如果只需同步test資料庫中的stu表）
      #replicate-do-table=test.teacher（如果只需同步test資料庫中的stu表）
      master-connect-retry=60
（主伺服器（Master）宕機或串連丟失的情況下，從伺服器（slave）線程重新嘗試串連主伺服器之前睡眠的秒數）

    修改完後儲存退出，並重啟mysql 如#service mysql restart （重啟正常，表示配置沒錯誤）

   
    在LinxuB上的/etc/my.cnf檔案上添加Master的配置

      #server-id= 1
（mysql標示,唯一否則啟動會出錯誤，因為原先已有標示為2，在此就可以注釋掉，一個mysql只需一個標示即可）
      log-bin=mysql-bin （記錄檔，預設存在/var/lib/mysql下記錄檔以mysql-bin為開頭）
      #log_bin= /var/log/mysql/mysql-bin.log（也可以自己指定，在此用# 表示注釋掉了）
      binlog-do-db =test（需要同步的資料庫名，如需同步多個資料庫，可以另起行如binlog-do-db =test1 ）
 
     修改完後儲存退出，並重啟mysql 如#service mysql restart （重啟正常，表示配置沒錯誤）

   4.啟動同步

     進到LinuxB（Master）的mysql上：

     mysql>show master status/G (查看master目前狀態)
     運行完以上命令顯示結果如下：
    
            File: mysql-bin.000003 （當前binlog記錄檔）
        Position: 231 （但前binlog記錄檔位置）
    Binlog_Do_DB: test （同步資料庫）
Binlog_Ignore_DB:     （不需同步的資料，當前為設定）

-------------------------------------------------------------
     進到LinuxA（Slave）的mysql上：
     
     mysql>show slave status/G (查看slave同步狀態)

     運行完以上命令顯示結果如下：

             Slave_IO_State: Waiting for master to send event （slave
的io線程，擷取master的binlog的日誌內容，該為其目前的狀態）
                Master_Host: 192.168.59.124 （所需同步Master的ip地址）
                Master_User: backb（同步所需的帳號）
                Master_Port: 3306（同步所需的mysql連接埠號碼）
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
（master中的binlong的記錄檔，如與master的記錄檔名不同則同步失效）
        Read_Master_Log_Pos: 98
（master中的binlong的記錄檔的位置，即mysql-bin.000001檔案中的第98位置開始同步，若與master上的位置不同則同
步失效）
             Relay_Log_File: localhost-relay-bin.000001
              Relay_Log_Pos: 231
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes  （Slave的io線程是否運行）
          Slave_SQL_Running: Yes （slave的sql線程是否運行，需要io線程和sql線程同為yes才有效）
            Replicate_Do_DB: test
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0

     以上顯示為正確同步 如果顯示的一下資料不是
       Slave_IO_State: Waiting for master to send event
       Slave_IO_Running: Yes 
        Slave_SQL_Running: Yes

     若與以上的三個狀態表示不同，則未實現同步；可以採用以上單向同步中的 change master 進行重新設定（ps：防火牆需關閉）

     以上都能正確配置完且顯示的狀態一致，那麼祝賀您成功實現雙向同步即 linuxA <--> linuxB 的同步；

  四、 mysql
資料同步相關說明（轉載於網路，google一下很多）

　　1. 同步機制實現概述

　　MySQL同步機制基於master把所有對資料庫的更新操作(更新、刪除
等)都記錄在二進位日誌裡。因此，想要啟用同步機制，在master就必須啟用二進位日誌。

　　每個slave接受來自master上在二進位日誌中記錄的更新操作，因此在slave上執行了這個操作的一個拷貝。應該非常重要地意識到，二進位日
志只是從啟用二進位日誌開始的

時刻才記錄更新操作的。所有的slave必須在啟用二進位日誌時把master上已經存在的資料拷貝過來。如果運行同步時slave上的資料和
master上啟用二進位日誌時的資料不一致的

話，那麼slave同步就會失敗。

　　2. 同步實現細節

　　MySQL同步功能由3個線程(master上1個，slave上2個)來實現。執行 START SLAVE
語句後，slave就建立一個I/O線程。I/O線程串連到master上，並請求master發送二進位日誌

中的語句。master建立一個線程來把日誌的內容發送到slave上。這個線程在master上執行 SHOW PROCESSLIST
語句後的結果中的 Binlog Dump 線程便是。slave上的I/O線程讀取

master的 Binlog Dump 線程發送的語句，並且把它們拷貝到其資料目錄下的中繼日誌(relay
logs)中。第三個是SQL線程，salve用它來讀取中繼日誌，然後執行它們來更新資料。

　　如上所述，每個mster/slave上都有3個線程。每個master上有多個線程，它為每個slave串連都建立一個線程，每個slave只有I
/O和SQL線程。

　　在MySQL
4.0.2以前，同步只需2個線程(master和slave各一個)。slave上的I/O和SQL線程合并成一個了，它不使用中繼日誌。

　　slave上使用2個線程的優點是，把讀日誌和執行分開成2個獨立的任務。執行任務如果慢的話，讀日誌任務不會跟著慢下來。例如，如果
slave停止了一段時間，那麼I/O線程

可以在slave啟動後很快地從master上讀取全部日誌，儘管SQL線程可能落後I/O線程好幾的小時。如果slave在SQL線程沒全部執行完就停
止了，不過I/O線程卻已經把所有的更新日

志都讀取並且儲存在本地的中繼日誌中了，因此在slave再次啟動後就會繼續執行它們了。這就允許在master上清除二進位日誌，因為slave已經無
需去master讀取更新日誌了。

　　在mysql中執行 SHOW PROCESSLIST 語句就會告訴我們所關心的master和slave上發生的情況

　　在master上，SHOW PROCESSLIST 的結果如下：

　　mysql> SHOW PROCESSLIST/G

　　*************************** 1. row ***************************

　　Id: 21

　　User: root

　　Host: localhost

　　db: mysql

　　Command: Query

　　Time: 0

　　State: NULL

　　Info: SHOW PROCESSLIST

　　*************************** 2. row
(MASTER線程)***************************

　　Id: 25

　　User: back

　　Host: 192.168.1.125:34085

　　db: NULL

　　Command: Binlog Dump

　　Time: 746

　　State: Has sent all binlog to slave; waiting for binlog to be updated

　　Info: NULL

　　2 rows in set (0.01 sec)

　　在這裡，線程2是為一個slave串連建立的。結果表明所有未完成的更新日誌已經都發送到slave了，master正等待新的更新日誌發生。

　　在slave上，SHOW PROCESSLIST 的結果如下：

　　mysql> SHOW PROCESSLIST/G

　　*************************** 1. row ***************************

　　Id: 2

　　User: system user

　　Host:

　　db: NULL

　　Command: Connect

　　Time: 70479

　　State: Waiting for master to send event

　　Info: NULL

　　*************************** 2. row (I/O線程) ***************************

　　Id: 3

　　User: system user

　　Host:

　　db: NULL

　　Command: Connect

　　Time: 16971

　　State: Has read all relay log; waiting for the slave I/O thread to
update it

　　Info: NULL

　　*************************** 3. row (SQL線程)***************************

　　Id: 8

　　User: root

　　Host: localhost

　　db: NULL

　　Command: Query

　　Time: 0

　　State: NULL

　　Info: SHOW PROCESSLIST

　　3 rows in set (0.00 sec)

　　這表明線程2是I/O線程，它正串連到master上；線程3是SQL線程，它執行中繼日誌中的更新操作。現在，這2個線程都處於空閑狀態，正等待新
的更新日誌。

　　注意，Time 欄位的值告訴我們slave上的日誌比master晚了多久。

　　3. Master 同步線程狀態

　　以下列出了master的 Binlog Dump 線程 State 欄位中最常見的幾種狀態。如果在master上沒有 Binlog Dump
線程，那麼同步就沒有在運行。也就是說，沒有slave串連上來

。

　　Sending binlog event to slave

　　事件是由二進位日誌構成，一個事件通常由更新語句加上其他資訊。線程讀取到一個事件並正發送到slave上。

　　Finished reading one binlog; switching to next binlog

　　讀取完了一個二進位日誌，正切換到下一個。

　　Has sent all binlog to slave; waiting for binlog to be updated

　　已經讀取完全部未完成更新日誌，並且全部都發送到slave了。它處於空閑狀態，正等待在master上執行新的更新操作以在二進位日誌中產生新的事
件，然後讀取它們。

　　Waiting to finalize termination

　　當前線程停止了，這個時間很短

　　4. Slave的I/O線程狀態

　　以下列出了slave的I/O線程 State 欄位中最常見的幾種狀態。從MySQL 4.1.1開始，這個狀態在執行 SHOW SLAVE
STATUS 語句結果的 Slave_IO_State 欄位也會出現。這意味

著可以只執行 SHOW SLAVE STATUS 語句就能瞭解到更多的資訊。

　　Connecting to master

　　該線程證嘗試串連到master上。

　　Checking master version

　　確定串連到master後出現的一個短暫的狀態。

　　Registering slave on master

　　確定串連到master後出現的一個短暫的狀態。

　　Requesting binlog dump

　　確定串連到master後出現的一個短暫的狀態。該線程向master發送一個請求，告訴它要請求的二進位檔案以及開始位置。

　　Waiting to reconnect after a failed binlog dump request

　　如果二進位日誌轉儲(binary log dump)請求失敗了(由於串連斷開)，該線程在休眠時進入這個狀態，並定期重連。重連的時間間隔由

　　--master-connect-retry 選項來指定。

　　Reconnecting after a failed binlog dump request

　　該線程正嘗試重連到master。

　　Waiting for master to send event

　　已經串連到master，正等待它發送二進位日誌。如果master閑置時，這個狀態可能會持續較長時間，如果它等待超過
slave_read_timeout 秒，就會發生逾時。這時，它就會

考慮中斷連線，然後嘗試重連。

　　Queueing master event to the relay log

　　已經讀取到一個事件，正把它拷貝到中繼日誌中以備SQL線程處理。

　　Waiting to reconnect after a failed master event read

　　讀日誌時發生錯誤(由於串連斷開)。該線程在重連之前休眠 master-connect-retry 秒。

　　Reconnecting after a failed master event read

　　正嘗試重連到master。當串連確定後，狀態就變成 Waiting for master to send event。

　　Waiting for the slave SQL thread to free enough relay log space

　　relay_log_space_limit
的值非零，中繼日誌的大小總和超過這個值了。I/O線程等待SQL線程先處理中繼日誌然後刪除它們以釋放足夠的空間。

　　Waiting for slave mutex on exit

　　當前線程停止了，這個時間很短。

　　5. Slave的SQL線程狀態

　　以下列出了slave的SQL線程 State 欄位中最常見的幾種狀態：

　　Reading event from the relay log

　　從中繼日誌裡讀到一個事件以備執行。

　　Has read all relay log; waiting for the slave I/O thread to update it

　　已經處理完中繼日誌中的全部事件了，正等待I/O線程寫入更新的日誌。

　　Waiting for slave mutex on exit

　　當前線程停止了，這個時間很短。

　　SQL線程的 State 欄位有時候也可能是一個SQL語句。這意味著它從中繼日誌中讀取到一個事件了，從中提取出SQL語句，並執行它。
     
   
      （自己畫的mysql同步原理圖）
     

 

 

  相關mysql資料同步資料的串連：

 

         http://imysql.cn/node/58

         http://deidara.blog.51cto.com/400447/122039

         http://www.phpx.com/tech/database/2009-01-15/65.html

         http://www.cnscn.org/htm_data/288/0811/17463.html

         http://www.koven.org/archives/203.html

        

http://www.cnblogs.com/harryguo/archive/2008/03/10/1099223.html

        

http://www.blogjava.net/lzj520/archive/2008/02/27/182485.html