MHA分管理节点和数据库节点,数据库节点由mysql主从或者主主从构成,当主库挂掉后,管理节点会自动将从节点提升为主节点;管理节点的角色类似于oracle数据库中的fast start failover中的observer,但mha上层可以通过keepalive部署VIP,程序连接数据库使用VIP,从而实现后台数据库的故障切换透明化
MHA节点包含三个脚本,依赖perl模块;
save_binary_logs:保存和复制当掉的主服务器二进制日志;
apply_diff_relay_logs:识别差异的relay log事件,并应用于其他salve服务器;
purge_relay_logs:清除relay log文件;
需要在所有mysql服务器上安装MHA节点,MHA管理服务器也需要安装。MHA管理节点模块内部依赖MHA节点模块;
MHA管理节点通过ssh连接管理mysql服务器和执行MHA节点脚本。MHA节点依赖perl的DBD::mysql模块;
本文环境介绍,操作系统均为rhel5.4
主库:192.168.123.13/dg53.yang.com
备库:192.168.123.14/dg54.yang.com
管理节点:192.168.123.15/dg55.yang.com
在开始之前,请先配置好服务器间的时间同步和名称解析
一:在数据库节点安装mha node
- [root@dg53 ~]# rpm -ivh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
- Retrieving http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
- warning: /var/tmp/rpm-xfer.yqwfYT: Header V3 DSA signature: NOKEY, key ID 217521f6
- Preparing... ########################################### [100%]
- 1:epel-release ########################################### [100%]
-
- [root@dg53 ~]# ls /etc/yum.repos.d/
- base.repo epel.repo epel-testing.repo rhel-debuginfo.repo
- [root@dg53 ~]# yum -y install perl-DBD-MySQL ncftp
- [root@dg53 ~]# wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.52.tar.gz -P /usr/local/src/tarbag/
- [root@dg53 ~]# cd /usr/local/src/tarbag/
- [root@dg53 tarbag]# tar -zxvpf mha4mysql-node-0.52.tar.gz -C ../software/
- [root@dg53 tarbag]# cd ../software/mha4mysql-node-0.52/
- [root@dg53 mha4mysql-node-0.52]# perl Makefile.PL
- [root@dg53 mha4mysql-node-0.52]# make && make install
二:管理节点
1:按照步骤一安装mha node
2:安装mha manager
- [root@dg55 ~]# yum -y install perl-Config-Tiny perl-Params-Validate perl-Log-Dispatch perl-Parallel-ForkManager
- [root@dg55 ~]# wget http://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.52.tar.gz -P /usr/local/src/tarbag/
- [root@dg55 ~]# cd /usr/local/src/tarbag/
- [root@dg55 tarbag]# tar -zxvpf mha4mysql-manager-0.52.tar.gz -C ../software/
- [root@dg55 tarbag]# cd ../software/mha4mysql-manager-0.52/
- [root@dg55 mha4mysql-manager-0.52]# perl Makefile.PL
- [root@dg55 mha4mysql-manager-0.52]# make && make install
3:编辑配置文件
- [root@dg55 mha4mysql-manager-0.52]# mkdir /etc/masterha
- [root@dg55 mha4mysql-manager-0.52]# mkdir -p /masterha/app1
- [root@dg55 mha4mysql-manager-0.52]# cp samples/conf/* /etc/masterha/
- [root@dg55 mha4mysql-manager-0.52]# cat /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=r_test
- repl_password=123456
- ping_interval=1
- shutdown_script=""
- #master_ip_failover_script="/usr/local/bin/master_ip_failover"
- master_ip_online_change_script=""
- report_script=""
- [server1]
- hostname=192.168.123.13
- master_binlog_dir="/mydata"
- candidate_master=1
- [server2]
- hostname=192.168.123.14
- master_binlog_dir="/mydata"
- candidate_master=1
4:配置manager节点和node节点以及node节点间的ssh公钥信任
- [root@dg55 ~]# ssh-keygen -t rsa
- [root@dg55 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.123.13
- [root@dg55 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.123.14
-
- [root@dg55 ~]# ssh 192.168.123.13 "ifconfig |grep 'inet addr' |head -1"
- inet addr:192.168.123.13 Bcast:192.168.123.255 Mask:255.255.255.0
- [root@dg55 ~]# ssh 192.168.123.14 "ifconfig |grep 'inet addr' |head -1"
- inet addr:192.168.123.14 Bcast:192.168.123.255 Mask:255.255.255.0
5:测试ssh连接
- [root@dg55 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
- Wed Jun 6 11:11:25 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
- Wed Jun 6 11:11:25 2012 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
- Wed Jun 6 11:11:25 2012 - [info] Reading server configurations from /etc/masterha/app1.cnf..
- Wed Jun 6 11:11:25 2012 - [info] Starting SSH connection tests..
- Wed Jun 6 11:11:25 2012 - [debug]
- Wed Jun 6 11:11:25 2012 - [debug] Connecting via SSH from root@192.168.123.13(192.168.123.13) to root@192.168.123.14(192.168.123.14)..
- Wed Jun 6 11:11:25 2012 - [debug] ok.
- Wed Jun 6 11:11:26 2012 - [debug]
- Wed Jun 6 11:11:25 2012 - [debug] Connecting via SSH from root@192.168.123.14(192.168.123.14) to root@192.168.123.13(192.168.123.13)..
- Wed Jun 6 11:11:26 2012 - [debug] ok.
- Wed Jun 6 11:11:26 2012 - [info] All SSH connection tests passed successfully.
6:测试主从复制情况,默认使用root用户连接
- mysql> select user,host,password from mysql.user;
- +--------+----------------+-------------------------------------------+
- | user | host | password |
- +--------+----------------+-------------------------------------------+
- | root | localhost | |
- | root | dg53.yang.com | |
- | root | 127.0.0.1 | |
- | root | ::1 | |
- | | localhost | |
- | | dg53.yang.com | |
- | r_test | 192.168.123.14 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
- +--------+----------------+-------------------------------------------+
- 7 rows in set (0.08 sec)
-
- mysql> use mysql;
- Database changed
-
- mysql> update user set host='192.168.123.%' where user='r_test';
- Query OK, 1 row affected (0.05 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- mysql> commit;
- Query OK, 0 rows affected (0.01 sec)
-
- mysql> update user set host='192.168.123.%' where host='localhost' and user='root' and password='';
- Query OK, 0 rows affected (0.00 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- mysql> update user set password=PASSWORD('123456') where user='root' and host='192.168.123.%';
- Query OK, 0 rows affected (0.00 sec)
- Rows matched: 1 Changed: 0 Warnings: 0
-
- mysql> flush privileges;
- Query OK, 0 rows affected (0.02 sec)
-
- [root@dg53 ~]# whereis mysqlbinlog
- mysqlbinlog: /usr/bin/mysqlbinlog
-
- [root@dg53 ~]# mv /usr/bin/mysql* /tmp
- [root@dg54 ~]# mv /usr/bin/mysql* /tmp
- [root@dg53 ~]# ln -s /usr/local/mysql5.5.25/bin/* /usr/local/bin/
- [root@dg54 ~]# ln -s /usr/local/mysql5.5.25/bin/* /usr/local/bin/
-
- [root@dg55 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
- Wed Jun 6 12:39:03 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
- Wed Jun 6 12:39:03 2012 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
- Wed Jun 6 12:39:03 2012 - [info] Reading server configurations from /etc/masterha/app1.cnf..
- Wed Jun 6 12:39:03 2012 - [info] MHA::MasterMonitor version 0.52.
- Wed Jun 6 12:39:03 2012 - [info] Dead Servers:
- Wed Jun 6 12:39:03 2012 - [info] Alive Servers:
- Wed Jun 6 12:39:03 2012 - [info] 192.168.123.13(192.168.123.13:3306)
- Wed Jun 6 12:39:03 2012 - [info] 192.168.123.14(192.168.123.14:3306)
- Wed Jun 6 12:39:03 2012 - [info] Alive Slaves:
- Wed Jun 6 12:39:03 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
- Wed Jun 6 12:39:03 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306)
- Wed Jun 6 12:39:03 2012 - [info] Primary candidate for the new Master (candidate_master is set)
- Wed Jun 6 12:39:03 2012 - [info] Current Alive Master: 192.168.123.13(192.168.123.13:3306)
- Wed Jun 6 12:39:03 2012 - [info] Checking slave configurations..
- Wed Jun 6 12:39:03 2012 - [warning] read_only=1 is not set on slave 192.168.123.14(192.168.123.14:3306).
- Wed Jun 6 12:39:03 2012 - [warning] relay_log_purge=0 is not set on slave 192.168.123.14(192.168.123.14:3306).
- Wed Jun 6 12:39:03 2012 - [info] Checking replication filtering settings..
- Wed Jun 6 12:39:03 2012 - [info] binlog_do_db= bbs,test, binlog_ignore_db= mysql
- Wed Jun 6 12:39:03 2012 - [info] Replication filtering check ok.
- Wed Jun 6 12:39:03 2012 - [info] Starting SSH connection tests..
- Wed Jun 6 12:39:05 2012 - [info] All SSH connection tests passed successfully.
- Wed Jun 6 12:39:05 2012 - [info] Checking MHA Node version..
- Wed Jun 6 12:39:05 2012 - [info] Version check ok.
- Wed Jun 6 12:39:05 2012 - [info] Checking SSH publickey authentication and checking recovery script configurations on the current master..
- Wed Jun 6 12:39:05 2012 - [info] Executing command: save_binary_logs --command=test --start_file=mysql-bin.000011 --start_pos=4 --binlog_dir=/mydata --output_file=/var/tmp/save_binary_logs_test --manager_version=0.52
- Wed Jun 6 12:39:05 2012 - [info] Connecting to root@192.168.123.13(192.168.123.13)..
- Creating /var/tmp if not exists.. ok.
- Checking output directory is accessible or not..
- ok.
- Binlog found at /mydata, up to mysql-bin.000011
- Wed Jun 6 12:39:06 2012 - [info] Master setting check done.
- Wed Jun 6 12:39:06 2012 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
- Wed Jun 6 12:39:06 2012 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user=root --slave_host=192.168.123.14 --slave_ip=192.168.123.14 --slave_port=3306 --workdir=/var/tmp --target_version=5.5.25-log --manager_version=0.52 --relay_log_info=/mydata/relay-log.info --slave_pass=xxx
- Wed Jun 6 12:39:06 2012 - [info] Connecting to root@192.168.123.14(192.168.123.14)..
- Checking slave recovery environment settings..
- Opening /mydata/relay-log.info ... ok.
- Relay log found at /mydata, up to dg54-relay-bin.000019
- Temporary relay log file is /mydata/dg54-relay-bin.000019
- Testing mysql connection and privileges.. done.
- Testing mysqlbinlog output.. done.
- Cleaning up test file(s).. done.
- Wed Jun 6 12:39:06 2012 - [info] Slaves settings check done.
- Wed Jun 6 12:39:06 2012 - [info]
- 192.168.123.13 (current master)
- +--192.168.123.14
-
- Wed Jun 6 12:39:06 2012 - [info] Checking replication health on 192.168.123.14..
- Wed Jun 6 12:39:06 2012 - [info] ok.
- Wed Jun 6 12:39:06 2012 - [warning] master_ip_failover_script is not defined.
- Wed Jun 6 12:39:06 2012 - [warning] shutdown_script is not defined.
- Wed Jun 6 12:39:06 2012 - [info] Got exit code 0 (Not master dead).
-
- MySQL Replication Health is OK.
7:启动管理节点进程
- [root@dg55 ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log < /dev/null 2>&1 &
- [1] 25516
- [root@dg55 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
- app1 (pid:25516) is running(0:PING_OK), master:192.168.123.13
三:测试failover过程
1:当前主库为192.168.123.13,关闭主库
- mysql> show slave hosts;
- +-----------+------+------+-----------+
- | Server_id | Host | Port | Master_id |
- +-----------+------+------+-----------+
- | 2 | | 3306 | 1 |
- +-----------+------+------+-----------+
- 1 row in set (0.00 sec)
-
- [root@dg53 ~]# service mysqld stop
- Shutting down MySQL...[ OK ]
2:在管理节点上观察日志输出
[root@dg55 ~]# tail -f /masterha/app1/manager.log Wed Jun 6 14:50:48 2012 - [info] 192.168.123.13 (current master) +--192.168.123.14 Wed Jun 6 14:50:48 2012 - [warning] master_ip_failover_script is not defined. Wed Jun 6 14:50:48 2012 - [warning] shutdown_script is not defined. Wed Jun 6 14:50:48 2012 - [info] Set master ping interval 1 seconds. Wed Jun 6 14:50:48 2012 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Wed Jun 6 14:50:48 2012 - [info] Starting ping health check on 192.168.123.13(192.168.123.13:3306).. Wed Jun 6 14:50:48 2012 - [info] Ping succeeded, sleeping until it doesn't respond.. Wed Jun 6 14:51:32 2012 - [warning] Got error on MySQL ping: 2006 (MySQL server has gone away) Wed Jun 6 14:51:32 2012 - [info] HealthCheck: SSH to 192.168.123.13 is reachable. Wed Jun 6 14:51:33 2012 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111) Wed Jun 6 14:51:33 2012 - [warning] Connection failed 1 time(s).. Wed Jun 6 14:51:34 2012 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111) Wed Jun 6 14:51:34 2012 - [warning] Connection failed 2 time(s).. Wed Jun 6 14:51:35 2012 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111) Wed Jun 6 14:51:35 2012 - [warning] Connection failed 3 time(s).. Wed Jun 6 14:51:35 2012 - [warning] Master is not reachable from health checker! Wed Jun 6 14:51:35 2012 - [warning] Master 192.168.123.13(192.168.123.13:3306) is not reachable! Wed Jun 6 14:51:35 2012 - [warning] SSH is reachable. Wed Jun 6 14:51:35 2012 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status.. Wed Jun 6 14:51:35 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Wed Jun 6 14:51:35 2012 - [info] Reading application default configurations from /etc/masterha/app1.cnf.. Wed Jun 6 14:51:35 2012 - [info] Reading server configurations from /etc/masterha/app1.cnf.. Wed Jun 6 14:51:35 2012 - [info] Dead Servers: Wed Jun 6 14:51:35 2012 - [info] 192.168.123.13(192.168.123.13:3306) Wed Jun 6 14:51:35 2012 - [info] Alive Servers: Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Wed Jun 6 14:51:35 2012 - [info] Alive Slaves: Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled Wed Jun 6 14:51:35 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306) Wed Jun 6 14:51:35 2012 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jun 6 14:51:35 2012 - [info] Checking slave configurations.. Wed Jun 6 14:51:35 2012 - [warning] read_only=1 is not set on slave 192.168.123.14(192.168.123.14:3306). Wed Jun 6 14:51:35 2012 - [warning] relay_log_purge=0 is not set on slave 192.168.123.14(192.168.123.14:3306). Wed Jun 6 14:51:35 2012 - [info] Checking replication filtering settings.. Wed Jun 6 14:51:35 2012 - [info] Replication filtering check ok. Wed Jun 6 14:51:35 2012 - [info] Master is down! Wed Jun 6 14:51:35 2012 - [info] Terminating monitoring script. Wed Jun 6 14:51:35 2012 - [info] Got exit code 20 (Master dead). Wed Jun 6 14:51:35 2012 - [info] MHA::MasterFailover version 0.52. Wed Jun 6 14:51:35 2012 - [info] Starting master failover. Wed Jun 6 14:51:35 2012 - [info] Wed Jun 6 14:51:35 2012 - [info] * Phase 1: Configuration Check Phase.. Wed Jun 6 14:51:35 2012 - [info] Wed Jun 6 14:51:35 2012 - [info] Dead Servers: Wed Jun 6 14:51:35 2012 - [info] 192.168.123.13(192.168.123.13:3306) Wed Jun 6 14:51:35 2012 - [info] Checking master reachability via mysql(double check).. Wed Jun 6 14:51:35 2012 - [info] ok. Wed Jun 6 14:51:35 2012 - [info] Alive Servers: Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Wed Jun 6 14:51:35 2012 - [info] Alive Slaves: Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled Wed Jun 6 14:51:35 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306) Wed Jun 6 14:51:35 2012 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jun 6 14:51:35 2012 - [info] ** Phase 1: Configuration Check Phase completed. Wed Jun 6 14:51:35 2012 - [info] Wed Jun 6 14:51:35 2012 - [info] * Phase 2: Dead Master Shutdown Phase.. Wed Jun 6 14:51:35 2012 - [info] Wed Jun 6 14:51:35 2012 - [info] Forcing shutdown so that applications never connect to the current master.. Wed Jun 6 14:51:35 2012 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master ip address. Wed Jun 6 14:51:35 2012 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Wed Jun 6 14:51:35 2012 - [info] * Phase 2: Dead Master Shutdown Phase completed. Wed Jun 6 14:51:35 2012 - [info] Wed Jun 6 14:51:35 2012 - [info] * Phase 3: Master Recovery Phase.. Wed Jun 6 14:51:35 2012 - [info] Wed Jun 6 14:51:35 2012 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Wed Jun 6 14:51:35 2012 - [info] Wed Jun 6 14:51:35 2012 - [info] The latest binary log file/position on all slaves is mysql-bin.000021:107 Wed Jun 6 14:51:35 2012 - [info] Latest slaves (Slaves that received relay log files to the latest): Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled Wed Jun 6 14:51:35 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306) Wed Jun 6 14:51:35 2012 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jun 6 14:51:35 2012 - [info] The oldest binary log file/position on all slaves is mysql-bin.000021:107 Wed Jun 6 14:51:35 2012 - [info] Oldest slaves: Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled Wed Jun 6 14:51:35 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306) Wed Jun 6 14:51:35 2012 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jun 6 14:51:35 2012 - [info] Wed Jun 6 14:51:35 2012 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase.. Wed Jun 6 14:51:35 2012 - [info] Wed Jun 6 14:51:35 2012 - [info] Fetching dead master's binary logs.. Wed Jun 6 14:51:35 2012 - [info] Executing command on the dead master 192.168.123.13(192.168.123.13:3306): save_binary_logs --command=save --start_file=mysql-bin.000021 --start_pos=107 --binlog_dir=/mydata --output_file=/var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.52 Creating /var/tmp if not exists.. ok. Concat binary/relay logs from mysql-bin.000021 pos 107 to mysql-bin.000021 EOF into /var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog .. Dumping binlog format description event, from position 0 to 107.. ok. Dumping effective binlog data from /mydata/mysql-bin.000021 position 107 to tail(126).. ok. Concat succeeded. Wed Jun 6 14:51:36 2012 - [info] scp from root@192.168.123.13:/var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog to local:/masterha/app1/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog succeeded. Wed Jun 6 14:51:36 2012 - [info] HealthCheck: SSH to 192.168.123.14 is reachable. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] * Phase 3.3: Determining New Master Phase.. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] Finding the latest slave that has all relay logs for recovering other slaves.. Wed Jun 6 14:51:37 2012 - [info] All slaves received relay logs to the same position. No need to resync each other. Wed Jun 6 14:51:37 2012 - [info] Searching new master from slaves.. Wed Jun 6 14:51:37 2012 - [info] Candidate masters from the configuration file: Wed Jun 6 14:51:37 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled Wed Jun 6 14:51:37 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306) Wed Jun 6 14:51:37 2012 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jun 6 14:51:37 2012 - [info] Non-candidate masters: Wed Jun 6 14:51:37 2012 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Wed Jun 6 14:51:37 2012 - [info] New master is 192.168.123.14(192.168.123.14:3306) Wed Jun 6 14:51:37 2012 - [info] Starting master failover.. Wed Jun 6 14:51:37 2012 - [info] From: 192.168.123.13 (current master) +--192.168.123.14 To: 192.168.123.14 (new master) Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] * Phase 3.3: New Master Diff Log Generation Phase.. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Wed Jun 6 14:51:37 2012 - [info] Sending binlog.. Wed Jun 6 14:51:37 2012 - [info] scp from local:/masterha/app1/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog to root@192.168.123.14:/var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog succeeded. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] * Phase 3.4: Master Log Apply Phase.. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed. Wed Jun 6 14:51:37 2012 - [info] Starting recovery on 192.168.123.14(192.168.123.14:3306).. Wed Jun 6 14:51:37 2012 - [info] Generating diffs succeeded. Wed Jun 6 14:51:37 2012 - [info] Waiting until all relay logs are applied. Wed Jun 6 14:51:37 2012 - [info] done. Wed Jun 6 14:51:37 2012 - [info] Getting slave status.. Wed Jun 6 14:51:37 2012 - [info] This slave(192.168.123.14)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000021:107). No need to recover from Exec_Master_Log_Pos. Wed Jun 6 14:51:37 2012 - [info] Connecting to the target slave host 192.168.123.14, running recover script.. Wed Jun 6 14:51:37 2012 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user=root --slave_host=192.168.123.14 --slave_ip=192.168.123.14 --slave_port=3306 --apply_files=/var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog --workdir=/var/tmp --target_version=5.5.25-log --timestamp=20120606145135 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.52 --slave_pass=xxx Wed Jun 6 14:51:37 2012 - [info] Applying differential binary/relay log files /var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog on 192.168.123.14:3306. This may take long time... Applying log files succeeded. Wed Jun 6 14:51:37 2012 - [info] All relay logs were successfully applied. Wed Jun 6 14:51:37 2012 - [info] Getting new master's binlog name and position.. Wed Jun 6 14:51:37 2012 - [info] mysql-bin.000023:107 Wed Jun 6 14:51:37 2012 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.123.14', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=107, MASTER_USER='r_test', MASTER_PASSWORD='xxx'; Wed Jun 6 14:51:37 2012 - [warning] master_ip_failover_script is not set. Skipping taking over new master ip address. Wed Jun 6 14:51:37 2012 - [info] ** Finished master recovery successfully. Wed Jun 6 14:51:37 2012 - [info] * Phase 3: Master Recovery Phase completed. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] * Phase 4: Slaves Recovery Phase.. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase.. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] Generating relay diff files from the latest slave succeeded. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase.. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] All new slave servers recovered successfully. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] * Phase 5: New master cleanup phease.. Wed Jun 6 14:51:37 2012 - [info] Wed Jun 6 14:51:37 2012 - [info] Resetting slave info on the new master.. Wed Jun 6 14:51:37 2012 - [info] Master failover to 192.168.123.14(192.168.123.14:3306) completed successfully. Wed Jun 6 14:51:37 2012 - [info] ----- Failover Report ----- app1: MySQL Master failover 192.168.123.13 to 192.168.123.14 succeeded Master 192.168.123.13 is down! Check MHA Manager logs at dg55.yang.com:/masterha/app1/manager.log for details. Started automated(non-interactive) failover. The latest slave 192.168.123.14(192.168.123.14:3306) has all relay logs for recovery. Selected 192.168.123.14 as a new master. 192.168.123.14: OK: Applying all logs succeeded. Generating relay diff files from the latest slave succeeded. 192.168.123.14: Resetting slave info succeeded. Master failover to 192.168.123.14(192.168.123.14:3306) completed successfully. |
3:在原从库192.168.123.14上查看结果
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000023 | 107 | bbs,test | mysql |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
-
- mysql> show slave status\G;
- *************************** 1. row ***************************
- Slave_IO_State:
- Master_Host: 192.168.123.13
- Master_User: r_test
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File:
- Read_Master_Log_Pos: 4
- Relay_Log_File: dg54-relay-bin.000001
- Relay_Log_Pos: 4
- Relay_Master_Log_File:
- Slave_IO_Running: No
- Slave_SQL_Running: No
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 0
- Relay_Log_Space: 126
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: NULL
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error:
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 1
- 1 row in set (0.00 sec)
-
- ERROR:
- No query specified
备注:本文只简单记录了mha的环境部署过程,更多高级的内容,例如配合keepalive使用VIP,让客户端连接数据库透明化以及手动failover和master_ip_failover_script脚本,半同步复制等功能后续将继续研究!
参考文章,感谢作者分享
http://who0168.blog.51cto.com/253401/699030
http://huoding.com/2011/12/18/139
http://www.vmcd.org/2012/04/mysql-high-availability-mha/
本文转自斩月博客51CTO博客,原文链接http://blog.51cto.com/ylw6006/890360如需转载请自行联系原作者
ylw6006