MySQL的高可用方案一般有如下几种:
keepalived+双主,MHA,PXC,MMM,Heartbeat+DRBD等,比较常用的是keepalived+双主,MHA和PXC。
HAProxy是一款免费的提供高可用性、负载均衡以及基于TCP(第四层)和HTTP(第七层)应用的代理软件,借助HAProxy可以快速并且可靠的提供基于TCP和HTTP应用的代理解决方案。
Keepalived主要作用是LoadBalance master和LoadBalance backup之间的健康检查,实现故障转换。
Mysql Replication主要作用是提高mysql并处理数据的能力以及实现容灾备份的作用。
架构规划:
keepalived+haproxy+mysql
服务器及其IP地址规划:
240 mysql_b2 192.168.0.240
242 mysql_b1 192.168.0.242
247 haproxy1 192.168.0.247
249 haproxy2 192.168.0.249
243 VIP 192.168.0.243
248 client 192.168.0.248
软件版本:
mysql:8.0.12
keepalived:2.0.8
haproxy:1.8.14
mydumper:0.9.1
软件下载链接:
wget http://fossies.org/linux/misc/haproxy-1.8.14.tar.gz
wget http://www.keepalived.org/software/keepalived-2.0.8.tar.gz
服务器准备:
docker network create --subnet=192.168.0.0/16 staticnet docker run -d --privileged -v `pwd`/mysql_data:/data -p 3000:3306 --name mysql_b2 --hostname mysql_b2 --net staticnet --ip 192.168.0.240 eiki/mysql:8.0.12 /usr/sbin/init docker run -d --privileged -v `pwd`/mysql_data:/data -p 4000:3306 --name mysql_b1 --hostname mysql_b1 --net staticnet --ip 192.168.0.242 eiki/mysql:8.0.12 /usr/sbin/init docker run -d --privileged -v `pwd`/mysql_data:/data -p 5001:8899 --name haproxy1 --hostname haproxy1 --net staticnet --ip 192.168.0.247 eiki/centos:7.5 /usr/sbin/init docker run -d --privileged -v `pwd`/mysql_data:/data -p 5002:8899 --name haproxy2 --hostname haproxy2 --net staticnet --ip 192.168.0.249 eiki/centos:7.5 /usr/sbin/init
Mysql主从复制配置:
过程略,参考请见https://www.cnblogs.com/EikiXu/p/9811764.html
MyDumper的安装,参考https://www.cnblogs.com/EikiXu/p/9816058.html
1.在master服务器上创建mysql用户(授权复制账户):
192.168.0.242:
create user 'replica'@'%' IDENTIFIED BY 'rooT_000'; grant REPLICATION SLAVE ON *.* TO 'replica'@'%';
2.编辑master服务器的mysql配置文件my.cnf:
[mysql] prompt = [\\u@\\h][\\d]>\\_ port = 3306 socket = /usr/local/mysql/data/mysql.sock [mysqld] server-id = 1 ###指定服务器的ID port = 3306 mysqlx_port = 33060 mysqlx_socket = /usr/local/mysql/data/mysqlx.sock datadir = /usr/local/mysql/data socket = /usr/local/mysql/data/mysql.sock pid-file = /usr/local/mysql/data/mysqld.pid log-error = error.log slow-query-log = 1 slow-query-log-file = slow.log long_query_time = 0.2 log-bin = mysql-bin ###开启二进制日志 relay-log = relay.log binlog_format =ROW relay_log_recovery = 1 character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect ='SET NAMES utf8mb4' innodb_buffer_pool_size = 1G join_buffer_size = 128M sort_buffer_size = 2M read_rnd_buffer_size = 2M log_timestamps = SYSTEM lower_case_table_names = 1 default-authentication-plugin =mysql_native_password max_allowed_packet = 500M ###控制其通信缓冲区的最大长度 plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" rpl_semi_sync_master_enabled = 0 rpl_semi_sync_master_timeout = 1000 auto_increment_increment=2 auto_increment_offset=1 binlog-ignore = mysql //忽略mysql和information_schema 数据库 binlog-ignore = information_schema binlog-do-db = blog //同步数据库,默认同步所有数据库 replicate-do-db=test
3.查看master状态;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1642 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.04 sec)
4.在slave端创建数据库blog,导出master端的blog库,导入到此库,并修改mysql主配置文件my.cnf server-id= 10 重启mysql数据库
192.168.0.240:
mydumper -h 192.168.0.242 --regex="test.*" -u root -p 'abc123' -r 300000 -G -E -R -v 3 -t 5 -o /data/backup20181031 > /data/mydumper20181031.log 2>&1 &
myloader -S /usr/local/mysql/data/mysql.sock -u root -p 'abc123' -v 3 -t 10 -e y -d /data/backup20181031 > /data/myloader20181031.log 2>&1 &
CHANGE MASTER TO
MASTER_HOST='192.168.0.242',
MASTER_USER='replica',
MASTER_PASSWORD='rooT_000',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1642;
5.mysql主从同步测试,show slave status\G;能看到Slave_IO_Running和Slave_SQL_Running都为YES即可。
[root@localhost][(none)]> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.242 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 441 Relay_Log_File: relay.000007 Relay_Log_Pos: 655 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test,test Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 441 Relay_Log_Space: 1020 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 3b27e7d5-d5d9-11e8-8004-0242ac110002 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 1 row in set (0.01 sec)
Haproxy安装及其配置,master和backup安装完全一样的,配置部分差异:
参考来源:https://blog.csdn.net/qq_36663951/article/details/80492092
创建haproxy程序用户以及配置文件目录
useradd -M -s /sbin/nologin haproxy
下载安装包、授权并解压
wget https://www.haproxy.org/download/1.8/src/haproxy-1.8.14.tar.gz
tar-zxvf haproxy-1.8.14.tar.gz
cd haproxy-1.8.14
make TARGET=linux2628 USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 PREFIX=/usr/local/haproxy
make install PREFIX=/usr/local/haproxy
cd /usr/local/haproxy
mkdir conf logs //在此目录下面建立conf,logs目录分别存放HAproxy的配置文件,PID文件和日志文件。
创建用户haproxy
grant all privileges on *.* to 'haproxy'@'%' identified by 'haproxy';
flush privileges;
vim /usr/local/haproxy/conf/haproxy.cfg
global log /dev/log local0 info log /dev/log local1 notice user haproxy group haproxy defaults log global retries 2 timeout connect 3000 timeout server 5000 timeout client 5000 listen mysql-cluster bind 0.0.0.0:3306 mode tcp #option mysql-check user haproxy option tcp-check balance roundrobin server mysql1 192.168.0.242:3306 check weight 2 server mysql2 192.168.0.240:3306 check weight 2 listen mysql-clusterstats bind 0.0.0.0:8899 mode http stats enable stats uri / stats realm Strictly\ Private stats auth status:mypass stats auth admin:mysqladmin #状态查看页面登陆帐号密码
vim /etc/rsyslog.d/haproxy.conf
if ($programname == 'haproxy' and $syslogseverity-text == 'info') then -/var/log/haproxy181/haproxy181-info.log
& ~
if ($programname == 'haproxy' and $syslogseverity-text == 'notice') then -/var/log/haproxy181/haproxy181-notice.log
& ~
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.cfg -c ###验证参数文件配置有效性
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.cfg ###启动haproxy
Haproxy开启系统日志支持。
vim /etc/syslog.conf
#添加: local3.* /var/log/haproxy.log #注意:local数字 该值与haproxy.conf中的配置保持一致 local0.* /var/log/haproxy.log #注意:local数字 该值与haproxy.conf中的配置保持一致 vim /etc/sysconfig/syslog #修改: SYSLOGD_OPTIONS="-r -m 0" #注释:-c 2 使用兼容模式,默认是 -c 5,-r开启远程日志,-m 0标记时间戳。单位是分钟,为0时,表示禁用该功能
#重新启动syslog服务
/etc/init.d/syslogrestart
[root@vm05 haproxy-1.8.9]# ss -antulp | grep haproxy
配置haproxy开机自启动
# chmod +x /etc/rc.d/rc.local
echo '/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.cfg' >> /etc/rc.d/rc.local
为了方便系统在开机时加载,还可以创建启动脚本:
vim /etc/rc.d/init.d/haproxy
#!/bin/sh # # haproxy # # chkconfig: - 85 15 # description: HAProxy is a free, very fast and reliable solution \ # offering high availability, load balancing, and \ # proxying for TCP and HTTP-based applications # processname: haproxy # config: /etc/haproxy/haproxy.cfg # pidfile: /var/run/haproxy.pid # Source function library. . /etc/rc.d/init.d/functions # Source networking configuration. . /etc/sysconfig/network # Check that networking is up. [ "$NETWORKING" = "no" ] && exit 0 exec="/usr/sbin/haproxy" prog=$(basename $exec) [ -e /etc/sysconfig/$prog ] && . /etc/sysconfig/$prog cfgfile=/etc/haproxy/haproxy.cfg pidfile=/var/run/haproxy.pid lockfile=/var/lock/subsys/haproxy check() { $exec -c -V -f $cfgfile $OPTIONS } start() { $exec -c -q -f $cfgfile $OPTIONS if [ $? -ne 0 ]; then echo "Errors in configuration file, check with $prog check." return 1 fi echo -n $"Starting $prog: " # start it up here, usually something like "daemon $exec" daemon $exec -D -f $cfgfile -p $pidfile $OPTIONS retval=$? echo [ $retval -eq 0 ] && touch $lockfile return $retval } stop() { echo -n $"Stopping $prog: " # stop it here, often "killproc $prog" killproc $prog retval=$? echo [ $retval -eq 0 ] && rm -f $lockfile return $retval } restart() { $exec -c -q -f $cfgfile $OPTIONS if [ $? -ne 0 ]; then echo "Errors in configuration file, check with $prog check." return 1 fi stop start } reload() { $exec -c -q -f $cfgfile $OPTIONS if [ $? -ne 0 ]; then echo "Errors in configuration file, check with $prog check." return 1 fi echo -n $"Reloading $prog: " $exec -D -f $cfgfile -p $pidfile $OPTIONS -sf $(cat $pidfile) retval=$? echo return $retval } force_reload() { restart } fdr_status() { status $prog } case "$1" in start|stop|restart|reload) $1 ;; force-reload) force_reload ;; check) check ;; status) fdr_status ;; condrestart|try-restart) [ ! -f $lockfile ] || restart ;; *) echo $"Usage: $0 {start|stop|status|restart|try-restart|reload|force-reload}" exit 2 esac
[root@vm05 src]# chmod +x /etc/rc.d/init.d/haproxy
[root@vm05 src]# chkconfig --add haproxy
登录地址:
http://192.168.0.247:8899
查看全部服务器是不是都是UP
-------测试是否是负载平衡
for i in $(seq 1 10); do mysql -uroot -pabc123 -h192.168.0.243 -e 'select @@server_id;'; done | egrep '[0-9]'
停止服务:
# killall haproxy
keepalived 安装:
安装依赖包:
yum -y install kernel kernel-devel* popt popt-devel libssl-dev libnl libnl-devel openssl openssl-* ipvsadm libnfnetlink-devel
详细安装步骤:
tar xzvf keepalived-2.0.8.tar.gz -C /usr/local/src
cd /usr/local/src/keepalived-2.0.8/
./configure --prefix=/usr/local/keepalived --sysconf=/etc
make && make install
cp /usr/local/src/keepalived-2.0.8/keepalived/etc/init.d/keepalived /etc/init.d/
cp /usr/local/src/keepalived-2.0.8/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/src/keepalived-2.0.8/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
chkconfig --add keepalived
chkconfig --level 2345 keepalived on
vi /etc/keepalived/keepalived.conf
route add 192.168.0.0 mask 255.255.255.0 10.200.22.128
keepalived主:
global_defs { notification_email { root@localhost } notification_email_from root@localhost smtp_server root smtp_connect_timeout 30 router_id NodeA } vrrp_script check_haproxy { script "/etc/keepalived/scripts/check_haproxy.sh" interval 3 #脚本执行间隔 weight 2 #执行脚本后优先级变更:5表示优先级+5;-5则表示优先级-5 } vrrp_instance VI_1 { state MASTER interface eth0 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.0.243 } track_script { check_haproxy } notify_master /etc/keepalived/scripts/state_master.sh notify_backup /etc/keepalived/scripts/state_backup.sh notify_fault /etc/keepalived/scripts/state_fault.sh }
keepalived从:
global_defs { notification_email { root@localhost } notification_email_from root@localhost smtp_server root smtp_connect_timeout 30 router_id NodeA } vrrp_script check_haproxy { script "/etc/keepalived/scripts/check_haproxy.sh" interval 3 #脚本执行间隔 weight 2 #执行脚本后优先级变更:5表示优先级+5;-5则表示优先级-5 } vrrp_instance VI_1 { state MASTER interface eth0 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.0.243 } track_script { check_haproxy } notify_master /etc/keepalived/scripts/state_master.sh notify_backup /etc/keepalived/scripts/state_backup.sh notify_fault /etc/keepalived/scripts/state_fault.sh }
vi /etc/keepalived/scripts/check_haproxy.sh
#!/bin/bash #nginx="/usr/local/haproxy/sbin/haproxy" PID=`ps -C haproxy --no-heading|wc -l` if [ "${PID}" = "0" ]; then /etc/init.d/haproxy start sleep 1 LOCK=`ps -C haproxy --no-heading|wc -l` if [ "${LOCK}" = "0" ]; then /etc/init.d/keepalived stop fi fi
vi /etc/keepalived/scripts/state_master.sh
#!/bin/bash echo -e >> $LOGFILE host=haproxy01 #设置当前的主机名 LOGFILE="/var/log/keepalived-state.log" echo "[Master]" >> $LOGFILE date >> $LOGFILE echo "The ${host} Starting to become master server...." >> $LOGFILE 2>&1 echo "Please run the “ipvsadm -Ln” check the keepalived state ..." >> $LOGFILE echo ".........................................................................!">> $LOGFILE echo >>$LOGFILE
vi /etc/keepalived/scripts/state_backup.sh
#!/bin/bash echo -e >> $LOGFILE host=haproxy02 #设置当前的主机名 LOGFILE="/var/log/keepalived-state.log" echo "[Backup]" >> $LOGFILE date >> $LOGFILE echo "The ${host} Starting to become Backup server...." >> $LOGFILE 2>&1 echo "Please run the “ipvsadm -Ln” check the state ..." >> $LOGFILE echo "........................................................................!">> $LOGFILE echo >> $LOGFILE
vi /etc/keepalived/scripts/state_fault.sh
#!/bin/bash echo -e >> $LOGFILE host=haproxy01 #设置当前的主机名 LOGFILE="/var/log/keepalived-state.log" echo "[fault errot ]" >> $LOGFILE date >> $LOGFILE echo "The ${host} is fault error...." >> $LOGFILE 2>&1 echo "Please check the server state ..." >> $LOGFILE echo "........................................................................!">> $LOGFILE echo >> $LOGFILE
参考来源:
https://blog.csdn.net/bbwangj/article/details/82969078
https://blog.csdn.net/wzb56_earl/article/details/49048787?utm_source=blogxgwz42
https://blog.csdn.net/weixin_41004350/article/details/78493035?utm_source=blogxgwz30
https://blog.csdn.net/qq_36276335/article/details/69942101?utm_source=blogxgwz20
https://blog.csdn.net/bbwangj/article/details/80346179?utm_source=blogxgwz1