MHA群集管理
創(chuàng)新互聯(lián)建站于2013年開始，先為上海等服務(wù)建站，上海等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為上海企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。
環(huán)境：
192.168.205.37: as MHA server
192.168.205.47: as Master server
192.168.205.57: as Slave1 server
192.168.205.67: as Slave2 server
版本：
OS: centos 7 1810 with mini install
mariadb-server 5.5.60
mha4MySQL-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
目地：
MHA使用perl腳本寫的程序，當(dāng)主節(jié)點down時如果從節(jié)點復(fù)制沒有完成時，當(dāng)他成為主節(jié)點后，它需要嘗試將主節(jié)點的二進(jìn)制日志復(fù)制到新的主節(jié)點，從而保證數(shù)據(jù)的完整性，所以它需要sshkey驗證。我們使用一臺做為管理節(jié)點，其中三個為數(shù)據(jù)庫節(jié)點，一主兩從，模擬主節(jié)點down機，看從是如何被提升為主節(jié)點的。
安裝數(shù)據(jù)庫
- 安裝mariadb使用如下腳本：
[root@master data]#cat maridb_yum.sh #!/bin/bash ID=`ip a show dev eth0 | sed -r '3!d;s@(.*inet)(.*)(/.*)@\2@' | cut -d. -f4` rpm -q mariadb-server ||yum install -y mariadb-server [ -d /data/mysql ] || mkdir -p /data/mysql [ -d /data/logs ] || mkdir -p /data/logs chown mysql:mysql /data/{mysql,logs} sed -i 's@datadir=/var/lib/mysql@datadir=/data/mysql@' /etc/my.cnf grep "log-bin" /etc/my.cnf || sed -i '/\[mysqld\]/a log-bin=/data/logs/bin' /etc/my.cnf grep "innodb_file_per_table" /etc/my.cnf || sed -i '/\[mysqld\]/a innodb_file_per_table = on' /etc/my.cnf grep "skip_name_resolve" /etc/my.cnf || sed -i '/\[mysqld\]/a skip_name_resolve = on' /etc/my.cnf grep "server-id" /etc/my.cnf || sed -i "/\[mysqld\]/a server-id=$ID" /etc/my.cnf service mariadb restart - 修改兩個從節(jié)點的配置文件
[root@slave1 data]#vi /etc/my.cnf [mysqld] read_only relay_log_purge=0 #不刪除中繼日志 建立主節(jié)點的復(fù)制位置和帳號
MariaDB [(none)]> show master logs; +------------+-----------+ | Log_name | File_size | +------------+-----------+ | bin.000001 | 30373 | | bin.000002 | 1038814 | | bin.000003 | 245 | +------------+-----------+ 3 rows in set (0.00 sec) MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant all on *.* to mhauser@'192.168.205.%' identified by 'centos'; Query OK, 0 rows affected (0.00 sec)配置從節(jié)點的change master to并啟動線程
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.205.47', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='bin.000003', -> MASTER_LOG_POS=245; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.47 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000003 Read_Master_Log_Pos: 401 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 679 Relay_Master_Log_File: bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: YesMHA實現(xiàn)
- 安裝包，mha依賴epel源，我們要把epel起用
[root@MHA ~]#yum install mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm - 在所有的sql節(jié)點上安裝node包
[root@master ~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm [root@slave1 data]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm [root@slave2 data]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm - 配置ssh key 驗證
[root@MHA ~]#ssh-keygen [root@MHA ~]#ssh-copy-id 192.168.205.37 [root@MHA ~]#cat .ssh/authorized_keys [root@MHA ~]#scp -r .ssh 192.168.205.47:/root/ [root@MHA ~]#scp -r .ssh 192.168.205.57:/root/ [root@MHA ~]#scp -r .ssh 192.168.205.67:/root/ - 生成配置MHA文件
[root@MHA ~]#mkdir /etc/mha [root@MHA ~]#vim /etc/mha/app1.cnf [server default] master_binlog_dir=/data/logs/ user=mhauser password=centos manager_workdir=/data/mastermha/app1/ manager_log=/data/mastermha/app1/manager.log remote_workdir=/data/mastermha/app1/ ssh_user=root repl_user=repluser repl_password=centos ping_interval=1 [server1] hostname=192.168.205.47 candidate_master=1 [server2] hostname=192.168.205.57 [server3] hostname=192.168.205.67 candidate_master=1 - 啟動之前進(jìn)行檢查ssh，repl復(fù)制是否準(zhǔn)備好
[root@MHA ~]#masterha_check_ssh --conf=/etc/mha/app1.cnf [root@MHA ~]#masterha_check_repl --conf=/etc/mha/app1.cnf - 起動進(jìn)程，此進(jìn)程前臺運行，當(dāng)主節(jié)點失敗時切換完成后它會終止，所以要想持續(xù)要重啟進(jìn)程
[root@MHA ~]#masterha_manager --conf=/etc/mha/app1.cnf Mon Aug 12 23:33:22 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Aug 12 23:33:22 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Mon Aug 12 23:33:22 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..測試
我們模擬一個正在寫入操作時，主節(jié)點down, 看服務(wù)器能不能正常切換并不能復(fù)制丟失的數(shù)據(jù)
MariaDB [(none)]> use test; Database changed MariaDB [test]> create table testlog (id int auto_increment primary key,name char(10),age int default 20); Query OK, 0 rows affected (0.01 sec) MariaDB [test]> MariaDB [test]> delimiter $$ MariaDB [test]> MariaDB [test]> create procedure sp_testlog() -> begin -> declare i int; -> set i = 1; -> while i <= 100000 -> do insert into testlog(name,age) values (concat('wang',i),i); -> set i = i +1; -> end while; -> end$$ Query OK, 0 rows affected (0.00 sec) MariaDB [test]> MariaDB [test]> delimiter ; MariaDB [test]> call sp_testlog;- 正在寫入數(shù)據(jù)時我們及時關(guān)閉47服務(wù)器，MHA檢測到節(jié)點，并響應(yīng)，成功提升其它的為主后退出程序。
[root@MHA ~]#masterha_manager --conf=/etc/mha/app1.cnf Mon Aug 12 23:33:22 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Aug 12 23:33:22 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Mon Aug 12 23:33:22 2019 - [info] Reading server configuration from /etc/mha/app1.cnf.. Mon Aug 12 23:45:16 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Aug 12 23:45:16 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Mon Aug 12 23:45:16 2019 - [info] Reading server configuration from /etc/mha/app1.cnf.. [root@MHA ~]# 我們可以查看日志看一下發(fā)生的情況，slave2提升為主
[root@MHA data]#cat mastermha/app1/manager.log ----- Failover Report ----- app1: MySQL Master failover 192.168.205.47(192.168.205.47:3306) to 192.168.205.67(192.168.205.67:3306) succeeded Master 192.168.205.47(192.168.205.47:3306) is down! Check MHA Manager logs at MHA:/data/mastermha/app1/manager.log for details. Started automated(non-interactive) failover. The latest slave 192.168.205.57(192.168.205.57:3306) has all relay logs for recovery. Selected 192.168.205.67(192.168.205.67:3306) as a new master. 192.168.205.67(192.168.205.67:3306): OK: Applying all logs succeeded. 192.168.205.57(192.168.205.57:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.205.57(192.168.205.57:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.205.67(192.168.205.67:3306) 192.168.205.67(192.168.205.67:3306): Resetting slave info succeeded. Master failover to 192.168.205.67(192.168.205.67:3306) completed successfully.- 我們來核實一下，在slave2上看一下slave的狀態(tài)，發(fā)現(xiàn)已經(jīng)沒了，因為它是主
MariaDB [(none)]> show slave status\G Empty set (0.00 sec) - 檢查一下slave1，發(fā)現(xiàn)主節(jié)點指向了slave2
MariaDB [test]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.67 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000005 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes - 在新主節(jié)點上看一下是否將read_only off掉，發(fā)現(xiàn)是，但不要忘記改回配置文件，如果重啟的話又會變成read_only
MariaDB [(none)]> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ 1 row in set (0.00 sec)
網(wǎng)頁名稱：MHA群集管理
網(wǎng)址分享：http://m.jiaotiyi.com/article/iiheci.html