mysql高可用集群之MHA详细搭建过程

一、MHA简介

1、MHA

是一套优秀的实现mysql高可用的解决方案；

数据库的自动故障切换操作能做到在0--30秒之内；

MHA能确保在故障切换过程中保证数据的一致性，以达到真正意义上的高可用。

2、MHA的组成

MHA Mangaer(管理节点)：可以单独部署在一台独立的机器上，管理其他节点，也可以部署在一台slave节点上，能管理多套mysql集群。

MHA Node(数据节点)：运行在每台mysql服务器上。

3、MHA集群架构

如下图所示，一个MHA Manager管理了三套mysql集群。

4、MHA工作过程

Manager定时探测集群中的master节点，当master故障时，Manager自动将拥有最新数据的slave提升为新的master

关键点：

从岩机崩溃的master保存二进制日志事件

识别最新更新的slave

应用中继日志(relay log) 到其他的slave

应用从master保存的二进制日志事件

提升一个slave为新的master

其他的slave连接最新的master

5、详细搭建过程

说明：本次搭建使用4台机器，一台主库，一台备用主库，一台从库，一台部署Manager管理节点，系统采用centos7.4

用途	IP地址	主机名
主库	192.168.2.100 vip 192.168.2.101 设置在eth0:0上	vmctl2
备用主库	192.168.2.150	vmctl3
从库	192.168.2.200	vmctl4
MHA Manager	192.168.2.210	vmctl6

 

1、主库 192.168.2.100 操作

     安装数据库mysql-5.7.28

#用逻辑卷来做安装mysql 存放数据的目录，方便后期空间的扩容
[root@vmctl2 ~]# lsblk
NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda      8:0    0   10G  0 disk 
├─sda1   8:1    0  296M  0 part /boot
├─sda2   8:2    0  8.7G  0 part /
└─sda3   8:3    0    1G  0 part [SWAP]
sdb      8:16   0    6G  0 disk 
sr0     11:0    1 1024M  0 rom  
[root@vmctl2 ~]# vgcreate  centos /dev/sdb  #创建卷组centos
  Physical volume "/dev/sdb" successfully created.
  Volume group "centos" successfully created
[root@vmctl2 ~]# vgs   #查看卷组
  VG     #PV #LV #SN Attr   VSize  VFree 
  centos   1   0   0 wz--n- <6.00g <6.00g

[root@vmctl2 ~]# lvcreate  -L 4G -n data centos #创建逻辑卷data，大小是4G
  Logical volume "data" created.
[root@vmctl2 ~]# lvs #查看逻辑卷
  LV   VG     Attr       LSize Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  data centos -wi-a----- 4.00g 
                                                      
[root@vmctl2 ~]# mkfs.ext4 /dev/centos/data #格式化逻辑卷data为ext4文件系统
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
262144 inodes, 1048576 blocks
52428 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=1073741824
32 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736

Allocating group tables: done                            
Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done 

[root@vmctl2 ~]# 
[root@vmctl2 ~]# blkid #查看UUID和文件系统
/dev/sda1: UUID="0e493985-9b58-47c4-8200-95a8fa18a31b" TYPE="xfs" 
/dev/sda2: UUID="88da310c-65e5-4823-bf60-4f3cdaffbb00" TYPE="xfs" 
/dev/sda3: UUID="0c337c1f-cf74-42bc-ab22-59c9e78c8e52" TYPE="swap" 
/dev/sdb: UUID="5cjJ8W-Zhg6-1mOy-2kmf-bvhY-xOL5-mdGtR8" TYPE="LVM2_member" 
/dev/mapper/centos-data: UUID="bb752efc-134f-4bee-9598-8215d0b6eb4d" TYPE="ext4" 

[root@vmctl2 ~]# mkdir -p /data/mysql  #创建mysql安装目录
[root@vmctl2 ~]# useradd  -s /sbin/nologin mysql #创建mysql用户，且不能登录系统
[root@vmctl2 ~]# chown -R mysql:mysql /data  #安装目录的所有者，所属组设置为mysql用户
[root@vmctl2 ~]# ls -ld /data/
drwxr-xr-x 3 mysql mysql 19 Jun 30 04:05 /data/
[root@vmctl2 ~]# ls -ld /data/mysql/
drwxr-xr-x 2 mysql mysql 6 Jun 30 04:05 /data/mysql/

[root@vmctl2 ~]# vim /etc/fstab #修改配置文件，永久挂载
/dev/centos/data        /data/mysql     ext4    defaults        0       0
[root@vmctl2 ~]# lsblk
NAME          MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda             8:0    0   10G  0 disk 
├─sda1          8:1    0  296M  0 part /boot
├─sda2          8:2    0  8.7G  0 part /
└─sda3          8:3    0    1G  0 part [SWAP]
sdb             8:16   0    6G  0 disk 
└─centos-data 253:0    0    4G  0 lvm  /data/mysql
sr0            11:0    1 1024M  0 rom 

[root@vmctl2 bin]# ls /data/mysql/
lost+found
[root@vmctl2 bin]# rm -rf /data/mysql/*

#安装mysql以主库为例
#卸载mariadb相关软件包
[root@vmctl2 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
[root@vmctl2 ~]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
[root@vmctl2 ~]# rpm -qa | grep mariadb

[root@vmctl2 mnt]# ls
mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@vmctl2 mnt]# tar -xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz  -C ./  #解压按装包

[root@vmctl2 mnt]# mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.28 #修改名称并移动到/usr/local/下

[root@vmctl2 mnt]# vim /etc/my.cnf  #创建编辑mysql配置文件
[mysqld]
user=mysql
basedir=/usr/local/mysql-5.7.28
datadir=/data/mysql
socket/tmp/mysql.sock #必须放在tmp目录下，否则需要建软连接到tmp
log-error=/data/mysql/mysql-err.log
pid-file=/data/mysql/mysql.pid

#开启binlog日志，且设置成mixed方式
server_id=100
log-bin=master100
binlog-format="mixed"

#开启半同步复制
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

#初始化
[root@vmctl2 mnt]# cd /usr/local/mysql-5.7.28/bin/
[root@vmctl2 bin]# ./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql-5.7.28 --datadir=/data/mysql --user=mysql --initialize

#查看mysql日志，获取密码root@localhost: oW;?a(L:<4gK
[root@vmctl2 bin]# cat /data/mysql/mysql-err.log 
2020-06-30T11:39:44.108354Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-30T11:39:44.298033Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-30T11:39:44.348095Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-30T11:39:44.411347Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 64b543b9-bac6-11ea-94ca-000c29e70d43.
2020-06-30T11:39:44.412375Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-06-30T11:39:45.223278Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-30T11:39:45.351193Z 1 [Note] A temporary password is generated for root@localhost: oW;?a(L:<4gK

#启动mysql
[root@vmctl2 bin]# cp -r /usr/local/mysql-5.7.28/support-files/mysql.server /etc/init.d/mysql
[root@vmctl2 bin]# service mysql start
Starting MySQL. SUCCESS! 

#建软连接方便命令行使用mysql命令
[root@vmctl2 bin]# ln -s /usr/local/mysql-5.7.28/bin/mysql /sbin/mysql

#登录mysql，首次登录强制让改密码。
[root@vmctl2 ~]# mysql  -uroot -p'oW;?a(L:<4gK'
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 2
Server version: 5.7.28-log

Copyright (c) 2000, 2019, 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> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (10.01 sec)

#查看binlog日志是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /data/mysql/master100       |
| log_bin_index                   | /data/mysql/master100.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)
#或者在mysql安装目录查看是否有binlog日志文件 master100.*
[root@vmctl2 ~]# ls /data/mysql/
auto.cnf         ib_buffer_pool  master100.000001  mysql.pid           server-key.pem
ca-key.pem       ibdata1         master100.000002  performance_schema  sys
ca.pem           ib_logfile0     master100.index   private_key.pem
client-cert.pem  ib_logfile1     mysql             public_key.pem
client-key.pem   ibtmp1          mysql-err.log     server-cert.pem
[root@vmctl2 ~]# 

#查看binlog文件列表
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| master100.000001 |       177 |
| master100.000002 |       398 |
+------------------+-----------+
2 rows in set (0.00 sec)

#修改root用户的host字段，实现root用户可以在任意机器上登录数据库
mysql> update  mysql.user set host='%' where user=root;
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| repluser      | %         | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

 

2、备用主库192.168.2.200 操作

     数据库配置

#数据库配置基本和192.168.2.100相同
#不同点，binlog日志配置不同，server_id不能相同，日志名称log-bin不能相同
[root@vmctl3 mysql-5.7.28]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql-5.7.28
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql-err.log
pid-file=/data/mysql/mysql.pid

#开启binlog日志，且设置成mixed方式
server_id=200
log-bin=master200
binlog-format="mixed"

#开启半同步复制
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1


#修改root用户的host字段，实现root用户可以在任意机器上登录数据库
mys