Mysql MHA(GTID)配置(實操)

標籤：centos   epel   .sql   lob   line   eth0   ase   ice   http   

實現環境
　　centos6.7
　　MYSQL5.6.36
　　主：192.168.1.191
　　從1：192.168.1.145
　　從2：192.168.1.146
　　監測：放在從2上 192.168.1.146
　　虛擬IP:192.168.1.222

準備軟體包：下載連結: https://pan.baidu.com/s/1jHYafcU 密碼: irbv
　　epel-release-6-8.noarch.rpm   (所有伺服器上都要)

　　mha4mysql-node-0.56-0.el6.noarch.rpm  (所有伺服器上都要)

　　mha4mysql-manager-0.56-0.el6.noarch.rpm   (監測伺服器)

　

步驟

1.在主DB伺服器上建立複製帳號 (在Master上執行)
# mysql
mysql> CREATE USER ‘repl‘@‘192.168.1.%‘ identified by ‘123456‘;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘192.168.1.%‘;

 

2.主伺服器配置 (在Master上執行)
# vim /etc/my.cnf
----------------------------內容如下---------------------------------

[mysqld]

basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306

# binary logging #
log-bin = /data/mysql/log-bin
# replice #
server_id = 191
gtid_mode = on
enforce_gtid_consiste = on
log_slave_updates = on
----------------------------內容如上---------------------------------

 

3.從伺服器配置 (在兩個從伺服器上執行)
# vim /etc/my.cnf
----------------------------內容如下----------------------------------

[mysqld]

basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306

# binary logging #
log-bin = /data/mysql/mysql-bin
# replice #
# server-id = 145    #選擇對應的
# server-id = 146   #選擇對應的
relay_log = /data/mysql/relay-bin
gtid_mode = on
enforce_gtid_consistency = on
log_slave_update = on
read_only = on
master_info_repository = TABLE
relay_log_info_repository = TABLE
----------------------------內容如上----------------------------------

 

4.重啟所有的MYSQL （所有伺服器上都執行）
　　# service mysqld restart

5.備份主要資料庫(如果主從上的資料庫版本一樣，可以全備) (在Master上執行)
　　# mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases > full.sql

6.將主要資料庫備份傳輸到從資料庫中 (在Master上執行)
　　# scp /root/full.sql [email protected]:/root
　　# scp /root/full.sql [email protected]:/root

7.將主要資料庫的備份匯入到從資料庫中 (在兩個從上執行)
　　# mysql -uroot -p < full.sql

8.初始複製鏈路 (在兩個從上執行)
　　# mysql
　　mysql>
change master to
master_host=‘192.168.1.191‘,
master_user=‘repl‘,
master_password=‘123456‘,
master_auto_position=1;
　　mysql>start slave;
　　mysql>show slave status\G

以上全部操作為基於GTID的複製配置部分

##########################################################################

9.SSH配置（目前使用者為root）（所有伺服器上都執行）
# ssh-keygen           #全部按斷行符號
# ssh-copy-id -i /root/.ssh/id_rsa ‘-p 22 192.168.1.191‘
# ssh-copy-id -i /root/.ssh/id_rsa ‘-p 22 192.168.1.145‘
# ssh-copy-id -i /root/.ssh/id_rsa ‘-p 22 192.168.1.146‘

 

10.安裝 mha4mysql-node-0.56-0.el6.noarch.rpm （所有伺服器上都執行）
# rpm -ivh epel-release-6-8.noarch.rpm
# yum -y install perl-DBD-MySQL   ncftp
# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

11.安裝 mha4mysql-manager-0.56-0.el6.noarch.rpm （監測伺服器上執行）
# yum -y  install  perl-Config-Tiny.noarch   perl-Time-HiRes   perl-Parallel-ForkManager    perl-Log-Dispatch-Perl.noarch
# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

 

12.建立MHA工作目錄及資料庫帳號
# mkdir -p /home/mysql_mha #建立MHA的工作目錄 （所有伺服器上都執行）
# mysql
mysql> grant all privileges on *.*  to [email protected]‘192.168.1.%‘identified by ‘123456‘; (只在主要資料庫上執行，可以同步到其他伺服器上)
mysql> show grants for [email protected]‘192.168.1.%‘ ;  #檢查建立結果（所有伺服器上都執行）

 

13.配置mha的設定檔 （監測伺服器上執行）
# mkdir -p /etc/mha
# touch /etc/mha/mysql_mha.cnf
# vim /etc/mha/mysql_mha.cnf
----------------------------內容如下----------------------------------
[server default]
user=mha
password=123456
manager_workdir=/home/mysql_mha             #MHA工作目錄
manager_log=/home/mysql_mha/manager.log   #MHA管理日誌
remote_workdir=/home/mysql_mha               #MHA遠程工作目錄
ssh_user=root                    #ssh時的使用者
repl_user=repl
repl_password=123456
ping_interval=1
master_binlog_dir=/data/mysql    #對應修改
master_ip_failover_script=/usr/bin/master_ip_failover  
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.1.191 -s 192.168.1.145 -s 192.168.1.146

[server1]
hostname=192.168.1.191
candidate_master=1
[server2]
hostname=192.168.1.145
candidate_master=1
[server3]
hostname=192.168.1.146
no_master=1          #不參入選舉

----------------------------內容如上----------------------------------

14.建立master_ip_failover指令碼，用於自動切換 （監測伺服器上執行）
# touch /usr/bin/master_ip_failover
# chomd 777 /usr/bin/master_ip_failover
# vim /usr/bin/master_ip_failover
----------------------------內容如下----------------------------------（指令碼是在網上找的，親測能用）
#!/usr/bin/env perl
use strict;
use warnings FATAL =>‘all‘;

use Getopt::Long;

my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);

my $vip = ‘192.168.1.222/24‘;   #對應修改虛擬IP就可以了
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
my $exit_code = 0;

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,
);

exit &main();

sub main {

#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "\n\n\n***************************************************************\n";
print "Disabling the VIP - $vip on old master: $orig_master_host\n";
print "***************************************************************\n\n\n\n";
&stop_vip();
$exit_code = 0;
};
if ([email protected]) {
warn "Got Error: [email protected]\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {

# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "\n\n\n***************************************************************\n";
print "Enabling the VIP - $vip on new master: $new_master_host \n";
print "***************************************************************\n\n\n\n";
&start_vip();
$exit_code = 0;
};
if ([email protected]) {
warn [email protected];
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}

# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub usage {
print
"Usage: master_ip_failover –command=start|stop|stopssh|status –orig_master_host=host –orig_master_ip=ip –orig_master_port=po
rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n";
}
----------------------------內容如上----------------------------------

 

15.檢查相關配置並啟動MHA（監測伺服器上執行）
# masterha_check_ssh --conf=/etc/mha/mysql_mha.cnf
# masterha_check_repl --conf=/etc/mha/mysql_mha.cnf
# nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf    &      #後台啟動

 

16.配置虛擬IP (在Master上執行)
# ip addr
# ifconfig eth0:1  192.168.1.222/24
# ip addr

 

17.測試能否自動轉移
# service mysqld stop (在Master上執行)
# ip addr (在Master上執行)
# ip addr (在從1上執行)

如果192.168.1.222成功的從192.168.1.191 飄移到192.168.1.146上就成功了。

以上操作步驟為本人實操記錄，實驗成功實現了VIP 飄移。

 

Mysql MHA(GTID)配置(實操)