MHA—MySQL 主从之Master高可用

实验准备工作

• 主机角色划分：

192.168.50.9     node9      MySQL主节点192.168.50.10    node10     MySQL从节点192.168.50.16    node16     MySQL从节点192.168.50.17  172.16.50.17  node17     MasterHA Manager & ProxySQL

1. 主节点

#修改配置文件vim /etc/my.cnf.d/server.cnf[mysqld]innodb_file_per_table=ONskip_name_resolve=ONserver_id=1log_bin=master-logrelay_log=relay-log#重启服务systemctl restart mariadb

1. 两个从节点分别执行(不同主机注意修改自己的server_id)

#修改配置文件vim /etc/my.cnf.d/server.cnf[mysqld]innodb_file_per_table=ONskip_name_resolve=ONserver_id=2relay_log=relay-loglog_bin=master-logrelay_log_purge=0read-only=1#重启服务systemctl restart mariadb

1. 创建ssh密钥，使任意两台主机间能够通过ssh自由通信

此处为了操作简单，可以使所有主机的密钥相同，具体做法为：在任意一台主机上生成一对密钥，将密钥发送至所有主机的对应目录下

#假如在node9节点主机上执行ssh-keygen -t rsa -P ''ssh-copy-id -i id_rsa.pub root@192.168.50.9#测试ssh连接可用性并更新know_hosts文件内容ssh root@192.168.50.9 'ip a'ssh root@192.168.50.10 'ip a'ssh root@192.168.50.16 'ip a'ssh root@192.168.50.17 'ip a'#scp -p id_rsa authorized_keys id_rsa.pub known_hosts root@192.168.50.10:/root/.ssh/scp -p id_rsa authorized_keys id_rsa.pub known_hosts root@192.168.50.16:/root/.ssh/scp -p id_rsa authorized_keys id_rsa.pub known_hosts root@192.168.50.17:/root/.ssh/

1. 获取mha4mysql-manager和mha4mysql-node两个程序包并安装

   • 在manager主机上需要同时安装manager和node包

yum install ./mha4mysql-manager-0.56-0.el6.noarch.rpm ./mha4mysql-node-0.56-0.el6.noarch.rpm

• 在node主机上仅需安装mha4mysql-node包

yum install ./mha4mysql-node-0.56-0.el6.noarch.rpm

1. 初始化MHA

因为192.168.50.16从节点主机未开启日志同步，故此处仅设置192.168.50.10作为主节点的备用节点

#创建目录mkdir /etc/masterha#创建、修改并增加配置文件内容vim /etc/masterha/app1.cnf[server default]user=mhaadminpassword=mhapassmanager_workdir=/data/masterha/app1manager_log=/data/masterha/app1/manager.logremote_workdir=/data/masterha/app1ssh_user=rootrepl_user=repladminrepl_password=replpassping_interval=1[server1]hostname=192.168.50.9candidate_master=1[server2]hostname=192.168.50.10candidate_master=1[server3]hostname=192.168.50.16candidate_master=0

1. 在node9主节点服务器上为mha创建并授权用户

GRANT ALL ON *.* TO 'mhaadmin'@'192.168.50.%' IDENTIFIED BY 'mhapass';GRANT ALL ON *.* TO 'repladmin'@'192.168.50.%' IDENTIFIED BY 'replpass';FLUSH PRIVILEGES;

1. 验证各主机是否可以基于ssh实现自由通信

masterha_check_ssh --conf=/etc/masterha/app1.cnf#成功信息[info] All SSH connection tests passed successfully

1. 对各mysql主机进行健康状态监测

masterha_check_repl --conf=/etc/masterha/app1.cnf#成功信息MySQL Replication Health is OK.

1. 启动进程

nohup masterha_manager --conf=/etc/masterha/app1.cnf &>> /data/masterha/app1/manager.log &

1. 监测状态

masterha_check_status --conf=/etc/masterha/app1.cnf #显示结果app1 (pid:4663) is running(0:PING_OK), master:192.168.50.9

1. 验证failover效果

   • 在node9上停止mysql服务

killall -9 mysqld mysqld_safe

• 在mha主机上会出现以下提示

[1]+  Done                    nohup masterha_manager --conf=/etc/masterha/app1.cnf &>>/data/masterha/app1/manager.log

• 查看mha日志

less /data/masterha/app1/manager.log#出现如下内容----- Failover Report -----app1: MySQL Master failover 192.168.50.9(192.168.50.9:3306) to 192.168.50.10(192.168.50.10:3306) succeededMaster 192.168.50.9(192.168.50.9:3306) is down!Check MHA Manager logs at localhost.localdomain:/data/masterha/app1/manager.log for details.Started automated(non-interactive) failover.The latest slave 192.168.50.10(192.168.50.10:3306) has all relay logs for recovery.Selected 192.168.50.10(192.168.50.10:3306) as a new master.192.168.50.10(192.168.50.10:3306): OK: Applying all logs succeeded.192.168.50.16(192.168.50.16:3306): This host has the latest relay log events.Generating relay diff files from the latest slave succeeded.192.168.50.16(192.168.50.16:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.50.10(192.168.50.10:3306)192.168.50.10(192.168.50.10:3306): Resetting slave info succeeded.Master failover to 192.168.50.10(192.168.50.10:3306) completed successfully.

• 修改node2（新的主节点）数据库的内容，从节点会同步执行

  1. 停止MHA的方法：

masterha_stop --conf=/etc/masterha/app1.cnf