Mysql + LVS + KeepAlived 主主同步

最新推荐文章于 2021-10-15 20:34:18 发布
最新推荐文章于 2021-10-15 20:34:18 发布
阅读量578 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/joinplay/article/details/47021549
版权

1、IP地址规划

     msyql-1   192.168.1.219     vip 192.168.1.230

     mysql-2    192.168.1.220    vip 192.168.1.230

2、安装mysql

3、配置mysql的主主同步

     建立mysql同步账户

     # mysql -uroot -pmysqlpassword

     mysql> GRANT ALL privileges ON *.* TO syncsql@'192.168.1.220' IDENTIFIED BY '123456';        mysql-1上执行

     mysql> GRANT ALL privileges ON *.* TO syncsql@'192.168.1.219' IDENTIFIED BY '123456';        mysql-2上执行

     配置my.cnf

     # vi /etc/my.cnf 

     log-bin=mysql-bin

    server-id       = 2   mysql-2上更改，不能和mysql-1重复

    在mysql-2同步mysql-1

    mysql> show master status;       在mysql-1上执行
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000013 |      106|              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

      

mysql> change master to        在mysql-2上执行
    -> master_host='192.168.1.219',
    -> master_user='syncsql',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000013',
    -> master_log_pos=106;

mysql> start slave;  

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.219
                  Master_User: syncsql
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000147
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000013
             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: 106
              Relay_Log_Space: 552
              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: 
1 row in set (0.00 sec)


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


同样在mysql-1上执行同步

mysql> change master to        在mysql-2上执行
    -> master_host='192.168.1.219',
    -> master_user='syncsql',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000006',
    -> master_log_pos=106;

mysql> start slave;  

mysql> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.220
                  Master_User: syncsql
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000160
                Relay_Log_Pos: 251
        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: 106
              Relay_Log_Space: 552
              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: 
1 row in set (0.00 sec)

可以分别在mysql-1和msyql-2上添加一些数据做测试主主是否同步

mysql> grant all privileges on *.* to 'root'@'%' identified by 'mysqlpassword' with grant option;   开启root的远程登录，不然vip地址无法用客户端连接

mysql> flush privileges;

4、安装lvs          mysql-1、mysql-2都需要安装

# yum install kernel-devel

# ln -s /usr/src/kernels/2.6.32-504.23.4.el6.x86_64/ /usr/src/linux

# yum install libnl* libpopt* popt-static

# wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.26.tar.gz

# tar zxvf ipvsadm-1.26.tar.gz

# cd ipvsadm-1.26

# make

# make install

5、安装keepalived   mysql-1、mysql-2都需要安装

# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

# tar zxvf keepalived-1.2.19.tar.gz

# cd keepalived-1.2.19

# ./configure --prefix=/usr/local/keepalived

# make

# make install

# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/

# mkdir /etc/keepalived


6、配置keepalived

# vim /etc/keepalived/keepalived.conf               mysql-1配置

! Configuration File for keepalived


global_defs {
   notification_email {
                 keepalive@163.com
   }
   notification_email_from  keepalived@mysql-1.com
   smtp_server localhost
   smtp_connect_timeout 30
   router_id LVS1
}


vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 180
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.230
    }
}


virtual_server 192.168.1.230 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 20
protocol TCP


    sorry_server 192.168.1.220 3306


    real_server 192.168.1.219 3306 {
        weight 3
        TCP_CHECK {
           connect_timeout 3
           nb_get_retry 3
           delay_before_retry 3
           connect_port 3306
                  }
       }
}



# vim /etc/keepalived/keepalived.conf    mysql-2  keepalived.conf配置

global_defs {
   notification_email {
   keepalived@163.com
   }
   notification_email_from root@localhost.com
   smtp_server  localhost
   smtp_connect_timeout 30
   router_id LVS1
}


vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.1.230
    }
}


virtual_server 192.168.1.230 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 20
    protocol TCP


    sorry_server 192.168.1.220 3306


    real_server 192.168.1.219 3306 {
        weight 3
        TCP_CHECK {
        connect_timeout 3
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306


        }
    }
}


8、配置realserver     mysql-1 mysql-2都做

# vim /etc/rc.d/init.d/realserver.sh 


#!/bin/bash
# description: Config realserver lo and apply noarp


SNS_VIP=192.168.1.230
/etc/rc.d/init.d/functions
case "$1" in


start)
       ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP
       /sbin/route add -host $SNS_VIP dev lo:0
       echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
       echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
       echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
       echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
       sysctl -p >/dev/null 2>&1
       echo "RealServer Start OK"
       ;;


stop)
       ifconfig lo:0 down
       route del $SNS_VIP >/dev/null 2>&1
       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
       echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
       echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
       echo "RealServer Stoped"
       ;;
       *)
       echo "Usage: $0 {start|stop}"
       exit 1
       esac

# chmod 755 /etc/rc.d/init.d/realserver.sh 
# chmod 755 /etc/rc.d/init.d/functions 

9、启动keepalived  和realserver        mysql-1  和mysql-2都做

 # /etc/init.d/keepalived start

# /etc/init.d/realserver.sh start

# chkconfig --add keepalived
# chkconfig keepalived on

# echo "/etc/init.d/realserver.sh start" >>/etc/rc.d/rc.local 

10、验证

# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.230:3306 rr persistent 20
  -> 192.168.1.219:3306           Local   3      2          0      


停掉mysql-1上的mysql

# service mysqld stop

# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.1.230:3306 rr persistent 20
  -> 192.168.1.220:3306           Route   1      1          0        

# tail -f /var/log/messages     mysql-1日志
Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: TCP connection to [192.168.1.219]:3306 failed !!!
Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Removing service [192.168.1.219]:3306 from VS [192.168.1.230]:3306
Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Lost quorum 1-0=1 > 0 for VS [192.168.1.230]:3306
Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Adding sorry server [192.168.1.220]:3306 to VS [192.168.1.230]:3306
Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Removing alive servers from the pool for VS [192.168.1.230]:3306
Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Remote SMTP server [0.0.0.0]:25 connected.
Jul 27 09:48:40 scentos Keepalived_healthcheckers[2472]: SMTP alert successfully sent.

# tail -f /var/log/messages     mysql-2日志

Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: TCP connection to [192.168.1.219]:3306 failed !!!
Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Removing service [192.168.1.219]:3306 from VS [192.168.1.230]:3306
Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Lost quorum 1-0=1 > 0 for VS [192.168.1.230]:3306
Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Adding sorry server [192.168.1.220]:3306 to VS [192.168.1.230]:3306
Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Removing alive servers from the pool for VS [192.168.1.230]:3306
Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Remote SMTP server [0.0.0.0]:25 connected.
Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: SMTP alert successfully sent.


# mysql -uroot -p -h 192.168.1.230   连接vip230看是否可以操作数据库









运维圈子
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
lvs实现mysql主从切换_Lvs+keepalived+mysql主从热备
weixin_39649736的博客
02-10 358
Lvs+keepalived+mysql主从热备实验拓扑图：一、安装LVS+keepalived(Master+Backup都需安装)需要软件ipvsadm-1.26.tar.gzkeepalived-1.2.13.tar.gz裸机需要安装如下插件popt-static-1.13-7.el6.x86_64.rpmyum -y installlibnl* popt* kernel* openssl*...
通过keepalived实现LVS主备
GLepoch的博客
01-14 380
通过keepalived实现LVS主备 前置要求： 按照《单机LVS实现请求分发（负载）DR模式》配置各台服务器。 安装keepalived 在node01和node02上面分别安装keepalived yum install keepalived ipvsadm -y 配置主LVS vim /etc/keepalived/keepalived.conf 删掉里面的内容并且写入下面配置： ! Configuration File for keepalived global_defs { not
mysql lvs 主主_MySQL 主主复制 + LVS + Keepalived 实现 MySQL 高可用性
weixin_34552424的博客
01-19 80
默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件，将需要的配置文件拷贝到指定位置# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/# c...
keepalived+lvs实现主备（全）
MayMatrix 的博客
10-15 644
参考文章：lvs+nginx负载均衡 1 lvs高可用(了解) 1.1 什么是高可用     lvs作为负载均衡器，所有请求都先到达lvs，可见lvs处于非常重要的位置，如果lvs服务器宕机后端web服务将无法提供服务，影响严重。     为了屏蔽负载均衡服务器的宕机，需要建立一个备份机。主服务器和备份机上都运行高可用（High Availability）监控程序，通过传送诸如“I am alive”...
lvs mysql_MySQL 主主复制 + LVS + Keepalived 实现 MySQL 高可用性
weixin_35802571的博客
01-18 201
原标题：MySQL 主主复制 + LVS + Keepalived 实现 MySQL 高可用性(点击上方公众号，可快速关注)作者：bestvivibestvivi.com/2015/09/09/MySQL主主复制+LVS+Keepalived实现MySQL高可用性/能够保证数据的冗余的同时可以做读写分离来分担系统压力，如果是主主复制还可以很好的避免主节点的单点故障。但是MySQL主主复制存在一些问...
mysql lvs+keepalived+mha_Keepalived+Lvs+Mysql主主复制
weixin_42501077的博客
02-07 212
一简单介绍Keepalived+lvs+mysql主主复制是比較经常使用的一种Mysql高可用方案，当中lvs 提供读负载均衡，Keepalived通过虚拟vip漂移实现故障自己主动转移，而Mysql尽管配置成主主复制。可是一般都是採用单点写入来保证数据一致性。整个方案实现很easy，仅需两台server就可以，安装配置也相对简单，但须要自己写脚本实现keepalived服务的开启和关闭，适用于仅...
LVS+Keepalived+MySQL半同步主主复制高可用方案.docx
07-15
【LVS+Keepalived+MySQL半同步主主复制高可用方案】 1. 方案概述 LVS（Linux Virtual Server）结合Keepalived构建的高可用解决方案，通常用于实现负载均衡和故障转移，以提高系统的整体可用性。在这个方案中，MySQL...
MySQL+lvs+keepalived+mha架构配置实战演练
05-18
MySQL+lvs+keepalived+mha架构是一种高可用性、负载均衡的数据库集群解决方案，用于构建强大且可靠的数据库服务。这种架构充分利用了各组件的优势，确保数据的安全性和系统的稳定性。 MySQL是世界上最流行的开源...
mysql+lvs+keepalived复制负载配置.pdf
10-27
在构建高可用性和负载均衡的数据库系统中，MySQL、LVS（Linux Virtual Server）和Keepalived的结合使用是一个常见的解决方案。这个配置主要用于确保数据服务的连续性和性能优化。以下是对这个配置的详细解析： **...
mysql+lvs+keepalived复制负载配置参考.pdf
11-04
MySQL-master1（192.168.1.4）和MySQL-master2（192.168.1.9）是两个主数据库服务器，它们接收并处理来自LVS的请求，数据同步确保了两个主节点的数据一致性。 LVS_server master（192.168.1.5）和LVS_server backup...
Keepalived+LVS+MySQL主从复制实现读写分离及高可用
wzy0623的专栏
07-14 4991
目录 一、架构设计 二、安装配置 1. 配置MySQL半同步复制 1.1半同步复制的基本概念 1.2半同步复制的潜在问题 1.3 半同步复制的安装部署 2. 下载安装LVS 3. 下载安装Keepalived 4. iptables配置 5. Keepalived配置 5. 编写RealServer的网络配置脚本 6. 启动RealServer和Keepalived 三、...
Keepalived+LVS+MySQL双主复制实现读写负载均衡及高可用
wzy0623的专栏
07-12 6890
目录 一、Keepalived和LVS简介 1. Keepalived简介 2. LVS简介 二、安装配置 1. 下载安装LVS 2. 下载安装Keepalived 3. Keepalived配置 5. 编写RealServer的网络配置脚本 三、测试 四、总结 参考：         上一篇我们使用Keepalived的HA功能，实现MySQL主从复制的自动故障切换。它的...
centos lvs+keepalived+mysql实现mysql数据库热备主主复制
爱代码也爱生活
07-15 5364
实验环境： linux：centos6.6 虚拟ip(vip)：192.168.135.199 mysql master：192.168.20.193 mysql slave：192.168.20.195 （这里只是为了后文方便区分，一个叫master，一个叫slave，其实在主主复制中，都是master也都是slave，没有主从之分） mysql的版本: [root@localho
财务报表，项目费用支出明细.xlsx
08-09
工资表，财务报表，对账表，付款申请，财务报告，费用支出表 适用人群：学习不同技术领域的小白或进阶学习者；可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。
ppap-第四版.pdf
08-09
ppap-第四版
铁路转辙机 ZD6 使用说明书
最新发布
08-09
铁路转辙机 ZD6 使用说明书
黑马商城docker镜像elasticsearch资源
08-09
黑马商城docker镜像elasticsearch资源
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值