mysql HA 方案（2）：MHA

以下摘錄自（http://code.google.com/p/mysql-master-ha/wiki/Overview）

MHA performs automating master failover and slave promotion with minimal downtime, usually within 10-30 seconds. MHA prevents replication consistency problems and saves on expenses of having to acquire additional servers. All this with zero performance degradation, no complexity (easy-to-install) and requiring no change to existing deployments.

MHA also provides scheduled online master switching, safely changing the currently running master to a new master, within mere seconds (0.5-2 seconds) of downtime (blocking writes only).

MHA provides the following functionality, and can be useful in many deployments in which high availability, data integrity and near non-stop master maintenance are required.

。。。。。。。。。。

 

應用要考慮的問題：
應用架構要考慮兩個問題（在一個特定的叢集下）：
1 自動識別master和slave的問題（master機器可能會切換）
2 負載均衡的問題（我們可以定義大概的讀寫比例，每臺機器可承擔的負載比例，當有機器離開叢集時，需要考慮這個）

比如說下面的場景：
From:
db-181 (current master)
+–db-183
+–db-184

To:
db-183 (new master)
+–db-184

這個方案需要機器至少4臺（1 master+ 3 slaves（其中一個專門作為備份））。

以下是我的測試過程:

準備，機器安排，db-182為管理機：

db-181 (current master)

 +--db-183

 +--db-184

 

準備工作，配置ssh:

mkdir ~/.ssh

 chmod 700 ~/.ssh

 ssh-keygen -t rsa

 ssh-keygen -t dsa

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

 

ssh db-181 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

ssh db-181 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

ssh db-183 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

ssh db-183 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

ssh db-184 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

ssh db-184 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

 

scp ~/.ssh/authorized_keys db-181:~/.ssh/authorized_keys

scp ~/.ssh/authorized_keys db-183:~/.ssh/authorized_keys

scp ~/.ssh/authorized_keys db-184:~/.ssh/authorized_keys

 

1 所有機器安裝DBD-mysql-4.020

 

mkdir /tmp/mysql-static  

cp /usr/lib64/mysql/*.a /tmp/mysql-static  

perl Makefile.PL --libs="-L/tmp/mysql-static -lmysqlclient"  

make  

make test  

make install  

 

2  所有機器Installing MHA Node

 

You can also install MHA Node from source. 

## Install DBD::mysql if not installed  

 

tar -zxf mha4mysql-node-0.53.tar.gz

cd mha4mysql-node-0.53

perl Makefile.PL

make

make install

 

3 管理機Installing MHA Manager

## Install DBD::mysql if not installed  

rpm -ivh perl-Config-Tiny-2.12-1.el5.rf.noarch.rpm

rpm -ivh perl-Params-Validate-0.95-1.el5.rf.x86_64.rpm

rpm -ivh  perl-Log-Dispatch-2.26-1.el5.rf.noarch.rpm

rpm -ivh perl-Parallel-ForkManager-0.7.5-2.2.el5.rf.noarch.rpm

 

tar -zxf mha4mysql-manager-0.53.tar.gz

cd mha4mysql-manager-0.53

perl Makefile.PL

make

make install

 

 

4 配置檔案

[server default]

manager_workdir=/masterha/app1

manager_log=/masterha/app1/manager.log

user=root

password=123zzz

ssh_user=root

repl_user=rep

repl_password=123zzz

ping_interval=1

 

[server1]

hostname=db-181

master_binlog_dir=/data/mysql

candidate_master=1

[server2]

hostname=db-183

master_binlog_dir=/data/mysql

candidate_master=1

[server3]

hostname=db-184

master_binlog_dir=/data/mysql

 

5 root 的許可權及MASTER_HOST（vip待試驗）

 

CHANGE MASTER TO

 MASTER_HOST='192.168.99.181',

 MASTER_USER='rep',

MASTER_PASSWORD='xxx',

 MASTER_LOG_FILE='mysql-bin.000004',

 MASTER_LOG_POS=107;

 

db-181
mysql> grant all privileges on *.* to 'root'@'db-182' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'db-181' identified by 'xxx'; 
Query OK, 0 rows affected (0.00 sec)

mysql>  grant all privileges on *.* to 'root'@'192.168.99.182' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)

db-183
mysql>  grant all privileges on *.* to 'root'@'192.168.99.182' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'db-182' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'db-183' identified by 'xxx'; 
Query OK, 0 rows affected (0.01 sec)

db-184
mysql>  grant all privileges on *.* to 'root'@'192.168.99.182' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'db-182' identified by 'xxx';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'db-184' identified by 'xxx'; 
Query OK, 0 rows affected (0.00 sec)