当前位置：移动技术网 > IT编程>数据库>Mysql > lvs+keepalived+mysql (完整版)

lvs+keepalived+mysql (完整版)

2020年07月09日 | 移动技术网IT编程 | 我要评论

在这里插入图片描述 ### MySQL主主复制+LVS+Keepalived实现MySQL高可用性

架构图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1pndFFSU-1594210072321)(assets/43136-20170124163407034-588511483.png)]

1.环境

192.168.183.100 VIP
192.168.183.188 txc-lvs01（LVS01,keepalived）
192.168.183.189 txc-lvs02（LVS02,keepalived）
192.168.183.190 txc-mysql01（MySQL 主）
192.168.183.191 txc-mysql02（MySQL 主）

2.配置

主机名

[root@txc-lvs01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.183.188 txc-lvs01
192.168.183.189 txc-lvs02
192.168.183.190 txc-mysql01
192.168.183.191 txc-mysql02
[root@txc-lvs01 ~]# scp /etc/hosts 192.168.183.189:/etc
[root@txc-lvs01 ~]# scp /etc/hosts 192.168.183.190:/etc
[root@txc-lvs01 ~]# scp /etc/hosts 192.168.183.191:/etc

keepalived配置

# keepalived MASTER配置（txc-lvs01）
[root@txc-lvs01 ~]# yum -y install keepalived
[root@txc-lvs01 ~]# cd /etc/keepalived/
[root@txc-lvs01 keepalived]# mv keepalived.conf{,.bak}
[root@txc-lvs01 keepalived]# vim keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id LVS_1
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.183.100/24
    }
}

virtual_server 192.168.183.100 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    nat_mask 255.255.255.0
    # persistence_timeout 50
    protocol TCP

    real_server 192.168.183.190 3306 {
        weight 1
        TCP_CHECK {
            connect_timeout 8
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }

    real_server 192.168.183.191 3306 {
        weight 1
        TCP_CHECK {
            connect_timeout 8
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}
[root@txc-lvs01 keepalived]# systemctl start keepalived.service
[root@txc-lvs01 keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:2f:a1:1e brd ff:ff:ff:ff:ff:ff
    inet 192.168.183.188/24 brd 192.168.183.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.183.100/24 scope global secondary ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::e108:c677:6246:9c33/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::a6d3:943b:8686:30ee/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::5f2a:ea12:d28f:4ec3/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever

# keepalived BACKUP配置（txc-lvs02）
[root@txc-lvs02 ~]# yum -y install keepalived
[root@txc-lvs02 ~]# cd /etc/keepalived/
[root@txc-lvs02 keepalived]# mv keepalived.conf{,.bak}
[root@txc-lvs02 keepalived]# vim keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id LVS_2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.183.100/24
    }
}

virtual_server 192.168.183.100 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    nat_mask 255.255.255.0
    # persistence_timeout 50
    protocol TCP

    real_server 192.168.183.190 3306 {
        weight 1
        TCP_CHECK {
            connect_timeout 8
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }

    real_server 192.168.183.191 3306 {
        weight 1
        TCP_CHECK {
            connect_timeout 8
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}
[root@txc-lvs02 keepalived]# systemctl start keepalived.service
[root@txc-lvs02 keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:eb:79:a1 brd ff:ff:ff:ff:ff:ff
    inet 192.168.183.189/24 brd 192.168.183.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::e108:c677:6246:9c33/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::a6d3:943b:8686:30ee/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::5f2a:ea12:d28f:4ec3/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

lvs配置

# txc-lvs01配置
[root@txc-lvs01 ~]# yum -y install ipvsadm
[root@txc-lvs01 ~]# ipvsadm -C
[root@txc-lvs01 ~]# 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.183.100:3306 rr
  -> 192.168.183.190:3306         Route   1      0          0         
  -> 192.168.183.191:3306         Route   1      0          0         
# txc-lvs02配置
[root@txc-lvs02 ~]# yum -y install ipvsadm
[root@txc-lvs02 ~]# ipvsadm -C
[root@txc-lvs02 ~]# ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

mysql配置

# 配置txc-mysql01作为txc-mysql02的master
# txc-mysql01配置
[root@txc-mysql01 ~]# rz
[root@txc-mysql01 ~]# ls
anaconda-ks.cfg                                 mysql-community-libs-5.7.30-1.el7.x86_64.rpm
mysql-community-client-5.7.30-1.el7.x86_64.rpm  mysql-community-server-5.7.30-1.el7.x86_64.rpm
mysql-community-common-5.7.30-1.el7.x86_64.rpm
[root@txc-mysql01 ~]# yum -y remove mariadb-libs.x86_64
[root@txc-mysql01 ~]# yum -y install mysql-community*
[root@txc-mysql01 ~]# systemctl enable mysqld --now
[root@txc-mysql01 ~]# grep password /var/log/mysqld.log
[root@txc-mysql01 ~]# mysqladmin -uroot -p'&o8wGopht1lr' password 'Qfedu.123com'
[root@txc-mysql01 ~]# cp /etc/my.cnf{,.bak}
[root@txc-mysql01 ~]# vim /etc/my.cnf
# 添加以下内容
# GTID
server_id=1
gtid_mode=on
enforce_gtid_consistency=on

# binlog
log_bin=mysql-bin
log-slave-updates=1
binlog_format=row
sync-master-info=1
sync_binlog=1

# relay log
skip_slave_start=1

[root@txc-mysql01 ~]# systemctl restart mysqld
[root@txc-mysql01 ~]# mysql -p'Qfedu.123com'
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
# 授权slave复制用户并刷新权限
mysql> grant replication slave,replication client on *.* to slave@'192.168.183.%' identified by 'Qfedu.123com';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 再次查看master状态
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 622
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 916d153c-b104-11ea-b5a3-000c29dd7734:1-2
1 row in set (0.00 sec)

# txc-mysql02配置
[root@txc-mysql02 ~]# rz
[root@txc-mysql02 ~]# ls
anaconda-ks.cfg                                 mysql-community-libs-5.7.30-1.el7.x86_64.rpm
mysql-community-client-5.7.30-1.el7.x86_64.rpm  mysql-community-server-5.7.30-1.el7.x86_64.rpm
mysql-community-common-5.7.30-1.el7.x86_64.rpm
[root@txc-mysql02 ~]# yum -y remove mariadb-libs.x86_64
[root@txc-mysql02 ~]# yum -y install mysql-community*
[root@txc-mysql02 ~]# systemctl enable mysqld --now
[root@txc-mysql02 ~]# grep password /var/log/mysqld.log
[root@txc-mysql02 ~]# mysqladmin -uroot -p'!K/qqg,rf1!o' password 'Qfedu.123com'
[root@txc-mysql02 ~]# cp /etc/my.cnf{,.bak}
[root@txc-mysql02 ~]# vim /etc/my.cnf
# 添加以下内容
# GTID
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on

# binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1

# relay log
skip_slave_start = 1
read_only = on

[root@txc-mysql02 ~]# systemctl restart mysqld
# 登录mysql，配置主从复制
[root@txc-mysql02 ~]# mysql -p'Qfedu.123com'
mysql> change master to master_host='192.168.183.190',master_user='slave',master_password='Qfedu.123com',master_auto_position=1;
# 启动从服务，查看状态
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.183.190
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 622
               Relay_Log_File: mysql2-relay-bin.000002
                Relay_Log_Pos: 835
        Relay_Master_Log_File: mysql-bin.000001
             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: 622
              Relay_Log_Space: 1043
              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: 916d153c-b104-11ea-b5a3-000c29dd7734
             Master_Info_File: /var/lib/mysql/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: 916d153c-b104-11ea-b5a3-000c29dd7734:1-2
            Executed_Gtid_Set: 916d153c-b104-11ea-b5a3-000c29dd7734:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

# 配置txc-mysql02作为txc-mysql01的master
[root@txc-mysql02 ~]# mysql -p'Qfedu.123com'
mysql> grant replication slave,replication client on *.* to slave@'192.168.183.%' identified by 'Qfedu.123com';
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

[root@txc-mysql01 ~]# mysql -p'Qfedu.123com'
mysql> change master to master_host='192.168.183.191',master_user='slave',master_password='Qfedu.123com',master_auto_position=1;
# 启动从服务，查看状态
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.183.191
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 622
               Relay_Log_File: mysql1-relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Master_Log_File: mysql-bin.000001
             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: 622
              Relay_Log_Space: 622
              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: 2
                  Master_UUID: 905d0d99-b104-11ea-b5b0-000c2944ae12
             Master_Info_File: /var/lib/mysql/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: 916d153c-b104-11ea-b5a3-000c29dd7734:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

3.验证

# 为两个数据库配置vip
[root@txc-mysql01 ~]# ifconfig ens33:0 192.168.183.100 netmask 255.255.255.255
[root@txc-mysql02 ~]# ifconfig ens33:0 192.168.183.100 netmask 255.255.255.255

# 使用vip在客户端上（不可以是keepalived的MASTER）远程登录数据库
[root@txc-lvs02 ~]# mysql -u 'slave' -p'Qfedu.123com' -h 192.168.183.100

# mysql负载均衡验证：停掉txc-mysql01的数据库，再次用vip远程登录
[root@txc-mysql01 ~]# systemctl stop mysqld.service
[root@txc-lvs02 ~]# mysql -u 'slave' -p'Qfedu.123com' -h 192.168.183.100

# lvs高可用验证：停掉txc-lvs01，vip自动飘到txc-lvs02上
[root@txc-lvs01 ~]# systemctl stop keepalived
[root@txc-lvs02 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:eb:79:a1 brd ff:ff:ff:ff:ff:ff
    inet 192.168.183.189/24 brd 192.168.183.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.183.100/24 scope global secondary ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::e108:c677:6246:9c33/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::a6d3:943b:8686:30ee/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::5f2a:ea12:d28f:4ec3/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

# 再次使用vip远程登录数据库
[root@txc-lvs01 ~]# mysql -uslave -p'Qfedu.123com' -h 192.168.183.100

inet6 fe80::e108:c677:6246:9c33/64 scope link tentative noprefixroute dadfailed 
   valid_lft forever preferred_lft forever
inet6 fe80::a6d3:943b:8686:30ee/64 scope link tentative noprefixroute dadfailed 
   valid_lft forever preferred_lft forever
inet6 fe80::5f2a:ea12:d28f:4ec3/64 scope link noprefixroute 
   valid_lft forever preferred_lft forever

再次使用vip远程登录数据库

[root@txc-lvs01 ~]# mysql -uslave -p’Qfedu.123com’ -h 192.168.183.100

本文地址：https://blog.csdn.net/qq_45897484/article/details/107214130

您可能感兴趣的文章:

如对本文有疑问，点击进行留言回复！！

行级锁，表级锁，乐观锁，悲观锁

名词解释表级锁（锁定整个表）行级锁（锁定一行）悲观锁（抽象性，不真实存在这个锁）乐观锁（抽象性，不真实存在这个锁... [阅读全文]
存在时更新不存在时新增：merge into using（071问题）

merge into using在写数据同步的脚本时，常常会遇到这样的需求：存在时 -> 更新，不存在时 ... [阅读全文]
Go语言连接MySQL和SQL Server

连接MySQL，先获取第三方包go get -u github.com/go-sql-driver/mysql连... [阅读全文]
mysql大量的waiting for table level lock怎么办

导读遇到经典的MDL等待场景时怎么分析和处理。本文节选自叶金荣有赞《乱弹MySQL》专栏。MySQL从5.5版本... [阅读全文]
LoadRunner 读取Mysql数据实现参数化

LoadRunner 读取mysql数据实现参数化步骤如下：一、安装Mysql的ODBC1、运行安装包，以默认选... [阅读全文]
Leetcode database 刷题 MySQL 简单部分

Leetcode database 刷题MySQL简单部分176#select( select disti... [阅读全文]
软考之路（三）：13系统架构师真题分析（3~7）

为了写博客的速度更快一些，对于一些简单的题目我们就一笔带过，捡一些重点难点咱们来说说第三题：分析：这种题型算是... [阅读全文]
mysql 1293 错误的解决

在部署项目时，我们的生产环境和测试环境一般都是一致的，但是在部署项目时偶尔也会遇到MySQL环境不一致的问题。遇... [阅读全文]
为什么要避免大事务以及大事务如何解决？

什么是大事务运行时间比较长，长时间未提交的事务就可以称为大事务大事务产生的原因操作的数据比较多大量的锁竞争事务中... [阅读全文]
Mysql下载以及进入MySQL遇到的各种问题集合

MySQL的下载很多不是第一次下载了,所以在下载之前先确保自己电脑里面的MySQL已经卸载干净了大体需要做这么几... [阅读全文]