美文网首页
Keepalived+MySQL高可用 双主热备

Keepalived+MySQL高可用 双主热备

作者: Sachin猿人 | 来源:发表于2018-10-30 16:25 被阅读0次

0.背景

结合系统业务、数据量大小和数据的重要性,目前常用的MySQL高可用方案有Keepalived+双主、MHA、MMM、PXC、MySQL Cluster和HeartBeat+DRBD,生产环境中常用的有Keepalived+双主、MHA和PXC,本文章的内容主要是搭建Keepalived+双主的环境。正常环境下写操作使用VIP连接数据库来做写操作,使用master2的地址来做读操作。若有其中的一个主机有异常,都切换还在vip上面。环境正常后再切换回来。
本章环境使用两个服务器和三个IP搭建而成,vip是绑定到master1和master2的网卡上的虚拟网卡。

keepalived.png

1.环境准备

角色                  主机ip                  主机名           操作系统版本          安装软件及版本     
VIP                 192.168.40.129         虚拟地址                     
master1             192.168.40.127         xwmaster1        CentOS 7.5          mysql5.7.21+keepalived2.0.8         
master2             192.168.40.128         xwmaster2        CentOS 7.5          mysql5.7.21+keepalived2.0.8 

2.MySQL服务安装

从 CentOS 7 系统开始,MariaDB 成为 yum 源中默认的数据库安装包。在 CentOS 7 及以上的系统中使用 yum 安装MySQL包将无法使用,在安装之前先卸载MariaDB,查看mariadb的安装包情况并卸载

yum list installed | grep mariadb
yum -y remove mariadb*

下载mysql的YUM源

cd /usr/local/src
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

安装mysql的yum源

rpm -ivh mysql57-community-release-el7-11.noarch.rpm

安装mysql-server

yum install -y mysql-server

启动mysql服务

server mysqld start

mysql获取mysql初始密码

cat /var/log/mysqld.log|grep 'A temporary password'

修改mysql密码由于mysql5.7安全性要求密码由小写字母大写字母数字和字符组成例如Password@123

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Password@123';

3.MySQL配置

在配置mysql之前要设置防火墙允许3306端口通过。

systemctl stop firewalld.service        #关闭firewalld防火墙
systemctl disable firewalld.service     #从开机启动中移除
yum install iptables-services -y        #iptables-services安装
vi /etc/sysconfig/iptables              #操作防火墙iptables文件  

-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT  #加入到防火墙

systemctl restart iptables.service      #重启防火墙
systemctl enable iptables.service       #加入开机启动

1.master1的配置操作/etc/my.cnf

[mysqld]
# 数据存储文件指定
datadir=/data/mysql
# sock文件路径
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#错误日志文件路径
log-error=/var/log/mysqld.log
#进程文件路径
pid-file=/var/run/mysqld/mysqld.pid

# mysql服务id
server-id=1

#二进制日志 存储数据发生改变的sql语句
log-bin=mysql-bin

#增量值得起点
auto-increment-offset=1
#每次增量间隔
auto-increment-increment=2

#设置需要同步的数据库名称
#binlog_do_db=<dbname>

2.数据同步授权

数据同步授权(iptables防火墙开启3306端口)这样I/O线程就可以以这个用户的身份连接到主服务器,并且读取它的二进制日志。
mysql> grant replication slave,replication client on *.* to root@'192.168.40.%' identified by "Password@123";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

最好将库锁住,仅仅允许读,以保证数据一致性;待主主同步环境部署后再解锁;
锁住后,就不能往表里写数据,但是重启mysql服务后就会自动解锁!
注意该参数设置后,如果自己同步对方数据,同步前一定要记得先解锁!
mysql> flush tables with read lock; 
Query OK, 0 rows affected (0.01 sec)

查看下log bin日志和pos值位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1106 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.master2的配置操作/etc/my.cnf

[mysqld]

datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
log-bin=mysql-bin
auto-increment-offset=2
auto-increment-increment=2

#设置需要同步的数据库名称
#binlog_do_db=<dbname>

4.数据同步授权

mysql> grant replication slave,replication client on *.* to root@'192.168.40.%' identified by "Password@123";
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> flush tables with read lock; 
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      630 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.master1做同步操作

先解锁步骤2中对master1的表解锁,为保持数据的一致性。
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

先去stop slave 以下报错是slave未开启
mysql> slave stop;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave stop' at line 1

mysql> change  master to master_host='192.168.40.128',master_user='root',master_password='Password@123',master_log_file='mysql-bin.000006',master_log_pos=630;
Query OK, 0 rows affected, 2 warnings (0.20 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.40.128
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 630
               Relay_Log_File: xwmaster1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 630

6.master2做同步操作

先解锁步骤4中对master2的表的锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> slave stop;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave stop' at line 1
mysql> change  master to master_host='192.168.40.127',master_user='root',master_password='Password@123',master_log_file='mysql-bin.000003',master_log_pos=1106;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>  start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.40.127
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1106
               Relay_Log_File: xwmaster2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 1106

7.master1和master2两台服务器都要授权允许root用户远程登录,用于在客户端登陆测试!

mysql> grant all on *.* to root@'192.168.40.%' identified by "Password@123";
Query OK, 0 rows affected (0.00 sec)
     
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

4.Keepalived的安装配置

本章的keepalived 只配置一个MASTER和一个BACKUP,可以配置多个BACKUP,若MASTER挂掉,会从BACKUP中选举出一个新的MASTER,旧的MASTER恢复,系统又恢复如初。

1.Keepalived编译安装过程

#安装包下载
cd /usr/local/src
wget http://www.keepalived.org/software/keepalived-2.0.8.tar.gz         
tar zxvf keepalived-2.0.8.tar.gz
cd keepalived-2.0.8/
yum install -y openssl-devel libnl-devel libnl3-devel libnfnetlink-devel
yum groupinstall "Development Tools" 
./configure --prefix=/usr/local/keepalived  

2.将编译好的keepalived打包复制到master1和master2

tar zcvf keepalived.tar keepalived
cp keepalived/bin/* /bin/
cp keepalived/sbin/* /sbin/
cp keepalived/etc/keepalived/ /etc/ -rf
cp keepalived/etc/sysconfig/* /etc/sysconfig/

3.修改keepalived的配置文件
vim /etc/keepalived/keepalived.conf master1与master底keepalived的基本配置一样,除了state 和 priority,在配置文件中有说明。

! Configuration File for keepalived 
global_defs {
    notification_email {                #关于邮件的设置
        sachin_0906@183.com             #email send to
    }

    notification_email_from notice@keepalived.cn
        smtp_server 127.0.0.1 
        smtp_connect_timeout 30
        router_id MASTER-HA
}

vrrp_script chk_mysql_port {            #检测mysql服务是否在运行。有很多方式,比如进程,用脚本检测等等
    script "/opt/chk_mysql.sh"          #这里通过脚本监测
    interval 2                          #脚本执行间隔,每2s检测一次
    weight -5                           #脚本结果导致的优先级变更,检测失败(脚本返回非0)则优先级 -5
    fall 2                              #检测连续2次失败才算确定是真失败。会用weight减少优先级(1-255之间)
    rise 1                              #检测1次成功就算成功。但不修改优先级
}

vrrp_instance VI_1 {
    state MASTER                        #master1 设置为MASTER
    interface eno1                      #指定虚拟ip的网卡接口
    mcast_src_ip 192.168.40.127         #绑定的地址
    virtual_router_id 51                #路由器标识,MASTER和BACKUP必须是一致的
    priority 101                        #定义优先级,数字越大,优先级越高,在同一个vrrp_instance下,MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后,就可以将VIP资源再次抢回来 
    advert_int 1         
    authentication {                    #认证类型PASS|AH(IPSEC)
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {                 #虚拟IP的设置即vip
        192.168.40.129
    }

    track_script {                      #监控脚本
        chk_mysql_port             
    }
}

4.监控脚本 /opt/chk_mysql.sh 主要监控3306端口是否被监听,若不被监听关闭keepalived服务

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    service keepalived stop
fi

5.设置keepalived为服务并加入开机自动动

vim /lib/systemd/system/keepalived.service 生成keepalived.service文件

[Unit]
Description=Keepalived
After=syslog.target network.target remote-fs.target nss-lookup.target
 
[Service]
Type=forking
PIDFile=/var/run/keepalived.pid
ExecStart=/sbin/keepalived -D
ExecReload=/bin/kill -s HUP $MAINPID
ExecStop=/bin/kill -s QUIT $MAINPID
PrivateTmp=true
 
[Install]
WantedBy=multi-user.target  

启动查看服务

service keepalived start
service keepalived status
#systemctl enable keepalived.service

6.将keepalived应用加入防火墙允许
追加/etc/sysconfig/iptables

-A INPUT -s 192.168.40.0/24 -d 224.0.0.18 -j ACCEPT
-A INPUT -s 192.168.40.0/24 -p vrrp -j ACCEPT

重启iptables服务

service iptables restart

7.开机启动mysqld和keepalived服务脚本

取消开机自启动服务mysqld 和keepalived,原因是系统开机自启动mysqld和keepalived的先后顺序不能确定,所以使用rc.local中的sh脚本启动服务。
a.取消开启自启服务:

systemctl disable mysqld.service    
systemctl disable keepalived.service

b.创建sh脚本加入如下内容,vim /opt/start_services.sh

#!/bin/bash
service mysqld start 
while true
do
    counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
    if [ "${counter}" -ne 0 ]; then
        service keepalived start
        exit 0
    fi

done

c./etc/rc.d/rc.local 文件末尾追加sh /opt/start_services.sh,并修改 /opt/start_services.sh/etc/rc.d/rc.local的执行权限

chmod +x /opt/start_services.sh
chmod +x /etc/rc.d/rc.local

5.测试keepalived+双主mysql的可用性

1.用过虚拟IP连接数据库(实际是连接优先级高master1),创建数据库、创建表和添加数据,在master上查看数据是否同步成功。
2.关闭master1 keepalived或者mysqld服务,通过虚拟IP是否仍然可以访问数据库。
3.添加一条数据,自增id是不是按偶数方式增长(是说明一切换到master2上面)
4.恢复master1的keepalived服务,添加一条数据自增ID是不是奇数方式增长(是说明已经切换为master1)

作者:sachin猿人原创

相关文章

  • Keepalived+MySQL高可用 双主热备

    0.背景 结合系统业务、数据量大小和数据的重要性,目前常用的MySQL高可用方案有Keepalived+双主、MH...

  • 高可用集群LVS+Keepalived

    这一部分主要是针对高可用,为了解决nginx的高可用,我们结合了keepalived来配置双机主备或者双主热备,要...

  • 双机热备,主备&双主

    双机热备特指基于高可用系统中的两台服务器的热备(或高可用),因两机高可用在国内使用较多,故得名双机热备,双机高可用...

  • MySQL 高可用性keepalived+mysql双主

    防伪码:明日复明日,明日何其多。 生产环境中一台mysql主机存在单点故障,所以我们要确保mysql的高可用性,即...

  • MySQL-18.高可用的基础-主备切换逻辑

    MySQL 要提供高可用能力,只有最终一致性是不够的。 双 M 结构的主备切换流程图: 1.主备延迟 主备切换可能...

  • keepalived+mysql主主同步

    keepalived+mysql主主同步是一种适合中小企业的高可用架构,相关资料已经很全了。本文是对一些细节的补充...

  • 九、宕机恢复原理

    1、Master负载并不很高,基本采用热备的方式来实现Master高可用 2、RegionServer宕机的恢复主...

  • kerberos:kerberos高可用安装

    kerberos高可用介绍 1.kerberos主备,主down掉,自动切备2.主备数据同步需自行编写脚本 ker...

  • 综合架构之keepalived(高可用)

    内容涉及 高可用原理高可用配置文件高可用服务常见问题实现高可用服务双主功能 keepalivd----------...

  • Redis--Sentinel

    Redis--Sentinel sentinel的目的: 1.为了实现高可用,即帮助主备切换。 切换主备的逻辑 启...

网友评论

      本文标题:Keepalived+MySQL高可用 双主热备

      本文链接:https://www.haomeiwen.com/subject/wjwgtqtx.html