MYSQL資料庫間同步資料

標籤：

http://blog.csdn.net/swandy45/article/details/6982421

環境要求：

Windows 作業系統

需要Mysql 3.23.15以後的版本。

假設資料庫A為主機，資料庫B為從機（A向B提供同步服務，即B中的資料來自A）

A機器：IP=10.10.151.166

B機器：IP=10.10.151.156

下面看單向同步的配置步驟：

1         在機器A中建立一個新的資料庫，sql語句：

[sql] view plain copy 

1. CREATE DATABASE backup_db;  
2. USE test;  
3. CREATE TABLE `backup_table` (  
4.  `id` int(11) NOT NULL auto_increment,  
5.  `name` varchar(20) character set utf8 NOT NULL,  
6.  `sex` varchar(2) character set utf8 NOT NULL,  
7.  PRIMARY KEY  (`id`)  
8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  

2     開啟A機器的mysql安裝目錄下的my.ini檔案，在檔案最後添加：

[plain] view plain copy 

1. server-id=1  
2. log-bin=c:\mysqlback #啟動同步事件的日誌記錄檔案  
3. binlog-do-db=test #提供資料同步服務的資料庫  

3      在機器B中建立一個和機器A結構相同的資料庫，sql語句：

[plain] view plain copy 

1. CREATE DATABASE backup_db;  
2. USE test;  
3. CREATE TABLE `backup_table` (  
4.  `id` int(11) NOT NULL auto_increment,  
5.  `name` varchar(20) character set utf8 NOT NULL,  
6.  `sex` varchar(2) character set utf8 NOT NULL,  
7.  PRIMARY KEY  (`id`)  
8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  

註：機器A和B的資料庫結構必須一致，否則無法構成同步

4       開啟B機器的mysql安裝目錄下的my.ini檔案，在檔案最後添加：

[plain] view plain copy 

1. server-id=2  
2. master-host=10.10.151.166 #主機A的地址  
3. master-user=ym #主機A提供給B的使用者，該使用者中需要包括資料庫test的許可權  
4. master-password=ym #訪問密碼  
5. master-port=3306 #連接埠，主機的MYSQL連接埠  
6. master-connect-retry=60 #稍候再試60秒，當主從伺服器串連意外斷開時資料庫每隔60秒進行一個重新串連  
7. replicate-do-db=test #同步的資料庫   
8. p>5   完成以上配置之後，在機器A的mysql控制台中輸入：</p>  

[plain] view plain copy 

1. GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO [email protected] IDENTIFIED BY  ‘ym‘ ;  

    #這句是為讓從伺服器有足夠的許可權從主伺服器是哪個接收二進位記錄檔

6    重啟機器A和B的mysql資料庫，

[plain] view plain copy 

1. 在機器B的mysql控制台：  
2.  Mysql>slave start;  
3. 如果想查看同步配置的情況，可以按如下輸入：  
4. 機器A的mysql控制台：  
5. Mysql>show master status;  
6. 機器B的mysql控制台：  
7. Mysql>show slave status;  

 7         在機器A中test資料庫中的backup_table表中插入一些資料，查看機器B中test資料庫中的backup_table表應該同步實現了資料的改動。

   下面是雙向的資料同步配置步驟：

    1還是上面使用的機器A和機器B，這是B是主機，A是從機，保持上面的配置不要改在機器A 的mysql安裝目錄下的my.ini檔案最後添加：
       master-host=10.10.151.156 #主機B的地址
       master-user=ym #主機B提供給A的使用者，該使用者中需要包括資料庫test的許可權
        master-password=ym #訪問密碼
        master-port=3306 #連接埠，主機的MYSQL連接埠
        master-connect-retry=60 #稍候再試60秒當主從伺服器串連意外斷開時資料庫每隔60秒進行一個重新串連
        replicate-do-db=test #同步的資料庫
        在機器B的mysql安裝目錄下的my.ini檔案最後添加       
        log-bin=c:\mysqlback #啟動同步事件的日誌記錄檔案
        binlog-do-db=test #提供資料同步服務的資料庫
       
      2 機器B的mysql控制台輸入：
         GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO [email protected] IDENTIFIED BY  ‘ym‘ ;    
      3 重啟機器A和機器B的mysql資料庫
        在機器B中test資料庫中的backup_table表中插入一些資料，查看機器A中test資料庫中的backup_table表應該同步實現了資料的改動。但不會導致迴圈
       註：實現mysql資料庫的資料同步，須將倆台windows作業系統的windows防火牆關閉
       Mysql 資料同步的原理：
       Mysql的資料同步，在Mysql官方網站文檔上，叫replication字面意思是重作，這個很準確的表明了Mysql資料庫操作的實質，是重作同樣的操作，以保持主數 據程式庫伺服器沒事master與從屬資料庫伺服器slave之間的資料保持一致。
       從MySQL3.23.15以後，Mysql支援單向的非同步複製。也就是說，1台Mysql伺服器充當Master(主庫)，1台或多台Mysql伺服器充當Slaves(從庫)，資料從 Master向Slaves進行非同步複製。注意，這種複製是非同步，有別於Mysql的同步複製實現（這種實現稱做Mysql叢集，Mysql Cluster）。
       當主庫有更新的時候，主庫會把更新操作的SQL寫入二進位日誌(Bin log它記錄了所有更新了資料或者已經潛在更新了資料的所有語句)，並維護一個二進位日誌 檔案的索引，以便於記錄檔輪迴（Rotate）。在從庫啟動非同步複製的時候，從庫會開啟兩個I/O線程，其中一個線程串連主庫，要求主庫把二進位日誌的變化部 分傳給從庫，並把傳回的日誌寫入本地磁碟。另一個線程則負責讀取本地寫入的二進位日誌，並在本地執行，以反映出這種變化保證主從資料庫之間的資料同步。 較老的版本在複製的時候只啟用一個I/O線程，實現這兩部分的功能。這種方法是利用了Mysql資料庫主（master）和從（slave）非同步複製功能，來實現資料庫 之間的同步。
       Windows系統中，Mysql安裝目錄下my.ini檔案中：
       log-bin=c:\mysqlback；
       這一句表示資料庫的二進位記錄檔都存放在C盤根目錄下，並且以mysqlback為檔案名稱，以.000001這樣的序號為為檔案的副檔名，每一個binlog檔案預設是 1GB，超過了會自動換到以.000002為副檔名的的檔案，索引檔案mysqlback.index檔案記錄了所有mysqlback的檔案名稱。當然也可以刪除這些二進位日誌 檔案：
       使用下面的兩個命令   
       PURGE {MASTER | BINARY} LOGS TO ‘log_name‘ //log_name不會被清除，刪除這個序號以//前的
       PURGE {MASTER | BINARY} LOGS BEFORE ‘date‘ //date不會被清除,刪除這個日期以前的
       刪除之前所有的二進位記錄檔，並重建新的二進位記錄檔尾碼從.000001開始。
       但是，當如果有一個正在啟動並執行從屬伺服器，該伺服器當前正在讀取正在試圖刪除的記錄檔時則該刪除語句不會起作用，而是會失敗，並伴隨一個錯誤。不過， 如果從屬伺服器是停止的，並且碰巧清理了其想要讀取的記錄檔，則從屬伺服器啟動後便不能複製。當從屬伺服器正在複製時，刪除語句可以安全運行，不需要 停止它們。
       在完成了主伺服器A的my.ini檔案的配置之後，重啟主伺服器A，從現在起對主伺服器A資料庫的增加、刪除和修改操作都會記錄在二進位記錄檔中（查詢操作不 做記錄）。但也可以指定從伺服器從哪裡開始和主伺服器進行同步，在從伺服器上執行下面sql操作：
       首先輸入命令：slave stop;
       然後輸入：Mysql > change master to 
       —>master_host=’master_host_name’,#主機IP地址
       —>master_user=’replication_user_name’,
       —>master_password=’replication_password’,
       —>master_log_file=’recorded_log_file_name’,
       —>master_log_pos=’recorded_log_position’;
       最後輸入命令：slave start；
       這裡，把上面的recorded_log_file_name改為想從哪個二進位記錄檔開始同步的檔案名稱，而recorded_log_position改為從該檔案的第幾條記錄開始同 步。
       先輸入slave stop;指令，再輸入上述指令，最後輸入slave start;指令
       當資料同步產生錯誤時，會在mysql安裝目錄下產生*.err記錄檔，同時同步線程會退出。
       purge binary logs to ‘mysql-bin.000003′;
       PURGE {MASTER | BINARY} LOGS TO ‘log_name‘
      　PURGE {MASTER | BINARY} LOGS BEFORE ‘date‘
      　用於刪除列於在指定的日誌或日期之前的日誌索引中的所有二進位日誌。這些日誌也會從屬記錄在日誌索引檔案中的清單中被刪除，這樣被給定的日誌成為第一個。
      　例如：
      　PURGE MASTER LOGS TO ‘mysql-bin.010‘;
      　PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00‘;
        清除3天前的 binlog
        PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
      　BEFORE變數的date自變數可以為‘YYYY-MM-DD hh:mm:ss‘格式。MASTER和BINARY是同義字。

MYSQL資料庫間同步資料