目录
Master、Slave1、Slave2 节点上安装 mysql5.7
修改 Master、Slave1、Slave2 节点的 MySQL 主配置文件
在 Master、Slave1、Slave2 节点上都创建两个软链接
一、MHA 简介
- MHA(Master High Availability)是一套优秀的 MySQL 高可用环境下故障切换和主从复制的软件。
- MHA 的出现就是为了解决 MySQL 单点故障的问题。
- 自动故障切换过程中,MHA 试图从宕机的主服务器上保存二进制日志,最大程度的保证数据不丢失。
- 使用半同步复制,可以大大降低数据丢失的风险,如果只有一个 slave 已经收到了最新的二进制日志,MHA 可以将最新的二进制日志应用于其他所有的 slave 服务器上,因此可以保证所有节点的数据一致性。
- 目前 MHA 支持一主多从架构,最少三台服务,即一主两从
- 由MHA Node(数据节点)与MHA Manager(管理节点)组成
二、MySQL MHA 集群构建
Master、Slave1、Slave2 节点上安装 mysql5.7
#!/bin/bash
#一键安装 mysql-5.7.17
#联网下载两个源码包,包在我的云主机上
#安装包下载在 /data 目录
#编译安装需较长时间,请耐心等待
mkdir /data
wget http://101.34.22.188/mysql-5.7.17/boost_1_59_0.tar.gz -P /data
wget http://101.34.22.188/mysql-5.7.17/mysql-5.7.17.tar.gz -P /data
systemctl stop firewalld && systemctl disable firewalld
setenforce 0
ntpdate ntp1.aliyun.com
tar zxvf /data/mysql-5.7.17.tar.gz -C /opt
tar zxvf /data/boost_1_59_0.tar.gz -C /usr/local
mv /usr/local/boost_1_59_0 /usr/local/boost
yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake make git perl expat-devel pcre-devel pcre
useradd -s /sbin/nologin mysql
cd /opt/mysql-5.7.17/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=/usr/local/boost \
-DWITH_SYSTEMD=1
cd /opt/mysql-5.7.17/
make -j 4 && make install
echo > /etc/my.cnf
cat > /etc/my.cnf<<EOF
[client]
port = 3306
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock
[mysql]
port = 3306
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock
auto-rehash
[mysqld]
user = mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character-set-server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections=2048
default-storage-engine=INNODB
max_allowed_packet=16M
server-id = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
EOF
chown -R mysql.mysql /usr/local/mysql/
chown mysql.mysql /etc/my.cnf
echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile
cd /usr/local/mysql/bin/
./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
ln -s /usr/local/mysql/bin/mysql /usr/local/sbin/
pgrep "mysqld" &> /dev/null
if [ $? -eq 0 ];then
echo -e "\033[32mmysqld服务运行正常\033[0m"
else
echo -e "\033[31mmysqld服务运行异常,请检查\033[0m"
fi
sleep 2
echo ' '
echo -e "\033[32mMySQL 没有设置密码,执行 mysql 命令登录\033[0m"
设置数据库密码
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
修改 Master、Slave1、Slave2 节点的 MySQL 主配置文件
master
vim /etc/my.cnf
[mysqld]
server-id = 1
log_bin = master-bin
log-slave-updates = true
systemctl restart mysqld
slave1
vim /etc/my.cnf
server-id = 2 #三台服务器的 server-id 不能一样
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
systemctl restart mysqld
slave2
vim /etc/my.cnf
server-id = 3 #三台服务器的 server-id 不能一样
log_bin= master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
systemctl restart mysqld
在 Master、Slave1、Slave2 节点上都创建两个软链接
ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
配置 mysql 一主两从
进行 mysql 授权
mysql -uroot -p123456
grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456'; #从数据库同步使用
grant all privileges on *.* to 'mha'@'192.168.10.%' identified by 'manager'; #manager 使用
grant all privileges on *.* to 'mha'@'master' identified by 'manager'; #防止从库通过主机名连接不上主库
grant all privileges on *.* to 'mha'@'slave1' identified by 'manager';
grant all privileges on *.* to 'mha'@'slave2' identified by 'manager';
flush privileges;
查看二进制文件和同步点
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 1897 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
安装 MHA 软件
安装 epel 源
yum install epel-release --nogpgcheck -y
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN
安装 node 组件
wget http://101.34.22.188/mha/node/mha4mysql-node-0.57.tar.gz -P /opt
cd /opt
tar zxvf mha4mysql-node-0.57.tar.gz
cd mha4mysql-node-0.57
perl Makefile.PL
make -j 4 && make install
安装 manager 组件
wget http://101.34.22.188/mha/manager/mha4mysql-manager-0.57.tar.gz -P /opt
cd /opt
tar zxvf mha4mysql-manager-0.57.tar.gz
cd mha4mysql-manager-0.57
perl Makefile.PL
make -j 4 && make install
在 manager 节点上配置 MHA
cp -rp /opt/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
复制 VIP 管理脚本
cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin
修改
vim /usr/local/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.10.200';
my $brdc = '192.168.10.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
#--------------------------------
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 \"`;
}
## 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=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}