MySQL Master-Master Replication Manager(2) – 環境建置、架設

前一篇 MySQL Master-Master Replication Manager(1) - 簡介看完後, 再來就是要建置這個環境囉~(註: 目前 MMM 是 stable 1.0 版)

MySQL Master-Master Replication Manager 前置作業

MySQL Master-Master 的架構, 需要準備的資訊如下:

機器 3台以上(最少3台): 2台做 MySQL Server, 1台做 Monitor(監控機器可與 Apache 等共用即可).
IP 5個以上(2N+1): MySQL Server 有幾台, 需要的 IP 是 MySQL Server 台數 x 2, 再加上 Monitor 要一個 IP.

MMM 前置作業手上該有的資訊:

db-1 192.168.1.181
db-2 192.168.1.182
db-mon 192.168.1.183
db-r 192.168.1.184 # 這個不用設進 db-1、db-2, db-mon 會自動設給它.(但需先決定db-1, db-2 哪台要先當 reader 或 writer)
db-w 192.168.1.185 # 這個不用設進 db-1、db-2, db-mon 會自動設給它.(但需先決定db-1, db-2 哪台要先當 reader 或 writer)
MySQL Replication 要設定的帳號: replication、密碼: slave.
Monitor 存取 MySQL 需要的帳號: rep_monitor、密碼: RepMonitor.
MySQL Agent 要設定的帳號: rep_agent、密碼: RepAgent.

開始之前, 除了上述該有的資訊外, 手上應該要有 3台機器, 3台機器設定分別如下:

db-1 192.168.1.181
db-2 192.168.1.182
db-mon 192.168.1.183
若有要用到 mmm_clone, mmm_backup, mmm_restore 等功能, 需要 LVM 支援, 除此之外, 沒有 LVM 還是能正常監控/轉換等, 下述環境也是在沒有 LVM 的狀況下測試的.

MySQL Master-Master Replication Manager 環境建置、架設

下述設定參考自: Master-Master Replication Example using MMM (設定檔參考: Configuration Examples) 註: 下述環境、設定檔位置是以 Debian Lenny 為主.

建置環境步驟

建置環境步驟主要如下述:

db-1, db-2 安裝 mysql-server
db-1, db-2 互設對方為 Master, 自己是對方的 Slave
抓取 mmm 的檔案, 裝需要的 Package 後, 執行 install.pl
設定 mmm_agent.conf 後, 於 db-1, db-2 跑 mmm_agent
db-mon 安裝需要的 Package 後, 執行 install.pl
設定 mmm_mon.conf 後, 於 db-mon 跑 mmm_mon
將 db-1, db-2 設定上線 mmm_control set_online db1, mmm_control set_online db2
測試 mmm_control show 是否正常, 將 /usr/local/mmm/scripts/init.d, logrotate.d 設定到 /etc 去.
使用 rcconf 設定開啟啟動即可.

實際執行步驟 - db-, db-2 互設 Replication, db-mon 建置

db-1 192.168.1.181

apt-get install mysql-server
vim /etc/mysql/my.cnf
# bind-address = 127.0.0.1 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log
/etc/init.d/mysql restart

做完此步驟, 請跳到 db-2 也先把此步驟做完.(順便抄下 db-2 的 show master status) db-2 做完上述步驟後, 再繼續下面:

mysql -u root
mysql> GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'192.168.1.183' identified by 'RepMonitor';
mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.181' identified by 'RepAgent';
mysql> GRANT replication slave on *.* to 'replication'@'%' identified by 'slave';
mysql> change master to master_host='192.168.1.182', master_port=3306, master_user='replication', master_password='slave'; # 文件上寫的做法
mysql> change master to master_host='192.168.1.182', master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000004', master_log_pos=98; # 我喜歡保守點的做法.(抓 db-2 mysql> show master status 資料)
/etc/init.d/mysql restart
mysql -u root
mysql> slave start;
mysql> show slave status G

這樣子應該 Replication 已經設定完成, Master 是 db2, 自己是 Slave, 再下來就是架設 MMM 囉~ 架設 MMM 步驟如下:

apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
apt-get install iproute
wget http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2
tar xvf mmm-1.0.tar.bz2
cd mmm-1.0
./install.pl
cp /usr/local/mmm/etc/examples/mmm_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf
vim /usr/local/mmm/etc/mmm_agent.conf # 下述只將修改部份列出
cluster_interface eth0 # Define current server id this db1 mode master # For masters peer db2 # Cluster hosts addresses and access params host db1 ip 192.168.1.181 port 3306 user rep_agent password RepAgent host db2 ip 192.168.1.182 port 3306 user rep_agent password RepAgent
mmmd_agent # 執行 agent, 或者 /usr/local/mmm/scripts/init.d/mmm_agent start
ps aux | grep mmmd
root 16115 0.0 0.0 41936 6904 ? S 15:02 0:00 perl /usr/local/sbin/mmmd_agent
修改完成的設定檔參考可下載: mmm_agent.conf
再來就繼續下述 db-2 的設定囉~

db-2 192.168.1.182

apt-get install mysql-server
vim /etc/mysql/my.cnf
# bind-address = 127.0.0.1 server-id = 2 log_bin = /var/log/mysql/mysql-bin.log
/etc/init.d/mysql restart

做完此步驟, 再回 db-1 繼續. (順便抄下 db-1 的 show master status)

mysql -u root
mysql> GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'192.168.1.183' identified by 'RepMonitor';
mysql> GRANT replication slave on *.* to 'replication'@'%' identified by 'slave';
mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.182' identified by 'RepAgent';
mysql> change master to master_host='192.168.1.181', master_port=3306, master_user='replication', master_password='slave'; # 文件上寫的做法
mysql> change master to master_host='192.168.1.181', master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000004', master_log_pos=98; # 我喜歡保守點的做法.(抓 db-1 mysql> show master status 資料)
/etc/init.d/mysql restart
mysql -u root
mysql> slave start;
mysql> show slave status G

這樣子應該 Replication 已經設定完成, Master 是 db1, 自己是 Slave, 再下來就是架設 MMM 囉~(下述步驟與上面一致, 只有設定檔有差異而已) 架設 MMM 步驟如下:

apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
apt-get install iproute
wget http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2
tar xvf mmm-1.0.tar.bz2
cd mmm-1.0
./install.pl
cp /usr/local/mmm/etc/examples/mmm_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf
vim /usr/local/mmm/etc/mmm_agent.conf
cluster_interface eth0 # Define current server id this db2 mode master # For masters peer db1 # Cluster hosts addresses and access params host db1 ip 192.168.1.181 port 3306 user rep_agent password RepAgent host db2 ip 192.168.1.182 port 3306 user rep_agent password RepAgent
mmmd_agent # 執行 agent, 或者 /usr/local/mmm/scripts/init.d/mmm_agent start
ps aux | grep mmmd
root 8837 0.0 0.0 41936 6904 ? S 15:02 0:00 perl /usr/local/sbin/mmmd_agent
修改完成的設定檔參考可下載: mmm_agent.conf

db-mon 192.168.1.183

apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
apt-get install iproute
apt-get install subversion # 目前 1.0 stable 的 mmm_mon 程式有 bug, 所以需要直接 checkout trunk 的來用
svn checkout http://mysql-master-master.googlecode.com/svn/trunk/ mysql-master-master-read-only
cd mysql-master-master-read-only/
./install.pl
cp /usr/local/mmm/etc/examples/mmm_mon.conf.example /usr/local/mmm/etc/mmm_mon.conf
vim /usr/local/mmm/etc/mmm_mon.conf
email root@localhost # 修改成有狀況要通知的 Email. host db1 ip 192.168.1.181 port 3306 user rep_monitor password RepMonitor mode master peer db2 host db2 ip 192.168.1.182 port 3306 user rep_monitor password RepMonitor mode master peer db1 # Mysql Reader role role reader mode balanced servers db1, db2 ip 192.168.1.185, 192.168.1.184 # Mysql Writer role role writer mode exclusive servers db1, db2 ip 192.168.1.185
/usr/local/mmm/scripts/init.d/mmm_mon start
mmm_control show
mmm_control set_online db1 # 讓 db1 上線
mmm_control set_online db2 # 讓 db2 上線
mmm_control show # 看到下述就成功了~
Config file: mmm_mon.conf Daemon is running! Servers status: db1(192.168.1.181): master/ONLINE. Roles: reader(192.168.1.185;), writer(192.168.1.185;) db2(192.168.1.182): master/ONLINE. Roles: reader(192.168.1.184;)
修改完成的設定檔參考可下載: mmm_mon.conf

設定 log rotate

下面這些步驟分別在 db1, db2, mon 設定即可.

cp /usr/local/mmm/scripts/logrotate.d/mmm /etc/logrotate.d/
vim /etc/logrotate.d/mmm
/opt/mmm/var/*.log { 修改成 /usr/local/mmm/var/*.log olddir /opt/mmm/var/old 修改成 olddir /usr/local/mmm/var/old

設定開機自動啟動

db1, db2 設定開機自動啟動

cp /usr/local/mmm/scripts/init.d/mmm_agent /etc/init.d/
apt-get install rcconf
rcconf
選取 mmm_agent 即可

mon 設定開機自動啟動

cp /usr/local/mmm/scripts/init.d/mmm_mon /etc/init.d/
apt-get install rcconf
rcconf
選取 mmm_mon 即可

再下來就只要會 mmm_control show, mmm_control set_online, mmm_control set_offline 即可.

當機/重開機的 SOP

若有重開機等狀況, 檢查步驟:

mysql -u root # 看 MySQL 是否有啟動, 若沒啟動 /etc/init.d/mysql start
ps aux | grep mmm # 看 mmm_agent 或 mmm_mon 是否有啟動, 若沒啟動 /etc/init.d/mmm_[agent|mon] start
再來在 mon 的機器下: mmm_control show, 若沒上線, 發現已經在 AWAITING_RECOVERY 狀態, 就可以 set_online 讓他上線囉~

測試

db1, db2 mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO 'mmm'@'%' IDENTIFIED BY 'mmm_password';
db1, db2 mysql> FLUSH PRIVILEGES;
寫程式去對 192.168.1.184, 192.168.1.185 做寫入/讀取的動作, 並試著重開等看看反應~
註: 目前測試狀況, 機器死掉時, 在 2秒內就會自動切換過去. :)

Source from Tsung's blog