阅读量:0
mysqlMHA
什么是MHA
高可用模式下的故障切换,基于主从复制.
至少需要3台
故障切换过程在0-30秒之内.
这过程和keepalive比较像,也是通过vip地址,根据vip地址所在的主机,确定主备.
主和备不是靠优先级确定的,主从复制的时候就确定了主,备是在MHA的过程中确定.
MHA的组件
MHA NODE 数据节点,每台mysql和管理服务器都要安装,监控服务器状态以及收集数据
MHA的manager 管理节点,管理mysql的高可用集群,可以单独部署在一台独立的服务器,也可以部署多个,实现主备之间切换,主发生故障,切换到备.
MHA的特点:
1.manager来实现主备切换
2.数据同步还是依靠二进制日志,最大程度上保证数据的完整
3.半同步的方式,都是为了实现数据的完整性.
一主多从的架构,最少要三台
1.主宕机,保存二进制日志
2.备从主的二进制日志当中更新,更新到自己的slave日志当中.
3.备撑住,同步到master的二进制文件
4.其他备服务器从新的主同步数据
5.原来的备成为主,其他的备服务器都和主继续同步数据
6.主备切换之后,mysql模式下,一般是继续以现有主,作为集群的主,重新把服务器加入到集群
实验
一主两从
搭建完成MHA的架构
主备之间的切换
故障恢复
4台机器
master 192.168.118.10 mysql 8.0 node组件
slave1 192.168.118.20 mysql 8.0 node组件
slave2 192.168.118.30 mysql 8.0 node组件
管理节点 192.168.118.40 mysql 8.0 node组件
#master 192.168.118.10 mysql 8.0 node组件
systemctl stop firewalld setenfore 0 yum -y install ntpdate -y date ntpdate ntp.aliyun.com hostnamectl set-hostname master su vim /etc/hosts 192.168.118.10 master 192.168.118.20 slave1 192.168.118.30 slave2 vim /etc/my.cnf server-id = 1 log-bin = master-bin binlog_format = MIXED log-slave-updates = true relay_log_recovery = 1 :wq systemctl restart mysqld ln -s /usr/local/mysql/bin/mysql /usr/sbin/ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/ #配置mysql一主两从 #所有数据库节点进行mysql授权 mysql -uroot -p123456 #从数据库同步使用 CREATE USER 'myslave'@'192.168.118.%' IDENTIFIED WITH mysql_native_password BY '123456'; GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.118.%'; #manager使用 CREATE USER 'mha'@'192.168.118.%' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'192.168.118.%' WITH GRANT OPTION; #防止从库通过主机名连接不上主库 CREATE USER 'mha'@'master' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'master'; CREATE USER 'mha'@'slave1' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'slave1'; CREATE USER 'mha'@'slave2' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'slave2'; #刷新 FLUSH PRIVILEGES; #查看 show master status; #第二步,四台同步操作 cd /opt #扔mha4mysql-node-0.57.tar.gz包 #安装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 tar -xf mha4mysql-node-0.57.tar.gz cd mha4mysql-node-0.57/ #编译安装 perl Makefile.PL make && make install ssh-keygen -t rsa ssh-copy-id 192.168.118.20 ssh-copy-id 192.168.118.30 ifconfig ens33:1 192.168.118.100/24 #slave1 192.168.118.20 mysql 8.0 node组件
systemctl stop firewalld setenfore 0 yum -y install ntpdate -y date ntpdate ntp.aliyun.com hostnamectl set-hostname slave1 su vim /etc/hosts 192.168.118.10 master 192.168.118.20 slave1 192.168.118.30 slave2 vim /etc/my.cnf server-id = 2 log_bin = master-bin binlog_format = MIXED relay-log = relay-log-bin relay-log-index = slave-relay-bin.index relay_log_recovery = 1 :wq systemctl restart mysqld ln -s /usr/local/mysql/bin/mysql /usr/sbin/ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/ #配置mysql一主两从 #所有数据库节点进行mysql授权 mysql -uroot -p123456 #从数据库同步使用 CREATE USER 'myslave'@'192.168.118.%' IDENTIFIED WITH mysql_native_password BY '123456'; GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.118.%'; #manager使用 CREATE USER 'mha'@'192.168.118.%' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'192.168.118.%' WITH GRANT OPTION; #防止从库通过主机名连接不上主库 CREATE USER 'mha'@'master' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'master'; CREATE USER 'mha'@'slave1' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'slave1'; CREATE USER 'mha'@'slave2' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'slave2'; flush privileges; change master to master_host='192.168.118.10',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=2921; start slave; show slave status\G; cd /opt #扔#扔mha4mysql-node-0.57.tar.gz包 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 tar -xf mha4mysql-node-0.57.tar.gz cd mha4mysql-node-0.57/ #编译安装 perl Makefile.PL make && make install ssh-keygen -t rsa ssh-copy-id 192.168.118.10 ssh-copy-id 192.168.118.30 #slave2 192.168.118.30 mysql 8.0 node组件
systemctl stop firewalld setenfore 0 yum -y install ntpdate -y date ntpdate ntp.aliyun.com hostnamectl set-hostname slave2 su vim /etc/hosts 192.168.118.10 master 192.168.118.20 slave1 192.168.118.30 slave2 vim /etc/my.cnf server-id = 3 log_bin = master-bin binlog_format = MIXED relay-log = relay-log-bin relay-log-index = slave-relay-bin.index relay_log_recovery = 1 :wq systemctl restart mysqld ln -s /usr/local/mysql/bin/mysql /usr/sbin/ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/ #配置mysql一主两从 #所有数据库节点进行mysql授权 mysql -uroot -p123456 #从数据库同步使用 CREATE USER 'myslave'@'192.168.118.%' IDENTIFIED WITH mysql_native_password BY '123456'; GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.118.%'; #manager使用 CREATE USER 'mha'@'192.168.118.%' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'192.168.118.%' WITH GRANT OPTION; #防止从库通过主机名连接不上主库 CREATE USER 'mha'@'master' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'master'; CREATE USER 'mha'@'slave1' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'slave1'; CREATE USER 'mha'@'slave2' IDENTIFIED WITH mysql_native_password BY 'manager'; GRANT ALL PRIVILEGES ON *.* TO 'mha'@'slave2'; flush privileges; change master to master_host='192.168.118.10',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=857; start slave; show slave status\G; cd /opt #扔mha4mysql-node-0.57.tar.gz包包 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 tar -xf mha4mysql-node-0.57.tar.gz cd mha4mysql-node-0.57/ #编译安装 perl Makefile.PL make && make install ssh-keygen -t rsa ssh-copy-id 192.168.118.10 ssh-copy-id 192.168.118.20 #test1
systemctl stop firewalld setenfore 0 yum -y install ntpdate -y date ntpdate ntp.aliyun.com cd /opt #扔mha4mysql-node-0.57.tar.gz包 #mha4mysql-manager-0.57.tar.gz包 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 tar -xf mha4mysql-node-0.57.tar.gz cd mha4mysql-node-0.57/ #编译安装 perl Makefile.PL make && make install cd .. tar -xf mha4mysql-manager-0.57.tar.gz cd mha4mysql-manager-0.57/ perl Makefile.PL make && make install cd /usr/local/bin/ #一路按回车键 ssh-keygen -t rsa ssh-copy-id 192.168.118.10 ssh-copy-id 192.168.118.20 ssh-copy-id 192.168.118.30 cd /opt/mha4mysql-manager-0.57/ cd samples/ cp -rp scripts/ /usr/local/bin/ cd /usr/local/bin/ ll cd /opt cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/ cd /usr/local/bin/ ls vim master_ip_failover #100dd全部清空 :set paste #复制脚本 :wq mkdir /etc/masterha cd /opt/mha4mysql-manager-0.57/samples/conf/ cp app1.cnf /etc/masterha/ cd /etc/masterha/ ls vim /etc/masterha/app1.cnf #清空 :set paste #复制第二个脚本 :wq #完成主的虚拟网卡后,检测状态 masterha_check_ssh -conf=/etc/masterha/app1.cnf masterha_check_repl -conf=/etc/masterha/app1.cnf #nohup 记录程序启动时的日志文件,保存到指定的位置. 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 & masterha_check_status --conf=/etc/masterha/app1.cnf cat /var/log/masterha/app1/manager.log | grep "current master" masterha_check_ssh
所有的数据库节点和管理节点通过ssh来进行互相通信，检查集群的ssh配置
masterha_check_repl
检查mysql的复制情况 数据同步
masterha_manager
manager 文件的启动脚本
masterha_check_status
检查MHA集群状态的文件
masterha_master_switch
控制故障转移
masterha_stop
关闭manager服务
master_ip_failpver 故障切换时,vip的管理脚本
master_ip_online_change 在线切换时,vip的管理脚本
power_manager 故障发生后,关闭主机的脚本
send_report 故障切换之后,发送报警的脚本
要复制的脚本1
#!/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.118.100'; my $brdc = '192.168.118.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; 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"; } 要复制的脚本2
[server default] manager_log=/var/log/masterha/app1/manager.log #主日志文件,报错就看他 manager_workdir=/var/log/masterha/app1 #manager的工作目录 master_binlog_dir=/usr/local/mysql/data #mysql主服务器的binlog二进制文件的保存目录 master_ip_failover_script=/usr/local/bin/master_ip_failover #自动切换 master_ip_online_change_script=/usr/local/bin/master_ip_online_change #在线切换 password=manager ping_interval=1 #ping主库的时间间隔,每一秒钟ping一次, #默认3秒一次,共ping三次,不通就会自动切换. remote_workdir=/tmp repl_password=123456 repl_user=myslave secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.118.20 -s 192.168.118.30 #从对主监听 shutdown_script="" ssh_user=root user=mha [server1] hostname=192.168.118.10 #主服务器 port=3306 [server2] candidate_master=1 #设置为备用master check_repl_delay=0 #默认是选择一个slave,这个slave和master的数据是最一致的, #如果slave的同步数据落后主100M,MHA永远也不会选择该服务器#作为备主 #不考虑主从之间的问题,强制指定slave为备服务器 hostname=192.168.118.20 #备用主服务器 port=3306 [server3] hostname=192.168.118.30 #从服务器2 port=3306 #test1 tail -f /var/log/masterha/app1/manager.log #mysql1 systemctl stop mysqld _check -s 192.168.118.20 -s 192.168.118.30
#从对主监听
shutdown_script=“”
ssh_user=root
user=mha
[server1]
hostname=192.168.118.10
#主服务器
port=3306
[server2]
candidate_master=1
#设置为备用master
check_repl_delay=0
#默认是选择一个slave,这个slave和master的数据是最一致的,
#如果slave的同步数据落后主100M,MHA永远也不会选择该服务器#作为备主
#不考虑主从之间的问题,强制指定slave为备服务器
hostname=192.168.118.20
#备用主服务器
port=3306
[server3]
hostname=192.168.118.30
#从服务器2
port=3306
#test1
tail -f /var/log/masterha/app1/manager.log
#mysql1
systemctl stop mysqld
