一、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