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

lvs+keepalived+mysql (完整版)

程序员文章站 2022-03-21 08:01:56
### MySQL主主复制+LVS+Keepalived实现MySQL高可用性架构图[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1pndFFSU-1594210072321)(assets/43136-20170124163407034-588511483.png)]1.环境192.168.183.100 VIP192.168.183.188 txc-lvs01(LVS01,keepalived)192.168.183.189 txc-lvs02(LVS...

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