1、实验环境介绍
OS:CentOS Linux release 7.6.1810 (Core)
db: 5.7.30 MySQL Community Server (GPL)
ip | hostname | ||
172.168.1.178 | mysqldb1 | master | Node |
172.168.1.179 | mysqldb2 | slave1 | Node |
172.168.1.180 | mysqldb3 | slave2 | Node Manager
|
172.168.1.188 | vip |
2、主从环境部署
基于GTID的半同步复制,一主两从
[root@mysqldb1 ~]# cat /etc/my.cnf
[client]
port = 3306
socket = /data/mysqldata/mysql.sock
default-character-set=utf8mb4
[mysql]
prompt="(\\u@\\h_\\d) [\\R:\\m]> "
[mysqld]
server_id=1
datadir=/data/mysqldata
socket=/data/mysqldata/mysql.sock
gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
binlog_format=row
symbolic-links=0
[mysqld_safe]
log-error=/data/mysqldata/error.log
pid-file=/data/mysqldata/mysql.pid
!includedir /etc/my.cnf.d
[root@mysqldb2 ~]# cat /etc/my.cnf
[client]
port = 3306
socket = /data/mysqldata/mysql.sock
default-character-set=utf8mb4
[mysql]
prompt="(\\u@\\h_\\d) [\\R:\\m]> "
[mysqld]
server_id=2
datadir=/data/mysqldata
socket=/data/mysqldata/mysql.sock
log_bin=on
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
symbolic-links=0
[mysqld_safe]
log-error=/data/mysqldata/error.log
pid-file=/data/mysqldata/mysql.pid
创建管理用户与同步用户
create user 'zs'@'%' identified by'123456';
grant all privileges on *.* to 'zs'@'%';
flush privileges;
create user 'repl'@'%' identified by'repl';
grant replication slave on *.* to 'repl'@'%';
flush privileges;
mysqldb1 全备
/usr/local/mysql/bin/mysqldump --single-transaction -uroot -pmysql -A >all.sql
scp all.sql root@mysqldb2:/root/
scp all.sql root@mysqldb3:/root/
mysqldb2 mysqldb3
>mysql -uroot -pmysql
>source all.sql
change master to master_host='172.168.1.178',master_user='repl',master_password='repl',master_auto_position=1;
start slave;
3、MHA软件部署
3.1、ssh互信
[root@mysqldb1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.168.1.178 mysqldb1
172.168.1.179 mysqldb2
172.168.1.180 mysqldb3
每个机器上执行
ssh-keygen -t dsa -P '' -f id_dsa
cat id_dsa.pub >> authorized_keys
scp 172.168.1.179:/root/.ssh/id_dsa.pub ./id_dsa.pub.179
scp 172.168.1.180:/root/.ssh/id_dsa.pub ./id_dsa.pub.180
cat id_dsa.pub.179 >> authorized_keys
cat id_dsa.pub.180 >> authorized_keys
scp authorized_keys 172.168.1.179:/root/.ssh
scp authorized_keys 172.168.1.180:/root/.ssh
测试ssh
ssh root@mysqldb2
ssh root@mysqldb3
ssh root@mysqldb1
yum源配置
[root@mysqldb1 yum.repos.d]# cat epel.repo
[epel]
name=Extra Packages for Enterprise Linux 7 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/7/$basearch
metalink=https://mirrors.fedoraproject.org/metalink?repo=epel-7&arch=$basearch
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
[epel-debuginfo]
name=Extra Packages for Enterprise Linux 7 - $basearch - Debug
#baseurl=http://download.fedoraproject.org/pub/epel/7/$basearch/debug
metalink=https://mirrors.fedoraproject.org/metalink?repo=epel-debug-7&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
gpgcheck=1
[epel-source]
name=Extra Packages for Enterprise Linux 7 - $basearch - Source
#baseurl=http://download.fedoraproject.org/pub/epel/7/SRPMS
metalink=https://mirrors.fedoraproject.org/metalink?repo=epel-source-7&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
gpgcheck=1
安装相关包
yum install -y lrzsz perl-DBD-MySQL perl-Config-Tiny* erl-Time-HiRes* erl-Params-Validate* perl-Log-Dispatch* erl-Parallel-ForkManager* perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker cpan perl-CPAN*
安装Node节点(3个机器)
unzip mha4mysql-node-master.zip
perl Makefile.PL
make && make install
安装manager节点(mysqldb3)
unzip mha4mysql-manager-master.zip
perl Makefile.PL
make
make install
(过程比较长,耐心等待)
配置管理节点
mkdir -p /usr/local/mha
mkdir -p /etc/mha
mkdir -p /usr/local/scripts
[root@mysqldb3 mha]# cat /etc/mha/mha.conf
[server default]
user=zs
password=123456
manager_workdir=/usr/local/mha
manager_log=/usr/local/mha/manager.log
remote_workdir=/usr/local/mha
ssh_user=root
repl_user=repl
repl_password=repl
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=172.168.1.178
port=3306
ssh_port=22
master_binlog_dir=/data/mysqldata
candidate_master=1
[server2]
hostname=172.168.1.179
port=3306
ssh_port=22
master_binlog_dir=/data/mysqldata
candidate_master=1
[server3]
hostname=172.168.1.180
port=3306
ssh_port=22
master_binlog_dir=/data/mysqldata
candidate_master=1
[root@mysqldb3 scripts]# cat /usr/local/scripts/master_ip_failover
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
## Note: This is a sample script and is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
my (
$command, $ssh_user, $orig_master_host,
$orig_master_ip, $orig_master_port, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password
);
my $vip = '172.168.1.188/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$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,
'new_master_user=s' => \$new_master_user,
'new_master_password=s' => \$new_master_password,
);
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