MHA实现数据库高可用
下载地址：
| 主机名 | IP | 角色 |
|---|---|---|
| mysql-master | 192.168.139.31 | 主MySQL |
| mysql-slave01 | 192.168.139.32 | 从MySQL |
| mysql-slave02 | 192.168.139.33 | 从MySQL |
| MHA | 192.168.139.34 | MHA管理端 |
1）各节点部署MHA
1.1）安装MHA
`mha节点`
[root@mha ~]# ll
total 116
-rw-r--r-- 1 root root 81024 Dec 30 21:11 mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
-rw-r--r-- 1 root root 36328 Dec 30 21:11 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mha ~]# yum install -y mha*.rpm
`master节点`
[root@mysql-master ~]# ll
total 40
-rw-------. 1 root root 1717 Dec 2 23:45 anaconda-ks.cfg
-rw-r--r-- 1 root root 36328 Dec 30 21:15 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mysql-master ~]# yum install -y mha*.rpm
`slave01节点`
[root@mysql-slave01 ~]# ll
total 40
-rw-------. 1 root root 1717 Dec 2 23:45 anaconda-ks.cfg
-rw-r--r-- 1 root root 36328 Dec 30 21:15 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mysql-slave01 ~]# yum install -y mha*.rpm
`slave02节点`
[root@mysql-slave02 ~]# ll
total 40
-rw-------. 1 root root 1717 Dec 2 23:45 anaconda-ks.cfg
-rw-r--r-- 1 root root 36328 Dec 30 21:16 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mysql-slave02 ~]# yum install -y mha*.rpm
1.2）配置管理节点MHA
[root@mha ~]# mkdir /etc/mastermha
[root@mha ~]# vim /etc/mastermha/app1.cnf
[server default]
user=mhauser # 用于远程连接MySQL所有节点的用户,需要有管理员的权限
password=stangj
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root # 用于实现远程ssh基于KEY的连接,访问二进制日志
repl_user=repluser # 主从复制的用户信息
repl_password=stj123
ping_interval=1 # 健康性检查的时间间隔
[server1]
hostname=192.168.139.31
candidate_master=1
[server2]
hostname=192.168.139.32
candidate_master=1 # 优先为新的master
[server3]
hostname=192.168.139.33
1.3）配置ssh免密
[root@mha ~]# ssh-keygen -t rsa -f /root/.ssh/id_rsa -N ''
[root@mha ~]# rm -f /root/.ssh/known_hosts
[root@mha ~]# cp /root/.ssh/id_rsa.pub /root/.ssh/authorized_keys
[root@mha ~]# for i in 192.168.139.{31..33} ;do scp -r /root/.ssh $i:/root/ ; done
2）主从的搭建
2.1）配置主
[root@mysql-master ~]# cat /etc/my.cnf
[mysqld]
server-id = 31 # 重要
log-bin # 重要
skip_name_resolve=1 # 重要
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
[root@mysql-master ~]# systemctl enable --now mariadb.service
[root@mysql-master ~]# mysql
# 查看二进制位置
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
# 创建账号
MariaDB [(none)]> grant replication slave on *.* to repluser@'%' identified by 'stj123';
# 创建一个管理员账号
MariaDB [(none)]> grant all on *.* to mhauser@'%' identified by 'stangj';
# 刷新权限
MariaDB [(none)]> FLUSH PRIVILEGES;
2.2）配置从
# CHANGE MASTER TO
# MASTER_HOST='192.168.139.31',
# MASTER_USER='repluser',
# MASTER_PASSWORD='stj123',
# MASTER_PORT=3306,
# MASTER_LOG_FILE='mariadb-bin.000001',
# MASTER_LOG_POS=245;
`slave01配置`
[root@mysql-slave01 ~]# grep -E '^[^#]' /etc/my.cnf
[mysqld]
server-id = 32 # 重要
log-bin # 重要
read_only # 重要
relay_log_purge=0 # 重要
skip_name_resolve=1 # 重要
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
`slave02配置`
[root@mysql-slave02 ~]# grep -E '^[^#]' /etc/my.cnf
[mysqld]
server-id = 33 # 重要
log-bin # 重要
read_only # 重要
relay_log_purge=0 # 重要
skip_name_resolve=1 # 重要
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d
`启动服务slave01和slave02都要配置`
[root@mysql-slave01/slave02 ~]# systemctl enable --now mariadb.service
[root@mysql-slave01/slave02 ~]# mysql
# 连接同步主mysql
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.139.31',
MASTER_USER='repluser',
MASTER_PASSWORD='stj123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;
# 启动服务
MariaDB [(none)]> start slave;
2.3）查看主从是否配置成功
# 验证主从状态
MariaDB [(none)]> show slave status\G
...............................
...............................
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 这两个值为YES则为主从配置成功
...............................
...............................
3）MHA验证与启动
3.1）检查Mha的环境
#检查环境
[root@mha ~]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf
..........
Sat Dec 30 22:03:13 2023 - [debug] ok.
Sat Dec 30 22:03:14 2023 - [info] All SSH connection tests passed successfully.
[root@mha ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
.............
Sat Dec 30 23:12:40 2023 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.139.33 --slave_ip=192.168.139.33 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.68-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Sat Dec 30 23:12:40 2023 - [info] Connecting to root@192.168.139.33(192.168.139.33:22)..
Creating directory /data/mastermha/app1/.. done.
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000003
Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000003
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Sat Dec 30 23:12:41 2023 - [info] Slaves settings check done.
Sat Dec 30 23:12:41 2023 - [info]
192.168.139.31(192.168.139.31:3306) (current master)
+--192.168.139.32(192.168.139.32:3306)
+--192.168.139.33(192.168.139.33:3306)
Sat Dec 30 23:12:41 2023 - [info] Checking replication health on 192.168.139.32..
Sat Dec 30 23:12:41 2023 - [info] ok.
Sat Dec 30 23:12:41 2023 - [info] Checking replication health on 192.168.139.33..
Sat Dec 30 23:12:41 2023 - [info] ok.
Sat Dec 30 23:12:41 2023 - [warning] master_ip_failover_script is not defined.
Sat Dec 30 23:12:41 2023 - [warning] shutdown_script is not defined.
Sat Dec 30 23:12:41 2023 - [info] Got exit code 0 (Not master dead).
3.2）启动MHA
# 启动服务
[root@mha ~]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null
# 查看日志
[root@mha ~]# cat /data/mastermha/app1/manager.log
4）验证服务
4.1）到主master人为造一些数据
[root@mysql-master ~]# mysql
MariaDB [(none)]> create database hellodb;
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
gender ENUM('M','F') default 'M'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
MariaDB [hellodb]> insert student (name,age)values('xiaoming',20);
MariaDB [hellodb]> insert student (name,age)values('wangwu',36);
MariaDB [hellodb]> insert student (name,age)values('tanghao',18);
4.2）人为让master故障验证
`关闭master的数据库`
[root@mysql-master ~]# systemctl stop mariadb.service
# mha日志输出
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mariadb-bin.000001
Sat Dec 30 23:13:43 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Dec 30 23:13:43 2023 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sat Dec 30 23:13:43 2023 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
4.3）验证slave01是否成为了新的主
[root@mysql-slave01 ~]# mysql
MariaDB [(none)]> select * from hellodb.student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 1 | xiaoming | 20 | M |
| 2 | wangwu | 36 | M |
| 3 | tanghao | 18 | M |
+----+----------+------+--------+
MariaDB [(none)]> show slave status\G
Empty set (0.00 sec) # 可以看出来 slave01 不是master的从节点了
[root@mysql-slave02 ~]# mysql
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.139.32 # 可以看出来slave02的主节点是slave01
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
以上则是MHA的高可用部署实现