author：JevonWei
版权声明：原创作品主节点高可用
MHA是一款开源的MySQL的高可用程序，他为MySQL主从复制架构提供了automating master failover功能。MHA在监控到master节点故障时，会提升
其中拥有最新数据的slave节点成为新的master节点。在此期间，MHA会通过其他从节点获取额外信息来避免一致性方面的额问题。MHA还提供了master 节点切换功能，即按需切master/slave节点MHA服务有两种角色，MHA Manager(管理节点)和HMA Node(数据节点)
MHA Manager：通常部署在一台独立机器上管理多个master/slave集群，每个master/slave集群称作一个application；MHA node：运行在每台MySQL服务器上(master/slave/manager),他通过监控具备解析和清理logs功能的脚本来加快故障转移。
MHA组件
Manager节点 -masterha_check_ssh:MHA依赖的SSH环境检测工具 -masterha_check_repl:MySQL复制环境检测工具 -masterha_manager:MHA服务主程序 -masterha_check_status:MHA运行状态检测工具 -masterha_master_monitor:MySQL master节点可用性检测工具 -masterha_master_switch:master节点切换工具 -masterha_conf_host:添加或删除配置节点 -masterha_stop:关闭MHA服务的工具Node节点 -save_binary_logs:保存和复制master的二进制日志 -apply_diff_relay_logs:识别差异的中继日志事件并应用于其他slave -filter_mysqlbinlog:去除不必要的ROLLBACK事件(MHA已不在使用这个工具) -purge_relay_logs:清除中继日志(不会阻塞SQL线程) 自定义扩展 -sccondary_check_script:通过多条网络路由检测master的可用性 -master_ip_failover_script:更新application使用的masterrip -shutdown_script：强制关闭master节点 -report_script:发送报告 -init_conf_load_script:加载初始化配置参数 -master_ip_online_change_script:更新master节点ip地址
构建高可用HMA架构
节点角色
mysql-master 172.16.252.82mysql-slave1 172.16.252.92mysql-slave2 172.16.252.100manager 172.16.252.67
配置各节点之间可以通过主机名通信
[root@manager ~]# vim /etc/hosts172.16.252.82 mysql-master172.16.252.92 mysql-slave1172.16.252.100 mysql-slave2172.16.252.67 manager 其他各主机依次配置主机名解析文件
mysql-master
[root@mysql-master ~]# vim /etc/my.cnf.d/server.cnf[mysqld]server_id = 1relay-log = relay-loglog-bin = master-logskip_name_resolve = ONinnodb_file_per_table = ON[root@mysql-master ~]# systemctl start mariadb[root@mysql-master ~]# mysql授权一个有权限做主从复制的用户MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass';MariaDB [(none)]> FLUSH PRIVILEGES;查看master节点的状态，记录二进制文件的位置信息，用于从节点的复制起点MariaDB [(none)]> SHOW MASTER STATUS;+-------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-log.000001 | 501 | | |+-------------------+----------+--------------+------------------+
mysql-slave1
[root@mysql-slave1 ~]# vim /etc/my.cnf.d/server.cnf[mysqld]server_id = 2relay-log = relay-loglog-bin = master-logskip_name_resolve = ONinnodb_file_per_table = ONread_only = ONrelay_log_purge = OFF[root@mysql-slave1 ~]# systemctl start mariadb [root@mysql-slave ~]# mysqlMariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'read_only'; \\查看数据库为只读+---------------+-------+| Variable_name | Value |+---------------+-------+| read_only | ON |+---------------+-------+连接主节点数据库MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.198.139',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=501;启动从节点复制进程MariaDB [(none)]> START SLAVE;查看SLAVE节点的状态MariaDB [(none)]> SHOW SLAVE STATUS\G;连接manager的用户MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass';
mysql-slave2
root@mysql-slave2 ~]# vim /etc/my.cnf.d/server.cnf [mysqld]server_id = 3relay-log = relay-loglog-bin = master-logskip_name_resolve = ONinnodb_file_per_table = ONread_only = ONrelay_log_purge = OFF[root@mysql-slave2 ~]# systemctl start mariadb [root@mysql-slave ~]# mysqlMariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'read_only'; \\查看数据库为只读+---------------+-------+| Variable_name | Value |+---------------+-------+| read_only | ON |+---------------+-------+连接主节点数据库MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.198.139',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=501;启动从节点复制进程MariaDB [(none)]> START SLAVE;查看SLAVE节点的状态MariaDB [(none)]> SHOW SLAVE STATUS\G;连接manager的用户MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass';
mysql-master(master/slave上都需有此用户权限来连接manager)
MariaDB [(none)]> GRANT ALL ON *.* TO 'mha'@'172.16.%.%' IDENTIFIED BY 'mhapass'；MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass';
MHA安装
github官网下载manager到本地[root@manager ~]# ls mha4mysql-node-0.56-0.el6.noarch.rpm mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm[root@manager ~]# yum install ./mha4mysql-* 安装MHA程序包复制mha4mysql-node-0.56-0.el6.noarch.rpm程序包到各节点上[root@manager ~]# scp mha4mysql-node-0.56-0.el6.noarch.rpm mysql-master:/root[root@manager ~]# scp mha4mysql-node-0.56-0.el6.noarch.rpm mysql-slave1:/root[root@manager ~]# scp mha4mysql-node-0.56-0.el6.noarch.rpm mysql-slave2:/rootmysql-master、mysql-slave1和mysql-slave2节点依次安装mha4mysql-node-0.56-0.el6.noarch.rpm程序包[root@mysql-master ~]# yum -y install ./mha4mysql-node-0.56-0.el6.noarch.rpm [root@mysql-slave1 ~]# yum -y install ./mha4mysql-node-0.56-0.el6.noarch.rpm[root@mysql-slave2 ~]# yum -y install ./mha4mysql-node-0.56-0.el6.noarch.rpm
配置各节点之间SSH互信的通信环境
HMA[root@manager ~]# ssh-keygen -t rsa -P '' 生成ssh秘钥文件[root@manager ~]# ssh-copy-id -i .ssh/id_rsa.pub root@manager[root@manager ~]# scp -p .ssh/id_rsa .ssh/authorized_leys root@mysql-master:/root/.ssh[root@manager ~]# scp -p .ssh/id_rsa .ssh/authorized_leys root@mysql-slave1:/root/.ssh[root@manager ~]# scp -p .ssh/id_rsa .ssh/authorized_leys root@mysql-slave2:/root/.ssh[root@mysql-master ~]# ssh -o StrictHostKeyChecking=no mysql-slave1 直接连接mysql-slave主机，无询问，无提示
初始化HMA
manager [root@manager ~]# mkdir /data/masterha [root@manager ~]# mkdir /data/masterha/app [root@manager ~]# mkdir /etc/masterha [root@manager ~]# vim /etc/masterha/app.cnf [server default] user=mha password=mhapass \\node节点连接manager的用户 manager_workdir=/data/masterha/app manager_log=/data/masterha/app/manager.log remote_workdir=/data/masterha/app ssh_user=root repl_user=repluser repl_password=replpass ping_interval=1 [server1] hostname=172.16.252.82 candidate_master=1 [server2] hostname=172.16.252.92 candidate_master=1 [server3] hostname=172.16.252.100 candidate_master=1
测试后端主机的集群
[root@manager ~]# masterha_check_repl --conf=/etc/masterha/app.cnf
测试运行HMA程序
[root@manager ~]# masterha_manager --conf=/etc/masterha/app.cnf
查看HMA集群的状态
[root@manager ~]# masterha_check_status