一、MySQL MHA架构介绍：
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，是一套优秀的作为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 Manager管理多组主从复制。
可以将MHA工作原理总结为如下：
（1）从宕机崩溃的master保存二进制日志事件（binlog events）; （2）识别含有最新更新的slave； （3）应用差异的中继日志（relay log）到其他的slave； （4）应用从master保存的二进制日志事件（binlog events）； （5）提升一个slave为新的master； （6）使其他的slave连接新的master进行复制；
二、MHA环境部署
- 环境规划
四台主机都关闭防火墙主机名 IP 节点信息 master 192.168.100.135 mysql主节点 slave1 192.168.100.136 mysql从节点 slave2 192.168.100.137 mysql从节点 manager 192.168.100.150 MHA manager节点
[root@master ~]# systemctl stop firewalld [root@master ~]# systemctl disable firewalld Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service. [root@master ~]# setenforce 0 [root@master ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config [root@master ~]# vi /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.100.135 master 192.168.100.136 slave1 192.168.100.137 slave2 192.168.100.150 manager ~ - 部署mysql主从（一主两从）
Ⅰ.mysql服务端安装mysql
①.卸载mariadb
[root@master ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@master ~]# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@master ~]# rpm -qa | grep mariadb
[root@master ~]#②.下载mysql软件包并解压
[root@master ~]# cd /usr/local/src/ ##软件包都放这
[root@master src]# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz ##如果显示没有wget命令安装即可 yum install -y wget
[root@master src]# ls
mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
使用scp命令将软件包传输到另外两台服务端上
[root@master src]# scp mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz root@192.168.100.136:/usr/local/src/
[root@master src]# scp mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz root@192.168.100.137:/usr/local/src/
[root@master src]# tar -zxvf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz ##3台都要解压③.安装mysql
[root@master src]# yum install -y ncurses-devel autoconf ##安装mysql所需依赖
[root@master src]# groupadd mysql
[root@master src]# useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql
[root@master src]# mkdir /data/mysql/data -p
[root@master src]# chown -R mysql:mysql /data/mysql
[root@master src]# mv mysql-5.7.32-linux-glibc2.12-x86_64 /usr/local/
[root@master src]# ln -s /usr/local/mysql-5.7.32-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@master src]# ll /usr/local/
总用量 4
drwxr-xr-x. 2 root root 6 8月 12 2015 bin
drwxr-xr-x. 2 root root 6 8月 12 2015 etc
drwxr-xr-x. 2 root root 6 8月 12 2015 games
drwxr-xr-x. 2 root root 6 8月 12 2015 include
drwxr-xr-x. 2 root root 6 8月 12 2015 lib
drwxr-xr-x. 2 root root 6 8月 12 2015 lib64
drwxr-xr-x. 2 root root 6 8月 12 2015 libexec
lrwxrwxrwx. 1 root root 47 6月 18 17:33 mysql -> /usr/local/mysql-5.7.32-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 4096 6月 18 17:28 mysql-5.7.32-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 8月 12 2015 sbin
drwxr-xr-x. 5 root root 46 6月 4 16:56 share
drwxr-xr-x. 2 root root 55 6月 18 17:33 src
[root@master src]# chown -R mysql.mysql /usr/local/mysql
[root@master src]# ④.配置
[root@master src]# vi /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
port = 3306
socket = /tmp/mysql.sock
log-error = error.log
slow_query_log_file = slow.log
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
lower_case_table_names =1
server_id=1 ##其他两台从服务器的 server_id分别设为server_id=2、server_id=3
gtid_mode=on
enforce_gtid_consistency=on
#binlog
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
#relay log
skip-slave-start=1
relay_log_purge=0 ##关闭定期清除。中继日志的清除是由SQL线程定期清除，若使用MHA则不让定期清除，因为mha做数据补齐时可能会用到中继日志。⑤.初始化mysql
[root@master ~]# /usr/local/mysql/bin/mysqld --initialize
[root@master ~]# grep 'temporary password' /data/mysql/data/error.log
2021-06-18T09:22:43.862711Z 1 [Note] A temporary password is generated for root@localhost: atrZqesj#3%; ##查看mysql密码⑥.复制启动脚本
[root@master ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql⑦.添加环境变量
[root@master ~]# echo "export PATH=/usr/local/mysql/bin:$PATH">>/etc/profile
[root@master ~]# source /etc/profile⑧.启动mysql
[root@master ~]# service mysql start
Starting MySQL... SUCCESS!
[root@master ~]# ps -ef |grep mysql ##查看进程
root 20545 1 0 17:24 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/master.pid
mysql 20883 20545 19 17:24 pts/2 00:00:02 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=error.log --open-files-limit=65535 --pid-file=/data/mysql/data/master.pid --socket=/tmp/mysql.sock --port=3306
root 20917 11158 0 17:25 pts/2 00:00:00 grep --color=auto mysql⑨.修改mysql密码
[root@master ~]# grep 'temporary password' /data/mysql/data/error.log
2021-06-18T09:22:43.862711Z 1 [Note] A temporary password is generated for root@localhost: atrZqesj#3%;
[root@master ~]# mysql -uroot -p"atrZqesj#3%;"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.32-log
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> alter user user() identified by '000000';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@master ~]# mysql -uroot -p000000
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.32-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> Ⅱ.配置主从
①.在主服务器上创建复制用户
mysql> grant replication slave on *.* to 'repl'@'192.168.100.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)②.在主服务器上备份全库，导入mysql主备份到从服务器上
[root@master ~]# mysqldump -uroot -p000000 --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /opt/mysqlbak_`date +%Y%m%d`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# ls /opt/
mysqlbak_20210618.sql
[root@master ~]# scp /opt/mysqlbak_20210618.sql root@192.168.100.136:/opt/
The authenticity of host '192.168.100.136 (192.168.100.136)' can't be established.
root@192.168.100.136's password:
mysqlbak_20210618.sql 100% 852KB 852.1KB/s 00:00
[root@master ~]# scp /opt/mysqlbak_20210618.sql root@192.168.100.137:/opt/
The authenticity of host '192.168.100.137 (192.168.100.137)' can't be established.
root@192.168.100.137's password:
mysqlbak_20210618.sql 100% 852KB 852.1KB/s 00:00
[root@slave1 ~]# ls /opt/
mysqlbak_20210618.sql
[root@slave1 ~]# mysql -uroot -p000000
mysql> source /opt/mysqlbak_20210618.sql
[root@slave2 ~]# ls /opt/
mysqlbak_20210618.sql
[root@slave2 ~]# mysql -uroot -p000000
mysql> source /opt/mysqlbak_20210618.sql③.配置从服务器上的连接
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.100.135',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.02 sec)④.启动slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G ##Slave_IO_Running与Slave_SQL_Running都是yes就代表从服务器配置成功。
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.135
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master-binlog.000002
Read_Master_Log_Pos: 709
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 930
Relay_Master_Log_File: master-binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes3. 授权MHA管理账户及配置节点间免密登录
①.授权MHA管理账户
[root@master ~]# mysql -uroot -p000000
mysql> grant all privileges on *.* to mha@'192.168.100.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)②.配置节点间免密登录
[root@master ~]# ssh-keygen ##一直回车
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
f2:89:42:ac:81:b0:73:fd:d6:58:3a:9f:d0:47:21:37 root@master
The key's randomart image is:
+--[ RSA 2048]----+
| |
| |
|. . E |
|.o o o o |
|+ o + . S . |
| o + . X o |
| . . O = . |
| o + o |
| o |
+-----------------+
[root@master ~]# cd /root/.ssh/
[root@master .ssh]# mv id_rsa.pub authorized_keys
[root@master .ssh]# scp -r /root/.ssh 192.168.100.136:/root
root@192.168.100.136's password:
known_hosts 100% 354 0.4KB/s 00:00
id_rsa 100% 1679 1.6KB/s 00:00
authorized_keys 100% 393 0.4KB/s 00:00
[root@master .ssh]# scp -r /root/.ssh 192.168.100.137:/root
root@192.168.100.137's password:
known_hosts 100% 354 0.4KB/s 00:00
id_rsa 100% 1679 1.6KB/s 00:00
authorized_keys 100% 393 0.4KB/s 00:00
[root@master .ssh]# scp -r /root/.ssh 192.168.100.150:/root
root@192.168.100.150's password:
known_hosts 100% 531 0.5KB/s 00:00
id_rsa 100% 1679 1.6KB/s 00:00
authorized_keys 100% 393 0.4KB/s 00:00
登录各节点验证
master： ssh 192.168.100.136
ssh 192.168.100.137
ssh 192.168.100.150
slave1： ssh 192.168.100.135
ssh 192.168.100.137
ssh 192.168.100.150
slave2： ssh 192.168.100.135
ssh 192.168.100.136
ssh 192.168.100.150
manager：ssh 192.168.100.135
ssh 192.168.100.136
ssh 192.168.100.1374 . 在mysql服务节点安装node
①.在安装前先安装依赖软件
yum install -y perl-DBD-MySQL②.下载安装
[root@master src]# wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@master src]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
准备中... ################################# [100%]
正在升级/安装...
1:mha4mysql-node-0.58-0.el7.centos ################################# [100%]
③.创建命令软链接（mha调用mysql命令默认在/usr/bin下面，不做此步mha会报错）
[root@master src]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
[root@master src]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog5 . 在管理节点（manager）上安装manage
①.安装必须软件
[root@manager ~]# yum install -y epel-release
[root@manager ~]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager②.安装mha4mysql-node，mha4mysql-manager依赖于mha2mysql-node
[root@manager src]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
准备中... ################################# [100%]
正在升级/安装...
1:mha4mysql-node-0.58-0.el7.centos ################################# [100%]③.安装mha4mysql-manager
[root@manager src]# wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[root@manager src]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
准备中... ################################# [100%]
正在升级/安装...
1:mha4mysql-manager-0.58-0.el7.cent################################# [100%]6 . 配置MHA
①.配置全局文件
[root@manager ~]# mkdir -p /masterha/manager
[root@manager ~]# vi /etc/masterha_default.cnf
manager_workdir=/masterha/manager
remote_workdir=/tmp②.对主从复制的配置文件app1
[root@manager ~]# mkdir -p /masterha/app1
[root@manager ~]# vi /masterha/app1.cnf
[server default]
manager_log=/masterha/app1/manager.log
master_binlog_dir=/data/mysql/data
user=mha
password=123456
repl_user=repl
repl_password=123456
ssh_user=root
ping_interval=2
[server1]
hostname=192.168.100.135
port=3306
[server2]
hostname=192.168.100.136
port=3306
candidate_master=1
[server3]
hostname=192.168.100.137
port=3306③.在mha上测试ssh连通性
[root@manager ~]# masterha_check_ssh --conf=/masterha/app1.cnf
Fri Jun 18 21:58:18 2021 - [info] All SSH connection tests passed successfully.④.在mha上检查主从复制状态
[root@manager ~]# masterha_check_repl --conf=/masterha/app1.cnf
MySQL Replication Health is OK.7 . 启动MHA
[root@manager ~]# nohup masterha_manager --conf=/masterha/app1.cnf > /masterha/app1/manager.log 2>&1 &
[1] 11114查看日志
[root@manager ~]# tail -f /masterha/app1/manager.log
Fri Jun 18 21:59:22 2021 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..8 . MHA自动故障转移
模拟db1关机，查看mha是否有故障转移到db2上，同时查看mha上的manager.log。发现主已经转移到db2上了。
在master上
[root@master .ssh]# poweroff查看manager的上的manager.log
[root@manager ~]# tail -f /masterha/app1/manager.log
app1: MySQL Master failover 192.168.100.135(192.168.100.135:3306) to 192.168.100.136(192.168.100.136:3306) succeeded
Master 192.168.100.135(192.168.100.135:3306) is down!
Check MHA Manager logs at manager:/masterha/app1/manager.log for details.
Started automated(non-interactive) failover.
Selected 192.168.100.136(192.168.100.136:3306) as a new master.
192.168.100.136(192.168.100.136:3306): OK: Applying all logs succeeded.
192.168.100.137(192.168.100.137:3306): OK: Slave started, replicating from 192.168.100.136(192.168.100.136:3306)
192.168.100.136(192.168.100.136:3306): Resetting slave info succeeded.
Master failover to 192.168.100.136(192.168.100.136:3306) completed successfully.在slave1上查看
mysql> show master status\G
*************************** 1. row ***************************
File: master-binlog.000002
Position: 852987
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: bc48acb5-d016-11eb-a335-000c297c8e6f:1-4,
efaee8c2-d01f-11eb-8fa1-000c290ca0b8:1-123
1 row in set (0.00 sec)在slave2上查看，发现Master_Host已变为192.168.100.136，说明主已转移成功
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.136
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master-binlog.000002
Read_Master_Log_Pos: 852987
Relay_Log_File: slave2-relay-bin.000002
Relay_Log_Pos: 852277
Relay_Master_Log_File: master-binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
扫一扫
487
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
