mysql中MHA配置及切換方式有哪些

這篇文章主要介紹mysql中MHA配置及切換方式有哪些，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

master節(jié)點(diǎn)/MHA管理節(jié)點(diǎn)：172.31.217.183
slave節(jié)點(diǎn)/MHA成員節(jié)點(diǎn)：172.31.217.182
已開啟半同步。

數(shù)據(jù)庫版本為5.7

配置免密碼登錄
master節(jié)點(diǎn)：
root@bd-dev-mingshuo-183:/opt/soft#ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
36:39:6b:1e:40:f2:85:31:db:d0:3e:ab:05:0e:fd:37 root@bd-dev-mingshuo-183
The key's randomart image is:
+--[ RSA 2048]----+
|      +.         |
|       B.        |
|    ..+.o        |
|    .+o.o.       |
|     oooSo       |
|      .o++E      |
|       o+. .     |
|      .o .       |
|        .        |
+-----------------+
root@bd-dev-mingshuo-183:/opt/soft#ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.31.217.182
root@172.31.217.182's password:
Now try logging into the machine, with "ssh 'root@172.31.217.182'", and check in:

  .ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

root@bd-dev-mingshuo-183:/u01#ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.31.217.183
root@172.31.217.183's password:
Now try logging into the machine, with "ssh 'root@172.31.217.183'", and check in:

  .ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

slave節(jié)點(diǎn)：
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.31.217.183
ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.31.217.182

slave節(jié)點(diǎn)：
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'read_only'\G
*************************** 1. row ***************************
Variable_name: read_only
        Value: ON
1 row in set (0.00 sec)

read_only為1代表是只讀，0代表讀寫。從庫只讀不會(huì)影響slave的日志應(yīng)用。但是不要把參數(shù)寫入?yún)?shù)文件，因?yàn)榭赡墚?dāng)這個(gè)slave切換為master就會(huì)造成普通用戶不能寫入。當(dāng)然這個(gè)參數(shù)在配置mha過程中是可選的。

部署安裝包
manager節(jié)點(diǎn)安裝manager包
所有節(jié)點(diǎn)安裝node包
先安裝node包
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

在master上創(chuàng)建mha管理賬號(hào)
grant all privileges on *.* to mha@'172.31.217.%' identified by 'oracle';
flush privileges;

創(chuàng)建目錄，用于存放mha配置文件和mha日志
mkdir -p /u01/mha/log
chown mysql.mysql -R mha

編輯配置文件
vi /u01/mha/mha.cnf

[server default]
manager_log=/u01/mha/log/manager.log
manager_workdir=/u01/mha/log

master_binlog_dir=/u01/mysql/3306/data
user=mha
password=oracle
ping_interval=2  
repl_user=repl_user
repl_password=oracle
ssh_user=root

[server1]
hostname=172.31.217.183
port=3306

[server2]
hostname=172.31.217.182
port=3306

配置文件可選參數(shù)：
[server default]模塊：
ping_interval=1         //設(shè)置監(jiān)控主庫，發(fā)送ping包的時(shí)間間隔，默認(rèn)是3秒，嘗試三次沒有回應(yīng)的時(shí)候自動(dòng)進(jìn)行railover
remote_workdir=/tmp     //設(shè)置遠(yuǎn)端mysql在發(fā)生切換時(shí)binlog的保存位置
report_script=/usr/local/send_report    //設(shè)置發(fā)生切換后發(fā)送的報(bào)警的腳本          
shutdown_script=""      //設(shè)置故障發(fā)生后關(guān)閉故障主機(jī)腳本（該腳本的主要作用是關(guān)閉主機(jī)放在發(fā)生腦裂,這里沒有使用） 從庫模塊：
candidate_master=1   //設(shè)置為候選master，如果設(shè)置該參數(shù)以后，發(fā)生主從切換以后將會(huì)將此從庫提升為主庫，即使這個(gè)主庫不是集群中事件最新的slave
check_repl_delay=0   //默認(rèn)情況下如果一個(gè)slave落后master 100M的relay logs的話，MHA將不會(huì)選擇該slave作為一個(gè)新的master，因?yàn)閷?duì)于這個(gè)slave的恢復(fù)需要花費(fèi)很長時(shí)間，通過設(shè)置check_repl_delay=0,MHA觸發(fā)切換在選擇一個(gè)新的master的時(shí)候?qū)?huì)忽略復(fù)制延時(shí)，這個(gè)參數(shù)對(duì)于設(shè)置了candidate_master=1的主機(jī)非常有用，因?yàn)檫@個(gè)候選主在切換的過程中一定是新的master

檢測(cè)同步及ssh登錄
masterha_check_ssh --conf=/u01/mha/mha.cnf
masterha_check_repl --conf=/u01/mha/mha.cnf

中間報(bào)了很多次錯(cuò)，部分解決方案：
ln -s /opt/mysql-5.7.23/bin/mysql /usr/bin/mysql
ln -s /opt/mysql-5.7.23/bin/mysqlbinlog /usr/bin/mysqlbinlog
卸載mha4mysql-manager-0.58-0.el7.centos.noarch.rpm，安裝mha4mysql-manager-0.56-0.el6.noarch.rpm

啟動(dòng)mha
nohup masterha_manager --conf=/u01/mha/mha.cnf > /u01/mha/log/manager.log 2>&1 &

檢查mha狀態(tài)
root@bd-dev-mingshuo-183:/opt/soft#masterha_check_status --conf=/u01/mha/mha.cnf
mha (pid:24910) is running(0:PING_OK), master:172.31.217.183 配置VIP
在server default模塊下面添加
master_ip_failover_script=/usr/local/bin/master_ip_failover

從源碼包中將master_ip_failover拷貝到/usr/local/bin/下面
cd /opt/soft/MHAsoft/mha4mysql-manager-0.56/samples/scripts
cp -ra master_ip_failover /usr/local/bin/master_ip_failover

修改/usr/local/bin/master_ip_failover
my $vip = '172.31.217.203/24';  #此處為你要設(shè)置的虛擬ip
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth3:$key $vip"; #此處改為你的網(wǎng)卡名稱
my $ssh_stop_vip = "/sbin/ifconfig eth3:$key down"; 注：
my (
    $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
    $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
);

將上面內(nèi)容添加到這里

GetOptions(
    'command=s'          => \$command,
    'ssh_user=s'         => \$ssh_user,
    'orig_master_host=s' => \$orig_master_host,
    'orig_master_ip=s'   => \$orig_master_ip,
    'orig_master_port=i' => \$orig_master_port,
    'new_master_host=s'  => \$new_master_host,
    'new_master_ip=s'    => \$new_master_ip,
    'new_master_port=i'  => \$new_master_port,
); 配置網(wǎng)卡VIP
ifconfig eth3:1 172.31.217.203/24

ifconfig
eth3      Link encap:Ethernet  HWaddr 54:0F:5D:2C:4D:77  
          inet addr:172.31.217.202  Bcast:172.31.217.255  Mask:255.255.255.0
          inet6 addr: fe80::560f:5dff:fe2c:4d77/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:74742667 errors:0 dropped:0 overruns:0 frame:0
          TX packets:12 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:52680755472 (49.0 GiB)  TX bytes:740 (740.0 b)

eth3:1    Link encap:Ethernet  HWaddr 54:0F:5D:2C:4D:77  
          inet addr:172.31.217.203  Bcast:172.31.217.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

停止mha
masterha_stop --conf=/u01/mha/mha.cnf

再次開啟mha
nohup masterha_manager --conf=/u01/mha/mha.cnf > /u01/mha/log/manager.log 2>&1 &

報(bào)錯(cuò)：
Bareword "FIXME_xxx" not allowed while "strict subs" in use at /usr/local/bin/master_ip_failover line 98.
Execution of /usr/local/bin/master_ip_failover aborted due to compilation errors.
Mon Sep 17 10:56:04 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln226]  Failed to get master_ip_failover_script status with return code 255:0.
Mon Sep 17 10:56:04 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/bin/masterha_manager line 50
Mon Sep 17 10:56:04 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Mon Sep 17 10:56:04 2018 - [info] Got exit code 1 (Not master dead).

直接把FIXME_xxx相關(guān)行注釋掉算了。

再次開啟mha
nohup masterha_manager --conf=/u01/mha/mha.cnf > /u01/mha/log/manager.log 2>&1 &
ok！

關(guān)閉主庫
mysqladmin -uroot -poracle shutdown

檢查備庫
mysql> show slave status;
Empty set (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: slave-relay-bin.000002
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
備庫已經(jīng)自動(dòng)切成了主庫。停掉的主庫上面的mha軟件也自動(dòng)停止了。 恢復(fù)之前的主從關(guān)系：
現(xiàn)在拉起停掉的主庫，會(huì)發(fā)現(xiàn)主庫沒有主動(dòng)加入到集群中去。
主庫查詢?nèi)罩疚恢茫?br/>mysql> show master status\G
*************************** 1. row ***************************
             File: master-bin.000005
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
備庫：
change master to
master_host='bd-dev-mingshuo-183',
master_port=3306,
master_user='repl_user',
master_password='oracle',
master_log_file='master-bin.000005',
master_log_pos=154;

start slave;
主庫啟用mha軟件，注意這里要加-ignore_last_failover參數(shù)，否則會(huì)報(bào)錯(cuò)：
Mon Sep 17 14:45:56 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Sep 17 14:45:56 2018 - [info] Reading application default configuration from /u01/mha/mha.cnf..
Mon Sep 17 14:45:56 2018 - [info] Reading server configuration from /u01/mha/mha.cnf..
Mon Sep 17 14:45:56 2018 - [info] MHA::MasterMonitor version 0.56.
Mon Sep 17 14:45:56 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln193] There is no alive slave. We can't do failover
Mon Sep 17 14:45:56 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 326
Mon Sep 17 14:45:56 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Mon Sep 17 14:45:56 2018 - [info] Got exit code 1 (Not master dead).

開啟mha軟件：
nohup masterha_manager -ignore_last_failover --conf=/u01/mha/mha.cnf > /u01/mha/log/manager.log 2>&1 &

上面是自動(dòng)failover的過程，后面再來測(cè)試一下手動(dòng)failover
停止mha manager：
masterha_stop --conf=/u01/mha/mha.cnf

停止master數(shù)據(jù)庫
mysqladmin -uroot -poracle shutdown

手動(dòng)切換
masterha_master_switch --master_state=dead --conf=/u01/mha/mha.cnf --dead_master_host=172.31.217.183 --dead_master_port=3306 --new_master_host=172.31.217.182  --new_master_port=3306 --ignore_last_failover
上面是自動(dòng)failover的過程，后面再來測(cè)試一下在線切換：
manager節(jié)點(diǎn)：
停止mha manager：
masterha_stop --conf=/u01/mha/mha.cnf
masterha_master_switch --conf=/u01/mha/mha.cnf  --master_state=alive --new_master_host=172.31.217.182 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=100
Mon Sep 17 15:47:29 2018 - [info] MHA::MasterRotate version 0.56.
Mon Sep 17 15:47:29 2018 - [info] Starting online master switch..
Mon Sep 17 15:47:29 2018 - [info]
Mon Sep 17 15:47:29 2018 - [info] * Phase 1: Configuration Check Phase..
Mon Sep 17 15:47:29 2018 - [info]
Mon Sep 17 15:47:29 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Sep 17 15:47:29 2018 - [info] Reading application default configuration from /u01/mha/mha.cnf..
Mon Sep 17 15:47:29 2018 - [info] Reading server configuration from /u01/mha/mha.cnf..
Mon Sep 17 15:47:29 2018 - [info] GTID failover mode = 0
Mon Sep 17 15:47:29 2018 - [info] Current Alive Master: 172.31.217.183(172.31.217.183:3306)
Mon Sep 17 15:47:29 2018 - [info] Alive Slaves:
Mon Sep 17 15:47:29 2018 - [info]   172.31.217.182(172.31.217.182:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Sep 17 15:47:29 2018 - [info]     Replicating from bd-dev-mingshuo-183(172.31.217.183:3306)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.31.217.183(172.31.217.183:3306)? (YES/no): YES
Mon Sep 17 15:47:33 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Mon Sep 17 15:47:33 2018 - [info]  ok.
Mon Sep 17 15:47:33 2018 - [info] Checking MHA is not monitoring or doing failover..
Mon Sep 17 15:47:33 2018 - [info] Checking replication health on 172.31.217.182..
Mon Sep 17 15:47:33 2018 - [info]  ok.
Mon Sep 17 15:47:33 2018 - [info] 172.31.217.182 can be new master.
Mon Sep 17 15:47:33 2018 - [info]
From:
172.31.217.183(172.31.217.183:3306) (current master)
 +--172.31.217.182(172.31.217.182:3306)

To:
172.31.217.182(172.31.217.182:3306) (new master)
 +--172.31.217.183(172.31.217.183:3306)

Starting master switch from 172.31.217.183(172.31.217.183:3306) to 172.31.217.182(172.31.217.182:3306)? (yes/NO): yes
Mon Sep 17 15:47:55 2018 - [info] Checking whether 172.31.217.182(172.31.217.182:3306) is ok for the new master..
Mon Sep 17 15:47:55 2018 - [info]  ok.
Mon Sep 17 15:47:55 2018 - [info] 172.31.217.183(172.31.217.183:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Mon Sep 17 15:47:55 2018 - [info] 172.31.217.183(172.31.217.183:3306): Resetting slave pointing to the dummy host.
Mon Sep 17 15:47:55 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Sep 17 15:47:55 2018 - [info]
Mon Sep 17 15:47:55 2018 - [info] * Phase 2: Rejecting updates Phase..
Mon Sep 17 15:47:55 2018 - [info]
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
Mon Sep 17 15:48:32 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Mon Sep 17 15:48:32 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..
Mon Sep 17 15:48:32 2018 - [info]  ok.
Mon Sep 17 15:48:32 2018 - [info] Orig master binlog:pos is master-bin.000007:154.
Mon Sep 17 15:48:32 2018 - [info]  Waiting to execute all relay logs on 172.31.217.182(172.31.217.182:3306)..
Mon Sep 17 15:48:32 2018 - [info]  master_pos_wait(master-bin.000007:154) completed on 172.31.217.182(172.31.217.182:3306). Executed 0 events.
Mon Sep 17 15:48:32 2018 - [info]   done.
Mon Sep 17 15:48:32 2018 - [info] Getting new master's binlog name and position..
Mon Sep 17 15:48:32 2018 - [info]  slave-relay-bin.000002:154
Mon Sep 17 15:48:32 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.31.217.182', MASTER_PORT=3306, MASTER_LOG_FILE='slave-relay-bin.000002', MASTER_LOG_POS=154, MASTER_USER='repl_user', MASTER_PASSWORD='xxx';
Mon Sep 17 15:48:32 2018 - [info] Setting read_only=0 on 172.31.217.182(172.31.217.182:3306)..
Mon Sep 17 15:48:32 2018 - [info]  ok.
Mon Sep 17 15:48:32 2018 - [info]
Mon Sep 17 15:48:32 2018 - [info] * Switching slaves in parallel..
Mon Sep 17 15:48:32 2018 - [info]
Mon Sep 17 15:48:32 2018 - [info] Unlocking all tables on the orig master:
Mon Sep 17 15:48:32 2018 - [info] Executing UNLOCK TABLES..
Mon Sep 17 15:48:32 2018 - [info]  ok.
Mon Sep 17 15:48:32 2018 - [info] Starting orig master as a new slave..
Mon Sep 17 15:48:32 2018 - [info]  Resetting slave 172.31.217.183(172.31.217.183:3306) and starting replication from the new master 172.31.217.182(172.31.217.182:3306)..
Mon Sep 17 15:48:32 2018 - [info]  Executed CHANGE MASTER.
Mon Sep 17 15:48:32 2018 - [info]  Slave started.
Mon Sep 17 15:48:32 2018 - [info] All new slave servers switched successfully.
Mon Sep 17 15:48:32 2018 - [info]
Mon Sep 17 15:48:32 2018 - [info] * Phase 5: New master cleanup phase..
Mon Sep 17 15:48:32 2018 - [info]
Mon Sep 17 15:48:32 2018 - [info]  172.31.217.182: Resetting slave info succeeded.
Mon Sep 17 15:48:32 2018 - [info] Switching master to 172.31.217.182(172.31.217.182:3306) completed successfully.

注意切換過程中會(huì)有一個(gè)地方詢問你
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
沒有disable主庫的寫入，切換之后連接這的應(yīng)用程序會(huì)繼續(xù)往里面寫入，這樣ok嗎？
這里我只是測(cè)試這個(gè)在線切換的過程的可用性，所以輸入了yes。
切換完成之后mha軟件暫停了。

以上是“mysql中MHA配置及切換方式有哪些”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！