MySQL主從複制（Master-Slave）

1 複制概述

   Mysql内建的複制功能是建構大型，高性能應用程式的基礎。将Mysql的資料分布到多個系統上去，這種分布的機制，是通過将Mysql的某一台主機的資料複制到其它主機（slaves）上，并重新執行一遍來實作的。複制過程中一個伺服器充當主伺服器，而一個或多個其它伺服器充當從伺服器。主伺服器将更新寫入二進制日志檔案，并維護檔案的一個索引以跟蹤日志循環。這些日志可以記錄發送到從伺服器的更新。當一個從伺服器連接配接主伺服器時，它通知主伺服器從伺服器在日志中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖并等待主伺服器通知新的更新。

  請注意當你進行複制時，所有對複制中的表的更新必須在主伺服器上進行。否則，你必須要小心，以避免使用者對主伺服器上的表進行的更新與對從伺服器上的表所進行的更新之間的沖突。

1.1 mysql支援的複制類型：

　 （1）基于語句的複制：在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL預設采用基于語句的複制，效率比較高。一旦發現沒法精确複制時，會自動選着基于行的複制。    

（2）基于行的複制：把改變的内容複制過去，而不是把指令在從伺服器上執行一遍. 從mysql5.0開始支援

（3）混合類型的複制: 預設采用基于語句的複制，一旦發現基于語句的無法精确的複制時，就會采用基于行的複制。

1.2 複制解決的問題

   MySQL複制技術有以下一些特點：

   (1) 資料分布 (Data distribution )

   (2) 負載平衡(load balancing)

   (3) 備份(Backups)

   (4) 高可用性和容錯行 High availability and failover

1.3 複制如何工作

  整體上來說，複制有3個步驟：  

   (1) master将改變記錄到二進制日志(binary log)中（這些記錄叫做二進制日志事件，binary log events）；

   (2) slave将master的binary log events拷貝到它的中繼日志(relay log)；

   (3) slave重做中繼日志中的事件，将改變反映它自己的資料。

複制配置

 有兩台MySQL資料庫伺服器Master和slave，Master為主伺服器，slave為從伺服器，初始狀态時，Master和slave中的資料資訊相同，當Master中的資料發生變化時，slave也跟着發生相應的變化，使得master和slave的資料資訊同步，達到備份的目的。

要點：

負責在主、從伺服器傳輸各種修改動作的媒介是主伺服器的二進制變更日志，這個日志記載着需要傳輸給從伺服器的各種修改動作。是以，主伺服器必須激活二進制日志功能。從伺服器必須具備足以讓它連接配接主伺服器并請求主伺服器把二進制變更日志傳輸給它的權限。

一、安裝MySQL

 說明：在兩台MySQL伺服器192.168.21.169和192.168.21.168上分别進行如下操作，安裝MySQL 5.5.22

二、配置MySQL主伺服器（192.168.21.169）

1 2 3 4 5 6 7

mysql -u root -p #進入MySQL控制台 create database AMT_DB; #建立資料庫AMT_DB insert into mysql. user (Host, User , Password )  values ( 'localhost' , 'archermind' , password ( '123456' )); #建立使用者archermind,建立MySQL主從資料庫同步使用者replication密碼123456 flush  privileges ; #重新整理系統授權表 #授權使用者replication隻能從192.168.21.168這個IP通路主伺服器192.168.21.169上面的資料庫，并且隻具有資料庫備份的權限 grant replication slave  on *.*  to 'replication' @ '192.168.21.168' identified  by '123456' with grant option ;

三、把MySQL主伺服器192.168.21.169中的資料庫AMT_DB導入到MySQL從伺服器192.168.21.168中

 1、導出資料庫AMT_DB

1 2	mysqldump -u root -p AMT_DB > /home/replication.sql #在MySQL主伺服器進行操作，導出資料庫AMT_DB到/home/replication.sql

備注：在導出之前可以先進入MySQL控制台執行下面指令

1 2 3

flush tables  with read lock; #資料庫隻讀鎖定指令，防止導出資料庫的時候有資料寫入 unlock tables; #解除鎖定

2、導入資料庫到MySQL從伺服器

1 2 3 4 5

mysql -u root -p #進入從伺服器MySQL控制台 create database AMT_DB; #建立資料庫 use AMT_DB #進入資料庫 source /home/replication.sql #導入備份檔案到資料庫 mysql -u replication -h 192.168.21.169 -p #測試在從伺服器上登入到主伺服器

四、配置MySQL主伺服器的my.cnf檔案

1 2 3 4 5 6

#vim /etc/my.cnf #編輯配置檔案，在[mysqld]部分添加下面内容 server- id =1  #設定伺服器id，為1表示主伺服器，注意：如果原來的配置檔案中已經有這一行，就不用再添加了。 log_bin=mysql-bin  #啟動MySQ二進制日志系統，注意：如果原來的配置檔案中已經有這一行，就不用再添加了。 binlog- do -db=AMT_DB  #需要同步的資料庫名，如果有多個資料庫，可重複此參數，每個資料庫一行 binlog-ignore-db=mysql  #不同步mysql系統資料庫 #service mysqld restart #重新開機MySQL

1 2 3 4 5 6 7 8

mysql -u root -p #進入mysql控制台 show master status; 檢視主伺服器，出現以下類似資訊 + ------------------+----------+--------------+------------------+ |File |Position | Binlog_Do_DB | Binlog_Ignore_DB | + ------------------+----------+--------------+------------------+ | mysql-bin.000001 | 7131 | AMT_DB |mysql | + ------------------+----------+--------------+------------------+ 1 row  in set (0.00 sec)

注意：這裡記住File的值：mysql-bin.000001和Position的值：7131，後面會用到。

五、配置MySQL從伺服器的my.cnf檔案

1 2 3 4 5 6 7

#vim /etc/my.cnf #編輯配置檔案，在[mysqld]部分添加下面内容 server- id =2  #配置檔案中已經有一行server-id=1，修改其值為2，表示為從資料庫 log-bin=mysql-bin  #啟動MySQ二進制日志系統，注意：如果原來的配置檔案中已經有這一行，就不用再添加了。 replicate- do -db=AMT_DB  #需要同步的資料庫名，如果有多個資料庫，可重複此參數，每個資料庫一行 replicate-ignore-db=mysql  #不同步mysql系統資料庫 :wq!  #儲存退出 #service mysqld restart #重新開機MySQL

注意：MySQL 5.1.7版本之後，已經不支援把master配置屬性寫入my.cnf配置檔案中了，隻需要把同步的資料庫和要忽略的資料庫寫入即可。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql -u root -p #進入MySQL控制台 slave stop; #停止slave同步程序 change master  to master_host= '192.168.21.169' ,master_user= 'replication' ,master_password= '123456' ,master_log_file= 'mysql-bin.000001' ,master_log_pos=7131; #執行同步語句 slave start; #開啟slave同步程序 SHOW SLAVE STATUS\G #檢視slave同步資訊，出現以下内容 *************************** 1. row *************************** Slave_IO_State:Waiting  for master  to send event Master_Host:192.168.21.169 Master_User:replication Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000001 Read_Master_Log_Pos:7131 Relay_Log_File:MySQLSlave-relay-bin.000001 Relay_Log_Pos:253 Relay_Master_Log_File:mysql-bin.000001 Slave_IO_Running:Yes Slave_SQL_Running:Yes Replicate_Do_DB:AMT_DB Replicate_Ignore_DB:mysql Replicate_Do_Table: Replicate_Ignore_Table: 1 row  in set (0.00 sec)

注意檢視：

   Slave_IO_Running: Yes

   Slave_SQL_Running: Yes

以上這兩個參數的值為Yes，即說明配置成功！

六、測試MySQL主從伺服器雙機熱備是否成功

1、進入MySQL主伺服器

1 2 3

mysql -u root-p #進入主伺服器MySQL控制台 use AMT_DB #進入資料庫 CREATETABLE test ( id  int not null primary key , name char (20)); #建立test

2、進入MySQL從伺服器

1 2 3

mysql -u root-p #進入MySQL控制台 use AMT_DB #進入資料庫 show tables; #檢視AMT_DB表結構，會看到有一個建立的表test，表示資料庫同步成功

至此，MySQL資料庫配置主從伺服器實作雙機熱備

本文轉自 chengxuyonghu 51CTO部落格，原文連結：http://blog.51cto.com/6226001001/1675106，如需轉載請自行聯系原作者