mysql mha 简书_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至于后台运行