[关闭]
@zhangsiming65965 2019-02-23T12:54:04.000000Z 字数 22592 阅读 158

基于GTID的MySQL主从复制及MySQL-MHA高可用方案实践

云计算

---Author：张思明 ZhangSiming

---Mail：1151004164@cnu.edu.cn

---QQ：1030728296

如果有梦想，就放开的去追；
因为只有奋斗，才能改变命运；

一，MySQL-MHA环境准备

1.1实验环境

主机名 IP地址 描述
MHA1 192.168.17.109 CentOS6.5,MySQL5.6
MHA2 192.168.17.110 CentOS6.5,MySQL5.6
MHA3 192.168.17.111 CentOS6.5,MySQL5.6

1.2主机名映射

  1. [root@MHA1 ~]# vim /etc/hosts
  2. [root@MHA1 ~]# cat /etc/hosts
  3. 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
  4. ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
  5. 192.168.17.109 MHA1
  6. 192.168.17.110 MHA2
  7. 192.168.17.111 MHA3

1.3关闭selinux和iptables

  1. [root@MHA1 ~]# cat /etc/redhat-release 
  2. CentOS release 6.5 (Final)
  3. [root@MHA1 ~]# uname -r
  4. 2.6.32-431.el6.x86_64
  5. [root@MHA1 ~]# service iptables stop
  6. [root@MHA1 ~]# chkconfig iptables off
  7. [root@MHA1 ~]# vim /etc/sysconfig/selinux 
  8. [root@MHA1 ~]# getenforce 0
  9. Disabled

二、MHA介绍

2.1MHA软件简介

MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件；
在MySQL故障切换过程中，MHA能做到0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换过程中，MHA能最大程度上保证数据库的一致性，以达到真正意义上的高可用；
MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以独立部署在一台独立的机器上管理多个Master-Slave集群，也可以部署在一台Slave上。当Master出现故障时，它可以自动将最新数据的Slave提升为新的Master，然后将所有其他的Slave重新指向新的Master。整个故障转移过程对应程序是完全透明的。

2.2工作流程

2.3MHA架构图

image_1d3gg82hh8t7bivdof1jpjjrt9.png-49.5kB

三、MySQL主从复制环境准备

3.1利用ansible一键部署三台MySQL5.6

  1. [root@Zhangsiming ~]# vim /etc/ansible/hosts 
  2. [root@Zhangsiming ~]# cat /etc/ansible/hosts
  3. [Nginx]
  4. web01 ansible_ssh_host=192.168.17.109 ansible_ssh_port=22 ansible_ssh_user=root ansible_ssh_pass=666666
  5. web02 ansible_ssh_host=192.168.17.110 ansible_ssh_port=22 ansible_ssh_user=root ansible_ssh_pass=666666
  6. web03 ansible_ssh_host=192.168.17.111 ansible_ssh_port=22 ansible_ssh_user=root ansible_ssh_pass=666666
  7. #ansible添加Nginx主机组，包括三台MHA节点
  8. [root@Zhangsiming ~]# cat mysql.sh 
  9. #!/bin/bash
  10. #designed by ZhangSiming
  11. id mysql
  12. if [ $? -ne 0 ];then
  13.         useradd -M -s /sbin/nologin mysql
  14. fi
  15. yum -y install ncurses-devel
  16. yum -y install libaio
  17. cd ~
  18. tar xf mysql-5.6.17-linux-glibc2.5-x86_64.tar.gz -C /usr/local
  19. ln -s /usr/local/mysql-5.6.17-linux-glibc2.5-x86_64 /usr/local/mysql
  20. /bin/cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
  21. /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
  22. chmod a+x /etc/init.d/mysqld
  23. chkconfig --add mysqld
  24. chkconfig mysqld on
  25. ln -s /usr/local/mysql/bin/* /usr/local/bin
  26. /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
  27. /etc/init.d/mysqld start
  28. sleep 5
  29. mysqladmin -uroot password "666666"
  30. chown -R mysql.mysql /usr/local/mysql
  31. #MySQL安装脚本

ansible执行脚本

  1. [root@Zhangsiming ~]# ansible Nginx -m script -a "/root/mysql.sh" 
  2. #测试结果
  3. [root@MHA1 ~]# netstat -antup | grep mysql
  4. tcp        0      0 :::3306                     :::*                        LISTEN      1332/mysqld         
  5. [root@MHA1 ~]# mysql -uroot -p666666 -e "show databases;"
  6. Warning: Using a password on the command line interface can be insecure.
  7. +--------------------+
  8. | Database           |
  9. +--------------------+
  10. | information_schema |
  11. | mysql              |
  12. | performance_schema |
  13. | test               |
  14. +--------------------+
  15. #脚本一键部署成功

3.2部署基于GTID的主从复制

3.2.1什么是GTID？

GTID（Global Transaction）全局事务标识符：是一个唯一的标识符，它创建并与源服务器（主）上提交的每个事务相关联。此标识符不仅对其发起的服务器是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有1对1的映射。
GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

3.2.2基于GTID的主从复制与传统主从复制的对比

对比 基于GTID的主从复制 传统主从复制
基于什么复制 GTID 二进制日志的POS
slave端是否需要开启binlog日志 需要 不需要

3.2.3基于GTID的主从复制工作原理

1.当一个事务在主库端执行并提交时，产生GTID，一同记录到主库binlog日志中；
2.binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值；
3.sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID；
4.如果有记录，说明该GTID的事务已经执行，slave会忽略；
5.如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，
在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行；
6.在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

3.2.3MySQL5.6版本新特性

3.2.4修改MySQL主库配置文件

  1. [root@MHA1 ~]# cat /etc/my.cnf
  2. [client]
  3. socket = /usr/local/mysql/data/mysql.sock
  4. #MySQL连接实例
  5. [mysqld]
  7. gtid_mode = ON
  8. log_slave_updates
  9. enforce_gtid_consistency
  10. #上三句话开启GTID功能，MySQL5.6才有的新功能
  12. lower_case_table_names = 1
  13. default-storage-engine = InnoDB
  14. #InnoDB存储引擎
  15. port = 3306
  16. #3306端口
  17. datadir = /usr/local/mysql/data
  18. #数据目录
  19. character-set-server = utf8
  20. #支持中文
  21. socket = /usr/local/mysql/data/mysql.sock
  23. log_bin = mysql-bin     #开启binlog日志
  24. binlog_format = row
  25. #强烈建议二进制日志用row格式，虽然占用空间大，但是严谨，不会产生数据错误
  26. server_id = 1             #设置server_id
  28. innodb_buffer_pool_size = 200M
  29. slave-parallel-workers = 8
  30. thread_cache_size = 600
  31. back_log = 600
  32. slave_net_timeout = 60
  33. max_binlog_size = 512M
  34. key_buffer_size = 8M
  35. query_cache_size = 64M
  36. join_buffer_size = 2M
  37. sort_buffer_size = 2M
  38. query_cache_type = 1
  39. thread_stack = 192K
  40. #一些优化配置

3.2.4修改MySQL从库配置文件

  1. [root@MHA3 ~]# cat /etc/my.cnf
  2. [client]
  3. socket = /usr/local/mysql/data/mysql.sock
  4. [mysqld]
  6. gtid_mode = ON
  7. log_slave_updates
  8. enforce_gtid_consistency
  10. lower_case_table_names = 1
  11. default-storage-engine = InnoDB
  12. port = 3306
  13. datadir = /usr/local/mysql/data
  14. character-set-server = utf8
  15. socket = /usr/local/mysql/data/mysql.sock
  17. log_bin = mysql-bin     
  18. #从库也必须开启binlog日志，目的是记录同步过的GTID
  19. binlog_format = row
  20. #强烈建议二进制日志用row格式，虽然占用空间大，但是严谨，不会产生数据错误
  21. relay-log = /usr/local/mysql/data/relay-bin
  22. #从库开启relay-log
  23. relay_log_purge = 0
  24. #因为从库SQL线程需要从relay-log获取GTID和自身二进制文件GTID对比，所以做好关闭自动清除relay-log的功能
  25. server_id = 10            #设置不同的server_id
  26. innodb_buffer_pool_size = 200M
  27. slave-parallel-workers = 8
  28. thread_cache_size = 600
  29. back_log = 600
  30. slave_net_timeout = 60
  31. max_binlog_size = 512M
  32. key_buffer_size = 8M
  33. query_cache_size = 64M
  34. join_buffer_size = 2M
  35. sort_buffer_size = 2M
  36. query_cache_type = 1
  37. thread_stack = 192K
  38. read-only = 1
  39. #从库设置只读

两个从库和主库配置文件完全一致，需要打开二进制日志功能，GTID功能，只是三者的server ID需要两两不同，这里我设置的主为1，从为5和10。

特别提示：

在以往如果是基于binlog日志的主从复制，则必须要记住主库的master状态信息。
但是在MySQL5.6版本里多了一个Gtid的功能，可以自动记录主从复制位置点的信息，并在日志中输出出来。

3.2.5配置并开启基于GTID的MySQL一主二从的主从复制架构

STEP1：启动MySQL主库服务，添加主从复制账号

  1. [root@MHA1 ~]# /etc/init.d/mysqld restart
  2. Starting MySQL. SUCCESS! 
  3. [root@MHA1 ~]# mysql -uroot -p666666 -e "grant replication slave on *.* to rep@'192.168.17.%' identified by '666666';"
  4. Warning: Using a password on the command line interface can be insecure.
  5. [root@MHA1 ~]# mysql -uroot -p666666 -e "show grants for rep@'192.168.17.%';"
  6. Warning: Using a password on the command line interface can be insecure.
  7. +---------------------------------------------------------------------------------------------------------------------------+
  8. | Grants for rep@192.168.17.%                                                                                               |
  9. +---------------------------------------------------------------------------------------------------------------------------+
  10. | GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.17.%' IDENTIFIED BY PASSWORD '*B2B366CA5C4697F31D4C55D61F0B17E70E5664EC' |
  11. +---------------------------------------------------------------------------------------------------------------------------+
  12. #创建成功

STEP2：启动MySQL从库服务，查看主库和从库GTID状态

  1. #确定主库从库都开启了GTID，查看GTID状态
  2. #主库
  3. [root@MHA1 ~]# mysql -uroot -p666666 -e "show global variables like '%gtid%';"
  4. Warning: Using a password on the command line interface can be insecure.
  5. +--------------------------+----------------------------------------+
  6. | Variable_name            | Value                                  |
  7. +--------------------------+----------------------------------------+
  8. | enforce_gtid_consistency | ON                                     |
  9. | gtid_executed            | d21eed35-2eeb-11e9-94d2-000c2987f310:1 |
  10. | gtid_mode                | ON                                     |
  11. | gtid_owned               |                                        |
  12. | gtid_purged              |                                        |
  13. +--------------------------+----------------------------------------+
  14. #从库
  15. [root@MHA2 ~]# /etc/init.d/mysqld start
  16. Starting MySQL SUCCESS! 
  17. [root@MHA2 ~]# mysql -uroot -p666666 -e "show global variables like '%gtid%';"
  18. Warning: Using a password on the command line interface can be insecure.
  19. +--------------------------+-------+
  20. | Variable_name            | Value |
  21. +--------------------------+-------+
  22. | enforce_gtid_consistency | ON    |
  23. | gtid_executed            |       |
  24. | gtid_mode                | ON    |
  25. | gtid_owned               |       |
  26. | gtid_purged              |       |
  27. +--------------------------+-------+

STEP3：在从库配置基于GTID的主从复制

  1. [root@MHA2 ~]# mysql -uroot -p666666 -e "change master to master_host='192.168.17.109',master_user='rep',master_password='666666',master_auto_position=1;"
  2. #master_auto_position=1代表开启GTID自动追踪主从复制需要同步的position
  3. Warning: Using a password on the command line interface can be insecure.
  4. [root@MHA2 ~]# mysql -uroot -p666666 -e "start slave;"
  5. Warning: Using a password on the command line interface can be insecure.
  6. #两个从库都这样配置，并开启主从复制

STEP4：查看主从复制状态

  1. [root@MHA3 ~]# mysql -uroot -p666666 -e "show slave status\G" | egrep "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master"
  2. Warning: Using a password on the command line interface can be insecure.
  3.              Slave_IO_Running: Yes
  4.             Slave_SQL_Running: Yes
  5.         Seconds_Behind_Master: 0
  6. #从库IO线程、SQL线程工作正常，主从复制延迟为0

四、部署MHA高可用

4.1部署MHA环境(所有节点)

  1. [root@MHA1 ~]# yum -y install perl-DBD-MySQL
  2. #安装依赖包
  3. [root@MHA1 ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm 
  4. #安装MHA节点rpm包
  5. Preparing...                ########################################### [100%]
  6.    1:mha4mysql-node         ########################################### [100%]
  7. [root@MHA1 ~]# mysql -uroot -p666666 -e "grant all privileges on *.* to mha@'192.168.17.%' identified by '666666';"
  8. Warning: Using a password on the command line interface can be insecure.
  9. #因为开启主从复制，主库创建的账号自动同步到从库

4.2部署MHA管理节点

  1. #MHA管理节点可以是独立的，也可以在MySQL主从架构的从库上，这里部署在MHA3上
  2. #使用epel源安装依赖包
  3. [root@MHA3 ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-6.repo
  4. [root@MHA3 ~]# yum -y install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
  5. #自定义MHA管理rpm包的yum依赖源，之后用localinstall方式安装MHA管理rpm包
  6. [root@MHA3 ~]# ls /rpm
  7. Atlas-2.2.1.el6.x86_64.rpm
  8. epel-release-6-8.noarch.rpm
  9. mha4mysql-manager-0.56-0.el6.noarch.rpm
  10. mha4mysql-node-0.56-0.el6.noarch.rpm
  11. perl-5.10.1-144.el6.x86_64.rpm
  12. perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
  13. perl-Email-Date-Format-1.002-5.el6.noarch.rpm
  14. perl-libs-5.10.1-144.el6.x86_64.rpm
  15. perl-Log-Dispatch-2.27-1.el6.noarch.rpm
  16. perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
  17. perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
  18. perl-MailTools-2.04-4.el6.noarch.rpm
  19. perl-MIME-Lite-3.027-2.el6.noarch.rpm
  20. perl-MIME-Types-1.28-2.el6.noarch.rpm
  21. perl-Module-Pluggable-3.90-144.el6.x86_64.rpm
  22. perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
  23. perl-Params-Validate-0.92-3.el6.x86_64.rpm
  24. perl-Pod-Escapes-1.04-144.el6.x86_64.rpm
  25. perl-Pod-Simple-3.13-144.el6.x86_64.rpm
  26. perl-TimeDate-1.16-13.el6.noarch.rpm
  27. perl-Time-HiRes-1.9721-144.el6.x86_64.rpm
  28. perl-version-0.77-144.el6.x86_64.rpm
  29. repodata
  30. [root@MHA3 ~]# cat /etc/yum.repos.d/CentOS-Media.repo 
  31. [c6-media]
  32. name=CentOS-$releasever - Media
  33. baseurl=file:///media/CentOS/
  34.         file:///media/cdrom/
  35.         file:///rpm
  36. 自定义/rpm的yum源
  37. gpgcheck=1
  38. enabled=1
  39. gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
  40. [root@MHA3 ~]# yum -y localinstall  mha4mysql-manager-0.56-0.el6.noarch.rpm

4.3编辑MHA配置文件

  1. [root@MHA3 ~]# mkdir -p /etc/mha
  2. #创建MHA的文件目录
  3. [root@MHA3 ~]# mkdir -p /var/log/mha/mha1
  4. #创建MHA的日志目录
  5. [root@MHA3 ~]# vim /etc/mha/mha1.cnf
  6. [root@MHA3 ~]# cat /etc/mha/mha1.cnf
  7. [server default]
  8. manager_log=/var/log/mha/mha1/manager
  9. #MHA管理日志的位置
  10. manager_workdir=/var/log/mha/mha1
  11. #MHA管理日志的目录路径
  12. master_binlog_dir=/usr/local/mysql/data
  13. #MySQL二进制文件位置
  14. user=mha
  15. password=666666
  16. #MHA用户密码
  17. ping_interval=2
  18. #健康检测监控时间
  19. repl_user=rep
  20. repl_password=666666
  21. #MySQL主从复制用户密码
  22. ssh_user=root
  23. #ssh连接主机用户
  25. #下面是三个主从复制架构节点
  26. [server1]
  27. hostname=192.168.17.109
  28. port=3306
  30. [server2]
  31. hostname=192.168.17.110
  32. port=3306
  34. candidate_master=1 
  35. #候选MySQL主
  36. check_repl_delay=0 
  37. #忽略延迟，MHA默认不会选择主从复制延迟大的从提升为主，这两句一般情况下一起用
  39. [server3]
  40. hostname=192.168.17.111
  41. port=3306

4.4MySQL三个主从复制节点配置ssh信任

  1. #注意一定是两两配置信任(自己也需要)
  2. [root@MHA1 ~]# ssh-keygen -t dsa -P "" -f ~/.ssh/id_dsa
  3. [root@MHA1 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.17.109
  4. [root@MHA1 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.17.110
  5. [root@MHA1 ~]# ssh-copy-id -i /root/.ssh/id_dsa.pub root@192.168.17.111
  6. #在三台服务器都运行一遍！

4.5MHA启动前测试

4.5.1ssh健康检查

  1. [root@MHA3 ~]# masterha_check_ssh --conf=/etc/mha/mha1.cnf
  2. Thu Feb 14 04:04:26 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
  3. Thu Feb 14 04:04:26 2019 - [info] Reading application default configuration from /etc/mha/mha1.cnf..
  4. Thu Feb 14 04:04:26 2019 - [info] Reading server configuration from /etc/mha/mha1.cnf..
  5. Thu Feb 14 04:04:26 2019 - [info] Starting SSH connection tests..
  6. Thu Feb 14 04:04:27 2019 - [debug] 
  7. Thu Feb 14 04:04:26 2019 - [debug]  Connecting via SSH from root@192.168.17.109(192.168.17.109:22) to root@192.168.17.110(192.168.17.110:22)..
  8. Thu Feb 14 04:04:26 2019 - [debug]   ok.
  9. Thu Feb 14 04:04:26 2019 - [debug]  Connecting via SSH from root@192.168.17.109(192.168.17.109:22) to root@192.168.17.111(192.168.17.111:22)..
  10. Thu Feb 14 04:04:27 2019 - [debug]   ok.
  11. Thu Feb 14 04:04:28 2019 - [debug] 
  12. Thu Feb 14 04:04:26 2019 - [debug]  Connecting via SSH from root@192.168.17.110(192.168.17.110:22) to root@192.168.17.109(192.168.17.109:22)..
  13. Thu Feb 14 04:04:27 2019 - [debug]   ok.
  14. Thu Feb 14 04:04:27 2019 - [debug]  Connecting via SSH from root@192.168.17.110(192.168.17.110:22) to root@192.168.17.111(192.168.17.111:22)..
  15. Thu Feb 14 04:04:28 2019 - [debug]   ok.
  16. Thu Feb 14 04:04:28 2019 - [debug] 
  17. Thu Feb 14 04:04:27 2019 - [debug]  Connecting via SSH from root@192.168.17.111(192.168.17.111:22) to root@192.168.17.109(192.168.17.109:22)..
  18. Thu Feb 14 04:04:27 2019 - [debug]   ok.
  19. Thu Feb 14 04:04:27 2019 - [debug]  Connecting via SSH from root@192.168.17.111(192.168.17.111:22) to root@192.168.17.110(192.168.17.110:22)..
  20. Thu Feb 14 04:04:28 2019 - [debug]   ok.
  21. Thu Feb 14 04:04:28 2019 - [info] All SSH connection tests passed successfully.
  22. #三台MySQL主从复制节点ssh健康检测成功

4.5.2主从复制检测

  1. #这里注意了，需要给所有节点都上一个MySQL主从复制账号，因为切换之后如果没有主从复制账号还咋验证？
  2. [root@MHA2 data]# mysql -uroot -p666666 -e "grant replication slave on *.* to rep@'192.168.17.%' identified by '666666';"
  3. [root@MHA3 data]# mysql -uroot -p666666 -e "grant replication slave on *.* to rep@'192.168.17.%' identified by '666666';"
  4. #主从复制检测
  5. [root@MHA3 ~]# which masterha_check_repl
  6. /usr/bin/masterha_check_repl
  7. [root@MHA3 ~]# masterha_check_repl --conf=/etc/mha/mha1.cnf
  8. Thu Feb 14 04:06:37 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
  9. Thu Feb 14 04:06:37 2019 - [info] Reading application default configuration from /etc/mha/mha1.cnf..
  10. Thu Feb 14 04:06:37 2019 - [info] Reading server configuration from /etc/mha/mha1.cnf..
  11. Thu Feb 14 04:06:37 2019 - [info] MHA::MasterMonitor version 0.56.
  12. Thu Feb 14 04:06:38 2019 - [info] GTID failover mode = 1
  13. Thu Feb 14 04:06:38 2019 - [info] Dead Servers:
  14. Thu Feb 14 04:06:38 2019 - [info] Alive Servers:
  15. Thu Feb 14 04:06:38 2019 - [info]   192.168.17.109(192.168.17.109:3306)
  16. Thu Feb 14 04:06:38 2019 - [info]   192.168.17.110(192.168.17.110:3306)
  17. Thu Feb 14 04:06:38 2019 - [info]   192.168.17.111(192.168.17.111:3306)
  18. Thu Feb 14 04:06:38 2019 - [info] Alive Slaves:
  19. Thu Feb 14 04:06:38 2019 - [info]   192.168.17.110(192.168.17.110:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
  20. Thu Feb 14 04:06:38 2019 - [info]     GTID ON
  21. Thu Feb 14 04:06:38 2019 - [info]     Replicating from 192.168.17.109(192.168.17.109:3306)
  22. Thu Feb 14 04:06:38 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
  23. Thu Feb 14 04:06:38 2019 - [info]   192.168.17.111(192.168.17.111:3306)  Version=5.6.17-log (oldest major version between slaves) log-bin:enabled
  24. Thu Feb 14 04:06:38 2019 - [info]     GTID ON
  25. Thu Feb 14 04:06:38 2019 - [info]     Replicating from 192.168.17.109(192.168.17.109:3306)
  26. Thu Feb 14 04:06:38 2019 - [info] Current Alive Master: 192.168.17.109(192.168.17.109:3306)
  27. Thu Feb 14 04:06:38 2019 - [info] Checking slave configurations..
  28. Thu Feb 14 04:06:38 2019 - [info] Checking replication filtering settings..
  29. Thu Feb 14 04:06:38 2019 - [info]  binlog_do_db= , binlog_ignore_db= 
  30. Thu Feb 14 04:06:38 2019 - [info]  Replication filtering check ok.
  31. Thu Feb 14 04:06:38 2019 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
  32. Thu Feb 14 04:06:38 2019 - [info] Checking SSH publickey authentication settings on the current master..
  33. Thu Feb 14 04:06:38 2019 - [info] HealthCheck: SSH to 192.168.17.109 is reachable.
  34. Thu Feb 14 04:06:38 2019 - [info] 
  35. 192.168.17.109(192.168.17.109:3306) (current master)
  36.  +--192.168.17.110(192.168.17.110:3306)
  37.  +--192.168.17.111(192.168.17.111:3306)
  39. Thu Feb 14 04:06:38 2019 - [info] Checking replication health on 192.168.17.110..
  40. Thu Feb 14 04:06:38 2019 - [info]  ok.
  41. Thu Feb 14 04:06:38 2019 - [info] Checking replication health on 192.168.17.111..
  42. Thu Feb 14 04:06:38 2019 - [info]  ok.
  43. Thu Feb 14 04:06:38 2019 - [warning] master_ip_failover_script is not defined.
  44. Thu Feb 14 04:06:38 2019 - [warning] shutdown_script is not defined.
  45. Thu Feb 14 04:06:38 2019 - [info] Got exit code 0 (Not master dead).
  47. MySQL Replication Health is OK.
  48. #主从复制检测成功

4.6启动MHA

  1. [root@MHA3 ~]# which masterha_manager
  2. /usr/bin/masterha_manager
  3. [root@MHA3 ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
  4. [1] 2340
  5. #nohup ... &:常用于禁止session(nohup)关闭或者ctrl+c(&)停止进程
  7. [root@MHA3 ~]# ps -elf | grep perl | grep -v grep
  8. 0 S root       2340   2077  0  80   0 - 48831 hrtime 04:14 pts/0    00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover
  9. #启动成功

4.7测试MHA自动切换master

STEP1:登录候选master(MHA2)查看信息状态

  1. [root@MHA2 ~]# mysql -uroot -p666666 -e "show slave status\G"
  2. Warning: Using a password on the command line interface can be insecure.
  3. *************************** 1. row ***************************
  4.                Slave_IO_State: Waiting for master to send event
  5.                   Master_Host: 192.168.17.109
  6. #主库IP为192.168.17。109
  7.                   Master_User: rep
  8.                   Master_Port: 3306
  9.                 Connect_Retry: 60
  10.               Master_Log_File: mysql-bin.000001
  11.           Read_Master_Log_Pos: 660
  12.                Relay_Log_File: relay-bin.000002
  13.                 Relay_Log_Pos: 870
  14.         Relay_Master_Log_File: mysql-bin.000001
  15.              Slave_IO_Running: Yes
  16.             Slave_SQL_Running: Yes

STEP2:停止MHA1上的MySQL服务

  1. [root@MHA1 ~]# /etc/init.d/mysqld stop
  2. Shutting down MySQL....... SUCCESS! 
  3. #停止成功

STEP3:查看MHA3上的主从同步状态

  1. [root@MHA3 data]# mysql -uroot -p666666 -e "show slave status\G"
  2. Warning: Using a password on the command line interface can be insecure.
  3. *************************** 1. row ***************************
  4.                Slave_IO_State: Waiting for master to send event
  5.                   Master_Host: 192.168.17.110
  6. #master变为了MHA2
  7.                   Master_User: rep
  8.                   Master_Port: 3306
  9.                 Connect_Retry: 60
  10.               Master_Log_File: mysql-bin.000004
  11.           Read_Master_Log_Pos: 231
  12.                Relay_Log_File: relay-bin.000003
  13.                 Relay_Log_Pos: 401
  14.         Relay_Master_Log_File: mysql-bin.000004
  15.              Slave_IO_Running: Yes
  16.             Slave_SQL_Running: Yes

STEP4:查看MHA2上的主从同步状态

  1. [root@MHA2 data]# mysql -uroot -p666666 -e "show master status;"
  2. Warning: Using a password on the command line interface can be insecure.
  3. +------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
  4. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                |
  5. +------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
  6. | mysql-bin.000004 |      231 |              |                  | 76efda2a-2fc2-11e9-9a4a-000c2987f310:1-2,
  7. b4654a7e-2fcd-11e9-9a93-000c2967becd:1 |
  8. +------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
  9. #MHA2作为了MySQL主从同步的主

STEP5:查看MHA状态

  1. [root@MHA3 data]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
  2. [1] 3721
  3. #MHA后台进程自动终止了
  4. [root@MHA3 data]# cat /etc/mha/mha1.cnf 
  5. [server default]
  6. manager_log=/var/log/mha/mha1/manager
  7. manager_workdir=/var/log/mha/mha1
  8. master_binlog_dir=/usr/local/mysql/data
  9. password=666666
  10. ping_interval=2
  11. repl_password=666666
  12. repl_user=rep
  13. ssh_user=root
  14. user=mha
  15. #查看MHA配置文件发现server1自动消失了
  16. [server2]
  17. candidate_master=1
  18. check_repl_delay=0
  19. hostname=192.168.17.110
  20. port=3306
  22. [server3]
  23. hostname=192.168.17.111
  24. port=3306

STEP6:查看MHA日志

  1. cat /var/log/mha/mha1/manager
  2. ...省略...
  3. Thu Feb 14 04:43:26 2019 - [info] Resetting slave info on the new master..
  4. #把从库信息录入到新的主上
  5. Thu Feb 14 04:43:27 2019 - [info]  192.168.17.110: Resetting slave info succeeded.
  6. Thu Feb 14 04:43:27 2019 - [info] Master failover to 192.168.17.110(192.168.17.110:3306) completed successfully.
  7. #成功热备master切换
  8. Thu Feb 14 04:43:27 2019 - [info] Deleted server1 entry from /etc/mha/mha1.cnf .
  9. #删除/etc/mha/mha1.cnf中的server1模块
  10. ...省略...

4.8进行MHA故障完善恢复

我们需要重启MHA1的MySQL，然后配置作为从库MHA2的从库；由于切换后MHA进程停止了，我们需要重新配置并启动。

STEP1:重启MHA1，并配置作为MHA2的从库

  1. [root@MHA1 ~]# /etc/init.d/mysqld start
  2. Starting MySQL.. SUCCESS! 
  3. [root@MHA1 ~]# mysql -uroot -p666666 -e "change master to master_host='192.168.17.110',master_user='rep',master_password='666666',master_auto_position=1;"
  4. [root@MHA1 ~]# mysql -uroot -p666666 -e "start slave;"
  5. [root@MHA1 ~]# mysql -uroot -p666666 -e "show slave status\G"
  6. Warning: Using a password on the command line interface can be insecure.
  7. *************************** 1. row ***************************
  8.                Slave_IO_State: Waiting for master to send event
  9.                   Master_Host: 192.168.17.110
  10.                   Master_User: rep
  11.                   Master_Port: 3306
  12.                 Connect_Retry: 60
  13.               Master_Log_File: mysql-bin.000006
  14.           Read_Master_Log_Pos: 231
  15.                Relay_Log_File: MHA1-relay-bin.000005
  16.                 Relay_Log_Pos: 401
  17.         Relay_Master_Log_File: mysql-bin.000006
  18.              Slave_IO_Running: Yes
  19.             Slave_SQL_Running: Yes

STEP2:将MHA配置文件里面缺失的部分补齐

因为MHA故障转移切换之后，MHA1服务器在MHA配置文件里面的部分就自动消失了，我们需要手动重新写回来。

  1. [root@MHA3 ~]# vim /etc/mha/mha1.cnf 
  2. [root@MHA3 ~]# cat /etc/mha/mha1.cnf
  3. [server default]
  4. manager_log=/var/log/mha/mha1/manager
  5. manager_workdir=/var/log/mha/mha1
  6. master_binlog_dir=/usr/local/mysql/data
  7. password=666666
  8. ping_interval=2
  9. repl_password=666666
  10. repl_user=rep
  11. ssh_user=root
  12. user=mha
  14. [server1]
  15. hostname=192.168.17.109
  16. port=3306
  17. candidate_master=1
  18. check_repl_delay=0
  19. #将MHA1的server补回来作为首选固定切换的MySQL主
  21. [server2]
  22. hostname=192.168.17.110
  23. port=3306
  25. [server3]
  26. hostname=192.168.17.111
  27. port=3306

STEP3:再次启动MHA进程

  1. [root@MHA3 ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
  2. [1] 1573
  3. [root@MHA3 ~]# ps -elf | grep perl | grep -v grep
  4. 0 S root       1573   1527  1  80   0 - 48831 hrtime 02:06 pts/0    00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover
  5. #成功启动

STEP4:停止MHA2服务器上的MySQL服务

  1. [root@MHA2 ~]# /etc/init.d/mysqld stop
  2. Shutting down MySQL..... SUCCESS!

STEP5:查看MHA3服务器上的MySQL主从同步状态

  1. [root@MHA3 ~]# mysql -uroot -p666666 -e "show slave status\G"
  2. Warning: Using a password on the command line interface can be insecure.
  3. *************************** 1. row ***************************
  4.                Slave_IO_State: Waiting for master to send event
  5.                   Master_Host: 192.168.17.109
  6. #MHA3的主自动换回了MHA1服务器的MySQL
  7.                   Master_User: rep
  8.                   Master_Port: 3306
  9.                 Connect_Retry: 60
  10.               Master_Log_File: mysql-bin.000006
  11.           Read_Master_Log_Pos: 447
  12.                Relay_Log_File: relay-bin.000002
  13.                 Relay_Log_Pos: 408
  14.         Relay_Master_Log_File: mysql-bin.000006
  15.              Slave_IO_Running: Yes
  16.             Slave_SQL_Running: Yes

STEP6:重启MHA2服务器的MySQL，重新配置MHA配置文件并启动MHA进程，恢复到初始状态

  1. #MHA2服务器
  2. [root@MHA2 ~]# /etc/init.d/mysqld start
  3. Starting MySQL. SUCCESS! 
  4. #MHA3服务器
  5. [root@MHA3 ~]# vim /etc/mha/mha1.cnf 
  6. [root@MHA3 ~]# cat /etc/mha/mha1.cnf
  7. [server default]
  8. manager_log=/var/log/mha/mha1/manager
  9. manager_workdir=/var/log/mha/mha1
  10. master_binlog_dir=/usr/local/mysql/data
  11. password=666666
  12. ping_interval=2
  13. repl_password=666666
  14. repl_user=rep
  15. ssh_user=root
  16. user=mha
  18. [server1]
  19. hostname=192.168.17.109
  20. port=3306
  22. [server2]
  23. candidate_master=1
  24. check_repl_delay=0
  25. hostname=192.168.17.110
  26. port=3306
  28. [server3]
  29. hostname=192.168.17.111
  30. port=3306
  31. [root@MHA3 ~]# nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
  32. [1] 1653

附录：源码安装MHA的方法

上面部署MHA我们用的是rpm方式，源码方式部署代码如下

  1. #MHA-Node部署
  2. [root@mysql-db01 ~]# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Params-Validate perl-CPAN perl-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
  3. [root@mysql-db01 ~]# tar xf mha4mysql-node-0.56.tar.gz -C /usr/src/
  4. [root@mysql-db01 ~]# cd /usr/src/mha4mysql-node-0.56/
  5. [root@mysql-db01 mha4mysql-node-0.56]# perl Makefile.PL
  6. [root@mysql-db01 mha4mysql-node-0.56]# make && make install
  8. #MHA-manager部署
  9. [root@mysql-db01 ~]# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Params-Validate perl-CPAN perl-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
  10. [root@mysql-db01 ~]# tar xf mha4mysql-manager-0.56.tar.gz -C /usr/src/
  11. [root@mysql-db01 ~]# cd /usr/src/mha4mysql-manager-0.56/
  12. [root@mysql-db01 mha4mysql-manager-0.56]# perl Makefile.PL
  13. [root@mysql-db01 mha4mysql-manager-0.56]# make && make install
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注