MHA ( Master High Availability )目前在MySQL高可用方面是一个相对成熟的解决方案 ,它由日本DeNA公司
youshimaton (现就职于Facebook公司)开发,是-套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用
软件。在MySQL故障切换过程中, MHA能做到在0~30秒之内自动完成数据库的故障切换操作,并且在进行故障切换的过
程中, MHA能在最大程度上保证数据的一致性,以达到真正意义上的高可用。
github地址: https://github.com/yoshinorim
该软件由两部分组成: MHA Manager (管理节点)和MHA Node (数据节点)。MHA Manager可以单独部署在一台
独立的机器上管理多个master-slave集群,也可以部署在一台slave节点 上。MHA Node运行在每台MySQL服务器上,
MHA Manager会定时探测集群中的master节点,当master出现故障时, 它可以自动将最新数据的slave提升为新的
master ,然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。
在MHA自动故障切换过程中, MHA试图从宕机的主服务器上保存二进制日志 ,最大程度的保证数据的不丢失,但这并
不总是可行的。例如,如果主服务器硬件故障或无法通过ssh访问, MHA没法保存二进制日志 ,只进行故障转移而丢失了
最新的数据。使用MySQL 5.5的半同步复制,可以大大降低数据丢失的风险
。MHA可以与半同步复制结合起来。如果只
有一个slave已经收到了最新的二进制日志, MHA可以将最新的二进制日志应用于其他所有的slave服务器.上,因此可以保
证所有节点的数据一致性。
目前MHA主要支持一主多从的架构,**要搭建MHA,要求一个复制集群中必须最少有三 台数据库服务器,**一主二从, 即一
台充当master ,一台充当备用master ,另外一台充当从库,因为至少需要三台服务器,出于机器成本的考虑,淘宝也在
该基础上进行了改造,目前淘宝TMHA已经支持一注- -从。(出自 :《深入浅出MySQL(第二版)》 )
官方介绍: https://code.google.com/p/mysql-master-ha/
下图展示了如何通过MHA Manager管理多组主从复制。
可以将MHA工作原理总结为如下:
(1 )从宕机崩溃的master保存:进制日志事件( binlog events );
(2)识别含有最新更新的slave ;
(3)应用差异的中继日志( relay log )到其他的slave ;
(4)应用从master保存的进制日志事件( binlog events) ;
(5)提升- -个slave为新的master ;
(6)使其他的slave连接新的masteri进行复制;
MHA软件由两部分组成, Manager工具包和Node工具包,具体的说明如下
Manager工具包主要包括以下几个工具:
masterha_ .check. ssh 检查MHA的SSH配置状况
masterha .check. rep1 检查MySQL复制状况
masterha manger 启动MHA
masterha_ .check_ status 检测当前MHA运行状态
masterha master. .monitor 检测master是否宕机
masterha master- switch 控制故障转移(自动或者手动)
masterha .conf_ host 添加或删余配置的server信息
Node工具包(这些工具通常由MHA Manager的脚本触发,无需人为操作)主要包括以下几个工具: .
save_ binary. _logs 保存和复制master的二进制日志
apply- diff. relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_ mysqlbinlog 去除不必要的ROLLBACK事件(MHA已不再使用这个工具)
purge. relay- .1ogs 清除中继日志(不会阻塞SQL线程)
1.安装部署
1.2资源准备
主机名 | ip | 安装软件 |
---|---|---|
c7m01 | 10.0.0.41 | node mysql |
– | – | |
c702 | 10.0.0.42 | node mysql |
– | – | |
c703 | 10.0.0.43 | node mysql |
– | – | |
c704 | 10.0.0.43 | node mysql |
– | – | |
vip | 10.0.0.49 | manager node mysql |
2.环境准备:
2.1同步时间
2.2host解析
cat >>/etc/hosts<<EOF
c7m01 10.0.0.41
c702 10.0.0.42
c703 10.0.0.43
c704 10.0.0.44
EOF
2.3关闭防火墙和selinux
2.4配置SSH免密登录
注意:是互相的并且最好不要禁掉密码登录,如果禁了就有问题
yum -y install sshpass
#!/bin/bash
UserName=root
IPlist=(10.0.0.41 10.0.0.42 10.0.0.43 10.0.0.44 )
#创建密钥
ssh-keygen -t dsa -f ~/.ssh/id_dsa -P "" &>/dev/null
#分发公钥
for i in ${IPlist[*]}
do
sshpass -p "123456" ssh-copy-id -i ~/.ssh/id_dsa.pub -p 22 -o StrictHostKeyChecking=no $UserName@$i &>/dev/null
done
3.mysql主从配置成功
3.1安装yum repo
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum -y install mysql-server mysql -y
3.2启动mysql
systemctl restart mysql
==修改密码==
update mysql.user set password=password('123456') where user='root' and host='localhost';
flush privileges;
3.3配置主从
3.3.1 c7m01主服务器操作
vim /etc/my.cnf
[mysqld
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
#跳过域名解析
#==重启mysql==
systemctl restart mysql
创建同步用户
mysql -uroot -p123456
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
mysql> flush privileges;
查看主库mysql的master状态
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 1045
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 5ab78bda-9b49-11ea-9cc9-000c29de703c:1-5
1 row in set (0.00 sec)
查看GTID
的状态
mysql> show global variables like '%gtid%';
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | 5ab78bda-9b49-11ea-9cc9-000c29de703c:1-5 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+------------------------------------------+
7 rows in set (0.00 sec)
3.3.2 c702主服务器操作
vim /etc/my.cnf
[mysqld
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
#跳过域名解析
#==重启mysql==
systemctl restart mysql
创建同步用户
mysql -uroot -p123456
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
mysql> flush privileges;
配置从服务器指向master
change master to master_host='10.0.0.41',master_user='rep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=909;
开启从服务器的复制功能
mysql> start slave;
检测从服务器的复制功能状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 909
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 314
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
是否变为yes
3.3.3 4c703主服务器操作
vim /etc/my.cnf
[mysqld
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
#跳过域名解析
#==重启mysql==
systemctl restart mysql
创建同步用户
mysql -uroot -p123456
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
mysql> flush privileges;
配置从服务器指向master
change master to master_host='10.0.0.41',master_user='rep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=909;
开启从服务器的复制功能
mysql> start slave;
检测从服务器的复制功能状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 909
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 314
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
是否变为yes
什么是GTID
●GTID(全球交易)全局事务标识符:是-一个唯一 的标识符,它创建并与源服务器(主). 上提交的每个事务相关
联。此标识符不仅对其发起的服务器是唯一的,而且在给定复制设置中的所有服务器上都是唯一的。 所有交易和
所有GTID之间都有1对1的映射。
●GTID实际上是由UUID + TID组成的。其中UUID是一 个MySQL的实例的唯一 标识TID代表了该实例上已经提交的
事务数量,并且随着事务提交单调递增。
GTID的新特性
(1)支持多线程复制:事实上是针对每个数据库开启相应的独立线程,即每个库有一个单独的( sql thread )
(2)支持启用GTID ,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后将master改为指向。在
mysq|5.6里,无须再知道binlog和POS点,只需要知道master的IP /端口/账号密码即可,因为同步复制是自动的,
MySQL的通过内部机制GTID自动找点同步。
(3)基于行复制只保存改变的列,大大节省磁盘空间,网络,内存等
(4)支持把主站和从站的相关信息记录在表中;原来是记录在文件里,现在则记录在表里,增强可用性
(5)支持延迟复制
4.安装MHA
所有节点操作
4.1安装依赖
yum install perl-DBD-MySQL -y
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
4.2授权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.3安装node节点
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
4.4安装MHA管理节点
注意:MHA管理节点不要装到mysql主库和切换的从库上(备用的主库),否则会在后面的vip无法漂移
[root@c704 ~]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mha4mysql-manager-0.58-0.el7.cent################################# [100%]
附带源码安装方式:
yum -y instal1 per1-DBD-MySQL per1-Config-Tiny per1-Par ams -validate per1-CPAN per 1-deve1
per 1-ExtUtils-CBuilder per 1-ExtUtils-MakeMaker
node节点:
tar xf mha4mysq1-node-0. 58.tar .9z
cd mha4mysq1-node-0. 58
per1 Makefile.PL
make & make insta11
manager节点:
tar xf mha4mysq1 -manager-0.58. tar .gz
cd mha4mysq1-manager-0. 58
per1 Makefile.PL
make & make install
按装完成后会在/usr/1oca1/bin目录下面生成以下脚本文件
4.5配置MHA
[root@c704 ~]# mkdir -p /etc/mha
[root@c704 ~]# mkdir -p /var/log/mha/app1
[root@c704 ~]# vim /etc/mha/app1.cnf
[server default]
manager_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.41
port=3306
[server2]
hostname=10.0.0.42
port=3306
[server3]
hostname=10.0.0.43
port=3306
ignore_fail=1
no_master=1
#candidate_master=1
#check_repl_delay=0
4.6启动测试
检查ssh
[root@c704 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Thu May 21 18:55:04 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 21 18:55:04 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu May 21 18:55:04 2020 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu May 21 18:55:04 2020 - [info] Starting SSH connection tests..
Thu May 21 18:55:06 2020 - [debug]
Thu May 21 18:55:04 2020 - [debug] Connecting via SSH from root@10.0.0.41(10.0.0.41:22) to root@10.0.0.42(10.0.0.42:22)..
Thu May 21 18:55:05 2020 - [debug] ok.
Thu May 21 18:55:05 2020 - [debug] Connecting via SSH from root@10.0.0.41(10.0.0.41:22) to root@10.0.0.43(10.0.0.43:22)..
Thu May 21 18:55:05 2020 - [debug] ok.
Thu May 21 18:55:06 2020 - [debug]
Thu May 21 18:55:05 2020 - [debug] Connecting via SSH from root@10.0.0.42(10.0.0.42:22) to root@10.0.0.41(10.0.0.41:22)..
Thu May 21 18:55:05 2020 - [debug] ok.
Thu May 21 18:55:05 2020 - [debug] Connecting via SSH from root@10.0.0.42(10.0.0.42:22) to root@10.0.0.43(10.0.0.43:22)..
Thu May 21 18:55:06 2020 - [debug] ok.
Thu May 21 18:55:07 2020 - [debug]
Thu May 21 18:55:05 2020 - [debug] Connecting via SSH from root@10.0.0.43(10.0.0.43:22) to root@10.0.0.41(10.0.0.41:22)..
Thu May 21 18:55:06 2020 - [debug] ok.
Thu May 21 18:55:06 2020 - [debug] Connecting via SSH from root@10.0.0.43(10.0.0.43:22) to root@10.0.0.42(10.0.0.42:22)..
Thu May 21 18:55:06 2020 - [debug] ok.
Thu May 21 18:55:07 2020 - [info] All SSH connection tests passed successfully.
检查主从复制
[root@c704 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
4.7启动MHA
[root@c704 ~]# 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] 6819
查看MHA状态
[root@c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:6819) is running(0:PING_OK), master:10.0.0.41
关闭MHA
[root@c704 ~]# masterha_stop --conf=/etc/mha/app1.cnf
从库加入新主
[root@c704 ~]# grep -i "CHANGE MASTER TO MASTER" /var/log/mha/app1/manager.log | tail -1
4.8测试MHA的故障转移
停掉c7mo1主库10.0.0.41
[root@c7m01 ~]# systemctl stop mysql
查看c703的slave状态,发现Master_ Host变成了10.0.0.42
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.000001
Read_Master_Log_Pos: 1797
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1589
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
查看c702的master状态
[root@c702 ~]# mysql -uroot -p123456 -e "show master status;"
Warning: Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| mysql-bin.000001 | 1797 |
查看c704的MHA的配置文件
[root@c704 ~]# cat /etc/mha/app1.cnf
[server default]
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
[server2]
hostname=10.0.0.42
port=3306
[server3]
hostname=10.0.0.43
ignore_fail=1
no_master=1
port=3306
发生故障时,MHA做了什么?
①当作为主库的c7m01上的MySQL宕机以后,mha通过检测发现c7m01的mysql宕机了,那么会将binlog日志最全的从库(c702)立刻提升为主库,而其他的从库会指向新的主库进行再次同步。
②MHA会自己结束自己的进程,还会将/etc/mha/app1.cnf配置文件中,坏掉的那台机器剔除。
4.9MHA故障还原
1.先将宕机的mysql修复,然后加入到mysql一主两从集群
[root@c704 ~]# grep "CHANGE MASTER TO MASTER" /var/log/mha/app1/manager.log | tail -1
Thu May 21 19:05:05 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.42', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
[root@c7m01 ~]# systemctl restart mysql
[root@c7m01 ~]# mysql -uroot -p123456 -e "CHANGE MASTER TO MASTER_HOST='10.0.0.42', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123456';"
[root@c7m01 ~]# mysql -uroot -p123456 -e "start slave;"
[root@c7m01 ~]# mysql -uroot -p123456 -e "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.000001
Read_Master_Log_Pos: 1797
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 448
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2.重新将[server1]标签添加到MHA配置文件,并且启动MHA
[server1]
hostname=10.0.0.41
port=3306
3.重启MHA
[root@c704 ~]# 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] 7092
#查看状态
[root@c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:7092) is running(0:PING_OK), master:10.0.0.42
5.配置vip 漂移
5.1IP漂移的两种方式
①通过keepalived的方式,管理虚拟IP的漂移。
②通过MHA自带脚本的方式,管理虚拟IP的漂移#用mha自带的一 -个VIP漂移的脚本,哪个提升为主,就飘到那个上
面,要根据binlog最新的slave提升。
5.2MHA脚本方式
修改MHA配置文件
[root@c704 ~]# vim /etc/mha/app1.cnf
[server default]
master_ip_failover_script=/usr/bin/master_ip_failover
编写漂移脚本 master_ip_failover
注意:修改脚本中的网卡名和ip地址
[root@c704 ~]# 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.49/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 "\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 \"`;
}
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\n";
}
[root@c704 ~]# chmod +x master_ip_failover
5.3手动绑定vip
[root@c702 ~]# ifconfig ens33:1 10.0.0.49/24
[root@c702 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:cd:14:44 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.49/24 brd 10.0.0.255 scope global secondary ens33:1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fecd:1444/64 scope link
valid_lft forever preferred_lft forever
5.4重启mha
[root@c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:7092) is running(0:PING_OK), master:10.0.0.42
[root@c704 ~]# 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
[root@c704 ~]# 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] 7546
[root@c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:7546) is running(0:PING_OK), master:10.0.0.42
5.5模拟主库宕机vip漂移
关闭slave2的主库mysql
[root@c702 ~]# systemctl stop mysql
[root@c702 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:cd:14:44 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
inet6 fe80::20c:29ff:fecd:1444/64 scope link
valid_lft forever preferred_lft forever
现在查看m01上mysql的master状态和vip
[root@c7m01 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:de:70:3c brd ff:ff:ff:ff:ff:ff
inet 10.0.0.41/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet 10.0.0.49/24 brd 10.0.0.255 scope global secondary ens33:1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fede:703c/64 scope link
valid_lft forever preferred_lft forever
[root@c7m01 ~]# mysql -uroot -p123456 -e 'show master status\G'
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1325
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 5ab78bda-9b49-11ea-9cc9-000c29de703c:1-7,
75b3a4ef-9b49-11ea-9cca-000c29cd1444:1-6
#故障还原
6.Mysql之Atlas(读写分离)
数据库中间件Atlas与Mycat比较分库分表压测报告https://blog.csdn.net/izhitao/article/details/71680714
Atlas是由Qihoo 360公司Web平台部基础架构团队开发维护的一-个基于MySQL协议的数据中间层项目。它在MySQL言
方推出的MySQL-Proxy 0.8.2版本的基础上,修改了大量bug ,添加了很多功能特性。目前该项目在360公司内部得到了广
泛应用,很多MySQL业务已经接入了Atlas平台,每天承载的读写请求数达几十亿条。
源码Github : https://github.com/Qihoo360/Atlas
6.1什么是读写分离
读写分离,基本的原理是让主数据库处理事务性增、删、改操作( INSERT. DELETE、 UPDATE ) , 而从数据库处理
SELECT查询操作。数据库复制把主数据库操作的变更同步到集群中的从数据库。
6.2为什么读写分离.
因为数据库的“写" (写10000条数据到oracle可能要3分钟)操作是比较耗时的。但是数据库的“读" (从oracle读10000
条数据可能只要5秒钟)。所以读写分离,解决的是,数据库的写入,影响了查询的效率。
6.3什么时候要读写分离
数据库不一定要读写分离,如果程序使用数据库较多时,而更新少,查询多的情况下会考虑使用,利用数据库主从同步
可以减少数据库压力,提高性能。当然,数据库也有其它优化方案。memcache 或是表折分,或是搜索引学。都是解
决方法。
6.4 Atlas的功用与应用场景
Atlas的功能有:
读写分离、从库负载均衡、自动分表、IP过滤、 SQL语句黑白名单、DBA可平滑上下线DB、自动摘除宕机的DB。
Atlas的使用场景:
Atlas是一个位于前端 应用与后端MySQL数据库之间的中间件,它使得应用程序员无需再关心读写分离、分表等与
MySQL相关的细节,可以专注于编写业务逻辑,同时使得DBA的运维工作对前端应用透明,上下线DB前端应用无感知。
6.5 Atlas的安装过程
注意:
1.Atlas只能安装运行在64位的系统上
2.Centos 5.X安装Atlas-XX.el5.x86_ _64.rpm , Centos 6.X安装Atlas-XX.el6.x86 _64.rpm(经过测试centos7也可以使用6
的版本)
3、后端mysq|版本应大于5.1 ,建议使用Mysql 5.6以上
1.安装altas
[root@c704 ~]# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
[root@c704 ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
2.修改配置文件
[root@c704 ~]# cp /usr/local/mysql-proxy/conf/test.cnf{,.bak}
[root@c704 ~]# egrep -v '^#|^$' /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.49:3306
proxy-read-only-backend-addresses = 10.0.0.42:3306,10.0.0.43:3306
pwds = rep:/iZxz+0GRoA=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log =ON
proxy-address = 0.0.0.0:1234
admin-address = 0.0.0.0:2345
charset = utf8
#加密方式 /usr/local/mysql-proxy/bin/encrypt 密码
[root@c704 ~]# /usr/local/mysql-proxy/bin/encrypt 123456
/iZxz+0GRoA=
[root@c704 ~]# /usr/local/mysql-proxy/bin/encrypt mha
O2jBXONX098=
3.启动atlas
[root@c704 conf]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
#/usr/local/mysql-proxy/bin/mysql-proxyd test start
#/usr/local/mysql-proxy/bin/mysql-proxyd test stop
#/usr/local/mysql-proxy/bin/mysql-proxyd test restart
4.启动atlas
[root@c704 ~]# ps -ef | grep mysql-proxy
root 9097 1 0 20:20 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 9098 9097 0 20:20 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 9364 8350 0 20:28 pts/0 00:00:00 grep --color=auto mysql-proxy
#修改文件时意外退出
Swap file ".test.cnf.swp" already exists!
[O]pen Read-Only, (E)dit anyway, (R)ecover, (D)elete it, (Q)uit, (A)bort:
[root@c704 conf]# ls -a
. .. test.cnf test.cnf.bak .test.cnf.swp
[root@c704 conf]# rm -rf .test.cnf.swp
#无法启动
[root@c704 conf]# /usr/local/mysql-proxy/bin/mysql-proxy test.cnf start #proxy+d
2020-05-21 20:19:59: (critical) chassis-frontend.c:122: Failed to get log directory, please set by --log-path
2020-05-21 20:19:59: (message) Initiating shutdown, requested from mysql-proxy-cli.c:381
2020-05-21 20:19:59: (message) shutting down normally, exit code is: 1
6.6Atlas读写分离测试
读测试
[root@c704 conf]# mysql -umha -pmha -P1234 -h10.0.0.44
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
#注:发现上面'server. jid每次的结果都不-样,分别是2台从库的server. jid ,并且每执行- -次命令 , server. jid就会变换一
次,这是因为默认读操作的权重都是1,两台从DB默认就是负载均衡。
写测试
[root@c704 conf]# mysql -umha -pmha -P1234 -h10.0.0.44
mysql> begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> create database www;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wg |
| www |
+--------------------+
5 rows in set (0.00 sec)
#错误
mysql> begin;select @@server_id;commit;
ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 6
Current database: *** NONE ***
#看一下vip 是不是绑定到机器上 我们去41上绑定
[root@c7m01 ~]# ifconfig ens33:1 10.0.0.49/24
[root@c7m01 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:de:70:3c brd ff:ff:ff:ff:ff:ff
inet 10.0.0.41/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet 10.0.0.49/24 brd 10.0.0.255 scope global secondary ens33:1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fede:703c/64 scope link
valid_lft forever preferred_lft forever
6.7Altas管理操作
登录管理接口
[root@c704 conf]# mysql -uuser -ppwd -P2345 -h 10.0.0.44
查看帮助信息
mysql> select * from help;
+----------------------------+---------------------------------------------------------+
| command | description |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
| SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id | online backend server, ... |
| ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... |
| ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |
| SELECT * FROM clients | lists the clients |
| ADD CLIENT $client | example: "add client 192.168.1.2", ... |
| REMOVE CLIENT $client | example: "remove client 192.168.1.2", ... |
| SELECT * FROM pwds | lists the pwds |
| ADD PWD $pwd | example: "add pwd user:raw_password", ... |
| ADD ENPWD $pwd | example: "add enpwd user:encrypted_password", ... |
| REMOVE PWD $pwd | example: "remove pwd user", ... |
| SAVE CONFIG | save the backends to config file |
| SELECT VERSION | display the version of Atlas |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)
查看后端的代理库
mysql> select * from backends;
+-------------+----------------+-------+------+
| backend_ndx | address | state | type |
+-------------+----------------+-------+------+
| 1 | 10.0.0.49:3306 | up | rw |
| 2 | 10.0.0.42:3306 | up | ro |
| 3 | 10.0.0.43:3306 | up | ro |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)
下线后端节点
mysql> set offline 3;
+-------------+----------------+---------+------+
| backend_ndx | address | state | type |
+-------------+----------------+---------+------+
| 3 | 10.0.0.43:3306 | offline | ro |
+-------------+----------------+---------+------+
1 row in set (0.00 sec)
mysql> select * from backends;
+-------------+----------------+---------+------+
| backend_ndx | address | state | type |
+-------------+----------------+---------+------+
| 1 | 10.0.0.49:3306 | up | rw |
| 2 | 10.0.0.42:3306 | up | ro |
| 3 | 10.0.0.43:3306 | offline | ro |
+-------------+----------------+---------+------+
上线后端节点
mysql> set off online 3;
ERROR 1105 (07000): use 'SELECT * FROM help' to see the supported commands
mysql> set online 3;
+-------------+----------------+---------+------+
| backend_ndx | address | state | type |
+-------------+----------------+---------+------+
| 3 | 10.0.0.43:3306 | unknown | ro |
+-------------+----------------+---------+------+
1 row in set (0.00 sec)
mysql> select * from backends;
+-------------+----------------+---------+------+
| backend_ndx | address | state | type |
+-------------+----------------+---------+------+
| 1 | 10.0.0.49:3306 | up | rw |
| 2 | 10.0.0.42:3306 | up | ro |
| 3 | 10.0.0.43:3306 | unknown | ro |
+-------------+----------------+---------+------+
3 rows in set (0.00 sec)