数据库的高可用

数据库的主从同步

数据库的读写分离

数据库的高可用

基于数据库的主从同步和读写分离展开下面的实验

数据库高可用背景

• 单台主库当数据并发量特别高的时候，可能会存在单点故障，所以我们引用了MHA技术，来实现主库故障备库在0-30秒完成自动故障切换

MHA特点

• 自动故障切换过程中，其中的虚拟ip会漂移到备库上
• 目前MHA支持一主多从架构，最少三台服务

MHA实验

实验环境：虚拟机linux平台
实验机器：master：12.0.0.8
slave1：12.0.0.13
slave2：12.0.0.7
manager（nginx）：12.0.0.10

• 安装三台mysql服务器的依赖环境

yum -y install ncurses-devel gcc-c++ perl-Module-Install

• 安装编译工具

[root@master opt]# ls
all.sql                      blbl.sql                cmake-2.8.6              fenge,sh                 mysql_all_2021-07-13.tar.gz  nginx-1.15.9.tar.gz  rh

[root@master cmake-2.8.6]# ./configure 
[root@master cmake-2.8.6]# gmake && gmake install

• 然后安装mysql5.7，基于主从同步配置后面做
• 在三个mysql服务器中分别做两个软链接

[root@master cmake-2.8.6]# ln -s /usr/local/mysql/bin/mysql /usr/sbin	//命令快捷方式
[root@master cmake-2.8.6]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin	//节点恢复，用于manager访问数据库解析二进制日志来使得健康检查成功

• 然后在三个mysql服务器上授权两个用户

grant replication slave on *.* to 'myslave'@'12.0.0.%' identified by '123456'; 	//用于从库同步
grant all privileges on *.* to 'mha'@'12.0.0.%' identified by 'manager';	//manager登入数据库使用

• 然后三个mysql数据库添加mha基于其他库登入授权

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';

• 在从库上开启只读功能

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

• 在master上查看二进制文件和同步点

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 在两台从服务器上进行同步

change master to master_host='12.0.0.8',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154;
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 12.0.0.7
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 

mha配置

• 在所有服务器上安装MHA依赖环境

[root@master cmake-2.8.6]# yum install epel-release --nogpgcheck -y
[root@master cmake-2.8.6]# yum install -y perl-DBD-MySQL \
> perl-Config-Tiny \
> perl-Log-Dispatch \
> perl-Parallel-ForkManager \
> perl-ExtUtils-CBuilder \
> perl-ExtUtils-MakeMaker \
> perl-CPAN

[root@master cmake-2.8.6]# ntpdate ntp1.aliyun.com		//同步一下时间
20 Jul 16:18:28 ntpdate[97183]: adjust time server 120.25.115.20 offset -0.001878 sec

• 然后在所有mysql服务器上安装node组件

[root@master opt]# ls
all.sql                      blbl.sql                cmake-2.8.6              fenge,sh                 mysql_all_2021-07-13.tar.gz  nginx-1.15.9.tar.gz  rh
apache-tomcat-9.0.16.tar.gz  blbl_yy_2021-07-13.sql  data                     jdk-8u201-linux-x64.rpm  mysql-bin.000003             php-7.1.10           webdata
blbl-dldl.sql                blbl_yy.sql             dir_SC_UTF8              mha4mysql-node-0.57      mysql-boost-5.7.20.tar.gz    php-7.1.10.tar.bz2   说明.htm

[root@master mha4mysql-node-0.57]# perl Makefile.PL
[root@master mha4mysql-node-0.57]make && make install

• 在MHA-manager节点上安装manager组件

[root@nginx opt]# ls
fenge.sh  mha4mysql-manager-0.57  mha4mysql-node-0.57  rh
[root@nginx mha4mysql-manager-0.57]# perl Makefile.PL
[root@nginx mha4mysql-manager-0.57]make && make install

• 安装后会生成几个工具

[root@nginx bin]# ls
masterha_check_repl  	//检查MySQL复制状况
masterha_check_status  	//检查当前MHA运行状态
masterha_manager         	//启动manager的脚本
masterha_master_switch    	//控制故障转移
masterha_stop			 //关闭manager
masterha_check_ssh   检查MHA的SSH配置状况，免交互情况
masterha_conf_host     	//添加或删除配置的server信息
masterha_master_monitor  	//检查master是否宕机	

• node安装后也会在/usr/local/bin目录下生成几个脚本

save_binary_logs  	//保存和复制master的二进制日志
apply_diff_relay_logs 	//识别差异的中继日志时间，并将其差异的事件应用于其他的slave
filter_mysqlbinlog		//去除不必要的ROLLBACK回滚事件
purge_relay_logs		//清除中继日志

• 配置所有机器间的相互免密登入

//mannager上
ssh-keygen
ssh-copy-id 12.0.0.10
ssh-copy-id 12.0.0.7
ssh-copy-id 12.0.0.13
ssh-copy-id 12.0.0.8

//三台mysql服务器上
ssh-keygen
ssh-copy-id 12.0.0.10
ssh-copy-id 12.0.0.7
ssh-copy-id 12.0.0.13
ssh-copy-id 12.0.0.8

• 在mannager上复制相关脚本

[root@nginx bin]# cp -ar /opt/mha4mysql-manager-0.57/samples/scripts/ /usr/local/bin/
[root@nginx scripts]# pwd
/usr/local/bin/scripts
[root@nginx scripts]# ls
master_ip_failover  //自动切换时 VIP管理的脚本
master_ip_online_change  //在线切换时 VIP的管理
power_manager  //故障发生后关闭主机的脚本
send_report		//因故障切换后发送报警的脚本

• 复制master_ip_failover到/usr/local/bin目录下，并修改内容

cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin
#!/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.226.100';		//浮动IP
my $brdc = '192.168.226.255';	//广播地址
my $ifdev = 'ens33';		//使用的网卡为ens33
my $key = '1';			//国际序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";							//使用ifoconfig命令将其启动，同时设置浮动地址
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";							//可以使用ifconfig命令将其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";
}

• 创建一个masterha并将app1.cnf复制过去，并修改里面的内容

[root@nginx scripts]# cd /etc/masterha/
[root@nginx masterha]# cp /opt/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/

[server default]
manager_log=/var/log/masterha/app1/manager.log		//manager工作目录
manager_workdir=/var/log/masterha/app1	//manager日志
master_binlog_dir=/usr/local/mysql/data	//master保存binlog的位置
master_ip_failover_script=/usr/local/bin/master_ip_failover	//设置自动failover时候切换脚本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change		//设置手动切换时候的切换脚本
password=manager	//以下都是密码管理的相关配置
ping_interval=1		//设置监控主库，发送ping包的事件间隔，默认为3秒，尝试三次没有回应的时候自动进性failover
remote_workdir=/tmp		//设置远端的MySQL在发生切换时binlog的保存位置
repl_password=123456	//设置复制用户的密码
repl_user=myslave  	//设置复制用户的账户
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 12.0.0.7 -s 12.0.0.13     //设置检查从服务器的脚本
shutdown_script=""    //设置故障发生后关闭故障主机脚本
ssh_user=root    //设置ssh的登录用户名
user=mha       //设置监控用户

[server1]
hostname=12.0.0.8
port=3306

[server2]
candidate_master=1		//设置候选master，如果设置了此参数，发送主从切换后，会将此库提升为主库
check_repl_delay=0			//关闭复制的检查，默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为新的master，设置为0的话会忽略此规则
hostname=12.0.0.7
port=3306

[server3]
hostname=12.0.0.13
port=3306

• 测试无密码认证，如果正常会输出successfully
  
• 在master上添加一个虚拟网卡

ifconfig ens33:1 12.0.0.100

• 测试主从同步
  
• 启动MHA，并在后台运行

nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &	  //当master服务器失效时，发生主从切换后，会把旧的master的ip从主配置文件删除

• 查看当前的master节点

[root@nginx masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:66462) is running(0:PING_OK), master:12.0.0.8

• 查看当前日志信息

模拟故障查看master变化并修复

• 杀掉主库的mysqld进程

pkill -9 mysql

• 修复配置文件，在原来的主上添加中继，将允许同步注释掉

log-bin=mysql-bin
#log-slave-updates=true
server_id=11
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

• 在主备上

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 在原主上

change master to master_host='12.0.0.7',master_user='myslave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=154;	//指定主备的二进制文件和节点
start slave；
set global read_only=1;	//设置只读
flush privileges;	//刷新权限

• 在manager上将自动删除的主信息再加上去

[server1]
hostname=12.0.0.8
port=3306

• 然后再启动manager

nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &

• 再查看master节点

[root@nginx masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:66462) is running(0:PING_OK), master:12.0.0.7

• 此时就成功看到虚拟ip飘到了主备上

 ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:10:f0:ef brd ff:ff:ff:ff:ff:ff
    inet 12.0.0.7/8 brd 12.255.255.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 12.0.0.100/8 brd 12.255.255.255 scope global secondary ens33:1
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe10:f0ef/64 scope link