Mysql MHA部署-04MHA配置

Mysql MHA部署-04MHA配置

一 Mysql MHA部署-01介紹

二 Mysql MHA部署-02主從復制

三 Mysql MHA部署-03MHA軟件安裝

四 Mysql MHA部署-04MHA配置

五 Mysql MHA部署-05故障轉移

六 Mysql MHA部署-06手動切換

七 Mysql MHA部署-07常見問題

架構說明：

參考：     http://www.zhaibibei.cn/mysql/mha/

一：MySQL MHA部署前準備

1.1 hosts文件

首先我們在四臺服務器上添加其他服務器的hosts信息

[root@rac1 mha]# vim /etc/hosts

192.168.2.222 rac4

192.168.2.187 rac1

192.168.2.188 rac2

192.168.2.223 rac3

1.2 配置免密碼登錄

接下來我們需要配置各服務請求間的免密碼登錄

ssh-kengen 命令后請一路下一步

---192.168.2.187 rac1

ssh-keygen -t rsa

ssh-copy-id 192.168.2.188

ssh-copy-id 192.168.2.223

ssh-copy-id 192.168.2.222

ssh-copy-id rac2

ssh-copy-id rac3

ssh-copy-id rac4

---192.168.2.188 rac2

ssh-keygen -t rsa

ssh-copy-id 192.168.2.187

ssh-copy-id 192.168.2.223

ssh-copy-id 192.168.2.222

ssh-copy-id rac1

ssh-copy-id rac3

ssh-copy-id rac4

---192.168.2.223 rac3

ssh-keygen -t rsa

ssh-copy-id 192.168.2.187

ssh-copy-id 192.168.2.188

ssh-copy-id 192.168.2.222

ssh-copy-id rac1

ssh-copy-id rac2

ssh-copy-id rac4

---192.168.2.222 rac4

ssh-keygen -t rsa

ssh-copy-id 192.168.2.187

ssh-copy-id 192.168.2.188

ssh-copy-id 192.168.2.223

ssh-copy-id rac1

ssh-copy-id rac2

ssh-copy-id rac3

1.3 創建監控賬戶

接下來我們創建用于MHA監控的數據庫賬戶

主庫 187：

master數據庫

mysql> grant all privileges on *.* to 'monitor'@'192.168.2.187' identified by '123456';

mysql> grant all privileges on *.* to 'monitor'@'192.168.2.188' identified by '123456';

mysql> grant all privileges on *.* to 'monitor'@'192.168.2.222' identified by '123456';

mysql> grant all privileges on *.* to 'monitor'@'192.168.2.223' identified by '123456';

mysql> grant all privileges on *.* to 'monitor'@'127.0.0.1' identified by '123456';

mysql> flush privileges;

mysql> select host,user from mysql.user;

+---------------+---------------+

| host          | user          |

+---------------+---------------+

| 127.0.0.1     | monitor       |

| 192.168.2.187 | monitor       |

| 192.168.2.187 | repl          |

| 192.168.2.188 | monitor       |

| 192.168.2.188 | repl          |

| 192.168.2.222 | monitor       |

| 192.168.2.223 | monitor       |

| 192.168.2.223 | repl          |

| localhost     | mysql.session |

| localhost     | mysql.sys     |

| localhost     | root          |

+---------------+---------------+

11 rows in set (0.00 sec)

1.4 關閉防火墻

如果防火墻開始，需要開通服務器間3306端口的通信

1.5 關閉relay日志自動清理

由于默認情況下從庫的relay logs會在SQL線程執行完畢后被自動刪除，但是對于MHA場景下，對于某些滯后從庫的恢復依賴于其他從庫的relay log，因此采取禁用自動刪除功能以及定期清理的辦法

使用purge_relay_logs腳本這個后面說明

主從庫3臺

relay_log_purge=0

1.6 從庫開啟只讀

從庫需要設置只讀模式

2臺從庫

read_only=1

1.7 master節點綁定VIP

剛開始需要手動將VIP綁定至master節點

[root@rac1 mysql]# ifconfig |grep 192

        inet 192.168.2.187  netmask 255.255.255.0  broadcast 192.168.2.255

        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255

[root@rac1 mysql]# /sbin/ifconfig enp0s3:2 192.168.2.189  netmask 255.255.255.0 up

[root@rac1 mysql]# ifconfig |grep 192

        inet 192.168.2.187  netmask 255.255.255.0  broadcast 192.168.2.255

        inet 192.168.2.189  netmask 255.255.255.0  broadcast 192.168.2.255

        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255

1.8 準備自定義腳本

這里我們通過網盤下載，放到/etc/mha/script下面并賦予可執行權限

鏈接: https://pan.baidu.com/s/18H12bTIYHi2H0dXs2DI7NA 提取碼: mwkq

管理節點：192.168.2.222 RAC4 

[root@rac4 ~]# mkdir /etc/mha/script -p            

[root@rac4 ~]# cp /package/mysql/MHA/script/* /etc/mha/script/

[root@rac4 ~]# ll -rth /etc/mha/script/

total 24K

-rw-r--r-- 1 root root 2.4K Mar 14 19:56 send_report

-rw-r--r-- 1 root root  11K Mar 14 19:56 master_ip_online_change

-rw-r--r-- 1 root root 4.3K Mar 14 19:56 master_ip_failover

接下來我們修改相關的文件

---master_ip_failover

該文件只需要修改

my $vip = '192.168.2.189';

my $key = "0";

my $ssh_start_vip = "/sbin/ifconfig enp0s3:$key $vip/24";

my $ssh_stop_vip = "/sbin/ifconfig enp0s3:$key down";

my $ssh_send_garp = "/sbin/arping -U $vip -I enp0s3 -c 1";

---master_ip_online_change

該文件也是需要修改

my $vip = '192.168.2.189';

my $key = "0";

my $ssh_start_vip = "/sbin/ifconfig enp0s3:$key $vip/24";

my $ssh_stop_vip = "/sbin/ifconfig enp0s3:$key down";

my $ssh_send_garp = "/sbin/arping -U $vip -I enp0s3 -c 1";

---send_report

該文件需要修改郵件配置，這個后面做介紹

二 MySQL MHA部署指南

2.1 管理節點配置

[root@rac4 script]# vim /etc/mha/mha.conf

[server default]

# mysql user and password

user=monitor

password=123456

ssh_user=root

# working directory on the manager

manager_workdir=/etc/mha/manager

manager_log=/etc/mha/manager/mha.log

ping_interval=1

# working directory on MySQL servers

remote_workdir=/etc/mha/node

# replication user

repl_user=repl

repl_password=rpl

master_binlog_dir=/datalog/mysql/binlog

## customized script

master_ip_failover_script=/etc/mha/script/master_ip_failover

master_ip_online_change_script=/etc/mha/script/master_ip_online_change

secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.2.188  -s 192.168.2.223 --user=root  --master_host=192.168.2.187 --master_ip=192.168.2.187  --master_port=3306

[server1]

hostname=rac1

candidate_master=1

[server2]

hostname=rac2

candidate_master=1

[server3]

hostname=rac3

no_master=1

接下來我們來解釋每個參數的意義：

manager_workdir 管理節點工作目錄，mha運行時會有狀態文件生成

masnager_log 管理節點生成的日志

ping_interval 管理節點檢測主庫狀態的間隔，默認為3s

remote_workdir MHA node工作目錄，不存在會自動建立，如果不允許創建，MHA Node自動異常退出

master_binlog_dir 指定主庫的二進制日志位置，防止管理節點無法連接主庫獲取日志位置

master_ip_failover_script 發生切換時VIP漂移腳本

master_ip_online_change_script 用于手動切換時VIP的漂移

secondary_check_script 若管理節點無法連接主庫，則調用該腳本從從庫檢測主庫狀態

candidate_master 指定如果主庫發生故障，優先提升該節點為主庫

no_master 指定該節點不會成為主庫

2.2 測試連接

2.2.1 測試ssh是否設置正確

管理節點

[root@rac4 script]# masterha_check_ssh --conf=/etc/mha/mha.conf

Sat Mar 14 20:10:42 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Sat Mar 14 20:10:42 2020 - [info] Reading application default configuration from /etc/mha/mha.conf..

Sat Mar 14 20:10:42 2020 - [info] Reading server configuration from /etc/mha/mha.conf..

Sat Mar 14 20:10:42 2020 - [info] Starting SSH connection tests..

Sat Mar 14 20:10:44 2020 - [debug] 

Sat Mar 14 20:10:42 2020 - [debug]  Connecting via SSH from root@rac1(192.168.2.187:22) to root@rac2(192.168.2.188:22)..

Sat Mar 14 20:10:43 2020 - [debug]   ok.

Sat Mar 14 20:10:43 2020 - [debug]  Connecting via SSH from root@rac1(192.168.2.187:22) to root@rac3(192.168.2.223:22)..

Sat Mar 14 20:10:44 2020 - [debug]   ok.

Sat Mar 14 20:10:45 2020 - [debug] 

Sat Mar 14 20:10:43 2020 - [debug]  Connecting via SSH from root@rac3(192.168.2.223:22) to root@rac1(192.168.2.187:22)..

Sat Mar 14 20:10:44 2020 - [debug]   ok.

Sat Mar 14 20:10:44 2020 - [debug]  Connecting via SSH from root@rac3(192.168.2.223:22) to root@rac2(192.168.2.188:22)..

Sat Mar 14 20:10:45 2020 - [debug]   ok.

Sat Mar 14 20:10:45 2020 - [debug] 

Sat Mar 14 20:10:43 2020 - [debug]  Connecting via SSH from root@rac2(192.168.2.188:22) to root@rac1(192.168.2.187:22)..

Sat Mar 14 20:10:44 2020 - [debug]   ok.

Sat Mar 14 20:10:44 2020 - [debug]  Connecting via SSH from root@rac2(192.168.2.188:22) to root@rac3(192.168.2.223:22)..

Sat Mar 14 20:10:44 2020 - [debug]   ok.

Sat Mar 14 20:10:45 2020 - [info] All SSH connection tests passed successfully.

測試通過會有成功字樣打印出來

2.2.2 測試復制是否正常

管理節點222(rac4)

[root@rac4 script]# masterha_check_repl --conf=/etc/mha/mha.conf

Sat Mar 14 20:14:42 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Sat Mar 14 20:14:42 2020 - [info] Reading application default configuration from /etc/mha/mha.conf..

Sat Mar 14 20:14:42 2020 - [info] Reading server configuration from /etc/mha/mha.conf..

Sat Mar 14 20:14:42 2020 - [info] MHA::MasterMonitor version 0.56.

Sat Mar 14 20:14:43 2020 - [info] GTID failover mode = 1

Sat Mar 14 20:14:43 2020 - [info] Dead Servers:

Sat Mar 14 20:14:43 2020 - [info] Alive Servers:

Sat Mar 14 20:14:43 2020 - [info]   rac1(192.168.2.187:3306)

Sat Mar 14 20:14:43 2020 - [info]   rac2(192.168.2.188:3306)

Sat Mar 14 20:14:43 2020 - [info]   rac3(192.168.2.223:3306)

Sat Mar 14 20:14:43 2020 - [info] Alive Slaves:

Sat Mar 14 20:14:43 2020 - [info]   rac2(192.168.2.188:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled

Sat Mar 14 20:14:43 2020 - [info]     GTID ON

Sat Mar 14 20:14:43 2020 - [info]     Replicating from 192.168.2.187(192.168.2.187:3306)

Sat Mar 14 20:14:43 2020 - [info]     Primary candidate for the new Master (candidate_master is set)

Sat Mar 14 20:14:43 2020 - [info]   rac3(192.168.2.223:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled

Sat Mar 14 20:14:43 2020 - [info]     GTID ON

Sat Mar 14 20:14:43 2020 - [info]     Replicating from 192.168.2.187(192.168.2.187:3306)

Sat Mar 14 20:14:43 2020 - [info]     Not candidate for the new Master (no_master is set)

Sat Mar 14 20:14:43 2020 - [info] Current Alive Master: rac1(192.168.2.187:3306)

Sat Mar 14 20:14:43 2020 - [info] Checking slave configurations..

Sat Mar 14 20:14:43 2020 - [info] Checking replication filtering settings..

Sat Mar 14 20:14:43 2020 - [info]  binlog_do_db= , binlog_ignore_db= 

Sat Mar 14 20:14:43 2020 - [info]  Replication filtering check ok.

Sat Mar 14 20:14:43 2020 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

Sat Mar 14 20:14:43 2020 - [info] Checking SSH publickey authentication settings on the current master..

Sat Mar 14 20:14:43 2020 - [info] HealthCheck: SSH to rac1 is reachable.

Sat Mar 14 20:14:43 2020 - [info] 

rac1(192.168.2.187:3306) (current master)

 +--rac2(192.168.2.188:3306)

 +--rac3(192.168.2.223:3306)

Sat Mar 14 20:14:43 2020 - [info] Checking replication health on rac2..

Sat Mar 14 20:14:43 2020 - [info]  ok.

Sat Mar 14 20:14:43 2020 - [info] Checking replication health on rac3..

Sat Mar 14 20:14:43 2020 - [info]  ok.

Sat Mar 14 20:14:43 2020 - [info] Checking master_ip_failover_script status:

Sat Mar 14 20:14:43 2020 - [info]   /etc/mha/script/master_ip_failover --command=status --ssh_user=root --orig_master_host=rac1 --orig_master_ip=192.168.2.187 --orig_master_port=3306 

Sat Mar 14 20:14:43 2020 - [info]  OK.

Sat Mar 14 20:14:43 2020 - [warning] shutdown_script is not defined.

Sat Mar 14 20:14:43 2020 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

測試通過會有成功字樣打印出來

通過輸出我們可以看到相關架構信息

3 啟動MHA

首先我們可以檢測MHA是否已啟動

管理節點

[root@rac4 script]# masterha_check_status --conf=/etc/mha/mha.conf

mha is stopped(2:NOT_RUNNING).

我們在manager上執行如下命令開啟MHA

管理節點

[root@rac4 script]# nohup masterha_manager    --conf=/etc/mha/mha.conf --remove_dead_master_conf &

--remove_dead_master_conf意思為當發生切換后，老的主庫信息會從配置文件刪除

[1] 4360

[root@rac4 script]# nohup: ignoring input and appending output to ‘nohup.out’

[root@rac4 script]# masterha_check_status --conf=/etc/mha/mha.conf

mha (pid:4360) is running(0:PING_OK), master:rac1

4停止MHA

[root@rac4 script]# masterha_stop -conf=/etc/mha/mha.conf 

Stopped mha successfully.

[1]+  Exit 1                  nohup masterha_manager --conf=/etc/mha/mha.conf --remove_dead_master_conf

[root@rac4 script]# masterha_check_status --conf=/etc/mha/mha.conf

mha is stopped(2:NOT_RUNNING).

5設置relay log清理計劃

前面我們說到我們已經取消了relay log的自動清理，這里需要在主從庫3臺上設置定時任務手動清理

這里首先需要在root用戶環境變量中增加mysql相關路徑

主從庫三臺

export MYSQL_HOME=/usr/local/mysql

export PATH=$HOME/bin:$MYSQL_HOME/bin:$PATH

0 4 1 * * /usr/bin/purge_relay_logs --user=monitor --password=123456 -disable_relay_log_purge --workdir=/tmp/ >> /tmp/purge_relay_logs.log 2>&1

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！