欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MYSQL(主主)双机热备+Keepalived实现故障切换

程序员文章站 2022-04-08 22:52:08
...

MYSQL(主主)双机热备+Keepalived实现故障切换

环境介绍:

Mysql VIP :192.168.160.13

Master1:192.168.160.11

Master2:192.168.160.12

Linux 环境:Centos 6.9

Mysql版本:Mysql5.6

1. 双主配置

第一步,配置my.cnf

Master1 : 192.168.160.11
[root@redis1 ~]# vi /etc/my.cnf

修改或添加以下配置:

#主标服务标识号,必需唯一
server-id = 1

# log_bin日志名称格式:${datadir}/mysql-bin.0000*
log_bin = mysql-bin

# 日志内容记录格式(建议使用这种)
binlog_format = mixed

# 不建议适用这种配置,如果跨域操作会出现同步异常。
# binlog_do_db = task
# binlog_ignore_db = mysql

#只在从库设置replicate_wild_do_table= hadoop.%或replicate_wild_ignore_table可以避免跨库更新问题
#如果有多个库需要同步的时候,需要多配置几次以下语句,而不是直接使用逗号分隔。(错误写法:replicate-wild-do-table=task.%,test.%)
replicate-wild-do-table=task.%

log-slave-updates
#经过1日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)。n=1是最安全的做法,但效率最低。默认设置是n=0。
sync_binlog = 1

# auto_increment,控制自增列AUTO_INCREMENT的行为
# 用于MASTER-MASTER之间的复制,防止出现重复值,
# auto_increment_offset=1设置步长,这里设置为1,这样Master的auto_increment字段产生的数值是:1, 3, 5, 7, …等奇数ID
auto_increment_offset = 1

# auto_increment_increment=n有多少台服务器,n就设置为多少,
auto_increment_increment = 2

#忽略所有复制产生的错误
slave-skip-errors = all
Master2 : 192.168.160.12
[root@redis1clone ~]# vi /etc/my.cnf

修改或添加以下配置:

#主标服务标识号,必需唯一(区别于master1)
server-id = 2

# auto_increment_offset=1设置步长,这里设置为2,这样Master的auto_increment字段产生的数值是:2,4,6,8 …等偶数ID
auto_increment_offset = 2

####省略其他几项和master1相同####

分别重启mysql

[root@redis1 ~]# service mysqld restart

第二步,设置同步(change master)

.
.
1. 分别登录mysql配置远程同步权限账户:

登录master1设置192.168.160.12可远程同步权限账户:

mysql> grant replication slave on *.* to 'replicate'@'192.168.160.12' identified by '123456';  
mysql> flush privileges; 

登录master2设置192.168.160.11可远程同步权限账户:

mysql> grant replication slave on *.* to 'replicate'@'192.168.160.11' identified by '123456';  
mysql> flush privileges; 

.
.
.
2. 设置master2同步更新master1

登录master1的mysql,执行如下指令:
——查看master1最新二进制日志文件及操作事物位置

mysql> show master status;

MYSQL(主主)双机热备+Keepalived实现故障切换

确保当前数据库无其他操作,如果是在生产环境下,记得先锁表再查看

先锁表:
mysql> flush tables with read locks;

再查看:
mysql> show master status;

完成同步后记得解锁(完成change master指令后)

解锁:
mysql> unlock tables;

登录master2的mysql,执行如下指令

# 1.停止slave服务进程
# 2.执行同步指令change master to 
# 3.启动slave服务进程
# 4.查看slave服务状态

mysql>stop slave; 

mysql>change master to 
    >master_host='192.168.160.11',
    >master_user='replicate',
    >master_password='123456',
    >master_log_file='mysql-bin.000007',
    >master_log_pos=386;


mysql> start slave;  

mysql> show slave status\G

如图两项为YES则说明设置同步更新成功

MYSQL(主主)双机热备+Keepalived实现故障切换
.
.
.
3. 设置master1同步更新master2(重复步骤2)
—登录master2的mysql,执行查看指令:查看master2最新二进制日志文件及操作事物位置
—登录master1的mysql,执行同步指令change master to
.
.
.
测试:略.
.

2. keepalived安装配置

master1:

  1. 安装keepalived依赖包

    [root@redis1clone ~]# yum install -y pcre-devel openssl-devel popt-devel
  2. 下载源码及安装编译

    [root@redis1clone ~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
    [root@redis1clone ~]# tar zxvf keepalived-1.2.7.tar.gz
    [root@redis1clone ~]# cd keepalived-1.2.7
    [root@redis1 keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived
    [root@redis1 keepalived-1.2.7]# make && make install
  3. Keepalived配置

    [root@redis1clone ~]# vi /etc/keepalived/keepalived.conf
    ! Configuration File for keepalived
    
    global_defs {
       notification_email {
            512902802@qq.com
       }
       notification_email_from admin@test.com
       smtp_server 127.0.0.1
       smtp_connect_timeout 30
       router_id MYSQL_HA  #标识,双主相同
    }
    
    vrrp_instance VI_1 {
        state BACKUP                #两台都设置BACKUP
        interface eth0              #绑定虚拟IP的网络接口
        virtual_router_id 51        #VRRP组名,两个节点的设置必须一样,以指明各个节点属于同一VRRP组
        priority 100                #主节点的优先级(1-254之间),备用节点必须比主节点优先级低
        advert_int 1                #组播信息发送间隔,两个节点设置必须一样
        nopreempt                   #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置
        authentication {            #设置验证信息,两个节点必须一致
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress {         #指定虚拟IP,两个节点设置必须一样
    
            192.168.160.13
        }
    
    }
    virtual_server 192.168.160.13 3306 {    #linux虚拟服务器(LVS)配置
        delay_loop 2                #每个2秒检查一次real_server状态
        lb_algo Wrr                 #LVS调度算法,rr|wrr|lc|wlc|lblc|sh|dh
        lb_kind DR                  #LVS集群模式 ,NAT|DR|TUN 如果不关闭,备用服务器不能通过VIP连接主MySQL
        persistence_timeout 50      #会话保持时间,同一IP的连接50秒内被分配到同一台真实服务器
        protocol TCP                #使用的协议是TCP还是UDP
        real_server 192.168.160.11 3306 {   #检测本地mysql,backup也要写检测本地mysql
            weight 3                #权重
            notify_down /usr/local/keepalived/mysql.sh    #当mysql服down时,执行此脚本,杀死keepalived实现切换
            TCP_CHECK {
                    connect_timeout 3    #连接超时
                    nb_get_retry 3       #重试次数
                    delay_before_retry 3 #重试间隔时间
            }
        }
    }
    ~
    [root@redis1clone ~]# vi /usr/local/keepalived/mysql.sh
    #!/bin/bash
    pkill keepalived
    
    [root@redis1clone ~]# chmod +x /usr/local/keepalived/mysql.sh
  4. 将Keepalived加入系统服务

    [root@redis1clone ~]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
    [root@redis1clone ~]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
    [root@redis1clone ~]# mkdir /etc/keepalived/
    [root@redis1clone ~]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
    [root@redis1clone ~]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
    [root@redis1clone ~]# /etc/init.d/keepalived start

    ->master2服务器只修改priority为90、nopreempt不设置、real_server设置本地IP。
    ->两个mysql都必须要有可远程访问的账户,例如:

    ```
    mysql> grant all on *.* to'keepalived'@'192.168.160.%' identified by 'ayou123';
    mysql> flush privileges;
    ```
    

  5. Keepalived测试

    使用Mysql VIP :192.168.160.13连接

    MYSQL(主主)双机热备+Keepalived实现故障切换

    连接成功后在13上执行插入语句:

    MYSQL(主主)双机热备+Keepalived实现故障切换

参考文章:
1.MySQL高可用性之Keepalived+Mysql(双主热备)
2.高可用的Mysql双机热备(Mysql_HA)