软件版本
MySQL: 5.7
MHA: 0.56
CentOS: 7.4
MySQL服务器
Master: 192.168.1.224 mysql01
Slave1: 192.168.1.225 mysql02
Slave2: 192.168.1.226 mysql03
MHA
Manager: 192.168.1.224 mysql01
Node1: 192.168.1.225 mysql02
Node2: 192.168.1.226 mysql03
VIP
192.168.1.221
配置主机解析
cat /etc/hosts 192.168.1.224 mysql01 192.168.1.225 mysql02 192.168.1.226 mysql03
在三台服务器搭建MySQL主从复制集群
1、配置MySQL yum安装源
cat /etc/yum.repos.d/MySQL.repo [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/ enabled=1 gpgcheck=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
2、安装MySQL
yum install mysql-community-{server,client,common,libs}-*
3、修改配置文件
#Master server-id=1 gtid_mode=on enforce_gtid_consistency=on log_bin binlog_format=row #Slave1 server-id=2 log_bin gtid_mode=on enforce_gtid_consistency=on log_slave_updates=1 binlog_format=row #Slave2 server-id=3 log_bin gtid_mode=on enforce_gtid_consistency=on log_slave_updates=1 binlog_format=row
4、启动MySQL,并获取初始密码
systemctl start mysqld grep password /var/log/mysqld.log
5、在三台服务器上修改root密码和创建复制账户
mysql -u root -p set sql_log_bin=0; alter user root@'localhost' identified by 'MySQL5.7'; create user root@'192.168.1.%' identified by 'MySQL5.7' grant all on *.* to root@'192.168.1.%'; flush privileges; set sql_log_bin=1;
6、指定Slave的Master,启动slave,查看状态
change master to master_host='192.168.1.224',master_user='root',master_password='MySQL5.7',master_auto_position=1; start slave; show slave status\G
在三台服务器实现免密码登录
ssh-keygen -t rsa ssh-copy-id mysql01 ssh-copy-id mysql02 ssh-copy-id mysql03
安装MHA
1、配置epel源,安装依赖包
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo yum install -y perl-CPAN* perl-DBD-MySQL* perl-Params-Validate perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
2、在三台服务器上安装node
tar -zxvf mha4mysql-node-0.56.tar.gz cd mha4mysql-node-0.56 perl Makefile.PL make && make install
3、在Mater安装manager
tar -zxvf mha4mysql-manager-0.57.tar.gz perl Makefile.PL make make install
4、创建目录和配置文件
mkdir -p /usr/local/mha mkdir -p /etc/mhacd /etc/mha/ cat /etc/mha/mha.conf ---------------------------------------------------------------------------------------------------- [server default] user=root password=MySQL5.7 manager_workdir=/usr/local/mha manager_log=/usr/local/mha/manager.log remote_workdir=/usr/local/mha ssh_user=root repl_user=root repl_password=MySQL5.7 ping_interval=1 master_ip_failover_script=/usr/local/scripts/master_ip_failover master_ip_online_change_script=/usr/local/scripts/master_ip_online_change [server1] hostname=192.168.1.224 ssh_port=22 master_binlog_dir=/var/lib/mysql candidate_master=1 port=3306 [server2] hostname=192.168.1.225 ssh_port=22 master_binlog_dir=/var/lib/mysql candidate_master=1 port=3306 [server3] hostname=192.168.1.226 ssh_port=22 master_binlog_dir=/var/lib/mysql no_master=1 port=3306 ----------------------------------------------------------------------------------------------------
5、创建目录和脚本,注意根据具体情况修个IP和网卡名
mkdir -p /usr/local/scripts cat /usr/local/scripts/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.221/24'; my $key = '0'; my $ssh_start_vip = "/sbin/ifconfig ens160:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig ens160:$key down"; 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" ) { my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host \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"; exit 0; } else { &usage(); exit 1; } } sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } sub stop_vip() { return 0 unless ($ssh_user); `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=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; } ---------------------------------------------------------------------------------------------------- cat /usr/local/scripts/master_ip_online_change ---------------------------------------------------------------------------------------------------- #!/usr/bin/env perl use strict; use warnings FATAL =>'all'; use Getopt::Long; my $vip = '192.168.1.221/24'; # Virtual IP my $key = "0"; my $ssh_start_vip = "/sbin/ifconfig ens160:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig ens160:$key down"; my $exit_code = 0; my ( $command, $orig_master_is_new_slave, $orig_master_host, $orig_master_ip, $orig_master_port, $orig_master_user, $orig_master_password, $orig_master_ssh_user, $new_master_host, $new_master_ip, $new_master_port, $new_master_user, $new_master_password, $new_master_ssh_user, ); GetOptions( 'command=s' => \$command, 'orig_master_is_new_slave' => \$orig_master_is_new_slave, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'orig_master_user=s' => \$orig_master_user, 'orig_master_password=s' => \$orig_master_password, 'orig_master_ssh_user=s' => \$orig_master_ssh_user, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, 'new_master_user=s' => \$new_master_user, 'new_master_password=s' => \$new_master_password, 'new_master_ssh_user=s' => \$new_master_ssh_user, ); 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 $orig_master_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 $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $orig_master_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"; } ---------------------------------------------------------------------------------------------------- chmod +x /usr/local/scripts/*
6、检查配置
/usr/local/bin/masterha_check_ssh --conf=/etc/mha/mha.conf
7、在Master上添加vip、启动mha、检查状态
ip addr add 192.168.1.221 dev ens160 nohup masterha_manager --conf=/etc/mha/mha.conf > /tmp/mha_manager.log < /dev/null 2>&1 & masterha_check_status --conf=/etc/mha/mha.conf
注意:切换后需要将mha.failover.complete文件删除,否则后面切换会失败