mysql MHA高可用
创建一主两从集群
master配置
cd /data/mysql
rm -fr *
vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog
初始化、启动服务、配置master
mysqld --initialize --user=mysql
/etc/init.d/mysqld start
mysql -p
mysql> alter user root@localhost identified by 'westos';
mysql> grant replication slave on *.* to repl@'%' identified by 'westos';
server2、3同理
change master to master_host='192.168.81.12', master_user='repl', master_password='westos', master_auto_position=1;
show slave status\G;
MHA部署
server4上安装管理端软件
cd MHA-7/
yum install -y *.rpm
管理端配置ssh免密
ssh-keygen
ssh-copy-id server4
把密钥复制到各节点
scp -r .ssh/ server1:
scp -r .ssh/ server2:
scp -r .ssh/ server3:
复制客户端软件
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:
server1、server2、server3上安装客户端软件
yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
MHA配置
server4
mkdir /etc/masterha
vim /etc/masterha/app1.cnf
[server default]
user=root #mysql管理员
password=westos #mysql管理员密码
ssh_user=root #ssh免密用户
repl_user=repl #mysql主从复制用户
repl_password=westos
master_binlog_dir=/data/mysql #mysql数据库目录
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 192.168.81.12 -s 192.168.81.13
ping_interval=3
# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log
[server1]
hostname=192.168.81.11
candidate_master=1
check_repl_delay=0
[server2]
hostname=192.168.81.12
candidate_master=1
check_repl_delay=0
[server3]
hostname=192.168.81.13
no_master=1
在master上设置mysql 管理员权限,slave节点会自动同步
mysql> grant all on *.* to root@'%' identified by 'westos';
server4
检测各节点ssh免密连接
masterha_check_ssh --conf=/etc/masterha/app1.cnf
检测主从复制集群状态
masterha_check_repl --conf=/etc/masterha/app1.cnf
故障切换
server4
手动切换(master正常)
masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=192.168.81.14 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
手动切换(master故障)
手动停止master节点上的mysql服务
server2
/etc/init.d/mysqld stop
server4
masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.81.12 --dead_master_port=3306 --new_master_host=192.168.81.11 --new_master_port=3306 --ignore_last_failover
故障节点需要手动修复,重新加入主从集群
[root@server2 ~]# /etc/init.d/mysqld start
[root@server2 ~]# mysql -pwestos
mysql> change master to master_host='192.168.81.11', master_user='repl', master_password='westos', master_auto_position=1;
故障切换后会生成lock文件,需要手动删除
[root@server4 app1]# rm -f app1.failover.complete
自动切换
启动manger程序,并打入后台运行,完成切换任务后进程会自动退出
[root@server4 masterha]# masterha_manager --conf=/etc/masterha/app1.cnf &
在进行自动切换之前,必须保证主从集群正常,一主两从
加入故障切换脚本
vim /etc/masterha/app1.cnf
[server default]
user=root
password=westos
ssh_user=root
repl_user=repl
repl_password=westos
master_binlog_dir=/data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 192.168.81.12 -s 192.168.81.13
ping_interval=3
master_ip_failover_script= /usr/local/bin/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
manager_workdir=/etc/masterha
manager_log=/etc/masterha/manager.log
[server1]
hostname=192.168.81.11
candidate_master=1
check_repl_delay=0
[server2]
hostname=192.168.81.12
candidate_master=1
check_repl_delay=0
[server3]
hostname=192.168.81.13
no_master=1
需要有执行权限
按需修改vip和网卡名称
关闭master以后,会自动发起故障切换,mha管理器在完成切换后自动退出程序。
查看故障切换日志