在之前的博客中，介紹了mysql的主從模型以及深層次的mysql的讀寫分離插件——ProxySQL，讓我們可以很大程度上提升數(shù)據(jù)庫服務器的性能和優(yōu)化用戶的體驗，但是，我們對于數(shù)據(jù)庫的可靠性似乎缺了一點，因為一旦master數(shù)據(jù)庫服務器宕機，我們的數(shù)據(jù)庫基本上就是癱瘓了，所以，我們需要一個解決方案針對于數(shù)據(jù)庫服務器的可靠性。

MHA(MySQL|Master? High Availability)是一種基于主從模型的相當成熟的一種解決方案，我們對于master做一個高可用，使得哪怕在master數(shù)據(jù)庫服務器宕機時，我們的slave可以及時頂上，直接變成master主機，保證服務可靠的運行；

由于mha4mysql不存在于鏡像倉庫和epel中，所以需要下載rpm包，我下載的是

mha4mysql-manager-0.56-0.el6.noarch.rpm

mha4mysql-node-0.56-0.el6.noarch.rpm

建議mha服務器和master服務器分開；否則master物理宕機的話，mha也會宕機；

Master和slave上安裝mha4mysql-node節(jié)點；

mha服務器兩個rpm包都需要安裝；

MHA：?? CentOS 7.5B? 172.16.75.2

Master： CentOS 7.5D? 172.16.75.4

Slave：?? CentOS 7.5C? 172.16.75.3

三臺主機都需要進行SSH免密通信；

因為如果master宕機，slave需要頂上，所以slave和master都需要開啟二進制日志和中繼日志；

Master配置文件：[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

#?Disabling?symbolic-links?is?recommended?to?prevent?assorted?security?risks

symbolic-links=0

#?Settings?user?and?group?are?ignored?when?systemd?is?used.

#?If?you?need?to?run?mysqld?under?a?different?user?or?group,

#?customize?your?systemd?unit?file?for?mariadb?according?to?the

#?instructions?in?http://fedoraproject.org/wiki/Systemd

innodb_file_per_table=ON

skip_name_resolve=ON

server_id=401

log_bin=/var/lib/mysql/binlog

sync_binlog=1

innodb_flush_log_at_trx_commit=1

relay_log_purge=0

relay_log=relay_log

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

#

#?include?all?files?from?the?config?directory

#

!includedir?/etc/my.cnf.d

Slave配置文件：[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

#?Disabling?symbolic-links?is?recommended?to?prevent?assorted?security?risks

symbolic-links=0

#?Settings?user?and?group?are?ignored?when?systemd?is?used.

#?If?you?need?to?run?mysqld?under?a?different?user?or?group,

#?customize?your?systemd?unit?file?for?mariadb?according?to?the

#?instructions?in?http://fedoraproject.org/wiki/Systemd

innodb_file_per_table=ON

skip_name_resolve=ON

server_id=301

read_only=ON

relay_log=slavelog

relay_log_purge=0

log_bin=binlog

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

#

#?include?all?files?from?the?config?directory

#

!includedir?/etc/my.cnf.d

然后其他配置不變，搭建主從模型；

1.下載完mha的rpm包后，分別在對應的節(jié)點安裝對應的安裝包，通過查看mha4mysql-manager配置文件，全是二進制執(zhí)行腳本。所以mha的配置文件需要自己寫；

[root@slave1?~]#?rpm?-ql?mha4mysql-manager

/usr/bin/masterha_check_repl

/usr/bin/masterha_check_ssh

/usr/bin/masterha_check_status

/usr/bin/masterha_conf_host

/usr/bin/masterha_manager

/usr/bin/masterha_master_monitor

/usr/bin/masterha_master_switch

/usr/bin/masterha_secondary_check

/usr/bin/masterha_stop

/usr/share/man/man1/masterha_check_repl.1.gz

/usr/share/man/man1/masterha_check_ssh.1.gz

/usr/share/man/man1/masterha_check_status.1.gz

/usr/share/man/man1/masterha_conf_host.1.gz

/usr/share/man/man1/masterha_manager.1.gz

/usr/share/man/man1/masterha_master_monitor.1.gz

/usr/share/man/man1/masterha_master_switch.1.gz

/usr/share/man/man1/masterha_secondary_check.1.gz

/usr/share/man/man1/masterha_stop.1.gz

/usr/share/perl5/vendor_perl/MHA/Config.pm

/usr/share/perl5/vendor_perl/MHA/DBHelper.pm

/usr/share/perl5/vendor_perl/MHA/FileStatus.pm

/usr/share/perl5/vendor_perl/MHA/HealthCheck.pm

/usr/share/perl5/vendor_perl/MHA/ManagerAdmin.pm

/usr/share/perl5/vendor_perl/MHA/ManagerAdminWrapper.pm

/usr/share/perl5/vendor_perl/MHA/ManagerConst.pm

/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm

/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm

/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm

/usr/share/perl5/vendor_perl/MHA/MasterRotate.pm

/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm

/usr/share/perl5/vendor_perl/MHA/Server.pm

/usr/share/perl5/vendor_perl/MHA/ServerManager.pm

2.創(chuàng)建mha相對應的配置文件；[root@slave1?~]#?mkdir?/etc/mha

[root@slave1?~]#?vim?/etc/mha/app1.cnf

[root@slave1?~]#

[root@slave1?~]#?cat?/etc/mha/app1.cnf

[server?default]

user=mha?#登錄用戶

password=mhapass

manager_workdir=/data/masterha/app1

manager_log=/data/masterha/app1/manager.log

remote_workdir=/data/masterha/app1

ssh_user=root

repl_user=repuser?#master做replication?slave授權的用戶

repl_password=123456

ping_interval=1

[server1]

hostname=172.16.75.4

candidate_master=1

[server2]

hostname=172.16.75.3

candidate_master=1

[root@slave1?~]#

[root@slave1?~]#?mkdir?-pv?/data/masterha/app1

mkdir:?已創(chuàng)建目錄?"/data"

mkdir:?已創(chuàng)建目錄?"/data/masterha"

mkdir:?已創(chuàng)建目錄?"/data/masterha/app1"

3.根據(jù)配置文件內容，master還需要對mha做一個授權用戶。

Master mysql：MariaDB?[(none)]>?grant?all?on?*.*?to?'mha'@'%'?identified?by?'mhapass';

Query?OK,?0?rows?affected?(0.08?sec)

4.在mha服務器上進行ssh測試和repl測試(最后顯示 OK即可)；[root@slave1?~]#?masterha_check_ssh?--conf=/etc/mha/app1.cnf

Wed?Nov??7?20:46:17?2018?-?[warning]?Global?configuration?file?/etc/masterha_default.cnf?not?found.?Skipping.

Wed?Nov??7?20:46:17?2018?-?[info]?Reading?application?default?configuration?from?/etc/mastermha/app1.cnf..

Wed?Nov??7?20:46:17?2018?-?[info]?Reading?server?configuration?from?/etc/mastermha/app1.cnf..

Wed?Nov??7?20:46:17?2018?-?[info]?Starting?SSH?connection?tests..

Wed?Nov??7?20:46:18?2018?-?[debug]

Wed?Nov??7?20:46:17?2018?-?[debug]??Connecting?via?SSH?from?root@172.16.75.4(172.16.75.4:22)?to?root@172.16.75.3(172.16.75.3:22)..

Wed?Nov??7?20:46:17?2018?-?[debug]???ok.

Wed?Nov??7?20:46:18?2018?-?[debug]

Wed?Nov??7?20:46:17?2018?-?[debug]??Connecting?via?SSH?from?root@172.16.75.3(172.16.75.3:22)?to?root@172.16.75.4(172.16.75.4:22)..

Wed?Nov??7?20:46:18?2018?-?[debug]???ok.

Wed?Nov??7?20:46:18?2018?-?[info]?All?SSH?connection?tests?passed?successfully.[root@slave1?~]#?masterha_check_repl??--conf=/etc/mha/app.cnf

…

172.16.75.4(172.16.75.4:3306)?(current?master)

+--172.16.75.3(172.16.75.3:3306)

Thu?Nov??8?09:37:35?2018?-?[info]?Checking?replication?health?on?172.16.75.3..

Thu?Nov??8?09:37:35?2018?-?[info]??ok.

Thu?Nov??8?09:37:35?2018?-?[warning]?master_ip_failover_script?is?not?defined.

Thu?Nov??8?09:37:35?2018?-?[warning]?shutdown_script?is?not?defined.

Thu?Nov??8?09:37:35?2018?-?[info]?Got?exit?code?0?(Not?master?dead).

MySQL?Replication?Health?is?OK.

5.啟動mha4mysql進程；[root@slave1?~]#?nohup?masterha_manager?--conf=/etc/mha/app.cnf?>?/data/masterha/app1/manager.log??2>&1?&

[1]?85154

6.檢測mha的狀態(tài)；[root@slave1?~]#?masterha_check_status?--conf=/etc/mha/app.cnf

app?(pid:85154)?is?running(0:PING_OK),?master:172.16.75.4

7.測試：

把master的mysql進程關掉；然后查看manager上的mha日志：

Master：[root@slave2?~]#?systemctl?stop?mariadb

Manager：[root@slave1?~]#?masterha_check_status?--conf=/etc/mha/app.cnf

app?master?is?down?and?failover?is?running(50:FAILOVER_RUNNING).?master:172.16.75.4

[root@slave1?~]#?cat?/data/masterha/app1/manager.log

…

…

-----?Failover?Report?-----

app:?MySQL?Master?failover?172.16.75.4(172.16.75.4:3306)?to?172.16.75.3(172.16.75.3:3306)?succeeded

Master?172.16.75.4(172.16.75.4:3306)?is?down!

Check?MHA?Manager?logs?at?slave1.ljy.com:/data/masterha/app1/manager.log?for?details.

Started?automated(non-interactive)?failover.

The?latest?slave?172.16.75.3(172.16.75.3:3306)?has?all?relay?logs?for?recovery.

Selected?172.16.75.3(172.16.75.3:3306)?as?a?new?master.

172.16.75.3(172.16.75.3:3306):?OK:?Applying?all?logs?succeeded.

Generating?relay?diff?files?from?the?latest?slave?succeeded.

172.16.75.3(172.16.75.3:3306):?Resetting?slave?info?succeeded.

Master?failover?to?172.16.75.3(172.16.75.3:3306)?completed?successfully.

最后，當出現(xiàn)Master failover to 172.16.75.3(172.16.75.3:3306) completed successfully.

就意味著我們的master已經(jīng)轉到slave上了，雖然之前的Master數(shù)據(jù)庫服務器down掉，但是我們通過mha及時的切換到slave主機上，保證數(shù)據(jù)庫服務器的可靠性。

遇到的大坑：Checking if super_read_only is defined and turned on..DBD::mysql::st execute failed: Unknown system variable?'super_read_only'at /usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm line 245.

一開始用的0.58版本的mha，結果在進行repl檢測的時候，出現(xiàn)了“super read only”的錯誤，這是因為我們的mysql版本和mha版本有不兼容的情況，換用0.56的版本就可以，因為0.58版本的mha兼容的是mariadb-10以后版本有”super_read_only”選項，我們正常CentOS-7系列主機上安裝的MariaDB-5.56沒有”super_read_only”選項，所以檢測repl權限時，會說我們沒有設置此選項；

總之，mha在生產(chǎn)環(huán)境中有很高的必要性，我們需要Proxysql做讀寫分離提升服務器性能的同時，又需要mha對master做高可用來保證服務器的可靠性，保障我們的數(shù)據(jù)庫不間斷的運行；

總結

以上是生活随笔為你收集整理的mysql replication health is not ok_MySQL的高可用——MHA的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。