MySql資料庫主從同步

標籤：

由於需要配置mysql的主從同步配置，現將配置過程記錄下，以備以後不時之需

  

1.1. 同步介紹

 

Mysql的 主從同步 是一個非同步複製過程，從一個 Master複製到另一個 Slave上。在 Master 與 Slave 之間的實現整個複製過程主要由三個線程來完成，其中兩個線程(Sql線程和IO線程)在 Slave 端，另外一個線程(IO線程)在 Master 端。　

　  要實現 MySQL 的 主從同步 ，首先必須開啟 Master 端的BinaryLog(mysql-bin)功能，否則無法實現。因為整個複製過程實際上就是Slave從Master端擷取該日誌然後再在自己身上完全順序的執行日誌中所記錄的各種操作。開啟 MySQL 的 Binary Log 可以通過在啟動 MySQL Server 的過程中使用 “—log-bin” 參數選項，或者在 my.cnf 設定檔中的 mysqld 參數組([mysqld]標識後的參數部分)增加 “log-bin” 參數項。

1.2. Mysql主從伺服器配置

主伺服器IP:192.168.1.60

從服務IP:192.168.1.61

Linux:centOS

Mysql版本：5.1.58

以下配置皆在此服務上進行配置

一、主要資料庫操作

查看主程式庫伺服器的my.cnf設定檔，根據mysql安裝包、版本不同，相關的路徑和設定檔路徑會大致不一樣，不過大多情況下是一樣的。該檔案在 etc/目錄下。Mysql服務也提供了對不同伺服器配置使用不同的設定檔，要使用它們的時候，只需用它們任一一個替換上面的my.cnf檔案即可。註：替換檔案名稱必須為my.cnf。

cnf設定檔一般會有四個，可以根據命令 # ll /usr/share/mysql/*.cnf 查看；

my-small.cnf    記憶體少於或等於64M，只提供很少的的資料庫服務；

my-medium.cnf   記憶體在32M--64M之間而且和其他服務一起使用，例如web；

my-large.cnf    記憶體有512M主要提供資料庫服務；

my-huge.cnf     記憶體有1G到2G，主要提供資料庫服務；

my-innodb-heavy-4G.cnf 記憶體有4G，主要提供較大負載資料庫服務（一般伺服器都使用這個）；

可以根據伺服器配置的不同選擇不同的cnf設定檔。

 

1、        配置my.cnf檔案

採用系統預設的etc/my.cnf設定檔進行配置，對主伺服器進行配置主要需要在設定檔中添加以下幾項（在添加的時候要確保它們不在檔案中存在，因為如選擇上面幾種不同的設定檔，配置裡的內容會不一樣,如果沒有相應的項，需要添加）。

用vi etc/my.cnf開啟檔案，對檔案進行修改，在[mysqld]下面進行添加修改：

Server-id = 1  #這是資料庫ID,此ID是唯一的，主庫預設為1，其他從庫以此ID進行遞增，ID值不能重複，否則會同步出錯；

log-bin = mysql-bin  二進位記錄檔，此項為必填項，否則不能同步資料；

binlog-do-db = testcreate  #需要同步的資料庫，如果需要同步多個資料庫；

則繼續添加此項。

binlog-do-db = testcreate1

binlog-do-db = testcreate2

binlog-ignore-db = mysql 不需要同步的資料庫；

至此主伺服器配置my.cnf配置完成，儲存退出檔案。

現在對Mysql伺服器進行重啟：#servicemysqld restart 如果重啟失敗，則證明設定檔錯誤，需重新檢查。

建議：對my.cnf修改之前最好對其進行備份，以免設定檔修改失敗不能及時重啟伺服器。

2、        配置完成後需要建立同步用的資料庫賬戶

主庫需要提供一個賬戶讓從庫對起串連並同步，用命令mysql伺服器

mysql>grantreplication slave on *.* to ‘testcreate’@’192.168.1.61’ identified by ‘123456’;

說明：’testcreate’：同步的資料庫；

      ‘192.168.1.61’：同步的資料庫地址；

        ‘123456’ ：同步的資料庫密碼，在從庫配置時需要設定。

3、        顯示主庫資訊

mysql>showmaster status;

執行上述命令顯示：

看到上述資訊則表示主庫建立成功。

4、        備份資料庫(如果從伺服器也為建立庫，則不需要此操作)

備份資料庫的方法有很多種：如果在同一區域網路可以直接複製資料庫，也可以用工具直接進行資料匯入。

複製資料庫用打包的方法：

# cd/data  轉到存放Mysql資料庫的路徑下

# tarcvfz testcreate.tar.gz db/

再從資料庫中進行複製解壓

# cd/data

# scp192.168.1.61:/data/testcreate.tar.gz

#tarxvfz testcreate.tar.gz

 

資料複製完畢；

二、 從庫配置

從伺服器的配置基本與主庫配置差不多，選擇的從程式庫伺服器為：192.168.1.61

1.    配置從庫服務my.cnf檔案

# vietc/my.cnf 對其進行如下修改：

Server-id = 2 #這裡ID改為2 因為主庫為1;

log-bin = mysql-bin  必填項，用於資料同步;

master-host = 192.168.1.60 主庫IP;

master-user = test 同步用的賬戶;

master-password = 123456 同步賬戶密碼，主庫時的設定;

master-port = 3306 同步資料庫的連接埠號碼。

2.     重啟從程式庫伺服器

#service mysqld restart

如果重啟失敗則設定檔有問題，需重新檢查配置。

3.    調整複製過來的資料庫的許可權（如果有從主庫複製過來的資料庫）

主庫複製過來的資料庫許可權都為root許可權，需要修改為mysql許可權

#chown–R mysql:mysql /var/lib/mysql

如果從庫是建立的庫就不需要進行此步驟

4.    顯示從伺服器狀態

進行mysql伺服器：

mysql>showslave status\G;  顯示

如果slave_io_running和slave_sql_running都為yes則表示從伺服器配置成功。

如果遇到關於position相關的錯誤，就需要再手動設定以下File/Postion的資訊

mysql>Changemaster to master_host = ‘192.168.1.60’

master_user= ‘test’

master_password=’123456’

master_log_file=’mysql-bin.000001’,  主庫的檔案資訊

master_log_pos=’106’;主庫的Position資訊

三、     主從伺服器測試

在主伺服器和從伺服器建立同樣的資料庫：testCreate 表為:test,欄位為ID,name

往主伺服器添加資料

此時從伺服器192.168.1.61中testcreate資料庫顯示為

表示主從伺服器同步成功。

四、     常見問題

在配置成伺服器時，從庫不能進行修改操作，即從庫只有讀的許可權，如果修改了從庫資料庫，則會造成同步失敗：如發現失敗，用showslave status\G;查看從伺服器的狀態資訊

如果出現slave_IO_Running:為NO時，則表示從資料庫連接失敗

Last_Error:會顯示出出錯日誌資訊

出現此問題主要是事物復原問題，解決方案有：

1、

mysql>slave stop; 停止從服務 

mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql>slave start; 啟動從服務

2、手動重設從伺服器

mysql>Changemaster to

master_host= ‘192.168.1.60’

master_user= ‘test’

master_password=’123456’

master_log_file=’mysql-bin.000001’; 主庫的檔案資訊

master_log_pos=’106’;主庫的Position資訊

到此Mysql主從服務配置完畢

在主伺服器中，主要是以寫資料為主，建議用InnoDB資料引擎，在讀方面用Mysql中的MyISAM引擎讀的效率很不錯。

MyIsAM引擎支援全文索引，但不支援事務，而InnoDB恰好相反！

MySql資料庫主從同步