版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wzy0623/article/details/81235448
目录
OS:CentOS Linux release 7.2.1511 (Core) MySQL:5.6.14 172.16.1.126:Heartbeat + haproxy + MySQL Semisync-Replication Master 172.16.1.127:Heartbeat + haproxy + MySQL Semisync-Replication Slave 172.16.1.100:VIP
具体架构如图1所示。
图1
从图1中看到,使用两台主机做MySQL主从复制,实现读写分离,用于提高查询性能。采用MySQL 5.6.x的半同步实现数据复制和同步。Heartbeat在这里主要用作主机健康状态检查以及实现haproxy两台主机之间的自动失败切换。任何一台主机宕机都不会影响对外提供服务(VIP可以漂移),保持MySQL数据库服务的高可用性。
Heartbeat是使用心跳进行通信和选举实现的高可用解决方案,利用其避免单点故障。通常这个解决方案中,至少有两台服务器运行Heartbeat,一台为Master,另一台为Backup,但对外表现为一个或一组VIP。Master会发送特定消息给Backup,当Backup收不到该消息时,则认为Master出现故障,Backup会接管VIP,继续提供服务,从而保证了高可用性。haproxy在本例的作用是提供读负载均衡。整体架构的设计原理和异常处理可描述如下:
参见https://blog.csdn.net/wzy0623/article/details/81045843#1.%20配置MySQL半同步复制。
参见https://blog.csdn.net/wzy0623/article/details/81218318#3.%20安装配置haproxy。 172.16.1.126上的/usr/local/haproxy/conf/haproxy.cfg文件内容如下:
global log 127.0.0.1 local2 # 日志定义级别 chroot /usr/local/haproxy # 当前工作目录 pidfile /var/run/haproxy.pid # 进程id maxconn 4000 # 最大连接数 user haproxy # 运行改程序的用户 group haproxy daemon # 后台形式运行 stats socket /usr/local/haproxy/stats defaults mode tcp # haproxy运行模式(http | tcp | health) log global # 采用全局定义的日志 option dontlognull # 不记录健康检查的日志信息 option redispatch # serverId对应的服务器挂掉后,强制定向到其他健康的服务器 retries 3 # 三次连接失败则服务器不用 timeout http-request 10s timeout queue 1m timeout connect 10s # 连接超时 timeout client 1m # 客户端超时 timeout server 1m # 服务器超时 timeout http-keep-alive 10s timeout check 10s # 心跳检测 maxconn 600 # 最大连接数 listen stats # 配置haproxy状态页(用来查看的页面) mode http bind :8888 stats enable stats hide-version # 隐藏haproxy版本号 stats uri /haproxyadmin?stats # 一会用于打开状态页的uri stats realm Haproxy\ Statistics # 输入账户密码时的提示文字 stats auth admin:admin # 用户名:密码 frontend read bind *:3307 # 使用3307端口。监听前端端口(表示任何ip访问3307端口都会将数据轮番转发到mysql服务器群组中) default_backend mysql_read # 后端服务器组名 backend mysql_read balance roundrobin # 使用轮询方式调度 server mysql1 172.16.1.126:3306 check port 3306 maxconn 300 server mysql2 172.16.1.127:3306 check port 3306 maxconn 300 frontend write bind *:3308 # 使用3308端口。监听前端端口(表示任何ip访问3308端口都会将数据轮番转发到mysql服务器群组中) default_backend mysql_write # 后端服务器组名 backend mysql_write server mysql1 172.16.1.126:3306 check port 3306 maxconn 300
如前所述,配置了两对frontend\backend。read绑定3307端口接收读请求,其对应的backend为mysql_read,其中定义两个台MySQL服务器,使用轮询策略实现读负载均衡。write绑定3308端口接收写请求,其对应的backend为mysql_write,其中只定义MySQL Master,即只有它接收写请求。
172.16.1.127上的/usr/local/haproxy/conf/haproxy.cfg文件内容如下:
global log 127.0.0.1 local2 # 日志定义级别 chroot /usr/local/haproxy # 当前工作目录 pidfile /var/run/haproxy.pid # 进程id maxconn 4000 # 最大连接数 user haproxy # 运行改程序的用户 group haproxy daemon # 后台形式运行 stats socket /usr/local/haproxy/stats defaults mode tcp # haproxy运行模式(http | tcp | health) log global # 采用全局定义的日志 option dontlognull # 不记录健康检查的日志信息 option redispatch # serverId对应的服务器挂掉后,强制定向到其他健康的服务器 retries 3 # 三次连接失败则服务器不用 timeout http-request 10s timeout queue 1m timeout connect 10s # 连接超时 timeout client 1m # 客户端超时 timeout server 1m # 服务器超时 timeout http-keep-alive 10s timeout check 10s # 心跳检测 maxconn 600 # 最大连接数 listen stats # 配置haproxy状态页(用来查看的页面) mode http bind :8888 stats enable stats hide-version # 隐藏haproxy版本号 stats uri /haproxyadmin?stats # 一会用于打开状态页的uri stats realm Haproxy\ Statistics # 输入账户密码时的提示文字 stats auth admin:admin # 用户名:密码 frontend read bind *:3307 # 使用3307端口。监听前端端口(表示任何ip访问3307端口都会将数据轮番转发到mysql服务器群组中) default_backend mysql_read # 后端服务器组名 backend mysql_read balance roundrobin # 使用轮询方式调度 server mysql1 172.16.1.126:3306 check port 3306 maxconn 300 server mysql2 172.16.1.127:3306 check port 3306 maxconn 300 frontend write bind *:3308 # 使用3308端口。监听前端端口(表示任何ip访问3308端口都会将数据轮番转发到mysql服务器群组中) default_backend mysql_write # 后端服务器组名 backend mysql_write server mysql1 172.16.1.127:3306 check port 3306 maxconn 300
只有最后一行与172.16.1.126的不同。当服务切换到Slave上时,接收读写请求的只有172.16.1.127一台主机。
参见https://blog.csdn.net/wzy0623/article/details/81188814#二、安装Heartbeat。
这里需要注意的是/usr/local/heartbeat/etc/ha.d/haresources文件的设置,该文件在所有Heartbeat主机上必须完全一致,内容只有如下一行:
hdp3 172.16.1.100 mysql
hdp3表示主机名,172.16.1.100是VIP,mysql是haresources文件同目录下resource.d目录下的一个自定义脚本文件名,即/usr/local/heartbeat/etc/ha.d/resource.d/mysql文件,其内容为如下两行:
/home/mysql/remove_slave.sh /etc/init.d/haproxy restart
当Heartbeat主机获得资源时,将自动把MySQL主从复制中的slave置为master,这是通过调用/home/mysql/remove_slave.sh文件完成的。之后重启haproxy服务,接收应用请求。
以下两个脚本文件在172.16.1.126与172.16.1.127上相同。(1)/home/mysql/remove_slave.sh 该文件的作用是重置MySQL复制中的角色,将Slave切换为Master,文件内容如下:
#!/bin/bash . /home/mysql/.bashrc user=root password=123456 log=/home/mysql/remove_slave.log echo "`date`" >> $log rm -rf /tmp/kill.sql mysql -u$user -p$password -e "select * into outfile '/tmp/kill.sql' from (select concat('kill ',id,';') from information_ schema.processlist where command='sleep' union all select 'set global read_only=OFF;' union all select 'stop slave;' unio n all select 'reset slave all;') t;" mysql -u$user -p$password < /tmp/kill.sql >> $log /bin/sed -i 's#read-only#\#read-only#' /home/mysql/mysql-5.6.14/my.cnf
(2)/home/mysql/mysql_check.sh 脚本用于检查本机MySQL的服务器状态。脚本内容与部署参见https://blog.csdn.net/wzy0623/article/details/81188814#4.%20创建MySQL服务检测脚本。
(1)启动两个主机的heartbeat服务 在我们这个场景中,一定要注意两个主机的heartbeat服务的启动顺序,要先启动172.16.1.127,再启动172.16.1.126。如果反过来先启动172.16.1.126,则再启动172.16.1.127时,hdp3获得VIP资源,会执行本地的mysql脚本。这时将会调用remove_slave.sh,重置172.16.1.127在MySQL主从复制中slave角色,以前的复制就失效了,那就必须要重建复制。先后在172.16.1.127、172.16.1.126上执行以下命令:
chkconfig heartbeat on systemctl start heartbeat
(2)在172.16.1.126上启动haproxy
systemctl start haproxy
启动完成后,可以看到VIP绑定在172.16.1.126上:
[root@hdp3~]#ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:50:56:a5:0f:77 brd ff:ff:ff:ff:ff:ff inet 172.16.1.126/24 brd 172.16.1.255 scope global ens32 valid_lft forever preferred_lft forever inet 172.16.1.100/24 brd 172.16.1.255 scope global secondary ens32:1 valid_lft forever preferred_lft forever inet6 fe80::250:56ff:fea5:f77/64 scope link valid_lft forever preferred_lft forever [root@hdp3~]#
在172.16.1.126上有heartbeat、haproxy、mysql_check相关进程:
[root@hdp3~]#ps -ef | grep heartbeat | grep -v grep root 608426 1 0 Jul26 ? 00:01:55 heartbeat: master control process root 608429 608426 0 Jul26 ? 00:00:03 heartbeat: FIFO reader root 608430 608426 0 Jul26 ? 00:00:10 heartbeat: write: bcast ens32 root 608431 608426 0 Jul26 ? 00:00:10 heartbeat: read: bcast ens32 root 608432 608426 0 Jul26 ? 00:00:08 heartbeat: write: ucast ens32 root 608433 608426 0 Jul26 ? 00:00:14 heartbeat: read: ucast ens32 root 608434 608426 0 Jul26 ? 00:00:17 heartbeat: write: ping 172.16.1.254 root 608435 608426 0 Jul26 ? 00:00:07 heartbeat: read: ping 172.16.1.254 haclust+ 608455 608426 0 Jul26 ? 00:00:04 /usr/local/heartbeat/libexec/heartbeat/ipfail [root@hdp3~]#ps -ef | grep haproxy | grep -v grep haproxy 608854 1 0 Jul26 ? 00:00:19 /usr/sbin/haproxy -D -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid [root@hdp3~]#ps -ef | grep mysql_check | grep -v grep root 575823 575822 0 Jul26 ? 00:01:24 /bin/bash /home/mysql/mysql_check.sh [root@hdp3~]#
在172.16.1.127上有heartbeat和mysql_check相关进程:
[root@hdp4/usr/local/haproxy/conf]#ps -ef | grep heartbeat | grep -v grep root 612316 1 0 Jul26 ? 00:01:37 heartbeat: master control process root 612319 612316 0 Jul26 ? 00:00:03 heartbeat: FIFO reader root 612320 612316 0 Jul26 ? 00:00:12 heartbeat: write: bcast ens160 root 612321 612316 0 Jul26 ? 00:00:07 heartbeat: read: bcast ens160 root 612322 612316 0 Jul26 ? 00:00:10 heartbeat: write: ucast ens160 root 612323 612316 0 Jul26 ? 00:00:13 heartbeat: read: ucast ens160 root 612324 612316 0 Jul26 ? 00:00:16 heartbeat: write: ping 172.16.1.254 root 612325 612316 0 Jul26 ? 00:00:06 heartbeat: read: ping 172.16.1.254 haclust+ 612475 612316 0 Jul26 ? 00:00:04 /usr/local/heartbeat/libexec/heartbeat/ipfail [root@hdp4/usr/local/haproxy/conf]#ps -ef | grep haproxy | grep -v grep [root@hdp4/usr/local/haproxy/conf]#ps -ef | grep mysql_check | grep -v grep root 290127 290126 0 Jul25 ? 00:04:21 /bin/bash /home/mysql/mysql_check.sh [root@hdp4/usr/local/haproxy/conf]#
至此,环境与初始化工作已经完成,下面进行功能测试。
1. 验证3307端口的读负载均衡转发策略
C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 127 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 127 | +---------------+-------+ C:\WINDOWS\system32>
2. 验证3308端口的读负载均衡转发策略
C:\WINDOWS\system32>mysql -utest -p123456 -P3308 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3308 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3308 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3308 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>
3. 模拟从库的mysqld crash 在172.16.1.127上执行以下命令:
pkill -9 mysqld
4. 再次使用两个端口连接,数据库服务正常。
C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3308 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3308 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>
5. 重新启动从库的mysql服务 在172.16.1.127上执行以下命令:
service mysql start
6. 再次验证3307端口的读负载均衡转发策略
C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 127 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 126 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 127 | +---------------+-------+ C:\WINDOWS\system32>
7. 模拟主库的mysqld crash 在172.16.1.126上执行以下命令:
pkill -9 mysqld
8. 再次验证两个端口的读负载均衡转发策略
C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 127 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3307 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 127 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3308 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 127 | +---------------+-------+ C:\WINDOWS\system32>mysql -utest -p123456 -P3308 -h172.16.1.100 -e "show variables like 'server_id'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 127 | +---------------+-------+ C:\WINDOWS\system32>
此时查看172.16.1.127上绑定的IP如下:
[root@hdp4/usr/local/haproxy/conf]#ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000 link/ether 00:50:56:a5:49:7f brd ff:ff:ff:ff:ff:ff inet 172.16.1.127/24 brd 172.16.1.255 scope global ens160 valid_lft forever preferred_lft forever inet 172.16.1.100/24 brd 172.16.1.255 scope global secondary ens160:0 valid_lft forever preferred_lft forever inet6 fe80::250:56ff:fea5:497f/64 scope link valid_lft forever preferred_lft forever [root@hdp4/usr/local/haproxy/conf]#
可以看到VIP已经漂移到172.16.1.127上。9. 验证切换后的新主库可读写。
C:\WINDOWS\system32>mysql -utest -p123456 -P3308 -h172.16.1.100 -e "use test; create table t1(a int); insert into t1 values (1),(2),(3); commit; select * from t1;" mysql: [Warning] Using a password on the command line interface can be insecure. +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ C:\WINDOWS\system32>
查看172.16.1.127上MySQL的slave status,已经为空:
[mysql@hdp4~]$mysql -u root -p123456 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 24 Server version: 5.6.14-log Source distribution Copyright (c) 2000, 2013, 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> show slave status\G Empty set (0.00 sec) mysql>
MySQL配置文件的read-only选项也已经注释:
[mysql@hdp4~]$more /home/mysql/mysql-5.6.14/my.cnf | grep read-only #read-only [mysql@hdp4~]$
本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。
我来说两句