99热在线精品一区二区三区_国产伦精品一区二区三区女破破_亚洲一区二区三区无码_精品国产欧美日韩另类一区

MySQL MHA部署-04MHA配置

創(chuàng)新互聯(lián)于2013年開始，先為無棣等服務(wù)建站，無棣等地企業(yè)，進行企業(yè)商務(wù)咨詢服務(wù)。為無棣企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。

一 Mysql MHA部署-01介紹

二 Mysql MHA部署-02主從復(fù)制

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

四 Mysql MHA部署-04MHA配置

五 Mysql MHA部署-05故障轉(zhuǎn)移

六 Mysql MHA部署-06手動切換

七 Mysql MHA部署-07常見問題

架構(gòu)說明：

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

一：MySQL MHA部署前準(zhǔn)備

1.1 hosts文件

首先我們在四臺服務(wù)器上添加其他服務(wù)器的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 配置免密碼登錄

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

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 創(chuàng)建監(jiān)控賬戶

接下來我們創(chuàng)建用于MHA監(jiān)控的數(shù)據(jù)庫賬戶

主庫 187：

master數(shù)據(jù)庫

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 關(guān)閉防火墻

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

1.5 關(guān)閉relay日志自動清理

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

使用purge_relay_logs腳本這個后面說明

主從庫3臺

relay_log_purge=0

1.6 從庫開啟只讀

從庫需要設(shè)置只讀模式

2臺從庫

read_only=1

1.7 master節(jié)點綁定VIP

剛開始需要手動將VIP綁定至master節(jié)點

[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 準(zhǔn)備自定義腳本

這里我們通過網(wǎng)盤下載，放到/etc/mha/script下面并賦予可執(zhí)行權(quán)限

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

管理節(jié)點：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

接下來我們修改相關(guān)的文件

---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 管理節(jié)點配置

[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

接下來我們來解釋每個參數(shù)的意義：

manager_workdir 管理節(jié)點工作目錄，mha運行時會有狀態(tài)文件生成

masnager_log 管理節(jié)點生成的日志

ping_interval 管理節(jié)點檢測主庫狀態(tài)的間隔，默認為3s

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

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

master_ip_failover_script 發(fā)生切換時VIP漂移腳本

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

secondary_check_script 若管理節(jié)點無法連接主庫，則調(diào)用該腳本從從庫檢測主庫狀態(tài)

candidate_master 指定如果主庫發(fā)生故障，優(yōu)先提升該節(jié)點為主庫

no_master 指定該節(jié)點不會成為主庫

2.2 測試連接

2.2.1 測試ssh是否設(shè)置正確

管理節(jié)點

[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 測試復(fù)制是否正常

管理節(jié)點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.

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

通過輸出我們可以看到相關(guān)架構(gòu)信息

3 啟動MHA

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

管理節(jié)點

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

mha is stopped(2:NOT_RUNNING).

我們在manager上執(zhí)行如下命令開啟MHA

管理節(jié)點

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

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

[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設(shè)置relay log清理計劃

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

這里首先需要在root用戶環(huán)境變量中增加mysql相關(guān)路徑

主從庫三臺

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

歡迎關(guān)注我的微信公眾號"IT小Chen"，共同學(xué)習(xí)，共同成長！！！

分享名稱：MysqlMHA部署-04MHA配置
鏈接URL：http://www.ef60e0e.cn/article/ppijej.html