MySQL高可用之MHA架构部署

1.MHA简介

    MHA（Master High Availability Manager and tools for MySQL）目前在MySQL高可用方面是一个相对成熟的解决方案，它是由日本人youshimaton采用Perl语言编写的一个脚本管理工具。目前MHA主要支持一主多从的架构，要搭建MHA，要求一个复制集群必须最少有3台数据库服务器，一主二从，即一台充当Master，一台充当备用Master，另一台充当从库。
    MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据库节点），MHA Manager 可以单独部署在一台独立的机器上管理多个 master-slave 集群，也可以部署在一台 slave 节点上。MHA Node 运行在每台 MySQL 服务器上，MHA Manager 会定时探测集群中的 master 节点，当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master，然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明

2.架构规划

角色                     ip地址            主机名            server_id                 类型
Monitor host            192.168.1.55    MHA-Monitor           -                      监控复制组
Master                  192.168.1.56    MHA-Master            1                      写入（主）
Candicate master        192.168.1.57    MHA-Slave1            2                      写入（主库的备用）
Slave                   192.168.1.58    MHA-Slave2            3                      写入

hostname修改方法：
vi /etc/hostname # 编辑配置文件永久生效

VIP:绑定到主库 192.168.1.54，主要目的是切服务器
操作系统为：Centos7.3

3.安装MySQL8

注：以下node 3个节点同时操作

1.用户及组
groupadd mysql
useradd -r -g mysql mysql

2.解压缩安装包
tar -xf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
ln -s /usr/local/mysql-8.0.19-linux-glibc2.12-x86_64 /usr/local/mysql8019
ln -s /usr/local/mysql8019 /usr/local/mysql
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/bashrc 
source /etc/bashrc
chown -R mysql.mysql /usr/local/mysql-8.0.19-linux-glibc2.12-x86_64

3.在线yum配置
yum install -y net-tools
yum install -y libtinfo*
yum -y install numactl
yum -y install libaio*
yum -y install perl perl-devel
yum -y install autoconf

4.mysql初始化
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data


5.启动Mysql自启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
chkconfig --level 345 mysqld on
systemctl restart mysqld
systemctl status mysqld

4.GTID同步

1. 配置参数文件
【Master1】
cat > /etc/my.cnf <<"EOF"
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 803306131
log-bin =
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
log-slave-updates=1
skip-name-resolve
log_timestamps = SYSTEM
#default-time-zone = '+8:00'
auto-increment-increment=1
auto-increment-offset=1
gtid-mode=ON
enforce-gtid-consistency=on
report_host=192.168.1.56
EOF



【Slave1】
cat > /etc/my.cnf <<"EOF"
#S1
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 803306132
log-bin =
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
skip-name-resolve
log_timestamps = SYSTEM
#default-time-zone = '+8:00'
gtid-mode=ON
enforce-gtid-consistency=ON
report_host=192.168.1.57
EOF

【Slave2】
cat > /etc/my.cnf <<"EOF"
#S2
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=
server-id = 803306133
log-bin =
binlog_format=row
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
skip-name-resolve
log_timestamps = SYSTEM
#default-time-zone = '+8:00'
gtid-mode=ON
enforce-gtid-consistency=ON
report_host=192.168.1.58
EOF


2.主从同步
#主库操作
mysql> create user repl@'%' identified with mysql_native_password by 'root';
mysql> grant replication slave on *.* to repl@'%' with grant option;
mysql> create user mha@'%' identified with mysql_native_password by 'root';
mysql> grant all on *.* to 'mha' @'%' with grant option;
mysql> flush privileges;
mysql> select user,host,grant_priv,Super_priv,password_last_changed from mysql.user;

mysql> show master status \G;
mysql> show slave hosts;
mysql> select @@server_id,@@server_uuid;

#从库操作
change master to
master_host='192.168.1.56',master_port=3306,master_user='repl',
master_password='root',master_auto_position=1;

mysql> start slave;
mysql> show slave status \G;
mysql> select user,host,grant_priv,Super_priv,password_last_changed from mysql.user;

启动从库会报server_uuid相同导致同步失败，修改即可
/usr/local/mysql/data 中auto.cnf修改即可
[root@mha-slave1 ~]# systemctl restart mysqld

Slave failed to initialize relay log info structure from the repository报错处理
mysql> reset slave;
mysql> start slave;

3.校验主从复制
#主库执行
mysql> create database mesdb;
mysql> use mesdb
mysql> create table mytb1(id int,name varchar(30));
mysql> insert into mytb1 values(1,@@hostname);
mysql> select * from mesdb.mytb1;

4.Master绑定VIP
#在主库上执行添加VIP的过程（第一次手动添加,后续启动切换）
[root@MHA-Master ~]# ifconfig
[root@MHA-Master ~]# /sbin/ifconfig ens33:1 192.168.1.54

5.互信设置

1.安装SSH服务
yum install openssh-server
yum -y install openssh-clients
[root@MHA-Master /]# systemctl status sshd
[root@MHA-Master /]# systemctl start sshd
[root@MHA-Master /]# systemctl enable sshd


注：4台机器互相免密码登录，注意，自己跟自己也要配免密码登录
(1)在Manager上配置到所有的Node节点的无密码验证
[root@MHA-Monitor /]# ssh-keygen -t rsa
[root@MHA-Monitor /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.56"
[root@MHA-Monitor /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.57"
[root@MHA-Monitor /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.58"
[root@MHA-Monitor /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.55"

(2)在Master上配置到所有的Node节点的无密码验证
[root@MHA-Master ~]# ssh-keygen -t rsa
[root@MHA-Master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.55"
[root@MHA-Master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.56"
[root@MHA-Master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.57"
[root@MHA-Master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.58"

(3)在Candicate Master上配置到所有的Node节点的无密码验证
[root@MHA-Slave1 /]# ssh-keygen -t rsa
[root@MHA-Slave1 /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.55"
[root@MHA-Slave1 /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.56"
[root@MHA-Slave1 /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.57"
[root@MHA-Slave1 /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.58"

(4)在Slave2上配置到所有的Node节点的无密码验证
[root@MHA-Slave2 ~]# ssh-keygen -t rsa
[root@MHA-Slave2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.55"
[root@MHA-Slave2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.56"
[root@MHA-Slave2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.57"
[root@MHA-Slave2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "root@192.168.1.58"

2.测试SSH
ssh 192.168.1.55 date
ssh 192.168.1.56 date
ssh 192.168.1.57 date
ssh 192.168.1.58 date

6.安装MHA软件

https://github.com/yoshinorim/mha4mysql-manager/releases
先安装Node，再安装Manager

1.安装MHA Node
在所有的MySQL服务器和MHA Manager主机上都安装MHA Node
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN

tar xf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58
perl Makefile.PL

yum install make -y
make && make install

2.装MHA Manager
MHA Manager中主要几个管理员的命令行工具，也是依赖一些Perl模块的，只在管理节点安装即可

tar zxf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58
perl Makefile.PL    --先NO，然后再YES
make && make install

7.配置MHA

1 在Manager上创建配置文件
注意：
1、由于脚本中并没有master_ip_failover脚本，启动时会报错，请到mha4mysqlmanager-0.5X/samples/scripts下拷贝对应脚本到指定位置。或注释掉master_ip_failover_script也可以。
2、MHA可以监控多个主从的集群，每个集群的配置文件可以用名字区分，因为这里只有一个集群，因此只有mha.cnf一个文件！

[root@MHA-Monitor ~]# more /etc/hosts
192.168.1.55 MHA-Monitor
192.168.1.56 MHA-Master
192.168.1.57 MHA-Slave1
192.168.1.58 MHA-Slave2

[root@MHA-Monitor /]# mkdir -p /usr/local/mha
[root@MHA-Monitor /]# mkdir -p /etc/mha

cat > /etc/mha/mha.cnf <<"EOF"
[server default]
manager_workdir=/usr/local/mha
manager_log=/usr/local/mha/manager_running.log
master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover
master_ip_online_change_script=/usr/local/mha/scripts/master_ip_online_change
ping_interval=1
secondary_check_script=/usr/local/bin/masterha_secondary_check -s MHA-Slave1 -s MHA-Slave2 --user=root --master_host=MHA-Master --master_ip=192.168.1.55 --master_port=3306
ssh_user=root
user=mha
password=root
repl_user=repl
repl_password=root

[server1]
hostname=192.168.1.56
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.1.57
port=3306

[server3]
hostname=192.168.1.58
port=3306
EOF

8.MHA服务启动

1.检查SSH情况
[root@MHA-Monitor ~]# masterha_check_ssh --conf=/etc/mha/mha.cnf
结果“All SSH connection tests passed successfully.”表示MHA的3个数据节点之间的SSH是正常的

2.检查复制情况
[root@MHA-Monitor ~]# masterha_check_repl --conf=/etc/mha/mha.cnf
“MySQL Replication Health is OK.”表示1主2从的架构目前是正常的

3.检查MHA状态
[root@MHA-Monitor /]# masterha_check_status --conf=/etc/mha/mha.cnf
mha is stopped(2:NOT_RUNNING).
注意：如果正常，会显示“PING_OK"，否则会显示“NOT_RUNNING"，这代表MHA监控没有开启

4.启动MHA Manager
nohup masterha_manager --conf=/etc/mha/mha.cnf < /dev/null > /usr/local/mha/manager_start.log 2>&1 &

5.关闭MHA-manager
[root@MHA-MES-Monitor-ip134 ~]# masterha_stop --conf=/etc/mha/mha.cnf

9.MHA故障转移

1.模拟主库Down机
[root@MHA-Master ~]# ifconfig

ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.56  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::6e36:de88:3e08:2788  prefixlen 64  scopeid 0x20<link>
        ether 00:50:56:24:4c:9c  txqueuelen 1000  (Ethernet)
        RX packets 3307  bytes 370292 (361.6 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 2643  bytes 586340 (572.5 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.54  netmask 255.255.255.0  broadcast 192.168.1.255
        ether 00:50:56:24:4c:9c  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1  (Local Loopback)
        RX packets 36  bytes 2932 (2.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 36  bytes 2932 (2.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        
此时主库服务器掉电，关机了
[root@MHA-Master ~]# shutdown now -h

2.查看从库MHA-Slave1是否为主库，此时查看VIP已经漂移过来
[root@MHA-Slave1 ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.57  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::6e36:de88:3e08:2788  prefixlen 64  scopeid 0x20<link>
        inet6 fe80::efe1:5d84:7c23:aa68  prefixlen 64  scopeid 0x20<link>
        inet6 fe80::1a36:43a5:1f86:59c7  prefixlen 64  scopeid 0x20<link>
        ether 00:50:56:36:f8:7b  txqueuelen 1000  (Ethernet)
        RX packets 2804  bytes 331816 (324.0 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 2556  bytes 938972 (916.9 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.54  netmask 255.255.255.0  broadcast 192.168.1.255
        ether 00:50:56:36:f8:7b  txqueuelen 1000  (Ethernet)

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1  (Local Loopback)
        RX packets 36  bytes 2932 (2.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 36  bytes 2932 (2.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        
此时发现， MHA-Slave1做为主库，MHA-Slave2做为从库

mysql> show master status \G;
*************************** 1. row ***************************
             File: MHA-Slave1-bin.000001
         Position: 155
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set: 0aff2757-44b5-11ed-a3bc-005056244c9c:1-11
1 row in set (0.00 sec)

mysql> show slave hosts;
+-----------+--------------+------+-----------+--------------------------------------+
| Server_id | Host         | Port | Master_id | Slave_UUID                           |
+-----------+--------------+------+-----------+--------------------------------------+
| 803306133 | 192.168.1.58 | 3306 | 803306132 | 0aff2757-44b5-11ed-a3bc-005056244c9e |
+-----------+--------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

MySQL高可用之MHA架构企业实战

MySQL高可用之MHA架构部署

1.MHA简介

2.架构规划

3.安装MySQL8

4.GTID同步

5.互信设置

6.安装MHA软件

7.配置MHA

8.MHA服务启动

9.MHA故障转移

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL高可用之MHA架构企业实战

MySQL高可用之MHA架构部署

1.MHA简介

2.架构规划

3.安装MySQL8

4.GTID同步

5.互信设置

6.安装MHA软件

7.配置MHA

8.MHA服务启动

9.MHA故障转移

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像