DBA（六）：MHA集群
生活随笔
收集整理的這篇文章主要介紹了
DBA（六）：MHA集群
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
MHA集群概述
MHA介紹
- MHA（Master High Availability）
- 由日本DeNA公司youshimaton開發
- 是一套優秀的實現MySQL高可用的解決方案
- 數據庫的自動解決故障切換操作能做到在0~30秒之內完成
- MHA能確保在故障切換過程中最大限度的保證數據的一致性，以達到真正意義上的高可用
- 是一個用Perl腳本語言寫的開源軟件
集群定義：用多臺服務器提供相同的服務
集群的類型：LB（負載均衡集群，多臺服務器平均分擔客戶請求） HA（高可用集群，提供熱備） HPC（應用于專業領域）
集群軟件：LVS HaProxy Nginx Keepalived（高可用）
Keepalived可以對所有服務進行高可用，MHA只對MySQL高可用，性能較高
MHA組成
- MHA Manager （管理節點）
- 管理所有數據庫服務器
- 可以單獨部署在一臺獨立的機器上
- 也可以部署在某臺數據庫服務器上
- MHA Node （數據節點）
- 存儲數據的MySQL服務器
- 運行在每臺MySQL服務器上
MHA工作過程
由Manager定時探測集群中的master節點，當master故障時，Manager自動將擁有最新數據的slave提升為新的master
部署MHA集群
環境準備
準備5臺主機，三臺用作數據庫服務器，1臺管理主機，1臺客戶機，設置一個VIP地址，用作地址漂移，要求每臺主機都要有mha軟件
軟件包鏈接提取碼：wvq4
- 拓撲圖
部署MHA集群
一、配置管理節點1.安裝軟件 [root@host57 mha]# ls app1.cnf master_ip_failover mha4mysql-manager-0.56.tar.gz mha4mysql-node-0.56-0.el6.noarch.rpm perl-Config-Tiny-2.14-7.el7.noarch.rpm perl-Email-Date-Format-1.002-15.el7.noarch.rpm perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm perl-Mail-Sender-0.8.23-1.el7.noarch.rpm perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm perl-MIME-Lite-3.030-1.el7.noarch.rpm perl-MIME-Types-1.38-2.el7.noarch.rpm perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm[root@host57 mha]# rm -rf perl-*.rpm[root@host57 mha]# ls app1.cnf mha4mysql-manager-0.56.tar.gz master_ip_failover mha4mysql-node-0.56-0.el6.noarch.rpm[root@host57 mha]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm #安裝mha-node軟件[root@host57 mha]# tar -xf mha4mysql-manager-0.56.tar.gz #解壓mha-manager軟件包[root@host57 mha]# ls app1.cnf mha4mysql-manager-0.56 mha4mysql-node-0.56-0.el6.noarch.rpm master_ip_failover mha4mysql-manager-0.56.tar.gz [root@host57 mha]# cd mha4mysql-manager-0.56/ #進入源碼目錄[root@host57 mha4mysql-manager-0.56]# ls #查看文件列表 AUTHORS COPYING inc Makefile.PL META.yml rpm t bin debian lib MANIFEST README samples tests[root@host57 mha4mysql-manager-0.56]# perl Makefile.PL #執行Perl命令，報錯沒有ExtUtils Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Makefile.pm line 4. BEGIN failed--compilation aborted at inc/Module/Install/Makefile.pm line 4. Compilation failed in require at inc/Module/Install.pm line 283. Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Can.pm line 6. BEGIN failed--compilation aborted at inc/Module/Install/Can.pm line 6. Compilation failed in require at inc/Module/Install.pm line 283. Can't locate ExtUtils/MM_Unix.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Metadata.pm line 349.[root@host57 mha4mysql-manager-0.56]# yum list | grep ExtUtils perl-ExtUtils-CBuilder.noarch 1:0.28.2.6-292.el7 centos perl-ExtUtils-Embed.noarch 1.30-292.el7 centos perl-ExtUtils-Install.noarch 1.58-292.el7 centos perl-ExtUtils-MakeMaker.noarch 6.68-3.el7 centos perl-ExtUtils-Manifest.noarch 1.61-244.el7 centos perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7 centos [root@host57 mha4mysql-manager-0.56]# yum -y install perl-ExtUtils*[root@host57 mha4mysql-manager-0.56]# perl Makefile.PL #執行Perl命令，報錯沒有CPAN *** Module::AutoInstall version 1.03 *** Checking for Perl dependencies... Can't locate CPAN.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/AutoInstall.pm line 277. [root@host57 mha4mysql-manager-0.56]# yum list | grep CPAN perl-CPAN.noarch 1.9800-292.el7 centos perl-CPAN-Changes.noarch 0.20-2.el7 centos perl-CPAN-Meta.noarch 2.120921-5.el7 centos perl-CPAN-Meta-Requirements.noarch 2.122-7.el7 centos perl-CPAN-Meta-YAML.noarch 0.008-14.el7 centos perl-CPANPLUS.noarch 0.91.38-4.el7 centos perl-CPANPLUS-Dist-Build.noarch 0.70-3.el7 centos perl-Parse-CPAN-Meta.noarch 1:1.4404-5.el7 centos perl-Test-CPAN-Meta.noarch 0.23-2.el7 centos [root@host57 mha4mysql-manager-0.56]# yum -y install perl-CPAN*[root@host57 mha4mysql-manager-0.56]# perl Makefile.PL #配置 *** Module::AutoInstall version 1.03 *** Checking for Perl dependencies... [Core Features] - DBI ...loaded. (1.627) - DBD::mysql ...loaded. (4.023) - Time::HiRes ...loaded. (1.9725) - Config::Tiny ...loaded. (2.14) - Log::Dispatch ...loaded. (2.41) - Parallel::ForkManager ...loaded. (1.18) - MHA::NodeConst ...loaded. (0.56) *** Module::AutoInstall configuration finished. Checking if your kit is complete... Looks good Writing Makefile for mha4mysql::manager Writing MYMETA.yml and MYMETA.json [root@host57 mha4mysql-manager-0.56]# make #編譯[root@host57 mha4mysql-manager-0.56]# make install #安裝[root@host57 mha4mysql-manager-0.56]# masterha_ [Tab][Tab] #查看安裝的可執行的命令 masterha_check_repl masterha_conf_host masterha_master_switch masterha_check_ssh masterha_manager masterha_secondary_check masterha_check_status masterha_master_monitor masterha_stop2.創建并編輯主配置文件[root@host57 mha4mysql-manager-0.56]# mkdir /etc/mha #創建工作目錄 [root@host57 mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /etc/mha/ #拷貝模板文件 [root@host57 mha4mysql-manager-0.56]# vim /etc/mha/app1.cnf #編輯主配置文件1 [server default] #管理服務器默認配置2 manager_workdir=/etc/mha #工作目錄3 manager_log=/etc/mha/manager.log #日志文件4 master_ip_failover_script=/etc/mha/master_ip_failover #故障切換腳本5 6 ssh_user=root #訪問ssh服務用戶7 ssh_port=22 #ssh服務端口8 9 repl_user=repluser #主服務器數據同步授權用戶10 repl_password=123qqq...A #密碼11 12 user=root #監控用戶13 password=123qqq...A #密碼14 15 [server1] #指定第一臺數據庫服務器16 hostname=192.168.4.51 #服務器IP地址17 port=3306 #服務端口18 candidate_master=1 #參選競選主服務器19 20 [server2] #指定第二臺數據庫服務器21 hostname=192.168.4.52 #服務器IP地址22 port=3306 #服務端口23 candidate_master=1 #參選競選主服務器24 25 [server3] #指定第三臺數據庫服務器26 hostname=192.168.4.53 #服務器IP地址27 port=3306 #服務端口28 candidate_master=1 #參與主服務器競選3.創建并編輯故障切換腳本(當57監視到數據庫服務器) [root@host57 mha4mysql-manager-0.56]# ls samples/scripts/master_ip_failover samples/scripts/master_ip_failover [root@host57 mha4mysql-manager-0.56]# cp samples/scripts/master_ip_failover /etc/mha [root@host57 mha4mysql-manager-0.56]# chmod +x /etc/mha/master_ip_failover #給腳本加權限[root@host57 mha4mysql-manager-0.56]# vim +33 /etc/mha/master_ip_failover 33 );34 my $vip = '192.168.4.100/24' ; #定義VIP地址35 my $key = "1" ; #定義變量$key36 my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip" ; #部署VIP地址命令，實驗網卡IP為ens3337 my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #釋放VIP地址命令 [root@host57 mha4mysql-manager-0.56]# ifconfig ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500inet 192.168.4.57 netmask 255.255.255.0 broadcast 192.168.4.255inet6 fe80::bbf7:a019:5cc1:a2b5 prefixlen 64 scopeid 0x20<link>ether 00:0c:29:0a:92:78 txqueuelen 1000 (Ethernet)RX packets 6172 bytes 1104619 (1.0 MiB)RX errors 0 dropped 0 overruns 0 frame 0TX packets 3988 bytes 741241 (723.8 KiB)TX errors 0 dropped 0 overruns 0 carrier 0 collisions 04.把VIP地址配置在當前的主服務器51上 （如果配錯了，可以在虛擬機本體上輸入ifdown ens33禁用網卡，再重新ifup ens33啟用網卡即可） [root@host51 mha]# ifconfig ens33:1 192.168.4.100/24 #部署VIP地址 [root@host51 mha]# ifconfig ens33 ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500inet 192.168.4.51 netmask 255.255.255.0 broadcast 192.168.4.255inet6 fe80::42bb:9312:f45:4e6a prefixlen 64 scopeid 0x20<link>ether 00:0c:29:87:50:30 txqueuelen 1000 (Ethernet)RX packets 4255 bytes 386706 (377.6 KiB)RX errors 0 dropped 0 overruns 0 frame 0TX packets 3030 bytes 388018 (378.9 KiB)TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0[root@host51 mha]# ifconfig ens33:1 ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500inet 192.168.4.100 netmask 255.255.255.0 broadcast 192.168.4.255ether 00:0c:29:87:50:30 txqueuelen 1000 (Ethernet)測試： 在50主機ping [root@host50 ~]# ping -c 2 192.168.4.100 PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data. 64 bytes from 192.168.4.100: icmp_seq=1 ttl=64 time=0.701 ms 64 bytes from 192.168.4.100: icmp_seq=2 ttl=64 time=0.601 ms二、配置數據節點1.51/52/53安裝mha_node軟件包[root@host51 ~]# cd mha [root@host51 mha]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm [root@host52 ~]# cd mha [root@host52 mha]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm [root@host53 ~]# cd mha [root@host53 mha]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm2.用戶授權監控用戶root,可以只在host51上執行授權命令，host52和host53會自動同步授權 [root@host51 mha]# mysql -uroot -p123456 mysql> grant all on *.* to root@"%" identified by "123qqq...A" ; Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> select user ,host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | repluser | % | | root | % | | mysql.sys | localhost | | root | localhost | +-----------+-----------+ 4 rows in set (0.01 sec)[root@host52 mha]# mysql -uroot -p123456 -e'select user ,host from mysql.user' mysql: [Warning] Using a password on the command line interface can be insecure. +-----------+-----------+ | user | host | +-----------+-----------+ | root | % | | mysql.sys | localhost | | root | localhost | +-----------+-----------+ [root@host53 mha]# mysql -uroot -p123456 -e'select user ,host from mysql.user' mysql: [Warning] Using a password on the command line interface can be insecure. +-----------+-----------+ | user | host | +-----------+-----------+ | root | % | | mysql.sys | localhost | | root | localhost | +-----------+-----------+在從服務器52和53主機添加數據同步的連接用戶repluser[root@host52 mha]# mysql -uroot -p123456 -e 'grant replication slave on *.* to repluser@"%" identified by "123qqq...A"' mysql: [Warning] Using a password on the command line interface can be insecure. [root@host52 mha]# mysql -uroot -p123456 -e'select user ,host from mysql.user' mysql: [Warning] Using a password on the command line interface can be insecure. +-----------+-----------+ | user | host | +-----------+-----------+ | repluser | % | | root | % | | mysql.sys | localhost | | root | localhost | +-----------+-----------+[root@host53 mha]# mysql -uroot -p123456 -e 'grant replication slave on *.* to repluser@"%" identified by "123qqq...A"' mysql: [Warning] Using a password on the command line interface can be insecure. [root@host53 mha]# mysql -uroot -p123456 -e'select user ,host from mysql.user' mysql: [Warning] Using a password on the command line interface can be insecure. +-----------+-----------+ | user | host | +-----------+-----------+ | repluser | % | | root | % | | mysql.sys | localhost | | root | localhost | +-----------+-----------+3.優化配置在51/52/53數據庫服務器啟用主從角色的半同步復制模式，禁止自動刪除中繼日志文件[root@host51 mha]# vim /etc/my.cnf4 [mysqld]5 server_id=516 log_bin=master51 7 plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" 8 rpl_semi_sync_master_enabled=1 #啟用master模塊9 rpl_semi_sync_slave_enabled=1 #啟用slave模塊10 relay_log_purge=0 #禁止刪除中繼日志文件在52/53數據庫服務器啟用binlog日志[root@host52 mha]# vim /etc/my.cnf4 [mysqld]5 server_id=52 6 log_bin=master52 7 plugin-load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"8 rpl_semi_sync_master_enabled=1 #啟用master模塊9 rpl_semi_sync_slave_enabled=1 #啟用slave模塊10 relay_log_purge=0 #禁止刪除中繼日志文件[root@host53 ~]# vim /etc/my.cnf4 [mysqld]5 server_id=536 log_bin=master53 7 plugin-load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" 8 rpl_semi_sync_master_enabled=1 #啟用master模塊 9 rpl_semi_sync_slave_enabled=1 #啟用slave模塊10 relay_log_purge=0 #禁止刪除中繼日志文件重啟51/52/53主機的數據庫服務[root@host51 mha]# systemctl restart mysqld [root@host52 mha]# systemctl restart mysqld [root@host53 mha]# systemctl restart mysqld檢查52 53主機從服務器的狀態[root@host52 mha]# mysql -uroot -p123456 -e 'show slave status \G' | grep -i yes mysql: [Warning] Using a password on the command line interface can be insecure.Slave_IO_Running: YesSlave_SQL_Running: Yes [root@host53 mha]# mysql -uroot -p123456 -e 'show slave status \G' | grep -i yes mysql: [Warning] Using a password on the command line interface can be insecure.Slave_IO_Running: YesSlave_SQL_Running: Yes檢查配置
一檢查集群環境 1.在管理主機，測試ssh配置 [root@host57 mha]# masterha_check_ssh --conf=/etc/mha/app1.cnf Tue Feb 25 01:12:09 2020 - [info] All SSH connection tests passed successfully.#debug后面是ok即為成功2.在管理主機，測試主從同步 [root@host57 mha]# masterha_check_repl --conf=/etc/mha/app1.cnf MySQL Replication Health is OK. #測試成功提示信息3.啟動管理服務 （啟動時會占用一個終端，可以在啟動之前再開一個終端連接，終端1用來啟動服務，終端2用來查看狀態）[root@host57 ~]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover #即使選項寫錯了，它的服務也可以照常啟動，只是會忽略該選項4.查看服務狀態 [root@host57 ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:4606) is running(0:PING_OK), master:192.168.4.51 #服務運行，監視主服務器192.168.4.51 [root@host57 ~]# ls /etc/mha #查看工作目錄下的文件列表 app1.cnf app1.master_status.health manager.log master_ip_failover測試配置
1.首先測試VIP地址是否依然存在，如果不存在則再次在51主機上添加VIP，這樣才可以使該IP為浮動IP[root@host51 ~]# ifconfig ens33:1 192.168.4.100/24 [root@host51 ~]# ifconfig ens33:1 ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500inet 192.168.4.100 netmask 255.255.255.0 broadcast 192.168.4.255ether 00:0c:29:87:50:30 txqueuelen 1000 (Ethernet) [root@host50 ~]# ping -c 2 192.168.4.100 PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data. 64 bytes from 192.168.4.100: icmp_seq=1 ttl=64 time=0.801 ms 64 bytes from 192.168.4.100: icmp_seq=2 ttl=64 time=0.535 ms2.測試在主服務器上添加授權用戶，讓客戶端可以登錄數據庫 [root@host51 ~]# mysql -uroot -p123456 mysql> create database gamedb; Query OK, 1 row affected (0.10 sec)mysql> create table gamedb.t1(id int); Query OK, 0 rows affected (0.13 sec)mysql> grant select ,insert on gamedb.* to tian@"%" identified by "123qqq...A"; Query OK, 0 rows affected, 1 warning (0.11 sec)mysql> select user ,host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | repluser | % | | root | % | | tian | % | | mysql.sys | localhost | | root | localhost | +-----------+-----------+ 5 rows in set (0.00 sec)客戶端50連接VIP訪問集群[root@host50 ~]# mysql -h192.168.4.100 -utian -p123qqq...A MySQL [(none)]> select @@hostname; +------------+ | @@hostname | +------------+ | host51 | +------------+ 1 row in set (0.00 sec) MySQL [(none)]> show grants; +--------------------------------------------------+ | Grants for tian@% | +--------------------------------------------------+ | GRANT USAGE ON *.* TO 'tian'@'%' | | GRANT SELECT, INSERT ON `gamedb`.* TO 'tian'@'%' | +--------------------------------------------------+ 2 rows in set (0.00 sec)MySQL [(none)]> insert into gamedb.t1 values(222); Query OK, 1 row affected (0.04 sec)MySQL [(none)]> insert into gamedb.t1 values(555); Query OK, 1 row affected (0.03 sec)MySQL [(none)]> insert into gamedb.t1 values(555); Query OK, 1 row affected (0.01 sec)MySQL [(none)]> insert into gamedb.t1 values(555); Query OK, 1 row affected (0.01 sec)MySQL [(none)]> insert into gamedb.t1 values(555); Query OK, 1 row affected (0.01 sec)MySQL [(none)]> select * from gamedb.t1; +------+ | id | +------+ | 222 | | 555 | | 555 | | 555 | | 555 | +------+ 5 rows in set (0.00 sec)在51主機上查看： mysql> select * from gamedb.t1; +------+ | id | +------+ | 222 | | 555 | | 555 | | 555 | | 555 | +------+ 5 rows in set (0.01 sec)在52主機上查看： [root@host52 ~]# mysql -uroot -p123456 -e 'select * from gamedb.t1' mysql: [Warning] Using a password on the command line interface can be insecure. +------+ | id | +------+ | 222 | | 555 | | 555 | | 555 | | 555 | +------+3.測試高可用```bash1. 將51主機的mysql服務停止 [root@host51 ~]# systemctl stop mysqld2.查看管理服務輸出的監控信息[root@host57 mha]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf \ > --ignore_last_failover #此時57管理主機的管理服務會報錯，因為51主機已經宕掉，管理服務此時會自動找到執行故障切換腳本 Thu Jun 20 17:05:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jun 20 17:05:58 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Thu Jun 20 17:05:58 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..Creating /var/tmp if not exists.. ok.Checking output directory is accessible or not..ok.Binlog found at /var/lib/mysql, up to master51.000002 Thu Jun 20 17:35:59 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jun 20 17:35:59 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Thu Jun 20 17:35:59 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..[root@host57 mha]# masterha_check_status --conf=/etc/mha/app1.cnf app1 is stopped(2:NOT_RUNNING). //監控到主服務器宕機 管理服務自動停止3.客戶端依然連接VIP地址，可以訪問到數據[root@host50 ~]# ping -c 2 192.168.4.100 #30秒內ping不通 PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data. 64 bytes from 192.168.4.100: icmp_seq=1 ttl=64 time=1.89 ms 64 bytes from 192.168.4.100: icmp_seq=2 ttl=64 time=0.777 ms[root@host50 ~]# mysql -h192.168.4.100 -uyaya55 -p123qqq...A //連接vip地址 MySQL [(none)]> insert into gamedb.t1 values(222); Query OK, 1 row affected (0.02 sec)MySQL [(none)]> insert into gamedb.t1 values(222); Query OK, 1 row affected (0.02 sec)MySQL [(none)]> insert into gamedb.t1 values(222); Query OK, 1 row affected (0.01 sec) MySQL [(none)]> select * from gamedb.t1; +------+ | id | +------+ | 222 | | 555 | | 555 | | 555 | | 555 | | 222 | | 222 | | 222 | +------+MySQL [(none)]> select @@hostname; +------------+ | @@hostname | +------------+ | host52 | +------------+ 1 row in set (0.01 sec)4.查看VIP地址 在host52主機查看到VIP地址。說明此時host53主機是host52主機的從服務器 [root@host52 ~]# ifconfig eth0:1 eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500inet 192.168.4.100 netmask 255.255.255.0 broadcast 192.168.4.255ether 52:54:00:f5:c4:6a txqueuelen 1000 (Ethernet)[root@host53 ~]# ifconfig eth0:1 //未查到vip地址 eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500ether 52:54:00:28:22:2e txqueuelen 1000 (Ethernet)[root@host53 ~]# mysql -uroot -p123qqq...A -e "show slave status\G" | grep -i 192 mysql: [Warning] Using a password on the command line interface can be insecure.Master_Host: 192.168.4.52 //主服務器Ip地址 [root@host53 ~]# mysql -uroot -p123qqq...A -e "show slave status\G" | grep -i yes mysql: [Warning] Using a password on the command line interface can be insecure.Slave_IO_Running: Yes //IO線程正常Slave_SQL_Running: Yes //SQL線程正常[root@host53 ~]# mysql -uroot -p123qqq...A -e "select * from gamedb.t1" //自動同步數據 +------+ | id | +------+ | 222 | | 555 | | 555 | | 555 | | 555 | | 222 | | 222 | | 222 | +------+5.查看主配置文件[root@host57 ~]# cat /etc/mha/app1.cnf [server default] manager_log=/etc/mha/manager.log manager_workdir=/etc/mha master_ip_failover_script=/etc/mha/master_ip_failover password=123qqq...A repl_password=123qqq...A repl_user=repluser ssh_port=22 ssh_user=root user=root[server2] candidate_master=1 hostname=192.168.4.52 port=3306[server3] candidate_master=1 hostname=192.168.4.53 port=3306 #此時已經沒有了server1的信息，因為故障切換腳本會自動將其剔除修復故障服務器
1.配置數據庫服務器啟動51主機數據庫服務 [root@host51 mha]# systemctl restart mysqld此時的51主機數據庫內的數據還是在宕機之前的數據 mysql> select * from gamedb.t1; +------+ | id | +------+ | 222 | | 555 | | 555 | | 555 | | 555 | +------+備份數據，使其與當前主服務器數據一致 [root@host52 ~]# mysqldump -uroot -p123456 --master-data gamedb > /root/gamedb.sql #在主服務器52做完全備份 [root@host52 ~]# grep master52 /root/gamedb.sql #查看日志名以及偏移量 CHANGE MASTER TO MASTER_LOG_FILE='master52.000001', MASTER_LOG_POS=907;恢復數據 [root@host52 ~]# scp /root/gamedb.sql root@192.168.4.51:/root #拷貝備份文件給51主機 [root@host51 ~]# mysql -uroot -p123456 gamedb < /root/gamedb.sql #51主機使用備份文件恢復數據[root@host51 ~]# mysql -uroot -p123456 -e 'select * from gamedb.t1' mysql: [Warning] Using a password on the command line interface can be insecure. +------+ | id | +------+ | 222 | | 555 | | 555 | | 555 | | 555 | | 222 | | 222 | | 222 | +------+ [root@host51 ~]# grep master52 /root/gamedb.sql CHANGE MASTER TO MASTER_LOG_FILE='master52.000001', MASTER_LOG_POS=907;[root@host51 ~]# mysql -uroot -p123456 mysql> change master to-> master_host="192.168.4.52",-> master_user="repluser",-> master_password="123qqq...A",-> master_log_file="master52.000001",-> master_log_pos=907; #指定主服務器信息 Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave; #啟動slave進程 Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G #查看狀態信息 *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.4.52Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master52.000001Read_Master_Log_Pos: 907Relay_Log_File: host51-relay-bin.000002Relay_Log_Pos: 319Relay_Master_Log_File: master52.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 907Relay_Log_Space: 527Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 52Master_UUID: a2f5308e-4e6a-11ea-9941-000c29e6ec7fMaster_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)2.配置管理服務器 [root@host57 ~]# vim /etc/mha/app1.cnf #手動添加51主機信息 [server1] candidate_master=1 hostname=192.168.4.51 port=33063.測試集群環境 [root@host57 mha4mysql-manager-0.56]# masterha_check_ssh --conf=/etc/mha/app1.cnf #測試ssh [info] All SSH connection tests passed successfully.//成功 [root@host57 mha4mysql-manager-0.56]# masterha_check_repl --conf=/etc/mha/app1.cnf #測試主從同步 MySQL Replication Health is OK. //成功4.重啟管理服務 [root@host57 mha4mysql-manager-0.56]# masterha_stop --conf=/etc/mha/app1.cnf //停止管理服務 Stopped app1 successfully. [root@host57 mha4mysql-manager-0.56]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf \ --ignore_last_failover //啟動管理服務 [root@host57 ~]# masterha_check_status --conf=/etc/mha/app1.cnf #查看狀態，服務運行，監視服務器52 app1 (pid:14134) is running(0:PING_OK), master:192.168.4.52總結
- 配置Mysql集群（MHA+Mysql主從同步）
ssh免密登錄
配置MySQL一主二從
安裝依賴的Perl軟件包
安裝mha軟件
創建并編輯主配置文件
創建故障切換腳本并指定VIP地址
把VIP地址配置在master主數據庫服務器
安裝mha軟件
授權監控用戶和主從同步連接用戶、啟用半同步復制模式、禁止自動刪除
在兩臺從服務器授權同步數據的連接用戶
測試ssh連接
測試主從同步
啟動管理服務，查看服務狀態（如何停止管理服務）
測試高可用
配置數據庫服務器
配置管理主機
總結
以上是生活随笔為你收集整理的DBA（六）：MHA集群的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： DBA（五）：数据分片、Mycat服务
- 下一篇： DBA（七）：PXC、MySQL存储引擎