mysql主從配置教程及容錯方案詳解

數據庫是我們網站中一個很重要的東西，所以數據庫一般都是實時或幾個小時備份一次的，下面我來給大家介紹mysql 5.5的主從配置來備份數據庫的方法。

主從配置我們需要有兩台mysql服務器,我們先要清楚兩點

1、mysql配置文件my.cnf的位置

2、如何啟動、停止mysql，找好啟動文件

假設有兩台機器，已經安裝好了mysql（盡量同版本，且兩台機器同一網絡，可以ping通）

有朋友說：“從服務器，不能低於主服務器的版本”，不過我是低於的，沒有出現問題。

主機A: 192.168.1.100

從機B:192.168.1.101

可以有多台從機

環境windows 2003系統

主服配置文件

server-id = 1
log-bin = mysql-bin
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-do-db = master_slave
//重啟主服
service mysqld restart
//查看主服狀態，並記下bin log pos，主服的所有操作均記錄在Binlog中
show master status;
從服配置文件
server_id = 2
log_bin = mysql-bin
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-do-db = master_slave
//重啟從服
service mysqld restart
//設定主服並啟動同步
mysql> change master to master_host='192.168.0.1', master_user='root', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106;
mysql> start slave;
mysql> show slave status;

環境：
主服務器地址：192.168.239.2，windows XP
從服務器地址：192.168.239.129，windows XP

主從服務器的版本都是5.0.19（主從服務器的版本可以不一樣，但是從服務器必須高於主服務器）

演習目標，讓主服務器的test數據庫在從服務器上生成一個鏡像

1 主從服務器都已經正常安裝MySQL，關閉主從服務器的MySQL服務
2 拷貝主服務器的test數據庫的數據目錄到從服務器的相應目錄
3 修改主服務器的MySQL的配置，增加以下配置：

server-id=1
log-bin=D:/APMXE5/mysql50/logs/master
binlog-do-db=test
#只鏡像test這個數據庫

4 啟動主服務器的MySQL，執行show master status;
應該能看到類似這樣的兩個值：

file：master.000001
position：128
記下來

5 在主服務器的MySQL創建一個新用戶replicuser（你可以隨便起，只要和下面的配置對應的上），從服務器就通過這個用戶登錄主服務器讀取二進制日志

6 修改從服務器的MySQL配置，增加以下配置：

server-id=2
master-host=192.168.239.2
master-user=replicuser
master-password=123456

7 重新啟動從服務器的MySQL，登錄後執行以下命令：

change master to master_host='192.168.239.2';
change master to master_user='replicuser';
change master to master_password='123456';
change master to master_log_file='master.000001';
change master to master_log_pos=128;

start slave

8 如果正常的話，就已經配置完畢，下面測試：然後在主服務器上新增一條記錄，到從服務器上能查到；從服務器先停掉，主服務器增加記錄，從服務器啟動後也能查到新增的記錄。

更新：dump數據的時候可以使用這個命令

/usr/bin/mysqldump xxx --master-data=1 --default-character=utf8 -uroot -p > xxx.sql

加紅的這個參數做兩件事：1 把logfile、logpos寫入到dump出來的SQL中；2 dump前讀鎖定全部表（可讀不可寫），dump後解鎖全部表，就是

其它話題，MySQL主從服務器的一些技巧

問題：主從服務器表類型的選擇

一般的共識是主服務器使用innodb，從服務器使用myisam，以便各盡其能。

問題：主從服務器字段類型的選擇

字段類型對於分頁等操作有很大影響。主服務器一般是innodb，因為不涉及查詢，所以可以使用varchar等來存儲字符串來節省空間，從服務器一般是 myisam，因為涉及查詢，所以必須在char和varchar之間仔細權衡，沒有varchar, text, blob字段的表是靜態表，反之是動態表，靜態表的檢索效率要比動態表好若干倍，一般來說，所有涉及大結果集的查詢都應該盡可能保證在靜態表上完成，這裡 說一個例子：比如說常見的articles表有title(varchar), body(text)等字段，在做文章列表的時候，因為不是靜態表，所以查詢不會很快，下面開始重構解決方案：把原來的articles表拆分成 subjects表和contents表，title字段設置為一個足夠的char類型放在subjects表裡，body字段還保持是text類型放到 contents表裡，subjects和contents表之間的關系是一對多，這樣，順帶著也方便的實現了多頁文章的功能，而且更重要的是在查詢文章 列表的時候，操作都是在subjects靜態表裡完成，效率肯定會比前一種方案提升很多。

問題：主從服務器NOW()函數造成數據不一致

假設在主服務器上執行一條INSERT …. VALUES ( …, NOW())，那麼在從服務器上也會同樣執行一條的SQL語句，但是一來主從服務器各自的時間設置可能就不一致，二來主從服務器間的SQL同步也可能存在 時間上的的延遲，這樣，NOW()在兩台服務器上的結果就可能不一致。解決方法是顯而易見的，就是不要使用NOW()，時間的計算在應用程序裡完成。這裡 介紹一個額外的小技巧：在PHP裡如果想獲得當前時間的時間戳，不要用time()，而應該使用$_SERVER[‘REQUEST_TIME’] （PHP版本大於5.1有效），這樣少做了一次系統調用，更有效率。

問題：主從服務器讀寫分離時讀操作失敗

先重現一下問題：比如說添加一條新數據，添加成功後根據last_insert_id跳轉到新添加數據的浏覽頁面。在此過程中添加新數據的操作是在主服務 器上完成的，浏覽新數據的操作實在從服務器上完成的，不過由於主從服務器間SQL同步存在延遲，所以當使用last_insert_id在從服務器上查詢 的時候，從服務器很可能還沒有還沒來得及同步到此記錄，所以讀操作失敗。解決思路也不復雜，在代碼裡加入一個緩存層（可以使用memcached），新添 加的數據都順手放到緩存層裡一份，新數據的讀操作也先查詢緩存層，這樣就不會再有讀操作失敗的問題了，當然刪除或者更新數據的時候也要順帶著處理好緩存數 據，可以使用觀察者模式來搞定。不過這樣緩存方案只限於讀取單一的記錄，對於讀取列表的記錄的情況，則是無效的。

問題：主從服務器索引是否有必要保持一致

一般都是利用主從服務器完成讀寫分離，從服務器上進行讀操作，主服務器進行寫操作，這樣的話，主服務器上僅保留主鍵，外鍵，唯一索引等必要的索引即可，以 便保持數據合法性，而對於那些原本用於優化SELECT操作的索引，可以全部刪除，如此的話主服務器的寫操作效率會提升很多。