MySQL 即時備援

其實整個原理很簡單…
master 端及 slave 端各維持一份二進制的 log 檔，並記錄各自的更新指標
slave 時時去查看 master 的 log ，有變動的話就要求 master 把 log 檔傳過來進行同步。
(說簡單其實也是搞了好久才弄懂~真是江湖一點訣)

*MySQL 版本: 5.0.32
=============================================================
Master 端操作…
Master ( main server )...
ip: xxx.xxx.xxx.mmm

設置 maser server id 及欲進行二進制 log 檔的資料庫作為同步用…
nano /etc/mysql/my.cnf
------------------------------------------------------------
[mysqld]
# bind-address = 127.0.0.1
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = db_name_to_backup
------------------------------------------------------------
*binlog_do_db 有多個資料庫就多用幾行來設置。

重新啟動 mysql…
/etc/init.d/mysql restart

進入 mysql command…
mysql -uroot -p
------------------------------------------------------------
新增一個使用者 backup 給 slave 端用，需有 REPLICATION SLAVE 權限，作為同步用帳號…
mysql>GRANT REPLICATION SLAVE ON *.* TO backup@'xxx.xxx.xxx.sss' IDENTIFIED BY 'set_password';

暫時鎖住資料庫…
mysql>FLUSH TABLES WITH READ LOCK;

這邊要 dump 出你要同步的資料庫，請參考 mysqldump 使用方法或用 phpmyadmin 匯出…
####################
匯出出你要同步的資料庫…
####################

看一下目前 log 指標檔及其指標位置(要記住紅色部份)…
mysql>show master status\G
--------------------------------------------
File: mysql-bin.000073
Position: 98
Binlog_Do_DB: db_name_to_backup
Binlog_Ignore_DB:
--------------------------------------------

解鎖資料庫…
mysql>UNLOCK TABLES;

離開 mysql command…
exit
------------------------------------------------------------

=============================================================
Slave 端操作…
Slave ( backup server )
ip:xxx.xxx.xxx.sss

先刪除原有的 master 資訊(如果有的話)…
rm /var/lib/mysql/master.info

設置 maser host 相關資料及欲進行二進制 log 檔同步的資料庫…
nano /etc/mysql/my.cnf
------------------------------------------------------------
[mysqld]
# bind-address = 127.0.0.1
server-id = 2
master-host = xxx.xxx.xxx.mmm
master-user = backup
master-password = set_password
master-port = 3306
master-connect-retry = 60
replicate-do-db = db_name_to_backup
log_bin = /var/log/mysql/mysql-bin.log
------------------------------------------------------------

重新啟動 mysql…
/etc/init.d/mysql restart

進入 mysql command…
mysql -uroot -p
------------------------------------------------------------
先停止原有的同步作業…(因為 mysql restart 會自動重啟)
mysql>slave stop;

####################
匯入你要同步的資料庫…
####################

變更同步資訊及指標位置…
mysql>CHANGE MASTER TO
MASTER_HOST='61.221.194.212',
MASTER_USER='backup',
MASTER_PASSWORD='set_password',
MASTER_LOG_FILE='mysql-bin.000073',
MASTER_LOG_POS=98;

啟動同步程序…
mysql>slave start;

查看一下同步狀態…(注意紅色部份要正確才是有在運行)
mysql>show master status\G;
----------------------------------------------------
Slave_IO_State: Waiting for master to send event
Master_Host: xxx.xxx.xxx.mmm
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000073
Read_Master_Log_Pos: 98
Relay_Log_File: dns1-relay-bin.000002
Relay_Log_Pos: 88631
Relay_Master_Log_File: mysql-bin.000073
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db_name_to_backup
Replicate_Ignore_DB:
...
...
----------------------------------------------------

*參考筆記…http://www.google.com/notebook/public/12398140405581905258/BDSVoIgoQnomkvpYj
http://www.gra2.com/article.php/setting-up-database-replication-on-mysql