网站导航
> 文档中心 > MySQL高可用之MHA架构企业实战

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

网友: 文档中心 2022-10-06 08:44:33

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

  • 前言
    • 📣 1.MHA简介
    • 📣 2.架构规划
    • 📣 3.安装MySQL8
    • 📣 4.GTID同步
      • ✨ 4.1 配置参数文件
      • ✨ 4.2 主从同步
      • ✨ 4.3 校验主从复制
      • ✨ 4.4 Master设置VIP
    • 📣 5.互信设置
    • 📣 6.安装MHA软件
      • ✨ 6.1 安装MHA Node
      • ✨ 6.2 装MHA Manager
    • 📣 7.配置MHA
    • 📣 8.MHA服务启动
    • 📣 9.MHA故障转移

前言

MHA目前在MySQL高可用方面是一个相对成熟的解决方案，最近客户需求，特分享部署方案给大家

MySQL高可用之MHA企业B站实践：
https://www.bilibili.com/video/BV1ge4y167tw/
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。整个故障转移过程对应用程序完全透明。

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

📣 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

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

📣 3.安装MySQL8

注：以下node 3个节点同时操作1.用户及组groupadd mysqluseradd -r -g mysql mysql2.解压缩安装包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/mysql8019ln -s /usr/local/mysql8019 /usr/local/mysqlecho "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/bashrc source /etc/bashrcchown -R mysql.mysql /usr/local/mysql-8.0.19-linux-glibc2.12-x86_643.在线yum配置yum install -y net-toolsyum install -y libtinfo*yum -y install numactlyum -y install libaio*yum -y install perl perl-develyum -y install autoconf4.mysql初始化/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

📣 4.GTID同步

✨ 4.1 配置参数文件

【Master1】cat > /etc/my.cnf <<"EOF"[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datauser=mysqlport=3306character_set_server=utf8mb4secure_file_priv=server-id = 803306131log-bin =binlog_format=rowbinlog-ignore-db = mysqlbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schemabinlog-ignore-db = sysreplicate_ignore_db=information_schemareplicate_ignore_db=performance_schemareplicate_ignore_db=mysqlreplicate_ignore_db=syslog-slave-updates=1skip-name-resolvelog_timestamps = SYSTEM#default-time-zone = '+8:00'auto-increment-increment=1auto-increment-offset=1gtid-mode=ONenforce-gtid-consistency=onreport_host=192.168.1.56EOF【Slave1】cat > /etc/my.cnf <<"EOF"#S1[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datauser=mysqlport=3306character_set_server=utf8mb4secure_file_priv=server-id = 803306132log-bin =binlog_format=rowbinlog-ignore-db = mysqlbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schemabinlog-ignore-db = sysreplicate_ignore_db=information_schemareplicate_ignore_db=performance_schemareplicate_ignore_db=mysqlreplicate_ignore_db=sysskip-name-resolvelog_timestamps = SYSTEM#default-time-zone = '+8:00'gtid-mode=ONenforce-gtid-consistency=ONreport_host=192.168.1.57EOF【Slave2】cat > /etc/my.cnf <<"EOF"#S2[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datauser=mysqlport=3306character_set_server=utf8mb4secure_file_priv=server-id = 803306133log-bin =binlog_format=rowbinlog-ignore-db = mysqlbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schemabinlog-ignore-db = sysreplicate_ignore_db=information_schemareplicate_ignore_db=performance_schemareplicate_ignore_db=mysqlreplicate_ignore_db=sysskip-name-resolvelog_timestamps = SYSTEM#default-time-zone = '+8:00'gtid-mode=ONenforce-gtid-consistency=ONreport_host=192.168.1.58EOF

✨ 4.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 tomaster_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;

✨ 4.3 校验主从复制

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

✨ 4.4 Master设置VIP

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

📣 5.互信设置

1. 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.测试SSHssh 192.168.1.55 datessh 192.168.1.56 datessh 192.168.1.57 datessh 192.168.1.58 date

📣 6.安装MHA软件

✨ 6.1 安装MHA Node

tar xf mha4mysql-node-0.58.tar.gzcd mha4mysql-node-0.58perl Makefile.PLyum install make -ymake && make install

✨ 6.2 装MHA Manager

MHA Manager中主要几个管理员的命令行工具，也是依赖一些Perl模块的，只在管理节点安装即可tar zxf mha4mysql-manager-0.58.tar.gzcd mha4mysql-manager-0.58perl Makefile.PL    --先NO，然后再YESmake && make install

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

📣 7.配置MHA

注意：1、由于脚本中并没有master_ip_failover脚本，启动时会报错，请到mha4mysqlmanager-0.5X/samples/scripts下拷贝对应脚本到指定位置。或注释掉master_ip_failover_script也可以。2、MHA可以监控多个主从的集群，每个集群的配置文件可以用名字区分，因为这里只有一个集群，因此只有mha.cnf一个文件！[root@MHA-Monitor /]# mkdir -p /usr/local/mha[root@MHA-Monitor /]# mkdir -p /etc/mhacat > /etc/mha/mha.cnf <<"EOF"[server default]manager_workdir=/usr/local/mhamanager_log=/usr/local/mha/manager_running.logmaster_ip_failover_script=/usr/local/mha/scripts/master_ip_failovermaster_ip_online_change_script=/usr/local/mha/scripts/master_ip_online_changeping_interval=1secondary_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=3306ssh_user=rootuser=mhapassword=rootrepl_user=replrepl_password=root[server1]hostname=192.168.1.56port=3306[server2]candidate_master=1check_repl_delay=0hostname=192.168.1.57port=3306[server3]hostname=192.168.1.58port=3306EOF

📣 8.MHA服务启动

1.检查SSH情况[root@MHA-Monitor ~]# masterha_check_ssh --conf=/etc/mha/mha.cnf2.检查复制情况[root@MHA-Monitor ~]# masterha_check_repl --conf=/etc/mha/mha.cnf3.检查MHA状态[root@MHA-Monitor /]# masterha_check_status --conf=/etc/mha/mha.cnf4.启动MHA Managernohup 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 ~]# ifconfigens33: 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 0ens33: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 -h2.查看从库MHA-Slave1是否为主库，此时查看VIP已经漂移过来[root@MHA-Slave1 ~]# ifconfigens33: 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 0ens33: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,sysExecuted_Gtid_Set: 0aff2757-44b5-11ed-a3bc-005056244c9c:1-111 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)
网络标签：

相关问题