mysql mha gtid_Mysql MHA(GTID)配置(实操)

实现环境

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 [email protected]%‘ identified by ‘123456‘;

mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]%‘;

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]%‘identified by ‘123456‘; (只在主数据库上执行，可以同步到其他服务器上)

mysql> show grants for [email protected]%‘ ;  #检查创建结果(所有服务器上都执行)

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 ($@) {

warn "Got Error: $@\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 ($@) {

warn $@;

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 飘移。

原文：http://www.cnblogs.com/chenqs/p/7358004.html