MariaDB高可用架构之MHA

该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。

目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从.

官方介绍：https://code.google.com/p/mysql-master-ha/

配置MHA实现mariadb 主从切换

前期准备

• 完成3台数据库服务器的半同步复制
• 3台数据库服务器安装mha4mysql-node
• 管理节点安装 mha4mysql-managermh、a4mysql-node (需要配置EPEL源)
• 完成4台服务器的ssh基于公钥的登录

主节点配置一个管理账号用户 mha的远程管理

MariaDB [(none)]> grant all on *.* to mhauser@‘192.168.148.%‘ identified by ‘centos‘;

从节点开启二进制日志

vim /etc/mysql/my.cnf log-bin=/data/bin/mysql-bin relay_log_purge=0 #不清除中继日志 skip_name_resolve=1 #跳过域名解析

管理节点编写配置文件

[root@localhost ~]# vim /etc/mastermha/app1.cnf [server default] user=mhauser password=centos manager_workdir=/opt/mastermha/app1 manager_log=/opt/mastermha/app1/manager.log remote_workdir=/opt/mastermha/app1 ssh_user=root repl_user=repluser repl_password=centos ping_interval=1 master_binlog_dir=/data/bin/ #指定二进制日志的存放路径 [server1] hostname=192.168.148.7 candidate_master=1 [server2] hostname=192.168.148.27 candidate_master=1 [server3] hostname=192.168.148.37 candidate_master=1

管理节点运行测试脚本

# ssh 公钥登录验证 [root@localhost ~]# masterha_check_ssh --cOnf=/etc/mastermha/app1.cnf Fri May 10 10:11:33 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Fri May 10 10:11:33 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Fri May 10 10:11:33 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Fri May 10 10:11:33 2019 - [info] Starting SSH connection tests.. Fri May 10 10:11:35 2019 - [debug] Fri May 10 10:11:33 2019 - [debug] Connecting via SSH from root@192.168.148.7(192.168.148.7:22) to root@192.168.148.27(192.168.148.27:22).. Fri May 10 10:11:34 2019 - [debug] ok. Fri May 10 10:11:34 2019 - [debug] Connecting via SSH from root@192.168.148.7(192.168.148.7:22) to root@192.168.148.37(192.168.148.37:22).. Fri May 10 10:11:34 2019 - [debug] ok. Fri May 10 10:11:36 2019 - [debug] Fri May 10 10:11:34 2019 - [debug] Connecting via SSH from root@192.168.148.37(192.168.148.37:22) to root@192.168.148.7(192.168.148.7:22).. Fri May 10 10:11:35 2019 - [debug] ok. Fri May 10 10:11:35 2019 - [debug] Connecting via SSH from root@192.168.148.37(192.168.148.37:22) to root@192.168.148.27(192.168.148.27:22).. Fri May 10 10:11:35 2019 - [debug] ok. Fri May 10 10:11:36 2019 - [debug] Fri May 10 10:11:34 2019 - [debug] Connecting via SSH from root@192.168.148.27(192.168.148.27:22) to root@192.168.148.7(192.168.148.7:22).. Fri May 10 10:11:34 2019 - [debug] ok. Fri May 10 10:11:34 2019 - [debug] Connecting via SSH from root@192.168.148.27(192.168.148.27:22) to root@192.168.148.37(192.168.148.37:22).. Fri May 10 10:11:35 2019 - [debug] ok. Fri May 10 10:11:36 2019 - [info] All SSH connection tests passed successfully. # 数据库主从复制测试 如果结果不是 "OK" 请检测主从复制配置 [root@localhost ~]# masterha_check_repl --cOnf=/etc/mastermha/app1.cnf Fri May 10 10:12:12 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Fri May 10 10:12:12 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Fri May 10 10:12:12 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Fri May 10 10:12:12 2019 - [info] MHA::MasterMonitor version 0.56. Fri May 10 10:12:13 2019 - [info] GTID failover mode = 0 Fri May 10 10:12:13 2019 - [info] Dead Servers: Fri May 10 10:12:13 2019 - [info] Alive Servers: Fri May 10 10:12:13 2019 - [info] 192.168.148.7(192.168.148.7:3306) Fri May 10 10:12:13 2019 - [info] 192.168.148.27(192.168.148.27:3306) Fri May 10 10:12:13 2019 - [info] 192.168.148.37(192.168.148.37:3306) Fri May 10 10:12:13 2019 - [info] Alive Slaves: Fri May 10 10:12:13 2019 - [info] 192.168.148.27(192.168.148.27:3306) Version=10.2.23-MariaDB-log (oldest major version between slaves) log-bin:enabled Fri May 10 10:12:13 2019 - [info] Replicating from 192.168.148.7(192.168.148.7:3306) Fri May 10 10:12:13 2019 - [info] Primary candidate for the new Master (candidate_master is set) Fri May 10 10:12:13 2019 - [info] 192.168.148.37(192.168.148.37:3306) Version=10.2.23-MariaDB-log (oldest major version between slaves) log-bin:enabled Fri May 10 10:12:13 2019 - [info] Replicating from 192.168.148.7(192.168.148.7:3306) Fri May 10 10:12:13 2019 - [info] Primary candidate for the new Master (candidate_master is set) Fri May 10 10:12:13 2019 - [info] Current Alive Master: 192.168.148.7(192.168.148.7:3306) Fri May 10 10:12:13 2019 - [info] Checking slave configurations.. Fri May 10 10:12:13 2019 - [info] Checking replication filtering settings.. Fri May 10 10:12:13 2019 - [info] binlog_do_db= , binlog_ignore_db= Fri May 10 10:12:13 2019 - [info] Replication filtering check ok. Fri May 10 10:12:13 2019 - [info] GTID (with auto-pos) is not supported Fri May 10 10:12:13 2019 - [info] Starting SSH connection tests.. Fri May 10 10:12:15 2019 - [info] All SSH connection tests passed successfully. Fri May 10 10:12:15 2019 - [info] Checking MHA Node version.. Fri May 10 10:12:16 2019 - [info] Version check ok. Fri May 10 10:12:16 2019 - [info] Checking SSH publickey authentication settings on the current master.. Fri May 10 10:12:16 2019 - [info] HealthCheck: SSH to 192.168.148.7 is reachable. Fri May 10 10:12:17 2019 - [info] Master MHA Node version is 0.56. Fri May 10 10:12:17 2019 - [info] Checking recovery script configurations on 192.168.148.7(192.168.148.7:3306).. Fri May 10 10:12:17 2019 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/bin/ --output_file=/opt/mastermha/app1/save_binary_logs_test --manager_version=0.56 --start_file=master-bin.000001 Fri May 10 10:12:17 2019 - [info] Connecting to root@192.168.148.7(192.168.148.7:22).. Creating /opt/mastermha/app1 if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /data/bin/, up to master-bin.000001 Fri May 10 10:12:17 2019 - [info] Binlog setting check done. Fri May 10 10:12:17 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Fri May 10 10:12:17 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user=‘mhauser‘ --slave_host=192.168.148.27 --slave_ip=192.168.148.27 --slave_port=3306 --workdir=/opt/mastermha/app1 --target_version=10.2.23-MariaDB-log --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx Fri May 10 10:12:17 2019 - [info] Connecting to root@192.168.148.27(192.168.148.27:22).. Checking slave recovery environment settings.. Opening /data/mysql/relay-log.info ... ok. Relay log found at /data/mysql, up to localhost-relay-bin.000002 Temporary relay log file is /data/mysql/localhost-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Fri May 10 10:12:18 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user=‘mhauser‘ --slave_host=192.168.148.37 --slave_ip=192.168.148.37 --slave_port=3306 --workdir=/opt/mastermha/app1 --target_version=10.2.23-MariaDB-log --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx Fri May 10 10:12:18 2019 - [info] Connecting to root@192.168.148.37(192.168.148.37:22).. Checking slave recovery environment settings.. Opening /data/mysql/relay-log.info ... ok. Relay log found at /data/mysql, up to localhost-relay-bin.000002 Temporary relay log file is /data/mysql/localhost-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Fri May 10 10:12:18 2019 - [info] Slaves settings check done. Fri May 10 10:12:18 2019 - [info] 192.168.148.7(192.168.148.7:3306) (current master) +--192.168.148.27(192.168.148.27:3306) +--192.168.148.37(192.168.148.37:3306) Fri May 10 10:12:18 2019 - [info] Checking replication health on 192.168.148.27.. Fri May 10 10:12:18 2019 - [info] ok. Fri May 10 10:12:18 2019 - [info] Checking replication health on 192.168.148.37.. Fri May 10 10:12:18 2019 - [info] ok. Fri May 10 10:12:18 2019 - [warning] master_ip_failover_script is not defined. Fri May 10 10:12:18 2019 - [warning] shutdown_script is not defined. Fri May 10 10:12:18 2019 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. # 启动管理程序，如果主数据库故障会进行主从切换 [root@localhost ~]# masterha_manager --cOnf=/etc/mastermha/app1.cnf

手工kill主数据库进程查看切换效果

[root@node1 ~]# ps -ef | grep mysqld root 9774 1 0 10:21 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/node1.localdomain.pid mysql 9936 9774 0 10:21 ? 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/node1.localdomain.err --pid-file=/data/mysql/node1.localdomain.pid --socket=/tmp/mysql.sock --port=3306 [root@node1 ~]# kill -9 9774 [root@node1 ~]# kill -9 9936 # 监控节点的日志显示 主库切换 [root@localhost mastermha]# tail /opt/mastermha/app1/manager.log Started automated(non-interactive) failover. The latest slave 192.168.148.27(192.168.148.27:3306) has all relay logs for recovery. Selected 192.168.148.27(192.168.148.27:3306) as a new master. 192.168.148.27(192.168.148.27:3306): OK: Applying all logs succeeded. 192.168.148.37(192.168.148.37:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.148.37(192.168.148.37:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.148.27(192.168.148.27:3306) 192.168.148.27(192.168.148.27:3306): Resetting slave info succeeded. Master failover to 192.168.148.27(192.168.148.27:3306) completed successfully. # 192.168.148.27 查看信息 MariaDB [mysql]> show slave hosts; +-----------+------+------+-----------+ | Server_id | Host | Port | Master_id | +-----------+------+------+-----------+ | 37 | | 3306 | 27 | +-----------+------+------+-----------+ # 192.168.148.37 查看信息 MariaDB [mysql]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.148.27 Master_User: repluser Master_Port: 3306 Connect_Retry: 60

MariaDB高可用架构之MHA