2008年2月20日 星期三

mysql 備份機制

以前我在備份資料庫時
是寫一個script檔 來定期的匯出 *.sql 到另一顆硬碟
但是那是因為在家裡 所以比較不會注意到復原所需的時間

如果在企業裡復原時間的長短是影響企業的未來
所以我開始尋找如何在主機掛掉時可以用最短的時間內復原
在 google 大神的幫助下 找到了一篇 mysql master/slave 的備份機制

來源:http://blog.pixnet.net/shian0745/post/13034955
希望大家能到原作者網站

預設環境
Master
= 192.168.1.102
Slave
= 192.168.1.103
要進行資料同步的資料庫
= shian
Mysql 安裝位置
= /usr/local/mysql
注意:
我所安裝的Mysql是用Tarball方式安裝,所以下面的Mysql路徑會有所不一樣 , 請依照你的主機路徑做變更

安裝流程
1. 在Master上開一個帳號給Slave
2. 關閉 Master 的 MySQL
3. 備份要同步化的資料庫
4. 編輯 /etc/my.cnf 檔
5. 啟動 Master 的MySQL
6. 查看Master 是否有設定正確
7. 將備份的資料庫檔 scp 到 Slave
8. 關閉 Slave 的 MySQL
9. 編輯 /etc/my.cnf 檔
10. 解開備份資料庫檔案
11. 啟動 Slave 的 MySQL
12. 測試同步化是否成功


開始安裝

一. 設定Master:
1. 開放一個帳號給Slave連接使用
# /usr/local/mysql/bin/mysql -u root -p
# GRANT REPLICATION SLAVE on *.* to cslave@192.168.1.103 identified by 'cslave0504';
註:
GRANT REPLICATION SLAVE 為MySQL 4.0.2 以後的版本,如果是之前的版本則使用 GRANT FILE
cslave = 帳號
192.168.1.103 = Slave ip
cslave0504 = 密碼
# quit

2. 將MySQL關掉
# /usr/locale/mysql/share/mysql/mysql.server stop

3. 備份要同步化的資料庫
# cd /usr/locale/mysql/var/
# tar -zcpf shian.tar.gz shian/
註:
tar 指令加了參數 p, 將檔案權限一併記錄起來, 解壓縮的時候才不用再變更檔案屬性

4. 編輯 /etc/my.cnf
註:
如果在/etc下沒有my.cnf檔,可以從/usr/local/mysql/share/mysql下複製 *.cnf 到 /etc/my.cnf
# cp /usr/local/mysql/share/mysql/my-small.cnf /etc/my.cnf
# vi /etc/my.cnf
[mysqld] 區段加入下面這些參數:
server-id = 1
log-bin = /usr/local/mysql/var/vmcentos-bin
binlog-do-db = shian

註:
server-id = 1 <= x其中x為主機id=1,2,3 1為最大所以在master上設server-id為1 log-bin = /usr/local/mysql/var/vmcentos-bin <= 記錄 Master 所有更動 LOG 記錄下來,讓 Slave 過來同步, 上面所寫的位置為我MySQL的位置 ,vmcentos:主機名稱 binlog-do-db = shian <= 對 shian 這資料庫做同步化的記錄 ,如需多個資料庫則必須再新增一行 binlog-do-db = XXX

5. 啟動Mysql

# /usr/local/mysql/share/mysql/mysql.server start

6. 查看 Master 是否有同步化 shian 資料庫
# /usr/local/mysql/bin/mysql -u root -p
# SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| vmcentos-bin.000001 | 596 | shian | |
+---------------------+----------+--------------+------------------+

如果在 Binlog_Do_DB 有出現所指定要同步化的資料庫如shian , 則表示 Master 正在同步化 shian 資料庫

7. 將備份的資料庫 shian 丟給其它的 Slave server 上
# cd /usr/local/mysql/var
# scp shian.tar.gz shian@192.168.1.103:/tmp

二. 設定Slave:
1. 將MySQL關掉
# /usr/locale/mysql/share/mysql/mysql.server stop

2. 編輯 /etc/my.cnf
如果在/etc下沒有my.cnf檔,可以從/usr/local/mysql/share/mysql下複製 *.cnf 到 /etc/my.cnf
# cp /usr/local/mysql/share/mysql/my-small.cnf /etc/my.cnf
# vi /etc/my.cnf
[mysqld] 區段加入下面這些參數:
server-id=3
log-bin=suse-bin
relay-log=suse-relay-bin
replicate-do-db=shian
replicate-same-server-id
master-host=192.168.1.102
master-user=cslave
master-password=cslave0504
master-port=3306
master-connect-retry=60

註:
server-id=3 <= 為主機id=3 log-bin=suse-bin <= Slave 二進位記錄檔 relay-log=suse-relay-bin <= Slave 二進位記錄檔 replicate-do-db=shian <= 指定要同步化的資料庫, 如需多個資料庫則多加 replicate-do-db=xxx 即可 replicate-same-server-id master-host=192.168.1.102 <= Master ip master-user=cslave <= Master 所給的帳號 master-password=cslave0504 <= Master 所給的密碼 master-port=3306 <= Master 的 MySQL port master-connect-retry=60 <= 錯誤發生重試的間隔時間(60秒)

3. 解開 Master 備份好的資料庫檔案
# cd /usr/local/mysql/var
# tar -zxvf /tmp/shian.tar.gz
註: 如果解出來的檔案、目錄權限不對的話, 可以用 chown、chmod 來變更

4. 啟動 MySQL
# /usr/local/mysql/share/mysql/mysql.server star

三. 測試是否同步化成功
進入Mysql後下 SHOW SLAVE STATUS\G; 這指令 ,
查看 Slave_IO_Running: Yes , Slave_SQL_Running: Yes, 這二個選項都為Yes表示同步化成功,
然後在 Master 主機上 新增一個 table 後並新增一筆資料, 然後在 Slave 如也有新增同樣的 table 並 也有相同的資料,則表示同步化成功,
如果 Slave 主機出問題或關機 ,並不會影響 Master 運作, 等 Slave 恢復正常後會自己做同步化Master的動作

四. 錯誤訊息
1. No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=suse-bin' to avoid this problem.
A: 在 /etc/my.cnf 的[mysqld]裡加上這參數 log-bin=suse-bin, (suse為我的主機名,所以每一台會不一樣)

2. Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=suse-relay-bin' to avoid this problem.
A: 在 /etc/my.cnf 的[mysqld]裡加上這參數 relay-log=suse-relay-bin (suse為我的主機名,所以每一台會不一樣)

3. [ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
A: 在 /etc/my.cnf 的[mysqld]裡加上這參數 replicate-same-server-id

4. 101110 22:41:54 [ERROR] Slave I/O thread: error connecting to master 'cslave@192.168.1.99:3306': Error: 'Access denied for user 'cslave'@'192.168.1.118' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400
A: 請登入slave的mysql裡,直接打入以下指令

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1',MASTER_USER='slave',MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;


參數說明可以參考這篇 http://twpug.net/docs/mysql-5.1/replication.html

以後就不用怕主機掛點時需要恢復資料庫的時間太長

沒有留言: