lvs+keepalived+mysql (完整版)

### 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