mysql主庫從庫在同一台伺服器_mysql資料庫主從庫配置之二

由于需要配置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同步資料庫的端口号。

注：mysql 從5.1.7版本之後my.cnf配置不支援這些參數，配了了啟動失敗。哥找了好久才發現這個問題，踩過的坑記錄下來。換成：

mysql>change master to master_host='masterIP', master_user='test', master_password='123456';

mysql>start slave;

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恰好相反！