企业级高性能MYSQL的用法-------（三）----------数据库的读写分离(mysql-proxy) 和 基于MHA的MySQL高可用架构
一.数据库的读写分离(mysql-proxy)
1.什么是读写分离？
MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。
使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。
2.为什么要读写分离？
因为数据库的写操作相对读操作是比较耗时的，所以数据库的读写分离，解决的是数据库的写入，影响了查询的效率。
3.读写分离的实现
实验环境：
selinux and firewalld disabled
server1:192.168.43.71（master）
server2:192.168.43.72（slave）
server3:192.168.43.73（mysql-proxy）
在server1 和 server2中 ：
关闭mysql，清除之前的所有数据，再开启mysql。
更改配置文件：
server1:
log-bin=mysql-bin
server-id=1
gtid_mode=ON 使用 gtid 的模式进行复制
enforce-gtid-consistency=ON
server2:
log-bin=mysql-bin
server_id=2
gtid_mode=ON
enforce-gtid-consistency=ON
重新初始化数据库后，登录
server1和server2要实现基于gtid的主从复制，这里就不多赘述了，详情见上一篇博文
在server3中
1. mysql-proxy配置：
tar zxf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local
cd /usr/local/
ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
cd /usr/local/mysql-proxy
mkdir conf
cd conf/
vim mysql-proxy.conf #创建配置文件
写入下面内容：注意不要加注释，否则会报错
1 [mysql-proxy]
2 user=root ##运行mysql-proxy用户
3 proxy-address=0.0.0.0:3306 ##mysql-proxy运行ip和端口
4 proxy-read-only-backend-addresses=192.168.43.72:3306 ##slave：只读
5 proxy-backend-addresses=192.168.43.71:3306 ##master：可读写
6 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本地址
7 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
8 plugins=proxy
9 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log #日志位置
10 log-level=debug #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
11 daemon=true ##打入后台
12 keepalive=true ##mysql-proxy崩溃时，尝试重启（持续连接）
2. 建立目录存放读写分离的日志：
mkdir /usr/local/mysql-proxy/log
3 修改读写分离配置文件：
vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
修改以下内容：
40 min_idle_connections = 1, #最小连接数
41 max_idle_connections = 2, #最大连接数，最大连接数大于2时发生读写分离
4. 给文件设置权限，再启动mysql-proxy（否则会启动失败）：
chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf #修改权限
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf #启动mysql-proxy
cat /usr/local/mysql-proxy/log/mysql-proxy.log #查看到server1和server2已经添加上
netstat -antlp #查看端口3306打开
在master上授权一个新的用户：
mysql> grant insert,update,select on *.* to yy@'%' identified by 'Xiaoxu@junjun021129';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> use westos;
Database changed
mysql> create table userinfo (
-> username varchar(10) not null,
-> phonenum varchar(10) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> desc userinfo;
在proxy端监控3306端口情况，确保读写分离启动：
yum install lsof-4.87-4.el7.x86_64 -y
lsof -i:3306
使用真机通过server3连接数据库，直到lsof监控到读写分离出现：
[root@rhel8_node2 yum.repos.d]# mysql -h 192.168.43.73 -uyy -pXiaoxu@junjun021129
bash: mysql: command not found... #真机中如果没有mysql，直接用yum install mariadb-server安装
Failed to search for file: Cannot update read-only repo
[root@rhel8_node2 yum.repos.d]# yum install -y mariadb-server
测试：
step1 master和slave状态都开启：
在真机中通过server3连接数据库：
注意：此时可以成功插入看到数据，并不能判断实现了读写分离
关闭server2（slave）：
stop slave;
在真机插入数据，却不能查看到数据，而在master上可以查看到插入的数据：
这时候在真机上看不到插入的数据，因为server2关闭了
开启server2的slave：
start slave;
在真机中再次select，可以查看到刚才插入的数据：
select * from userinfo;
说明：实现读写分离，读操作是在server2（slave）上
关掉server1（master）：
systemctl stop mysqld
在物理机上连接数据库，发现插入数据失败：
insert into userinfo values (‘user3’,‘333’);
说明：实现读写分离，写操作在server1（master）上
二 . 基于MHA的MySQL高可用架构
1.什么是MHA？
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于 Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。
在MySQL故障切换过程中，MHA能做到在 0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其 他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。
目前MHA主要支持一主多从的架构，要搭建MHA：要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库。
实验环境
server1—mysql
server2—mysql
server3—mysql
server4—MHA
2.基于MHA的MySQL高可用架构搭建
实现三个数据库之间的主从复制
step1 修改配置文件：
server1 server2 server3中只有server_id不同，其它参数相同
在server1中：
vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog
在server2中：
vim /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog
在server3中：
vim /etc/my.cnf
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog
step2 打开服务，过滤密码，安全初始化：
systemctl start mysqld
cat /var/log/mysqld.log | grep password
mysql_secure_installation
step3 写入主从复制策略：
server1：
[root@server1 mysql]# mysql -uroot -pXiaoxu@junjun021129
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to repl@'192.168.43.%' identified by 'Xiaoxu@junjun021129';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 | 1004 | | | 263fe1de-978a-11ea-a72e-000c29a5b17f:1-4 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql>
在server2和server3中：
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.43.71',
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = 'Xiaoxu@junjun021129',
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
配置MHA
1. 安装MHA：
需要下载以下安装包:
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-* mha4mysql-node-0.58-0.el7.centos.noarch.rpm
2. 配置免密访问（这一步的前提是写好解析）：
ssh-keygen #生成密钥（一路回车即可）
ssh-copy-id server1 #发送密钥
ssh-copy-id server2
ssh-copy-id server3
ssh server1 #测试是否配置成功（可以免密登陆）
server1 2 3之间互相不免密，拷贝 server4 上的密钥给 server1 2 3：
scp -r .ssh server1:
scp -r .ssh server2:
scp -r .ssh server3:
3. 在三台mysql节点中安装所需MHA插件：
yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
4 配置MHA工作目录及配置文件：
mkdir /etc/masterha
vim /etc/masterha/ajj1.cnf
[server default]
manager_workdir=/etc/masterha
manager_log=/var/log/masterha.log
master_binlog_dir=/etc/masterha
user=root
password=Xiaoxu@junjun021129
ping_interval=1
remote_workdir=/tmp
repl_password=Xiaoxu@junjun021129
repl_user=repl
ssh_user=root
[server1]
hostname=192.168.43.71
port=3306
[server2]
hostname=192.168.43.72
port=3306
candidate_master=1
check_repl_delay=0
[server3]
hostname=192.168.43.73
port=3306
no_master=1
5. 检测ssh连接(免密登录）：
masterha_check_ssh --conf=/etc/masterha/app1.cnf
6 检测复制功能（主从复制）：
因为 server4 默认是用 root 远程连接数据库,但是在配置数据库是已经禁用了 root 的远程连接
在主库上server1上授权用户：
mysql> grant all on *.* to root@'%' identified by 'Xiaoxu@junjun021129';
Query OK, 0 rows affected, 1 warning (0.00 sec)
masterha_check_repl --conf=/etc/masterha/ajj1.cnf
然后我们就可以测试在server4中进行切换了。
测试切换
1.手动切换master结点
当前我们的master结点是server1，我们手动切换是需要挂掉server1的mysql服务。
1. 关闭master的mysql：
systemctl stop mysqld
2 手动切换：（在MHA服务端进行操作）
masterha_master_switch \
--master_state=dead \ 状态为挂掉的
--conf=/etc/masterha/ajj1.cnf \ 配置文件
--dead_master_host=192.168.43.71 \ 挂掉的master地址
--dead_master_ip=192.168.43.71 \
--dead_master_port=3306 \
--new_master_host=192.168.43.72 \ 新的master地址
--new_master_port=3306
过程中间都写yes,然后回车
注意：手动切换之前,需要保证主从同步正常,repl 复制用户能够远程连接
从上图可以看出重置了master节点，切换到了server2上。
在server3上看到它的master已经变成了server2：
mysql> show slave status\G
而server2已经变成master了：
mysql> show master status;
以上就是手动离线切换！！！！！！！！！
下面我们来进行手动在线切换！！！！！！
手动开启server1，作为slave加入集群：
systemctl start mysqld
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.43.72',
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = 'Xiaoxu@junjun021129',
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
刚才做的是master挂掉后的切换，即死切。这次在master正常时切换，即活切
在上一步测试完手动离线切换后会在/etc/masterha目录下生成一个ajj1.failover.complete文件，是来记录 failover情况的，再进行failover时必须先把这个文件删除，不然会有所影响
1. 删除app1.failover.complete文件：(在MHA主机上)
cd /etc/masterha/
rm -fr app1.failover.complete
2.手动切换
masterha_master_switch --conf=/etc/masterha/ajj1.cnf --master_state=alive --new_master_host=192.168.43.71 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
#过程中间都写yes
3. 查看master情况：
在server2和server3中查看到master切换到server1上：
mysql> show slave status\G
而server1成为master：
mysql> show master status;
以上两种就是我们手动离线和在线的切换！！！！！！！！！！
2.半自动切换
1. 开启MHA manager：
nohup masterha_manager --conf=/etc/masterha/app1.cnf &>/dev/null &
2. 在server1中查看进程：
ps ax | grep mysql
kill -9 9216 #关闭进程
ps ax | grep mysql #再次查看mysql进程又开启了
systemctl stop mysqld #手动关闭mysql
3. 在server4中查看日志，发现已经成功切换：
cat /var/log/masterha.log
4. 在server3中查看到master变成server2：
mysql> show slave status\G
5 手动开启server1，作为slave加入集群：
systemctl start mysqld
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.43.72',
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = 'Xiaoxu@junjun021129',
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
配置脚本和vip漂移
[root@mha masterha]# vim 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.43.100/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
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@mha masterha]# vim master_ip_online_change
#!/usr/bin/env perl
use strict;
use warnings FATAL =>'all';
use Getopt::Long;
my $vip = '192.168.43.100/24'; # Virtual IP
my $key = "1";
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
my $exit_code = 0;
my (
$command, $orig_master_is_new_slave, $orig_master_host,
$orig_master_ip, $orig_master_port, $orig_master_user,
$orig_master_password, $orig_master_ssh_user, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password, $new_master_ssh_user,
);
GetOptions(
'command=s' => \$command,
'orig_master_is_new_slave' => \$orig_master_is_new_slave,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'orig_master_user=s' => \$orig_master_user,
'orig_master_password=s' => \$orig_master_password,
'orig_master_ssh_user=s' => \$orig_master_ssh_user,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
'new_master_user=s' => \$new_master_user,
'new_master_password=s' => \$new_master_password,
'new_master_ssh_user=s' => \$new_master_ssh_user,
);
exit &main();
sub main {
#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "\n\n\n***************************************************************\n";
print "Disabling the VIP - $vip on old master: $orig_master_host\n";
print "***************************************************************\n\n\n\n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "\n\n\n***************************************************************\n";
print "Enabling the VIP - $vip on new master: $new_master_host \n";
print "***************************************************************\n\n\n\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";
`ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $orig_master_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@mha masterha]# cp master_ip_failover master_ip_online_change /usr/local/bin/
[root@mha masterha]# cd /usr/local/bin/
[root@mha bin]# chmod +x master_ip_failover master_ip_online_change
修改MHA配置文件：
cd /etc/masterha/
vim ajj1.cnf
添加以下内容：
5 master_ip_failover_script= /usr/local/bin/master_ip_failover
6 master_ip_online_change_script= /usr/local/bin/master_ip_online_change
给server2添加 vip（因为目前server2是master）：
ip addr add 192.168.43.100/24 dev eth0
ip addr show
vip漂移：
masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
一路yes
查看vip漂移情况：
在server2中发现vip消失：
ip addr show
在server1中vip出现：
查看master情况：
在server3中查看到master变为server1：
mysql> show slave status\G
3.全自动切换
1. 开启manager：
nohup masterha_manager --conf=/etc/masterha/ajj1.cnf &>/dev/null &
2. 模拟故障：
在server1中：
systemctl stop mysqld
3 在server4(mha主机上)上查看日志：
cat /var/log/masterha.log #可以看到切换成功的信息,vip也成功漂移
查看vip漂移情况：
在server1中发现vip消失：
ip addr show
在server2中vip出现：
ip addr show
查看master情况：
在server3中查看到master变为server2：
mysql> show slave status\G
139
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
