1.mysql-proxy实现读写分离的原理
MYSQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。
使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。
mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等 MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。
对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。
当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多 个proxy的连接参数即可。
工作原理如图：
2.mysql-proxy实现读写分离的操作：
（1）所需环境：
| 主机ip | 角色 |
|---|---|
| 172.25.33.1 | 写库 |
| 172.25.33.2 | 读库 |
| 172.25.33.3 | proxy代理 |
在实验开始前，将读库和写库设置为基于gtid的主从异步复制，具体参考上一篇博文
（2）server3的操作：：
<1>下载安装包并解压
[root@server3 ~]# ls
mysql-community-client-5.7.24-1.el7.x86_64.rpm
mysql-community-common-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-server-5.7.24-1.el7.x86_64.rpm
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local
<2>停掉server3上的mysqld,因为代理也是3306端口。并且将解压目录重命名
[root@server3 ~]# cd /usr/local
[root@server3 local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy #重命名，方便一点
<3>创建配置目录和日志目录
[root@server3 local]# cd mysql-proxy/
[root@server3 mysql-proxy]# ls
bin include lib libexec licenses share
[root@server3 mysql-proxy]# mkdir log
[root@server3 mysql-proxy]# ls
bin conf include lib libexec licenses log mysql-proxy.conf share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# cd conf
[root@server3 conf]# vim mysql-proxy.conf
[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.33.1:3306
proxy-read-only-backend-addresses=172.25.33.2:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
plugins=proxy
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true
<4>在lua读写分离脚本中，限制最小和最大连接个数 当超出最大连接个数时，就会开启主从复制
[root@server3 mysql-proxy]# vim share/doc/mysql-proxy/rw-splitting.lua
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 2,
is_debug = false
}
end
<5>开启mysql-proxy
[root@server3 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
ps ax
1382 ? S 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-fil
1383 ? S 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-fil
[root@server3 log]# cat mysql-proxy.log
2019-07-28 13:19:20: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=1383 alive
2019-07-28 13:19:20: (debug) chassis-unix-daemon.c:157: waiting for 1383
2019-07-28 13:19:20: (debug) chassis-unix-daemon.c:121: we are the child: 1383
2019-07-28 13:19:20: (critical) plugin proxy 0.8.5 started
2019-07-28 13:19:20: (debug) max open file-descriptors = 1024
2019-07-28 13:19:20: (message) proxy listening on port 0.0.0.0:3306
2019-07-28 13:19:20: (message) added read/write backend: 172.25.33.1:3306
2019-07-28 13:19:20: (message) added read-only backend: 172.25.33.2:3306
[root@server3 log]# netstat -tnlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1383/mysql-proxy
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 824/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 917/master
tcp6 0 0 :::22 :::* LISTEN 824/sshd
tcp6 0 0 ::1:25 :::* LISTEN 917/master
<6>创建用来读写分离的用户并授权
在server1上：mysql> grant insert,update,select on *.* to wsp@'%' identified by 'Liuyan&123';
Query OK, 0 rows affected, 1 warning (0.38 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
mysql> create database redhat;
Query OK, 1 row affected (0.05 sec)
mysql> use redhat;
Database changed
mysql> create table usertb(
-> username varchar(10) not null,
-> password varchar(15) not null);
Query OK, 0 rows affected (0.57 sec)
mysql> desc usertb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| password | varchar(15) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
现在，整个基于mysql-proxy的配置已经完毕，开始测试
<1>首先：真机连三个172.25.33.3,因为只有超过2个才开始读写分离
root@foundation33 ~]# mysql -h 172.25.33.3 -uwsp -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| redhat |
| sys |
| westos |
+--------------------+
6 rows in set (0.00 sec)
<2>监控3306端口，看谁访问了3306端口,列出当前系统打开的文件 -i:用来显示符合条件的进程情况
[root@server3 log]# yum install lsof
[root@server3 log]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysql-pro 1383 root 10u IPv4 27688 0t0 TCP *:mysql (LISTEN)
mysql-pro 1383 root 11u IPv4 31395 0t0 TCP server3:mysql->foundation33.ilt.example.com:34mysql> use redhat;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from usertb;
Empty set (0.00 sec)
886 (ESTABLISHED)
mysql-pro 1383 root 12u IPv4 31396 0t0 TCP server3:39662->server1:mysql (ESTABLISHED)
mysql-pro 1383 root 13u IPv4 31400 0t0 TCP server3:mysql->foundation33.ilt.example.com:34894 (ESTABLISHED)
mysql-pro 1383 root 14u IPv4 31401 0t0 TCP server3:39664->server1:mysql (ESTABLISHED)
mysql-pro 1383 root 15u IPv4 31666 0t0 TCP server3:mysql->foundation33.ilt.example.com:34896 (ESTABLISHED)
mysql-pro 1383 root 16u IPv4 31667 0t0 TCP server3:58558->server2:mysql (ESTABLISHED)
<3>然后在server2上 stop slave，否则会同步到server2上，看不到读写分离的效果;
<4>在真机插入数据：
MySQL [(none)]> use redhat;
MySQL [redhat]> insert into usertb values(‘user1’,‘123’);
<5>在server1中查看，可以看到数据：
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
<6>在server2中查看，看不到数据，实现了读写分离：
mysql> use redhat;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from usertb;
Empty set (0.00 sec)
3.Mysql的高可用
（1）所需环境：
server1：172.25.33.1（管理节点）
server4：172.25.33.4（管理节点）
server2：172.25.33.2（普通节点）
server3：172.25.33.3（普通节点）
备注：在操作之前使server1、server2、server3为基于gtid的主从异步复制
（2）server4的操作：
下载管理软件
[root@server4 ~]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-manager-0.58.tar.gz
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@server4 ~]# yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-* mha4mysql-node-0.58-0.el7.centos.noarch.rpm
生成密钥，并发给server1 2 3 ，使server4可以免密登陆
[root@server4 ~]# ssh-keygen
[root@server4 ~]#ssh-copy-id root@172.25.33.1
[root@server4 ~]# ssh-copy-id root@172.25.33.2
[root@server4 ~]# ssh-copy-i root@172.25.33.3
[root@server4 ~]# ssh root@172.25.33.1
[root@server1 ~]# exit
logout
Connection to server1 closed.
[root@server4 ~]# ssh root@172.25.33.2
Last login: Sun Jul 28 08:59:35 2019 from foundation33.ilt.example.com
[root@server2 ~]# exit
logout
Connection to server2 closed.
[root@server4 ~]# ssh root@172.25.33.3
Last login: Sun Jul 28 13:14:37 2019 from foundation33.ilt.example.com
[root@server3 ~]# exit
logout
Connection to server3 closed.
##将MHA软件同发送给server1 2 3
[root@server4 ~]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 7.7MB/s 00:00
[root@server4 ~]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 8.4MB/s 00:00
[root@server4 ~]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 8.4MB/s 00:00
（2）server1、2、3的操作：
将server4发送的软件在server1、server2和server3中安装
[root@server1 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@server2 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@server3 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
（3）server4配置MHA：
<1>创建mha工作目录及编写配置文件
mkdir /etc/masterha
cd /etc/masterha/
vim master.cnf
[server default]
manager_workdir=/etc/masterha
manager_log=/var/log/masterha.log # manager 日志文件
master_binlog_dir=/etc/masterha
password=Liuyan&123 #MySQL管理帐号和密码
user=root
ping_interval=1
remote_workdir=/tmp
repl_password=Liuyan&123
repl_user=repl # 复制帐号和密码
ssh_user=root # 系统ssh用户
[server1]
hostname=172.25.33.1
port=3306
[server2]
hostname=172.25.33.2
port=3306
candidate_master=1 #候选master
check_repl_delay=0
[server3]
hostname=172.25.33.3
port=3306
no_master=1 #no_master表示这个节点不能作为master
<2>检测ssh连接
通过masterha_check_ssh检查ssh连接，由于之前我们设置的是server4与server1，2，3建立免密连接，但server1，2，3之间却不是免密连接的，因此我们需要使各结点均可免密登录即可。
[root@server4 masterha]# scp -r ~/.ssh server1:
id_rsa 100% 1675 1.3MB/s 00:00
id_rsa.pub 100% 394 368.6KB/s 00:00
known_hosts 100% 543 475.0KB/s 00:00
[root@server4 masterha]# scp -r ~/.ssh server2:
id_rsa 100% 1675 1.1MB/s 00:00
id_rsa.pub 100% 394 342.7KB/s 00:00
known_hosts 100% 543 511.8KB/s 00:00
[root@server4 masterha]# scp -r ~/.ssh server3:
id_rsa 100% 1675 1.1MB/s 00:00
id_rsa.pub 100% 394 365.1KB/s 00:00
known_hosts
masterha_check_ssh --conf=/etc/masterha/master.cnf ##可以看到ssh连接成功
<3>检查复制功能
由于之前我们对数据库进行了安全初始化，数据库不允许root用户远程登录，因此我们需要在主库中给root用户授权，然后才能通过复制功能检测（在server1中授权即可）
grant all on *.* to root@'%' identified by 'Liuyan&123';
flush privileges;
masterha_check_repl --conf=/etc/masterha/master.cnf #检查复制功能，复制功能ok
<4>静默开启masterha_manager，用于检测该结点的manager是否正常
nohup masterha_manager --conf=/etc/masterha/master.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha.log 2>&1 &
ps aux | grep masterha_manager
masterha_stop --conf=/etc/masterha/master.cnf #出现manager进程表示正常，需要将其关闭
（4）高可用集群搭建成功， 测试手动切换master
测试当server1节点服务停止后，server2能否接替成为主节点
##在server1中关闭服务
[root@server1 ~]# systemctl stop mysqld
##在server4中手动切换，先关闭manager，不关的话切不了，manager就是自动切换的工具
[root@server4 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/master.cnf --dead_master_host=172.25.33.1 --dead_master_ip=172.25.33.1 --dead_master_port=3306 --new_master_host=172.25.33.2 --new_master_port=3306
在其他结点测试，查看主数据库是否切换，server3中，查看从结点的状态，可以看到master的主机地址已经切换，在server2中从节点状态为空
为了方便后续实验，恢复server1数据库，将server1作为slave加到高可用集群中
systemctl start mysqld
mysql -uroot -pLiuyan&123
change master to master_host='172.25.33.2',master_user='repl',master_password='Liuyan&123',master_auto_position=1;
start slave;
show slave status\G
（5）手动在线切换master
<1>在/etc/masterha目录下生成一个master.failover.complete文件，是来记录failover情况的，再进行failover时必须先把这个文件删除，不然不会failover
cd /etc/masterha
rm -fr master.failover.complete
<2>手动在线切换
masterha_master_switch --master_state=alive --conf=/etc/masterha/master.cnf --new_master_host=172.25.33.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
<3>==测试:server1中查看之前从结点的设置，变为空，则证明现在server1是master，server2和server3中查看从结点状态，会发现master的ip变为server1的 ip
（5） 测试自动切换master
实际上就是编写了两个脚本，把它们加入到配置文件中，在出问题时，通过使用这两个脚本，实现了master的一个专门的ip的漂移，以此来实现master的切换
首先清理master.failover.complete
然后修改两个脚本
[root@server4 bin]# vim master_ip_failover
[root@server4 bin]# vim master_ip_online_change
将脚本加到配置文件中
vim /etc/masterha/master.cnf
server4开启mha manager
nohup masterha_manager --conf=/etc/masterha/master.cnf &>/dev/null &
pa aux | grep master_manager
将主节点挂掉
[root@server1 ～]#systemctl stop mysqld
此时，观察vip是否可以从server1漂移到server2中，同时也可以在server2和server3中观察节点状态。至此，就实现了自动切换主节点
183
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
