准备环境：
1.至少三台虚拟机
2.MySQL5.6 (支持事务一致性)
一.环境准备 （四台虚拟机操作相同）
1. 时间同步
[root@ tom42 ~]# echo "*/5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com >/dev/null 2>&1" >>/var/spool/cron/root
[root@ tom42 ~]# crontab -e //查看都有那些定时任务，也可以添加
2.域名解析 注：tom42、43、44 mha45 是主机名
2.1 更改主机名称 永久更改 如果连着xshell 断开重新连接就生效了
[root@ tom42 ~]# hostnamectl set-hostname tom42
2.2 添加域名解析
[root@ tom42 ~]# vim /etc/hosts tom42 10.0.0.42 tom43 10.0.0.43 tom44 10.0.0.44 mha45 10.0.0.45
3 关闭防火墙 和sselinux
[root@ tom42 ~]# systemctl stop firewalld [root@ tom42 ~]# systemctl disable firewalld [root@ tom42 ~]# setenforce 0 setenforce: SELinux is disabled
4 脚本ssh 免密码登录
[root@ tom42 ~]# vim ssh.sh #!/bin/bash yum -y install sshpass &> /dev/null read -p "请输入服务器密码：" passwd UserName=root IP="10.0.0." #创建密钥 ssh-keygen -t dsa -f ~/.ssh/id_dsa -P "" &>/dev/null #分发公钥 for i in 42 43 44 45 do sshpass -p "$passwd" ssh-copy-id -i ~/.ssh/id_dsa.pub -p 22 -o StrictHostKeyChecking=no $UserName@$IP$i &>/dev/null done
二. MySQL主从配置 （这里配置的是一主两从）
1. MySQL的安装 （三台虚拟机操作相同）
1.1 wget +install
[root@ tom42 ~]# wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm [root@ tom42 ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm [root@ tom42 ~]# yum -y install mysql-server
1.2 重启 mysql
[root@ tom42 ~]# systemctl restart mysql
1.3 MySQL设置密码
mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2. 配置主虚拟机 （10.0.0.42）
2.1 编辑 配置文件
[root@ tom42 ~]# vim /etc/my.cnf server-id=1 log-bin=mysql-bin #禁止mysql自动删除relaylog工能 relay_log_purge = 0 #mysql5.6已上的特性，开启gtid，必须主从全开 gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates = 1 skip-name-resolve
2.2 重启动mysql
[root@ tom42 ~]# systemctl restart mysql
2.3 给MySQL数据库授权
[root@ tom42 ~]# mysql -uroot -p123456 mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456'; //授权 10.0.0.0/24
mysql> flush privileges;
2.4查看master状态
mysql> show master status G;
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 530
Binlog_Do_DB:
2.4 查看GTID的状态
mysql> show global variables like '%gtid%'; +---------------------------------+------------------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------------------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_executed | 6defe244-4e52-11ea-a6ea-000c2962e584:1-2 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+------------------------------------------+
3.配置其中两台从主机 （10.0.0.43、44）操作相同
3.1 编辑 配置文件 注：不同点 (server-id=2 server-id=3 )
[root@ tom43 ~]# vim /etc/my.cnf server-id=2 log-bin=mysql-bin relay_log_purge = 0 gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates = 1
skip-name-resolve
3.2 重启动mysql
[root@ tom43 ~]# systemctl restart mysql
3.3 给MySQL数据库授权
[root@ tom43 ~]# mysql -uroot -p123456 mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456'; //授权 10.0.0.0/24 mysql> flush privileges
3.4 stop slave;
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='10.0.0.42',master_user='rep',master_password='123456',master_log_file='mysql-bin.000009',master_log_pos=231;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.42
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 530
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 314
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
三.安装配置MHA 三台主机都操作
1.准备组件
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm
2.安装依赖
[root@ tom42 ~]# yum -y install perl-DBD-MySQL [root@ tom42 ~]#yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
3. mha 授权用户
mysql> grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
4.安装nod节点
[root@ tom42 ~]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
四 安装MHA管理节点操作 45主机操作
1.安装
[root@ mha45 ~]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm Preparing... ################################# [100%] Updating / installing... 1:mha4mysql-manager-0.58-0.el7.cent################################# [100%]
2.配置MHA
[root@ mha45 ~]# mkdir -p /etc/mha [root@ mha45 ~]# mkdir -p /var/log/mha/app1 [root@ mha45 ~]# vim /etc/mha/app1.cnf ger_log=/var/log/mha/app1/manager.log manager_workdir=/var/log/mha/app1 master_binlog_dir=/var/lib/mysql user=mha password=mha ping_interval=2 repl_password=123456 repl_user=rep ssh_user=root [server1] hostname=10.0.0.42 port=3306 [server2] hostname=10.0.0.43 port=3306 [server3] hostname=10.0.0.44 port=3306 ignore_fail=1 no_master=1 #candidate_master=1 #check_repl_delay=0
3.检测ssh 连接 和主从复制检测
[root@ mha45 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
[root@mha45 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
4.启动MHA
[root@ mha45 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & [1] 3127 [root@ mha45 ~]# ps -ef|grep mha //查看进程 root 3127 2749 2 14:26 pts/1 00:00:01 perl /usr/bin/masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover root 3155 2749 0 14:27 pts/1 00:00:00 grep --color=auto mha [root@ mha45 ~]# masterha_check_status --conf=/etc/mha/app1.cnf //查看状态 app1 (pid:3127) is running(0:PING_OK), master:10.0.0.42
五.测试 MHA
1.停掉主库42
[root@ tom42 ~]# systemctl stop mysql
2.查看主库是否跳转到43
mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+ | mysql-bin.000001 | 1534 | | | 6defe244-4e52-11ea-a6ea-000c2962e584:3-4, 9dd0a0df-53a9-11ea-89bd-000c2914fadc:1-5 | +------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3. 44 确定主机是否跳转
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.43
Master_User: rep
Master_Port: 3306
六.恢复mysql 和MHA的配置文件 加上VIP地址
1.恢复42的MySQL
1.1 启动 42主机的 MySQL （这是一开始的主服务器）
[root@ tom42 ~]# systemctl restart mysql
1.2 MHA 44机器查看master主机的更改
[root@ tom44 ~]# grep "CHANGE MASTER TO MASTER" /var/log/mha/app1/manager.log Fri Feb 21 14:48:59 2020 - [info] All other slaves should start replicationld be: CHANGE MASTER TO MASTER_HOST='10.0.0.43', MASTER_PORT=3306, MASTER_AU='rep', MASTER_PASSWORD='xxx';
1.3 重新配置 42主机 stop slave CCHANGE MASTER 从1.2操作里面复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.42', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.43', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.42', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123456';
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave;
Query OK, 0 rows affected (0.11 sec)
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.3
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 231
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 401
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1.4 配置文件恢复
[root@ tom44 bin]# vim /etc/mha/app1.cnf [server default] master_ip_failover_script=/usr/bin/master_ip_failover //这一行是后加的 为了设置VIP地址 manager_log=/var/log/mha/app1/manager.log manager_workdir=/var/log/mha/app1 master_binlog_dir=/var/lib/mysql password=mha ping_interval=2 repl_password=123456 repl_user=rep ssh_user=root user=mha [server1] //如果server1 的主机宕机 MHA会自动删除这三行配置，还原MHA的时候配置需要手动添加 hostname=10.0.0.42 port=3306 [server2] hostname=10.0.0.43 port=3306 [server3] hostname=10.0.0.44 ignore_fail=1 no_master=1 port=3306
1.5 42主机测试是否恢复完毕
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.43
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 231
Relay_Log_File: mysqld-relay-bin.000007
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
2.设置VIP地址
2.1 43主机手动配置VIP地址（这个操作是在你现在的master主机上）
[root@ tom43 ~]# ifconfig ens33:1 10.0.0.81/24
[root@ tom43 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:14:fa:dc brd ff:ff:ff:ff:ff:ff
inet 10.0.0.43/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet 10.0.0.81/24 brd 10.0.0.255 scope global secondary ens33:1 //有这个显示代表成功
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe14:fadc/64 scope link
2.2. 在44 主机上 下面图中 标黄的文件需要我们编辑 （为了将主机之间的vip连接起来）
（需要注意的 是下面的VIP地址要与master主机配置的vip地址一致 网卡要与环境中的网卡一致 ）
[root@ tom44 ~]# 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 = '10.0.0.81/24';
my $key = '1';
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,
);
exit &main();
sub main {
print "
IN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===
";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host
";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@
";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK
";
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
";
}
3.开启MHA
3.1 查看原来的进程还在不在 （如果有 就用kill 杀掉）
[root@ tom44 ~]# ps -ef |grep mha
3.2 关闭
[root@ tom44 ~]# masterha_stop --conf=/etc/mha/app1.cnf Stopped app1 successfully. //看到这一行 才能代表成功 [1]+ Exit 1 nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1
3.3 开启
[root@ tom44 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & [1] 5527
3.4 查看进程
[root@ tom44 ~]# ps -ef |grep mha root 5527 5481 0 17:19 pts/0 00:00:13 perl /usr/bin/masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover root 13591 5481 0 21:28 pts/0 00:00:00 grep --color=auto mha
3.5 查看状态
[root@ tom44 ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:5527) is running(0:PING_OK), master:10.0.0.43 //看到这样的返回值说明MHA成功
七 测试VIP转移是否可以成功
1. 停止 MySQL 主 现在的master主机是 43
[root@ tom43 ~]# systemctl stop mysql
2. 查看Vip是否漂移 漂移到从主机42上
[root@ tom42 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:62:e5:84 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.42/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet 10.0.0.81/24 brd 10.0.0.255 scope global secondary ens33:1 //查到VIp代表漂移成功
valid_lft forever preferred_lft forever