MySQL数据库配置mha实现主服务自动切换

在mysql的复制，当主服务崩溃了，利用mha实现主服务自动切换，并能使其他从服务切换到新的主机。下面是部署步骤

（1）准备四台机器：主服务 192.168.12.235，备主 192.168.12.234 ，从服务 192.168.12.232 管理节点 192.168.12.227

（2）修改各台主机名 如 管理节点 192.168.12.227
 cat /etc/hosts
127.0.0.1   localhost.localdomain localhost
192.168.12.227 sh-gs-dbmg0227
192.168.12.235 sh-gs-db0235
192.168.12.234 sh-gs-db0234
192.168.12.232 sh-gs-db0232

（3）数据节点
安装mha4mysql-node-0.52.tar.gz  mha4mysql-manager-0.53.tar.gz，由于mha4mysql-node 依赖 perl-DBD-MySQL， mha4mysql-manager依赖 perl-Config-Tiny perl-Params-Validate perl-Log-Dispatch perl-Parallel-ForkManager 。所以现在这些依赖包。实验使用yum 安装。
对三台mariadb数据节点只需安装mha4mysql-node-0.52.tar.gz ，本文没有写mariadb的安装以及复制。
[root@sh-gs-db0235 ~]# rpm -ivh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
[root@sh-gs-db0235 ~]# yum -y install perl-DBD-MySQL  ncftp
[root@sh-gs-db0235 ~]# tar -zxf mha4mysql-node-0.52.tar.gz
[root@sh-gs-db0235 ~]# cd mha4mysql-node-0.52
[root@sh-gs-db0235 ~]#  perl Makefile.PL   
[root@sh-gs-db0235 ~]# make && make install 

(4)管理节点
[root@sh-gs-dbmg0227 ~]#  rpm -ivh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
[root@sh-gs-dbmg0227 ~]# yum -y install perl-DBD-MySQL  ncftp
[root@sh-gs-dbmg0227 ~]# tar -zxf mha4mysql-node-0.52.tar.gz 
[root@sh-gs-dbmg0227 ~]# cd mha4mysql-node-0.52
[root@sh-gs-dbmg0227 ~]# perl Makefile.PL   
[root@sh-gs-dbmg0227 ~]# make && make install 
[root@sh-gs-dbmg0227 ~]#  yum -y install perl-Config-Tiny perl-Params-Validate perl-Log-Dispatch perl-Parallel-ForkManager perl-Config-IniFiles
[root@sh-gs-dbmg0227 ~]# tar -zxf mha4mysql-manager-0.53.tar.gz
[root@sh-gs-dbmg0227 ~]# perl Makefile.PL   
[root@sh-gs-dbmg0227 ~]# make && make install 
[root@sh-gs-dbmg0227 ~]# mkdir /etc/masterha
[root@sh-gs-dbmg0227 /]# mkdir -p /master/app1
[root@sh-gs-dbmg0227 ~]#  cp samples/conf/* /etc/masterha/
配置管理节点
[root@sh-gs-dbmg0227 ~]# more  /etc/masterha/app1.cnf
[server default]
manager_workdir=/masterha/app1   
manager_log=/masterha/app1/manager.log
user=root
password=123456
ssh_user=root
repl_user=repl
repl_password=repl
ping_interval=1
shutdown_script=""
#master_ip_failover_script=/usr/local/mha4mysql/mha4mysql-manager-0.52/samples/scripts/master_ip_failover  (这脚本放上会出现错误)
#master_ip_online_change_script=/usr/local/mha4mysql/mha4mysql-manager-0.52/samples/scripts/master_ip_online_change (这脚本放上会出现错误)
report_script=""
[server1]
hostname=192.168.12.235
master_binlog_dir=/app/mariadb538
candidate_master=1
[server2]
hostname=192.168.12.234
master_binlog_dir=/app/mariadb538
candidate_master=1
[server3]
hostname=192.168.12.232
master_binlog_dir=/app/mariadb538
no_master=1

（5）在mysql 添加用户，复制设置
mysql 主节点
grant replication slave  on *.* to 'repl'@'192.168.12.%' identified by 'repl';
grant replication all on *.* to 'root'@'192.168.12.227' identified by '123456';
备主节点
grant replication slave on *.* to 'repl'@'192.168.12.%' identified by 'repl';
grant replication all on *.* to 'root'@'192.168.12.227' identified by '123456';
set read_Only=1
set relay_log_purge=0
从节点
 grant replication all on *.* to 'root'@'192.168.12.227' identified by '123456';
set read_Only=1
set relay_log_purge=0

（6）配置ssh
[root@sh-gs-dbmg0227 /]# ssh-keygen -t rsa 
[root@sh-gs-dbmg0227 /]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.12.235
[root@sh-gs-dbmg0227 /]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.12.234  
[root@sh-gs-dbmg0227 /]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.12.232
[root@sh-gs-db0232 tmp]# ssh-keygen -t rsa
[root@sh-gs-db0232 tmp]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.12.235
[root@sh-gs-db0232 tmp]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.12.234
[root@sh-gs-db0234 tmp]# ssh-keygen -t rsa 
[root@sh-gs-db0234 tmp]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.12.232
[root@sh-gs-db0234 tmp]#  ssh-copy-id -i .ssh/id_rsa.pub root@192.168.12.235
[root@sh-gs-db0235 tmp]# ssh-keygen -t rsa 
[root@sh-gs-db0235 tmp]ssh-copy-id -i .ssh/id_rsa.pub root@192.168.12.232
[root@sh-gs-db0235 tmp]ssh-copy-id -i .ssh/id_rsa.pub root@192.168.12.234

(7)测试ssh
 [root@sh-gs-dbmg0227 /]# masterha_check_ssh --cOnf=/etc/masterha/app1.cnf
[root@sh-gs-dbmg0227 /]# masterha_check_ssh --cOnf=/etc/masterha/app1.cnf
Mon Jan  7 18:49:01 2013 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jan  7 18:49:01 2013 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Mon Jan  7 18:49:01 2013 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Mon Jan  7 18:49:01 2013 - [info] Starting SSH connection tests..
Mon Jan  7 18:49:02 2013 - [debug]
Mon Jan  7 18:49

(8)测试复制
[root@sh-gs-dbmg0227 /]#  masterha_check_repl --cOnf=/etc/masterha/app1.cnf
Mon Jan  7 18:49:50 2013 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jan  7 18:49:50 2013 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Mon Jan  7 18:49:50 2013 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Mon Jan  7 18:49:50 2013 - [info] MHA::MasterMonitor version 0.52.
Mon Jan  7 18:49:51 2013 - [info] Dead Servers:
Mon Jan  7 18:49:51 2013 - [info] Alive Servers:
Mon Jan  7 18:49:51 2013 - [info]   192.168.12.235(192.168.12.235:3306)
Mon Jan  7 18:49:51 2013 - [info]   192.168.12.234(192.168.12.234:3306)
Mon Jan  7 18:49:51 2013 - [info]   192.168.12.232(192.168.12.232:3306)
Mon Jan  7 18:49:51 2013 - [info] Alive Slaves:
Mon Jan  7 18:49:51 2013 - [info]   192.168.12.234(192.168.12.234:3306)  Version=5.3.8-MariaDB-log (oldest major version between slaves) log-bin:enabled
Mon Jan  7 18:49:51 2013 - [info]     Replicating from 192.168.12.235(192.168.12.235:3306)
Mon Jan  7 18:49:51 2013 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jan  7 18:49:51 2013 - [info]   192.168.12.232(192.168.12.232:3306)  Version=5.3.8-MariaDB-log (oldest major version between slaves) log-bin:enabled
Mon Jan  7 18:49:51 2013 - [info]     Replicating from 192.168.12.235(192.168.12.235:3306)
Mon Jan  7 18:49:51 2013 - [info]     Not candidate for the new Master (no_master is set)
Mon Jan  7 18:49:51 2013 - [info] Current Alive Master: 192.168.12.235(192.168.12.235:3306)
Mon Jan  7 18:49:51 2013 - [info] Checking slave configurations..
Mon Jan  7 18:49:51 2013 - [warning]  read_Only=1 is not set on slave 192.168.12.234(192.168.12.234:3306).
Mon Jan  7 18:49:51 2013 - [warning]  relay_log_purge=0 is not set on slave 192.168.12.234(192.168.12.234:3306).
Mon Jan  7 18:49:51 2013 - [warning]  relay_log_purge=0 is not set on slave 192.168.12.232(192.168.12.232:3306).
Mon Jan  7 18:49:51 2013 - [info] Checking replication filtering settings..
Mon Jan  7 18:49:51 2013 - [info]  binlog_do_db= , binlog_ignore_db=
Mon Jan  7 18:49:51 2013 - [info]  Replication filtering check ok.
Mon Jan  7 18:49:51 2013 - [info] Starting SSH connection tests..
Mon Jan  7 18:49:53 2013 - [info] All SSH connection tests passed successfully.
Mon Jan  7 18:49:53 2013 - [info] Checking MHA Node version..
Mon Jan  7 18:49:54 2013 - [info]  Version check ok.
Mon Jan  7 18:49:54 2013 - [info] Checking SSH publickey authentication and checking recovery script configurations on the current master..
Mon Jan  7 18:49:54 2013 - [info]   Executing command: save_binary_logs --command=test --start_file=mysql-bin.000006 --start_pos=4 --binlog_dir=/app/mariadb538 --output_file=/var/tmp/save_binary_logs_test --manager_version=0.52
Mon Jan  7 18:49:54 2013 - [info]   Connecting to root@192.168.12.235(192.168.12.235)..
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /app/mariadb538, up to mysql-bin.000006
Mon Jan  7 18:49:55 2013 - [info] Master setting check done.
Mon Jan  7 18:49:55 2013 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Mon Jan  7 18:49:55 2013 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=root --slave_host=192.168.12.234 --slave_ip=192.168.12.234 --slave_port=3306 --workdir=/var/tmp --target_version=5.3.8-MariaDB-log --manager_version=0.52 --relay_log_info=/app/mariadb538/relay-log.info  --slave_pass=xxx
Mon Jan  7 18:49:55 2013 - [info]   Connecting to root@192.168.12.234(192.168.12.234)..
  Checking slave recovery environment settings..
    Opening /app/mariadb538/relay-log.info ... ok.
    Relay log found at /app/mariadb538, up to sh-gs-db0234-relay-bin.000004
    Temporary relay log file is /app/mariadb538/sh-gs-db0234-relay-bin.000004
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Mon Jan  7 18:49:55 2013 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=root --slave_host=192.168.12.232 --slave_ip=192.168.12.232 --slave_port=3306 --workdir=/var/tmp --target_version=5.3.8-MariaDB-log --manager_version=0.52 --relay_log_info=/app/mariadb538/relay-log.info  --slave_pass=xxx
Mon Jan  7 18:49:55 2013 - [info]   Connecting to root@192.168.12.232(192.168.12.232)..
  Checking slave recovery environment settings..
    Opening /app/mariadb538/relay-log.info ... ok.
    Relay log found at /app/mariadb538, up to sh-gs-db0232-relay-bin.000004
    Temporary relay log file is /app/mariadb538/sh-gs-db0232-relay-bin.000004
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Mon Jan  7 18:49:55 2013 - [info] Slaves settings check done.
Mon Jan  7 18:49:55 2013 - [info]
192.168.12.235 (current master)
 +--192.168.12.234
 +--192.168.12.232
Mon Jan  7 18:49:55 2013 - [info] Checking replication health on 192.168.12.234..
Mon Jan  7 18:49:55 2013 - [info]  ok.
Mon Jan  7 18:49:55 2013 - [info] Checking replication health on 192.168.12.232..
Mon Jan  7 18:49:55 2013 - [info]  ok.
Mon Jan  7 18:49:55 2013 - [warning] master_ip_failover_script is not defined.
Mon Jan  7 18:49:55 2013 - [warning] shutdown_script is not defined.
Mon Jan  7 18:49:55 2013 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.

（9）启动management
[root@sh-gs-dbmg0227 /]# nohup masterha_manager --cOnf=/etc/masterha/app1.cnf> /tmp/mha_manager.log  &1 &
[root@sh-gs-dbmg0227 /]# masterha_check_status --cOnf=/etc/masterha/app1.cnf
app1 (pid:7127) is running(0:PING_OK), master:192.168.12.235

（10）在备节点，从节点 执行定期删除中继日志
00 00 * * * /usr/local/bin/purge_relay_logs ?user=root ?password=123456 ?disable_relay_log_purge >> /masterha/purge_relay_logs.log 2>&1

（11）测试 关闭mysql主服务服务，主机宕机。主服务是否自动切换。
 
mysql-master-ha详细手册： http://code.google.com/p/mysql-master-ha/wiki/TableOfContents?tm=6