8.基于MHA的Mysql高可用集群

1. 架构图

MHA集群.jpg

NOTE: 1.一定要做时间同步 2.一定要关闭防火墙，selinux，清空iptables

2. 配置主从服务

2.1 主服务器配置

[root@mysql_master ~]#  yum install mariadb-server -y
[root@mysql_master ~]#  vim /etc/my.cnf
    [mysqld]
    server-id=138
    log-bin
    skip_name-resolve　　　配置MHA集群时一定要配置此项

[root@mysql_master ~]# mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* to stone@'192.168.177.%' identified by 'stone';
Query OK, 0 rows affected (0.00 sec)  此用户用户向从服务器传送bin-log日志

MariaDB [(none)]> GRANT ALL ON *.* to mhauser@'192.168.177.%' identified by 'mhauser';
Query OK, 0 rows affected (0.00 sec)     此为管理用户,当主服务器宕机时提升从为新的主节点
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       539 |
+--------------------+-----------+
1 row in set (0.00 sec)  默认是0001 245开始，从这个文件的245开始

从服务器配置

[root@mysql_slave1 ~]# yum install mariadb-server -y
[root@mysql_slave1 ~]# vim /etc/my.cnf
  [mysqld]
  server-id=139
  log-bin
  read-only
  skip_name_resolve
  relay_log_purge=0
[root@mysql_slave1 ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.177.138',
    ->   MASTER_USER='stone',
    ->   MASTER_PASSWORD='stone',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000001',
    ->   MASTER_LOG_POS=245;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
MariaDB [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

[root@mysql_slave2 ~]# yum install mariadb-server -y
[root@mysql_slave2 ~]# vim /etc/my.cnf
  [mysqld]
  server-id=141
  log-bin
  read-only
  skip_name_resolve
  relay_log_purge=0
[root@mysql_slave2 ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.177.138',
    ->   MASTER_USER='stone',
    ->   MASTER_PASSWORD='stone',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000001',
    ->   MASTER_LOG_POS=245;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
MariaDB [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

2.2 实现节点之间ssh key验证

192.168.177.137:
[root@manage_mha ~]# ssh-keygen
[root@manage_mha ~]# ls -a .ssh
[root@manage_mha ~]# ssh-copy-id 192.168.177.137
[root@manage_mha ~]# cd .ssh
[root@manage_mha ~]# scp -r .ssh 192.168.177.138:/root/
[root@manage_mha ~]# scp -r .ssh 192.168.177.139:/root/
[root@manage_mha ~]# ssh 192.168.177.139
Last login: Tue Jul  7 02:43:30 2020 from 192.168.177.1
[root@mysql_slave1 ~]# 验证ssh key配置成功
[root@manage_mha ~]# scp -r .ssh 192.168.177.141:/root/

2.3 MHA配置和启动

1.MHA安装manager和node包
2.其余节点安装node即可
3.相关rpm包如下,用yum安装会自动解决以来关系
链接：https://pan.baidu.com/s/1b6Xls8gFgoAJ77KVMpQ3SA 
提取码：vq19

[root@manage_mha ~]# ll
-rw-r--r--  1 root root 87119 Jan  1  2020 mha4mysql-manager-0.56-0.el6.noarch.rpm
-rw-r--r--  1 root root 36326 Jan  1  2020 mha4mysql-node-0.56-0.el6.noarch.rpm
[root@manage_mha ~]# yum install mha*.rpm
[root@manage_mha ~]# mkdir /etc/mha/
[root@manage_mha ~]# vim /etc/mha/app1.cnf
    [server default]
    user=mhauser
    password=mhauser
    manager_workdir=/data/mastermha/app1
    manager_log=/data/mastermha/app1/manager.log
    remote_workdir=/data/mastermha/app1
    ssh_user=root
    repl_user=stone
    repl_password=stone
    ping_interval=1
    [server1]
    hostname=192.168.177.138
    candidate_master=1
    [server2]
    hostname=192.168.177.139
    [server3]
    hostname=192.168.177.141
    candidate_master=1

2.4 校验配置文件启动MHA

[root@manage_mha ~]# masterha_check_ssh -conf=/etc/mha/app1.cnf
[root@manage_mha ~]# masterha_check_repl -conf=/etc/mha/app1.cnf
[root@manage_mha ~]# masterha_manager -conf=/etc/mha/app1.cnf
Tue Jul  7 00:50:27 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Jul  7 00:50:27 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Tue Jul  7 00:50:27 2020 - [info] Reading server configuration from /etc/mha/app1.cnf..

2.5 校验MHA集群效果

将主服务器192.168.177.138宕机

[root@manage_mha ~]# tail /data/mastermha/app1/manager.log -f 
app1: MySQL Master failover 192.168.177.138(192.168.177.138:3306) to 192.168.177.141(192.168.177.141:3306) succeeded
Master 192.168.177.138(192.168.177.138:3306) is down!
Check MHA Manager logs at manage_mha:/data/mastermha/app1/manager.log for details.

到192.168.177.141上登陆查看
[root@mysql_slave2 ~]# mysql 
MariaDB [(none)]> show slave status;
Empty set (0.00 sec)
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

到另一从服务器上192.168.177.139查看
[root@mysql_slave1 ~]# mysql
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.177.141
                  Master_User: stone
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 326
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 612
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

NOTE: 1.虽192.168.177.141晋升为主服务器,只是用户mhauser修改,其配置文件仍旧为read-only,需要修改配置文件才可以彻底生效. 2.需要及时处理宕机的主服务器,并尽快将其配置为新主的从服务器 3.实际应用中可配合screen命令,将管理节点MHA至于后台运行