MHA简介
MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案,它由日本DeNA公司的youshimaton(现就职于Facebook公司)开发,是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中,MHA能做到在0~30秒之内自动完成数据库的故障切换操作,并且在进行故障切换的过程中,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,要求一个复制集群中必须最少有三台数据库服务器,一主二从,即一台充当master,一台充当备用master,另外一台充当从库。
MHA + MySQL半同步复制实现高可用
在主从半同步复制的基础上,MySQL半同步+MHA+Keepalived实现MySQL高可用。
已经搭建好了一主多从半同步复制 的MySQL环境
- 192.168.204.128(master)
- 192.168.204.129(slave)
- 192.168.204.130(slave)
- 192.168.204.132(mha-manager)
#####MHA配置安装
- 依赖包:
- 下载地址:http://rpm.pbone.net/
- https://downloads.mariadb.com/MHA/
- https://pan.baidu.com/s/1DKIPlw2-65cS-or-XFhNrg
- 依赖及软件
mha4mysql-manager-0.56.tar.gz
mha4mysql-node-0.56.tar.gz
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.22-7.3.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
perl-Params-Validate-0.92-3.el6.i686.rpm
- mha工具包
- masterha_check_ssh : 检查MHA的SSH配置。
- masterha_check_repl : 检查MySQL复制。
- masterha_manager : 启动MHA。
- masterha_stop:停止mha。
- masterha_check_status : 检测当前MHA运行状态。
- masterha_master_monitor : 监测master是否宕机。
- masterha_master_switch : 控制故障转移(自动或手动)。
- masterha_conf_host : 添加或删除配置的server信息。
- Node工具(这些工具通常由MHAManager的脚本触发,无需人手操作)。
- save_binary_logs : 保存和复制master的二进制日志。
- apply_diff_relay_logs : 识别差异的中继日志事件并应用于其它slave。
- filter_mysqlbinlog : 去除不必要的ROLLBACK事件(MHA已不再使用这个工具)。
- purge_relay_logs : 清除中继日志(不会阻塞SQL线程)。
-
创建MHA用户(在mysql master上执行)
mysql> grant all privileges on . to ‘mha’@‘192.168.204.%’ identified by ‘mha’;
mysql> flush privileges; -
配置SSH无密码登录认证
-
在mha-manager(192.168.204.132)上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.128
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.129
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.130 -
在mysql-master(192.168.204.128)上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.129
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.130
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.132 -
在mysql-slave(192.168.204.129)上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.128
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.130
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.132 -
在mysql-slave(192.168.204.130)上
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.128
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.129
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.204.132
-
配置hosts
[root@panda ~]# vim /etc/hosts
192.168.204.132 mha-manager
192.168.204.128 mysql-master
192.168.204.129 mysql-slave01
192.168.204.130 mysql-slave02
说明:在所有机器上的 /etc/hosts中添加上面配置.
创建两个软连接:
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql -
部署MHA
在所有运行MySQL服务的服务器上安装运行MHA Node,无论是master还是slave。由于MHA Manager需要MHA Node,因此在运行MHA Manager的服务器上也需要安装MHA Node。当然也可以在任意一个slave上运行MHA Manager。
-
在3台主机上(master、slave01和slave02)上分别安装mha4mysql-node包,这里以master为例,其它主机同理。
yum install perl-DBD-MySQL -y rpm -ivh rpm -ivh https://downloads.mariadb.com/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm
-
在manager上安装mha4mysql-manager和mha4mysql-node包
[root@mha-manager ~]# yum install perl perl-DBD-MySQL perl-ExtUtils-Embed cpan [root@mha-manager ~]# rpm -ivh https://downloads.mariadb.com/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm [root@mha-manager ~]# wget https://downloads.mariadb.com/MHA/mha4mysql-manager-0.56.tar.gz [root@mha-manager ~]# tar zvxf mha4mysql-manager-0.56.tar.gz [root@mha-manager ~]# cd mha4mysql-manager-0.56 [root@mha-manager ~]# perl Makefile.PL [root@mha-manager mha4mysql-manager-0.56]# make && make install [root@mha-manager mha4mysql-manager-0.56]# mkdir -p /usr/local/mha/scripts [root@mha-manager mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /usr/local/mha/mha.cnf [root@mha-manager mha4mysql-manager-0.56]# cp samples/scripts/* /usr/local/mha/scripts/
yum安装不了的下载RPM包安装,按安装顺序整理如下:
compat-db43-4.3.29-15.el6,perl-Mail-Sender-0.8.16-3.el6,perl-Mail-Sendmail-0.79-12.el6,perl-Config-Tiny-2.12-7.1.el6,perl-Parallel-ForkManager-0.7.9-1.el6,perl-Log-Dispatch-2.27-1.el6
其中,在安装perl-Log-Dispatch-2.27-1.el6包时还会遇到有模块的包需要安装,按安装顺序整理如下:
perl-TimeDate-1.16-11.1.el6,perl-MIME-Types-1.28-2.el6,perl-MailTools-2.04-4.el6,perl-Email-Date-Format-1.002-5.el6,perl-Params-Validate-0.92-3.el6,perl-MIME-Lite-3.027-2.el6
- 修改manager端mha的配置文件,如下
[server default]
manager_workdir=/usr/local/mha
manager_log=/usr/local/mha/manager.log
user=mha
password=mha
ssh_user=root
repl_user=backup
repl_password=backup
ping_interval=1
[server1]
hostname=192.168.204.128
master_binlog_dir=/usr/local/mysql/data/
candidate_master=1
[server2]
hostname=192.168.204.129
master_binlog_dir=/usr/local/mysql/data/
candidate_master=1
[server3]
hostname=192.168.204.130
master_binlog_dir=/usr/local/mysql/data
no_master=1
- 检查ssh是否畅通
[root@mha-manager mha]# masterha_check_ssh --conf=/usr/local/mha/mha.cnf
Wed May 30 06:35:05 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 30 06:35:05 2018 - [info] Reading application default configuration from /usr/local/mha/mha.cnf..
Wed May 30 06:35:05 2018 - [info] Reading server configuration from /usr/local/mha/mha.cnf..
Wed May 30 06:35:05 2018 - [info] Starting SSH connection tests..
Wed May 30 06:35:06 2018 - [debug]
Wed May 30 06:35:05 2018 - [debug] Connecting via SSH from root@192.168.204.128(192.168.204.128:22) to root@192.168.204.129(192.168.204.129:22)..
Wed May 30 06:35:05 2018 - [debug] ok.
Wed May 30 06:35:05 2018 - [debug] Connecting via SSH from root@192.168.204.128(192.168.204.128:22) to root@192.168.204.130(192.168.204.130:22)..
Wed May 30 06:35:05 2018 - [debug] ok.
Wed May 30 06:35:06 2018 - [debug]
Wed May 30 06:35:05 2018 - [debug] Connecting via SSH from root@192.168.204.129(192.168.204.129:22) to root@192.168.204.128(192.168.204.128:22)..
Wed May 30 06:35:05 2018 - [debug] ok.
Wed May 30 06:35:05 2018 - [debug] Connecting via SSH from root@192.168.204.129(192.168.204.129:22) to root@192.168.204.130(192.168.204.130:22)..
Wed May 30 06:35:06 2018 - [debug] ok.
Wed May 30 06:35:06 2018 - [debug]
Wed May 30 06:35:06 2018 - [debug] Connecting via SSH from root@192.168.204.130(192.168.204.130:22) to root@192.168.204.128(192.168.204.128:22)..
Wed May 30 06:35:06 2018 - [debug] ok.
Wed May 30 06:35:06 2018 - [debug] Connecting via SSH from root@192.168.204.130(192.168.204.130:22) to root@192.168.204.129(192.168.204.129:22)..
Wed May 30 06:35:06 2018 - [debug] ok.
Wed May 30 06:35:06 2018 - [info] All SSH connection tests passed successfully.
- masterha_check_repl工具检查mysql主从复制是否成功
[root@mha-manager mha]# masterha_check_repl --conf=/usr/local/mha/mha.cnf
Wed May 30 06:36:04 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 30 06:36:04 2018 - [info] Reading application default configuration from /usr/local/mha/mha.cnf..
Wed May 30 06:36:04 2018 - [info] Reading server configuration from /usr/local/mha/mha.cnf..
Wed May 30 06:36:04 2018 - [info] MHA::MasterMonitor version 0.56.
Wed May 30 06:36:04 2018 - [info] GTID failover mode = 0
Wed May 30 06:36:04 2018 - [info] Dead Servers:
Wed May 30 06:36:04 2018 - [info] Alive Servers:
Wed May 30 06:36:04 2018 - [info] 192.168.204.128(192.168.204.128:3306)
Wed May 30 06:36:04 2018 - [info] 192.168.204.129(192.168.204.129:3306)
Wed May 30 06:36:04 2018 - [info] 192.168.204.130(192.168.204.130:3306)
Wed May 30 06:36:04 2018 - [info] Alive Slaves:
Wed May 30 06:36:04 2018 - [info] 192.168.204.129(192.168.204.129:3306) Version=5.6.16-log (oldest major version between slaves) log-bin:enabled
Wed May 30 06:36:04 2018 - [info] Replicating from 192.168.204.128(192.168.204.128:3306)
Wed May 30 06:36:04 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Wed May 30 06:36:04 2018 - [info] 192.168.204.130(192.168.204.130:3306) Version=5.6.16-log (oldest major version between slaves) log-bin:enabled
Wed May 30 06:36:04 2018 - [info] Replicating from 192.168.204.128(192.168.204.128:3306)
Wed May 30 06:36:04 2018 - [info] Not candidate for the new Master (no_master is set)
Wed May 30 06:36:04 2018 - [info] Current Alive Master: 192.168.204.128(192.168.204.128:3306)
Wed May 30 06:36:04 2018 - [info] Checking slave configurations..
Wed May 30 06:36:04 2018 - [warning] relay_log_purge=0 is not set on slave 192.168.204.129(192.168.204.129:3306).
Wed May 30 06:36:04 2018 - [warning] relay_log_purge=0 is not set on slave 192.168.204.130(192.168.204.130:3306).
Wed May 30 06:36:04 2018 - [info] Checking replication filtering settings..
Wed May 30 06:36:04 2018 - [info] binlog_do_db= , binlog_ignore_db= information_schema,mysql
Wed May 30 06:36:04 2018 - [info] Replication filtering check ok.
Wed May 30 06:36:04 2018 - [info] GTID (with auto-pos) is not supported
Wed May 30 06:36:04 2018 - [info] Starting SSH connection tests..
Wed May 30 06:36:06 2018 - [info] All SSH connection tests passed successfully.
Wed May 30 06:36:06 2018 - [info] Checking MHA Node version..
Wed May 30 06:36:06 2018 - [info] Version check ok.
Wed May 30 06:36:06 2018 - [info] Checking SSH publickey authentication settings on the current master..
Wed May 30 06:36:06 2018 - [info] HealthCheck: SSH to 192.168.204.128 is reachable.
Wed May 30 06:36:06 2018 - [info] Master MHA Node version is 0.56.
Wed May 30 06:36:06 2018 - [info] Checking recovery script configurations on 192.168.204.128(192.168.204.128:3306)..
Wed May 30 06:36:06 2018 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data/ --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000009
Wed May 30 06:36:06 2018 - [info] Connecting to root@192.168.204.128(192.168.204.128:22)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /usr/local/mysql/data/, up to mysql-bin.000009
Wed May 30 06:36:07 2018 - [info] Binlog setting check done.
Wed May 30 06:36:07 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed May 30 06:36:07 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.204.129 --slave_ip=192.168.204.129 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.16-log --manager_version=0.56 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx
Wed May 30 06:36:07 2018 - [info] Connecting to root@192.168.204.129(192.168.204.129:22)..
Checking slave recovery environment settings..
Opening /usr/local/mysql/data/relay-log.info ... ok.
Relay log found at /usr/local/mysql/data, up to mysql-relay-bin.000015
Temporary relay log file is /usr/local/mysql/data/mysql-relay-bin.000015
Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Wed May 30 06:36:07 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.204.130 --slave_ip=192.168.204.130 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.16-log --manager_version=0.56 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx
Wed May 30 06:36:07 2018 - [info] Connecting to root@192.168.204.130(192.168.204.130:22)..
Checking slave recovery environment settings..
Opening /usr/local/mysql/data/relay-log.info ... ok.
Relay log found at /usr/local/mysql/data, up to mysql-relay-bin.000011
Temporary relay log file is /usr/local/mysql/data/mysql-relay-bin.000011
Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Wed May 30 06:36:07 2018 - [info] Slaves settings check done.
Wed May 30 06:36:07 2018 - [info]
192.168.204.128(192.168.204.128:3306) (current master)
+--192.168.204.129(192.168.204.129:3306)
+--192.168.204.130(192.168.204.130:3306)
Wed May 30 06:36:07 2018 - [info] Checking replication health on 192.168.204.129..
Wed May 30 06:36:07 2018 - [info] ok.
Wed May 30 06:36:07 2018 - [info] Checking replication health on 192.168.204.130..
Wed May 30 06:36:07 2018 - [info] ok.
Wed May 30 06:36:07 2018 - [warning] master_ip_failover_script is not defined.
Wed May 30 06:36:07 2018 - [warning] shutdown_script is not defined.
Wed May 30 06:36:07 2018 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
#####mha实验模拟
-
在每次做mha实验的时候,我们都最好先执行如下命令做检测
[root@mha-manager ~]# masterha_check_ssh --conf=/usr/local/mha/mha.cnf
[root@mha-manager ~]# masterha_check_repl --conf=/usr/local/mha/mha.cnf -
在manager端启动mha服务并时刻监控日志文件的输出变化
[root@mha-manager mha]# nohup masterha_manager --conf=/usr/local/mha/mha.cnf > /tmp/mha_manager.log 2>&1 &
[root@mha-manager mha]# ps -ef|grep masterha | grep -v ‘grep’ -
测试master宕机后,时候会自动切换
测试前查看slave01,slave02的主从同步情况
-
slave01
[root@mysql-slave01 bin]# mysql -uroot -proot -e ‘show slave status\G’ |egrep ‘Master_Host|Slave_IO_Running:|Slave_SQL_Running:’
Warning: Using a password on the command line interface can be insecure.
Master_Host: 192.168.204.128
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes -
slave02
[root@mysql-slave02 mysql]# mysql -uroot -proot -e ‘show slave status\G’ |egrep ‘Master_Host|Slave_IO_Running:|Slave_SQL_Running:’
Warning: Using a password on the command line interface can be insecure.
Master_Host: 192.168.204.128
Slave_IO_Running: Yes
Slave_SQL_Running: Yes -
停止master的mysql服务
[root@mysql-master mysql]# service mysql stop
Shutting down MySQL… SUCCESS!
----- Failover Report ----- mha: MySQL Master failover 192.168.204.128(192.168.204.128:3306) to 192.168.204.129(192.168.204.129:3306) succeeded Master 192.168.204.128(192.168.204.128:3306) is down! Check MHA Manager logs at mha-manager:/usr/local/mha/manager.log for details. Started automated(non-interactive) failover. The latest slave 192.168.204.129(192.168.204.129:3306) has all relay logs for recovery. Selected 192.168.204.129(192.168.204.129:3306) as a new master. 192.168.204.129(192.168.204.129:3306): OK: Applying all logs succeeded. 192.168.204.130(192.168.204.130:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.204.130(192.168.204.130:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.204.129(192.168.204.129:3306) 192.168.204.129(192.168.204.129:3306): Resetting slave info succeeded. Master failover to 192.168.204.129(192.168.204.129:3306) completed successfully.
-
查看slave02的主从同步信息
[root@mysql-slave02 mysql]# mysql -uroot -proot -e ‘show slave status\G’ |egrep ‘Master_Host|Slave_IO_Running:|Slave_SQL_Running:’
Warning: Using a password on the command line interface can be insecure.
Master_Host: 192.168.204.129
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- 恢复master服务
-
删除故障转移文件
[root@mha-manager mha]# rm -rf /usr/local/mha/mha.failover.complete -
重启mysql服务
[root@master ~]# service mysqld start
Starting MySQL (Percona Server)… SUCCESS! -
在manager的日子文件中找到主从同步的sql语句
[root@mha-manager mha]# grep MASTER_HOST /usr/local/mha/manager.log
Wed May 30 07:17:55 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=‘192.168.204.129’, MASTER_PORT=3306, MASTER_LOG_FILE=‘mysql-bin.000008’, MASTER_LOG_POS=316, MASTER_USER=‘backup’, MASTER_PASSWORD=‘xxx’; -
在master上启动主从同步,密码为backup
mysql> change master to master_host=‘192.168.204.129’,master_user=‘backup’,master_password=‘backup’,master_log_file=‘mysql-bin.000010’, master_log_pos=120;
mysql> start slave;
Query OK, 0 rows affected (0.05 sec) -
在master和slave02上执行,检查主从同步是否都正常,这里以master为例,slave02同理
[root@mysql-master mysql]# mysql -uroot -proot -e ‘show slave status\G’ |egrep ‘Master_Host|Slave_IO_Running:|Slave_SQL_Running:’
Warning: Using a password on the command line interface can be insecure.
Master_Host: 192.168.204.129
Slave_IO_Running: Yes
Slave_SQL_Running: Yes