Mysql+Keepalived双主互备高可用详细配置

Mysql+Keepalived双主互备架构搭架

配置前进行校时操作

  1. #安装ntpdate工具
  2. yum install ntpdate -y
  3. #使用ntpdate校时(后面的是ntp服务器)
  4. ntpdate pool.ntp.org

一 配置mysql双主备
  0.测试mysql安装
  1. yum install mariadb mariadb-server -y
  2. systemctl restart mariadb
  3. systemctl enable mariadb
  4. mysql_secure_installation
  1.DB1修改配置文件(需重启)
  1. vim /etc/my.cnf
  2. #在[mysqld]添加
  3. server-id=166
  4. #开启mysql日志功能
  5. log-bin=mysql-bin
  6. #定义日志命名格式
  7. relay-log=mysql-relay-bin
  8. #以下table复制过滤
  9. #replicate-wild-ignore-table=test.%
  10. #replicate-wild-ignore-table=mysql.%
  11. #replicate-wild-ignore-table=performance_schema.%
   2.DB2修改配置文件(需重启)
  1. vim /etc/my.cnf
  2. #在[mysqld]添加
  3. server-id=168
  4. #开启mysql日志功能
  5. log-bin=mysql-bin
  6. #定义日志命名格式
  7. relay-log=mysql-relay-bin
   3.DB1,DB2分别创建复制帐号
  1. mysql -u root -p
  2. #创建用户slave_up允许从192.168.1网段登录
  3. create user 'slave_cp'@'192.168.1.%' identified by 'pass';
  4. grant replication slave on *.* to 'slave_cp'@'192.168.1.%';
  5. exit
   4.DB1获取二进制日志信息
  1. mysql -u root -p
  2. #对数据库进行只读锁定(防止查看二进制日志同时有人对数据库修改操作)
  3. flush tables with read lock;
  4. #查询主机二进制文件信息
  5. show master status;
  6. +------------------+----------+--------------+------------------+
  7. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  8. +------------------+----------+--------------+------------------+
  9. | mysql-bin.000001 | 494 | | |
  10. +------------------+----------+--------------+------------------+
  11. 1 row in set (0.00 sec)
  12. #解除只读锁定
  13. unlock tables;
  14. exit
   5.同步数据库
  1. #DB1
  2. #备份主数据库,并上传给从数据库
  3. mysqldump -uroot -p123456 --all-databases --lock-all-tables >/tmp/dbdump.sql
  4. scp /tmp/dbdump.sql 192.168.1.168:/tmp/
  5. #DB2
  6. #从服务器导入主服务器数据库
  7. mysql -uroot -p </tmp/dbdump.sql
    6.在DB2上将DB1设置为主服务器
  1. #开启mysql防火墙端口
  2. firewall-cmd --add-port=3306/tcp --permanent
  3. firewall-cmd --reload
  1. change master to
  2. master_host='192.168.1.168' ,
  3. master_user='slave_cp',
  4. master_password='pass',
  5. master_log_file='mysql-bin.000001',
  6. master_log_pos=494;
  7. #启动slave
  8. start slave;
  9. #查看是否正常工作
  10. show slave status\G
  11. exit
    7.在DB2获取二进制日志信息
  1. mysql -u root -p
  2. #对数据库进行只读锁定(防止查看二进制日志同时有人对数据库修改操作)
  3. flush tables with read lock;
  4. #查询主机二进制文件信息
  5. show master status;
  6. +------------------+----------+--------------+------------------+
  7. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  8. +------------------+----------+--------------+------------------+
  9. | mysql-bin.000001 | 513721| | |
  10. +------------------+----------+--------------+------------------+
  11. 1 row in set (0.00 sec)
  12. #解除只读锁定
  13. unlock tables;
  14. exit

    8.在DB1将DB2设置为主服务器
  1. change master to
  2. master_host='192.168.1.168' ,
  3. master_user='slave_cp', 
  4. master_password='pass',
  5. master_log_file='mysql-bin.000001', 
  6. master_log_pos=513721;
  7. #启动slave
  8. start slave;
  9. #查看是否正常工作
  10. show slave status\G
  11. exit
二 Keepalived配置(/etc/keepalived/keepalived.conf)(yum install keepalived -y)(注意:关闭selinux策略 setenforce 0
   1.keepalived配置文件配置
  1. ! Configuration File for keepalived
  2. global_defs {
  3. #设置报警通知邮件地址,可以设置多个
  4. notification_email {
  5. msun1996@163.com
  6. }
  7. #设置邮件的发送地址
  8. notification_email_from keepalived@msun.com
  9. #设置smtp server的地址,该地址必须是存在的
  10. smtp_server 127.0.0.1
  11. #设置连接smtp server的超时时间
  12. smtp_connect_timeout 30
  13. #运行Keepalived服务器的标识,发邮件时显示在邮件标题中的信息
  14. router_id mysql_msun
  15. }
  16. # 检测haproxy脚本
  17. vrrp_script chk_mysql {
  18. script "/etc/keepalived/mysqlcheck/check_slave.sh"
  19. interval 2
  20. weight 2
  21. }
  22. #定义VRRP实例,实例名自定义
  23. vrrp_instance mysql_msun {
  24. #指定Keepalived的角色,MASTER主机 BACKUP备份
  25. state BACKUP #此处两个都设置为BACKUP
  26. #指定HA监测的接口
  27. interface eno16777736
  28. #虚拟路由标识,这个标识是一个数字(1-255),在一个VRRP实例中主备服务器ID必须一样
  29. virtual_router_id 68
  30. #优先级,数字越大优先级越高,在一个实例中主服务器优先级要高于备服务器
  31. priority 100 #从服务器99
  32. #设置主备之间同步检查的时间间隔单位秒
  33. advert_int 1
  34. #设置不抢占模式(DB1设置即可)
  35. nopreempt
  36. #设置验证类型和密码
  37. authentication {
  38. #验证类型有两种{PASS|HA}
  39. auth_type PASS
  40. #设置验证密码,在一个实例中主备密码保持一样
  41. auth_pass 1689
  42. }
  43. track_script {
  44. chk_mysql # 执行监控的服务
  45. }
  46. #定义虚拟IP地址,可以有多个,每行一个
  47. virtual_ipaddress {
  48. 192.168.1.160
  49. }
  50. }

   2.创建监控脚本(/etc/keepalived/mysqlcheck/check_slave.sh)
  1. #!/bin/bash
  2. #This scripts is check for Mysql Slave status
  3. Mysqlbin=/usr/bin/mysql
  4. user=root
  5. pw='123456'
  6. port=3306
  7. host=127.0.0.1
  8. #最大延时
  9. sbm=120
  10. #Check for $Mysqlbin
  11. if [ ! -f $Mysqlbin ];then
  12. echo 'Mysqlbin not found,check the variable Mysqlbin'
  13. exit 99
  14. fi
  15. #Get Mysql Slave Status
  16. IOThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_IO_Running:'|awk '{print $NF}'`
  17. SQLThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk '{print $NF}'`
  18. SBM=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master:'|awk '{print $NF}'`
  19. #Check if the mysql run
  20. if [[ -z "$IOThread" ]];then
  21. exit 1
  22. fi
  23. #Check if the thread run
  24. if [[ "$IOThread" == "No" || "$SQLThread" == "No" ]];then
  25. exit 1
  26. elif [[ $SBM -ge $sbm ]];then
  27. exit 1
  28. else
  29. exit 0
  30. fi
    3.防火墙策略
  1. #运行VPPR组播(keepalived)
  2. firewall-cmd --permanent --add-rich='rule family="ipv4" destination address="224.0.0.18" protocol value="vrrp" accept'
  3. firewall-cmd --reload

   4.测试
       1.正常启动DB1、DB2信息
  1. [root@DB1 ~]# ip a
  2. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
  3. link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  4. inet 127.0.0.1/8 scope host lo
  5. valid_lft forever preferred_lft forever
  6. inet6 ::1/128 scope host
  7. valid_lft forever preferred_lft forever
  8. 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
  9. link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
  10. inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
  11. valid_lft forever preferred_lft forever
  12. inet 192.168.1.160/32 scope global eno16777736
  13. valid_lft forever preferred_lft forever
  14. inet6 fe80::20c:29ff:fe43:3013/64 scope link
  15. valid_lft forever preferred_lft forever
  1. [root@DB2 ~]# ip a
  2. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
  3. link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  4. inet 127.0.0.1/8 scope host lo
  5. valid_lft forever preferred_lft forever
  6. inet6 ::1/128 scope host
  7. valid_lft forever preferred_lft forever
  8. 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
  9. link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
  10. inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
  11. valid_lft forever preferred_lft forever
  12. inet6 fe80::20c:29ff:feda:7d9d/64 scope link
  13. valid_lft forever preferred_lft forever
       2.关闭DB1数据库,观察IP
  1. [root@DB1 ~]# systemctl stop mariadb
  2. [root@DB1 ~]# ip a
  3. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
  4. link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  5. inet 127.0.0.1/8 scope host lo
  6. valid_lft forever preferred_lft forever
  7. inet6 ::1/128 scope host
  8. valid_lft forever preferred_lft forever
  9. 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
  10. link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
  11. inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
  12. valid_lft forever preferred_lft forever
  13. inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic
  14. valid_lft 2591817sec preferred_lft 604617sec
  15. inet6 fe80::20c:29ff:fe43:3013/64 scope link
  16. valid_lft forever preferred_lft forever
  1. [root@DB2 ~]# ip a
  2. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
  3. link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  4. inet 127.0.0.1/8 scope host lo
  5. valid_lft forever preferred_lft forever
  6. inet6 ::1/128 scope host
  7. valid_lft forever preferred_lft forever
  8. 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
  9. link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
  10. inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
  11. valid_lft forever preferred_lft forever
  12. inet 192.168.1.160/32 scope global eno16777736
  13. valid_lft forever preferred_lft forever
  14. inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic
  15. valid_lft 2591669sec preferred_lft 604469sec
  16. inet6 fe80::20c:29ff:feda:7d9d/64 scope link
  17. valid_lft forever preferred_lft forever
       3.重启主机mariadb,观察IP
  1. [root@DB1 ~]# ip a #DB1不切换成主机(因为设置为不抢占模式)
  2. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
  3. link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  4. inet 127.0.0.1/8 scope host lo
  5. valid_lft forever preferred_lft forever
  6. inet6 ::1/128 scope host
  7. valid_lft forever preferred_lft forever
  8. 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
  9. link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
  10. inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
  11. valid_lft forever preferred_lft forever
  12. inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic
  13. valid_lft 2591605sec preferred_lft 604405sec
  14. inet6 fe80::20c:29ff:fe43:3013/64 scope link
  15. valid_lft forever preferred_lft forever
  1. [root@DB2 ~]# ip a
  2. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
  3. link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  4. inet 127.0.0.1/8 scope host lo
  5. valid_lft forever preferred_lft forever
  6. inet6 ::1/128 scope host
  7. valid_lft forever preferred_lft forever
  8. 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
  9. link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
  10. inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
  11. valid_lft forever preferred_lft forever
  12. inet 192.168.1.160/32 scope global eno16777736
  13. valid_lft forever preferred_lft forever
  14. inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic
  15. valid_lft 2591533sec preferred_lft 604333sec
  16. inet6 fe80::20c:29ff:feda:7d9d/64 scope link
  17. valid_lft forever preferred_lft forever
       4.再次关闭DB2的maraiadb,问题出现了,预想当DB2宕机,IP未能正常漂移!!!
  1. [root@DB2 ~]# systemctl stop mariadb
  2. [root@DB2 ~]# ip a
  3. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
  4. link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  5. inet 127.0.0.1/8 scope host lo
  6. valid_lft forever preferred_lft forever
  7. inet6 ::1/128 scope host
  8. valid_lft forever preferred_lft forever
  9. 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
  10. link/ether 00:0c:29:da:7d:9d brd ff:ff:ff:ff:ff:ff
  11. inet 192.168.1.168/24 brd 192.168.1.255 scope global eno16777736
  12. valid_lft forever preferred_lft forever
  13. inet 192.168.1.160/32 scope global eno16777736
  14. valid_lft forever preferred_lft forever
  15. inet6 2001:da8:4002:3301:20c:29ff:feda:7d9d/64 scope global noprefixroute dynamic
  16. valid_lft 2591326sec preferred_lft 604126sec
  17. inet6 fe80::20c:29ff:feda:7d9d/64 scope link
  18. valid_lft forever preferred_lft forever

  1. [root@DB1 ~]# ip a
  2. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
  3. link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
  4. inet 127.0.0.1/8 scope host lo
  5. valid_lft forever preferred_lft forever
  6. inet6 ::1/128 scope host
  7. valid_lft forever preferred_lft forever
  8. 2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
  9. link/ether 00:0c:29:43:30:13 brd ff:ff:ff:ff:ff:ff
  10. inet 192.168.1.166/24 brd 192.168.1.255 scope global eno16777736
  11. valid_lft forever preferred_lft forever
  12. inet6 2001:da8:4002:3301:20c:29ff:fe43:3013/64 scope global noprefixroute dynamic
  13. valid_lft 2591241sec preferred_lft 604041sec
  14. inet6 fe80::20c:29ff:fe43:3013/64 scope link
  15. valid_lft forever preferred_lft forever
   关于这个问题的思考
   先说说本案例中采用的是Keepalived+mysql的组合,Keepalived用了双备模式,采用脚本检测应用是否正常来决定keepalived的权重,从而让正常的数据库能得到VIP而正常工作,考虑到了如果DB1宕掉(仅mysql服务)(DB1的权重会低于DB2),DB2接管服务,而DB1再次正常时(权重又会高于DB2),为了避免数据库来回切换,而在DB1的keepalived配置中设置了不抢占模式来避免此问题。但是,导致了一个新的问题,就算是DB2的数据库不正常工作,(权重更低于DB2),但因为DB1不抢占模式,DB2仍然会占有VIP,(除非是Keepalived服务宕掉)。
   关于这个问题的思考解决方案:
   1.keepalived取消使用不抢占模式,从而使主机切换正常,但这样也就使DB1的数据库服务宕掉再正常启动时,会抢占DB2的VIP,使数据库进行不必要的切换。
   2.修改检测脚本keepalived使用检测脚本正常时返回0,不正常时,直接让keepalived服务本身宕掉。这样情况下,DB1服务mysql服务再次正常(并启动keepalived),不会抢占DB2的VIP;当DB2mysql服务不正常工作时,(会自动停止keepalived),从而使DB1正常得到VIP。  
  1. #!/bin/bash
  2. #This scripts is check for Mysql Slave status
  3. Mysqlbin=/usr/bin/mysql
  4. user=root
  5. pw='123456'
  6. port=3306
  7. host=127.0.0.1
  8. #最大延时
  9. sbm=120
  10. #Check for $Mysqlbin
  11. if [ ! -f $Mysqlbin ];then
  12. echo 'Mysqlbin not found,check the variable Mysqlbin'
  13. pkill keepalived
  14. fi
  15. #Get Mysql Slave Status
  16. IOThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_IO_Running:'|awk '{print $NF}'`
  17. SQLThread=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Slave_SQL_Running:'|awk '{print $NF}'`
  18. SBM=`$Mysqlbin -h $host -P $port -u$user -p$pw -e 'show slave status\G' 2>/dev/null|grep 'Seconds_Behind_Master:'|awk '{print $NF}'`
  19. #Check if the mysql run
  20. if [[ -z "$IOThread" ]];then
  21. pkill keepalived
  22. fi
  23. #Check if the thread run
  24. if [[ "$IOThread" == "No" || "$SQLThread" == "No" ]];then
  25. pkill keepalived
  26. elif [[ $SBM -ge $sbm ]];then
  27. pkill keepalived
  28. else
  29. exit 0
  30. fi


©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值