MySQL高可用之MHA架構企業實戰
theme: awesome-green
持續創作，加速成長！這是我參與「掘金日新計劃 · 10 月更文挑戰」的第7天，點選檢視活動詳情
📢📢📢📣📣📣 哈嘍！大家好，我是【IT邦德】，江湖人稱jeames007，10年DBA工作經驗 一位上進心十足的【大資料領域博主】！😜😜😜 中國DBA聯盟(ACDU)成員，目前從事DBA及程式程式設計 擅長主流資料Oracle、MySQL、PG 運維開發，備份恢復，安裝遷移，效能優化、故障應急處理等。 ✨ 如果有對【資料庫】感興趣的【小可愛】，歡迎關注【IT邦德】💞💞💞 ❤️❤️❤️感謝各位大可愛小可愛！❤️❤️❤️
@TOC
前言
MHA目前在MySQL高可用方面是一個相對成熟的解決方案，最近客戶需求，特分享部署方案給大家
MySQL高可用之MHA企業B站實踐：
http://www.bilibili.com/video/BV1ge4y167tw/
📣 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.架構規劃
```bash 角色 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
```bash 注：以下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 ```
📣 4.GTID同步
✨ 4.1 配置引數檔案
```bash 【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 ```
✨ 4.2 主從同步
```bash
主庫操作
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; ```
✨ 4.3 校驗主從複製
```bash
主庫執行
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.4 Master設定VIP
```bash
在主庫上執行新增VIP的過程（第一次手動新增,後續啟動切換）
[root@MHA-Master ~]# ifconfig [root@MHA-Master ~]# /sbin/ifconfig ens33:1 192.168.1.54 ```
📣 5.互信設定
```bash 1. 4臺機器互相免密碼登入，注意，自己跟自己也要配免密碼登入 (1)在Manager上配置到所有的Node節點的無密碼驗證 [root@MHA-Monitor /]# ssh-keygen -t rsa [root@MHA-Monitor /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Monitor /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Monitor /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Monitor /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]"
(2)在Master上配置到所有的Node節點的無密碼驗證 [root@MHA-Master ~]# ssh-keygen -t rsa [root@MHA-Master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]"
(3)在Candicate Master上配置到所有的Node節點的無密碼驗證 [root@MHA-Slave1 /]# ssh-keygen -t rsa [root@MHA-Slave1 /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Slave1 /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Slave1 /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Slave1 /]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]"
(4)在Slave2上配置到所有的Node節點的無密碼驗證 [root@MHA-Slave2 ~]# ssh-keygen -t rsa [root@MHA-Slave2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Slave2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Slave2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]" [root@MHA-Slave2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub "[email protected]"
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軟體
✨ 6.1 安裝MHA Node
```bash tar xf mha4mysql-node-0.58.tar.gz cd mha4mysql-node-0.58 perl Makefile.PL
yum install make -y make && make install ```
✨ 6.2 裝MHA Manager
```bash 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
```bash 注意： 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/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服務啟動
```bash 1.檢查SSH情況 [root@MHA-Monitor ~]# masterha_check_ssh --conf=/etc/mha/mha.cnf
2.檢查複製情況 [root@MHA-Monitor ~]# masterha_check_repl --conf=/etc/mha/mha.cnf
3.檢查MHA狀態 [root@MHA-Monitor /]# masterha_check_status --conf=/etc/mha/mha.cnf
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故障轉移
```bash 1.模擬主庫Down機 [root@MHA-Master ~]# ifconfig
ens33: flags=4163
ens33:1: flags=4163
lo: flags=73
此時主庫伺服器掉電，關機了 [root@MHA-Master ~]# shutdown now -h
2.檢視從庫MHA-Slave1是否為主庫，此時檢視VIP已經漂移過來
[root@MHA-Slave1 ~]# ifconfig
ens33: flags=4163
ens33:1: flags=4163
lo: flags=73
此時發現， 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) ```
- 還用寬表？體驗一下DQL成就新一代的BI吧
- 大資料計算，如何優化SQL?
- MySQL高可用之MHA架構企業實戰
- 【牛客刷題-SQL大廠面試真題】NO5.某寶店鋪分析（電商模式）
- 內卷時代，35歲程式猿轉行DBA｜2022 年中總結
- MySQL高可用之多源複製
- MySQL8.0新特性搶先看，效能又雙叒提升了
- Oracle表碎片對效能有多大影響？
- 趣味端午節，程式猿特殊的獻禮
- 【微信小程式開發】第1篇：開發工具安裝及程式配置
- CentOS7部署Mysql8.0.27
- MySQL中COLLATE是用來做什麼的？
- PostgreSQL大總結，小白最適合了
- Mysql快速找回root密碼妙招
- Python字典介紹
- Mysql日期型別剖析
- Python元組詳解
- Python列表詳解
- Python字串詳解
- Windows安裝Mysql（msi 圖形安裝）