软件简介
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换过程中，MHA能最大程度上保证数据库的一致性，以达到真正意义上的高可用。
MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以独立部署在一台独立的机器上管理多个Master-Slave集群，也可以部署在一台Slave上。当Master出现故障时，它可以自动将最新数据的Slave提升为新的Master，然后将所有其他的Slave重新指向新的Master。整个故障转移过程对应程序是完全透明的。
实验环境
[root@localhost ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.163.161 mysql-db01
192.168.163.162 mysql-db02
192.168.163.163 mysql-db03
关闭防火墙
[root@localhost ~]# systemctl disable firewalld
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl stop NetworkManager
[root@localhost ~]# systemctl disable NetworkManage
安装（3台都装）
[root@localhost ~]# yum -y install ncurses-devel
[root@localhost ~]# yum -y install libaio
[root@localhost ~]# tar xf mysql-5.6.17-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ln -s /usr/local/mysql-5.6.17-linux-glibc2.5-x86_64 /usr/local/mysql
[root@localhost ~]# useradd mysql -s /sbin/nologin -M
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
[root@localhost ~]# /bin/cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
[root@localhost ~]# /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# ln -s /usr/local/mysql/bin/* /usr/local/bin/
主库配置文件
[root@localhost ~]# cat /etc/my.cnf
[client]
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
gtid_mode = ON
log_slave_updates
enforce_gtid_consistency
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /usr/local/mysql/data
character-set-server = utf8
socket = /usr/local/mysql/data/mysql.sock
log_bin = mysql-bin #开启binlog日志
server_id = 1 #设置server_id
innodb_buffer_pool_size = 200M
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
max_binlog_size = 512M
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
mysql-db02 配置文件
[root@localhost ~]# cat /etc/my.cnf
[client]
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
gtid_mode = ON
log_slave_updates
enforce_gtid_consistency
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /usr/local/mysql/data
character-set-server = utf8
socket = /usr/local/mysql/data/mysql.sock
log_bin = mysql-bin #开启binlog日志
server_id = 5 #设置server_id
innodb_buffer_pool_size = 200M
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
max_binlog_size = 512M
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
mysql-db03 配置文件
[root@localhost mha]# cat /etc/my.cnf
[client]
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
relay_log_purge = 0
gtid_mode = ON
log_slave_updates
enforce_gtid_consistency
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /usr/local/mysql/data
character-set-server = utf8
socket = /usr/local/mysql/data/mysql.sock
log_bin = mysql-bin #开启binlog日志
server_id = 10 #设置server_id
innodb_buffer_pool_size = 200M
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
max_binlog_size = 512M
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
开启GTID模块
配置主从复制(mysql-db02,mysql-db03)
mysql> change master to master_host='192.168.163.161',master_user='rep',master_password='111111',master_auto_position=1;
部署MHA
环境准备（所有节点mysql-db01,mysql-db02,mysql-db03）
[root@mysql-db01 ~]# yum -y install perl-DBD-MySQL
#安装mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mysql-db01 rpm]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Preparing... ########################################### [100%]
1:mha4mysql-node ########################################### [100%]
[root@mysql-db01 ~]# mysql -uroot -p123123
mysql> grant all privileges on *.* to mha@'192.168.163.%' identified by '111111';
Query OK, 0 rows affected (0.00 sec)
部署管理节点（mha-manager）
[root@mysql-db03 ~]# yum -y install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
#安装manager包
[root@mysql-db03 rpm]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
Preparing... ########################################### [100%]
1:mha4mysql-manager ########################################### [100%]
编辑配置文件
#创建配置文件目录
[root@mysql-db03 ~]# mkdir -p /etc/mha
#创建日志目录
[root@mysql-db03 ~]# mkdir -p /var/log/mha/mha1
[root@mysql-db03 mha]# cat /etc/mha/mha1.cnf
[server default]
manager_log=/var/log/mha/mha1/manager #manager管理日志存放路径
manager_workdir=/var/log/mha/mha1 #manager管理日志的目录路径
master_binlog_dir=/usr/local/mysql/data #binlog日志的存放路径
user=mha #管理账户
password=111111 #管理账户密码
ping_interval=2 #存活检查的间隔时间
repl_user=rep #主从复制的授权账户
repl_password=111111 #主从复制的授权账户密码
ssh_user=root #用于ssh连接的账户
[server1]
hostname=192.168.163.161
port=3306
[server2]
#candidate_master=1 #此条暂时注释掉（后面解释）
#check_repl_delay=0 #此条暂时注释掉（后面解释）
hostname=192.168.163.162
port=3306
[server3]
hostname=192.168.163.163
port=3306
配置ssh信任（3台都做）
[root@mysql-db03 ~]# ssh-keygen -t dsa -P "" -f ~/.ssh/id_dsa >/dev/null 2>&1
#发送mysql-db03公钥，包括自己
[root@mysql-db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.163.161
[root@mysql-db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.163.162
[root@mysql-db03 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.163.163
#发送mysql-db02公钥，包括自己
[root@mysql-db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.163.161
[root@mysql-db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.163.162
[root@mysql-db02 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.163.163
#发送mysql-db01公钥，包括自己
[root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.163.161
[root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.163.162
[root@mysql-db01 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.163.163
ssh检查检测
[root@mysql-db03 ~]# masterha_check_ssh --conf=/etc/mha/mha1.cnf
主从复制检测
[root@mysql-db03 ~]# masterha_check_repl --conf=/etc/mha/mha1.cnf
mysql-db02和mysql-db03上添加主从复制的用户即可。
grant replication slave on *.* to rep@'192.168.163.%' identified by '111111';
启动MHA
[root@mysql-db03 ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
[root@mysql-db03 ~]# ps -ef | grep perl | grep -v grep
root 4961 4690 0 06:33 pts/2 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover
停掉mysql-db01的MySQL服务
[root@mysql-db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!
查看mysql-db03上的MySQL从库同步状态
查看mysql-db02上的MySQL，主库同步状态。
查看mysql-db03上的mha进程状态
mha的故障还原测试
将故障宕机的mysql-db01的MySQL服务启动并授权进行从同步
[root@mysql-db01 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@mysql-db01 ~]# mysql -uroot -p123123
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123123';
mysql> start slave;
将mha配置文件里缺失的部分补全
启动mha进程
停掉mysql-db02上的MySQL服务
查看mysql-db03上的主从同步状态：
[root@localhost ~]# mysql -uroot -p111111 -e 'show slave status\G'
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 192.168.163.161
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 231
Relay_Log_File: localhost-relay-bin.000006
Relay_Log_Pos: 401
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
启动mysql-db02上的MySQL服务
[root@mysql-db02 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@mysql-db02 ~]# mysql -uroot -p123123
mysql> CHANGE MASTER TO MASTER_HOST='192.168.163.161', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='111111';
mysql> start slave;
1250
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
