知识为进步之母,而进步又为富强之源泉。这篇文章主要讲述Mysql集群高可用架构(MHA)相关的知识，希望能为你提供帮助。
Linux系统版本:[root@master ~]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)

mysql数据版本:  mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar

文章图片

角色分配:Master      10.40.42.103   (mha master)
Slave1     10.40.42.105   (mha node1)
Slave2   10.40.42.127   (mha node2)
安装mysql:  yum localinstall mysql-community-* -y
?配置一主多从:master主库/etc/my.cn的配置:?[root@master ~]# grep -v "^#" /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=1     # 服务ID
log_bin=/var/lib/mysql/mysql-bin   # 指定二进制日志路径
binlog_format=ROW     # 以行的方式保存二进制日志
skip-name-resolve   # 不将IP地址解析成名字
relay_log=relay-log   # 开启中继日志
Node1的/etc/my.cn配置:[root@node1 ~]#   grep -v "^#" /etc/my.cnf                
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
server-id=2
read-only
log_bin=/var/lib/mysql/mysql-bin
binlog_format=row
skip-name-resolve
relay_log_purge=0     # 默认是清除中继日志，选择不清除
relay_log=relay-log   # 开启中继日志
Node2的/etc/my.cn配置:[root@node2 ~]#   grep -v "^#" /etc/my.cnf                
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
server-id=3
read-only
skip-name-resolve
relay_log_purge=0 # 不清除中继日志
log_bin=/var/lib/mysql/mysql-bin
binlog_format=row
relay_log=relay-log #开启中继日志
master创建主从复制用户权限:mysql> grant replication   slave   on *.* to rep@\'10.40.42.%\' identified by \'1qaz@WSX\';         Query OK, 0 rows affected, 1 warning (0.11 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.07 sec)
 
文章图片


文章图片

Node1启动主从复制:mysql> CHANGE MASTER TO MASTER_HOST=\'10.40.42.103\',MASTER_USER=\'rep\',MASTER_PASSWORD=\'1qaz@WSX\',MASTER_PORT=3306,MASTER_LOG_FILE=\'mysql-bin.000001\',MASTER_LOG_POS=597;
Query OK, 0 rows affected, 2 warnings (0.24 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
 
文章图片

?Node2启动主从复制:?mysql> CHANGE MASTER TO MASTER_HOST=\'10.40.42.103\',MASTER_USER=\'rep\',MASTER_PASSWORD=\'1qaz@WSX\',MASTER_PORT=3306,MASTER_LOG_FILE=\'mysql-bin.000001\',MASTER_LOG_POS=597;
Query OK, 0 rows affected, 2 warnings (0.59 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
 
文章图片

查看node1和node2的主从状态:  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

文章图片

master节点再创建一个super权限的用户:登录三台数据库服务器，创建mhauser账号并授权，也可以直接使用root账户或其他有权限的已有用户
需要的权限：Super,select,create,insert,update,delete,drop,reload
 
这个账号主要提供给mah管理监控主从和自动选举等操作使用。
mysql> grant all privileges on *.* to \'mhauser\'@\'%\' identified by \'1qaz@WSX\';
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
?MHA的安装:下载mha软件:?
mha的安装依赖epel-release源,先安装epel-release源。
wget ??https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm??
wget ??https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm??
管理节点安装manger和node软件包(master主库)
Node节点安装node软件包(salve1和slave2)
master节点的安装:[root@master ~]# yum localinstall mha4mysql-* -y
 
文章图片

两台台node节点的安装:  yum localinstall mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
 
文章图片

MHA manager管理工具:  在manager节点安装完成后会生成一些管理工具，manager的主要管理工具有：
  masterha_check_ssh：检查MHA的SSH配置状况
  masterha_check_repl：检查MySQL复制状况
  masterha_manger：启动MHA
  masterha_check_status：检测当前MHA运行状态
  masterha_master_monitor：检测master是否宕机
  masterha_master_switch：控制故障转移（自动或者手动）
  masterha_conf_host：添加或删除配置的server信息
生成ssh秘钥:由于MHA manager通过SSH访问所有的node节点，各个node节点也同样通过SSH来相互发送不同的relay log 文件，所以要在每一个node和manager上配置SSH无密码登陆。
master和node各个节点之间相互无秘钥登录:[root@master ~]# ssh-keygen
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.42.103
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.42.105
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.42.127
 
文章图片

[root@node1 ~]#   ssh-keygen
# ssh-copy-id -i /root/.ssh/id_rsa.pub ??root@10.40.42.103??
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.42.105
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.42.127
 
文章图片

[root@node2 ~]#   ssh-keygen
[root@node2 ~]#   ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.42.103
[root@node2 ~]#   ssh-copy-id -i /root/.ssh/id_rsa.pub ??root@10.40.42.105??
[root@node2 ~]#   ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.42.127
 
文章图片

三台主机/etc/hosts增加解析:[root@master ~]# cat /etc/hosts  
127.0.0.1     localhost localhost.localdomain localhost4 localhost4.localdomain4
::1                 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.40.42.103       master
10.40.42.105       node1
10.40.42.127     node2
 
?MHA配置:
三台主机创建mha远程数据目录:?
[root@master ~]# mkdir /etc/mha/data -p
 
文章图片

创建mha.cnf作为mha的运行配置文件:[root@master mha]# cat mha.cnf
[server default]
manager_workdir=/etc/mha/
manager_log=/etc/mha/app1.log
master_binlog_dir=/var/lib/mysql
master_ip_failover_script=/usr/local/bin/master_ip_failover
#master_ip_online_change_script=/usr/local/bin/master_ip_online_change
user=mhauser
password=1qaz@WSX
ping_interval=1
remote_workdir=/etc/mha/data
repl_user=rep
repl_password=1qaz@WSX
ssh_user=root
 
[server1]
hostname=10.40.42.103
port=3306
candidate_master=1
 
[server2]
hostname=10.40.42.105
port=3306
candidate_master=1
check_repl_delay=0
 
[server3]
hostname=10.40.42.127
port=3306
check_repl_delay=0
no_master=1
检验  mha master和node之间免秘钥登录:[root@master mha]# masterha_check_ssh --conf /etc/mha/mha.cnf
【Mysql集群高可用架构(MHA)】