MySQL 主从复制+Keepalived高可用

最新推荐文章于 2024-08-14 09:00:00 发布
最新推荐文章于 2024-08-14 09:00:00 发布
阅读量1.1k 收藏 5
点赞数
分类专栏： MySQL 文章标签： mysql 其他
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38890951/article/details/115294217
版权

 

本文档通过个人各种测试后总结而得，有任何冒犯，请提出改正。

1、主从搭建

###节点分配情况，前期准备工作，关于配置关闭防火墙、关闭selinux、配置hosts请自行问度娘。

系统：CentOS 7
数据库：mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
关闭iptables和selinux、配置/etc/hosts文件
节点信息：
192.168.110.128 master
192.168.110.129 slave
192.168.110.130 vip

 1.1、配置MySQL主从节点

###修改Master节点配置文件“/etc/my.cnf”

[root@localhost bin]#  vi /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
server-id=128
log-bin=mysql-bin

###修改slave节点配置文件“/etc/my.cnf”

[root@localhost bin]#  vi /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
server-id=129
log-bin=mysql-bin

1.2、启动数据库

###数据库master节点启动，如已启动，请重启MySQL服务：service mysqld restart

[root@master ~]# service mysqld start
Starting MySQL. SUCCESS! 
[root@master ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

###数据库slave节点启动，如已启动，请重启MySQL服务：service mysqld restart

[root@slave ~]# service mysqld start
Starting MySQL. SUCCESS! 
[root@slave ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

！Tip：本环境是自己个人搭建试验环境，不存在参数优化配置，如需优化请自行度娘。

1.3、创建复制账号

###进入mysql主节点，赋予从库权限账号，允许用户在主库上读取日志，并给它replication slave的权限。
       ###创建数据库账号：copyuser/123，“%”表示允许所有机器成为从库，也可设置为固定IP

[root@master ~]# mysql -uroot -p
Enter password:输入数据库密码
mysql> 
mysql> CREATE USER 'copyuser'@'%' IDENTIFIED BY '123';    #创建账号
mysql> GRANT REPLICATION SLAVE ON *.* TO 'copyuser'@'%';  #赋予权限
mysql>flush privileges; #刷新权限

1.4、配置同步

###master数据库节点执行 “SHOW MASTER STATUS”，不区分大小写

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

mysql>

###打开数据库slave节点的mysql会话，执行同步SQL语句，MASTER_LOG_FILE 、MASTER_LOG_POS参照上图中的File、Position对应的值。

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.110.128',
    ->     MASTER_USER='copy',
    ->     MASTER_PASSWORD='123',
    ->     MASTER_LOG_FILE='mysql-bin.000008',
    ->     MASTER_LOG_POS=154;
  • MASTER_HOST : 主数据库IP
  • MASTER_USER : 主数据库授权账号
  • MASTER_PASSWORD : 主数据库授权密码
  • MASTER_LOG_FILE : 在主数据库中通过SHOW MASTER STATUS命令获取File字段的值
  • MASTER_LOG_POS : 在主数据库中通过SHOW MASTER STATUS命令获取Position字段的值

###启动从数据库slave的同步进程

mysql>start slave;

###查看从数据库slave的状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.110.128
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000024
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000008
             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: 154
              Relay_Log_Space: 527
              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: 128
                  Master_UUID: df84cf69-8fa2-11eb-ae10-000c29b0667c
             Master_Info_File: /usr/local/mysql/data/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: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

###当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行主从复制验证了。

1.5、验证同步

##master上创建测试库和测试表，slave上查看

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql> create table user (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into user values (1,'laowang');
Query OK, 1 row affected (0.02 sec)

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

### slave 查看数据库是否存在 master 节点创建的库和表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| test             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | laowang| |
+----+--------+
1 rows in set (0.00 sec)

###至此，主从复制搭建同步就完成了，简单吧☺，如若以上步骤存在不解，请留言我们一起解决。

2、配置 Keepalived 实现高可用

2.1、主从节点分别安装 keepalived

  • ###安装方式1，在线安装 yum 方式

1、安装

先安装依赖包
[root@master ~]# yum install -y curl gcc openssl-devel libnl3-devel net-snmp-devel
再安装keepalived
[root@master ~]# yum install -y keepalived

2、启动

[root@master ~]# systemctl start keepalived   //启动keepalived
[root@master ~]# systemctl enable keepalived  //加入开机启动keepalived
[root@master ~]# systemctl restart keepalived  //重新启动keepalived
[root@master ~]# systemctl status keepalived   //查看keepalived状态
  • ###安装方式2，源码包

1、官网 下载keepalived 的最新版本，解压并安装

[root@master src]# pwd
/usr/local/src
[root@master src]# wget http://www.keepalived.org/software/keepalived-2.2.2.tar.gz
[root@master src]# tar zxvf keepalived-2.2.2.tar.gz
[root@master src]# cd keepalived-2.2.2
[root@master keepalived-2.2.2]# ./configure --prefix=/usr/local/keepalived
[root@master keepalived-2.2.2]# make && make install

完成后会生成以下路径

[root@master keepalived]# tree -l /usr/local/keepalived/
/usr/local/keepalived/
├── bin
│   └── genhash
├── etc
│   ├── keepalived
│   │   ├── keepalived.conf
│   │   └── samples
│   │       ├── client.pem
│   │       ├── dh1024.pem
│   │       ├── keepalived.conf.conditional_conf
│   │       ├── keepalived.conf.fwmark
│   │       ├── keepalived.conf.HTTP_GET.port
│   │       ├── keepalived.conf.inhibit
│   │       ├── keepalived.conf.IPv6
│   │       ├── keepalived.conf.misc_check
│   │       ├── keepalived.conf.misc_check_arg
│   │       ├── keepalived.conf.PING_CHECK
│   │       ├── keepalived.conf.quorum
│   │       ├── keepalived.conf.sample
│   │       ├── keepalived.conf.SMTP_CHECK
│   │       ├── keepalived.conf.SSL_GET
│   │       ├── keepalived.conf.status_code
│   │       ├── keepalived.conf.track_interface
│   │       ├── keepalived.conf.UDP_CHECK
│   │       ├── keepalived.conf.virtualhost
│   │       ├── keepalived.conf.virtual_server_group
│   │       ├── keepalived.conf.vrrp
│   │       ├── keepalived.conf.vrrp.localcheck
│   │       ├── keepalived.conf.vrrp.lvs_syncd
│   │       ├── keepalived.conf.vrrp.routes
│   │       ├── keepalived.conf.vrrp.rules
│   │       ├── keepalived.conf.vrrp.scripts
│   │       ├── keepalived.conf.vrrp.static_ipaddress
│   │       ├── keepalived.conf.vrrp.sync
│   │       ├── root.pem
│   │       ├── sample.misccheck.smbcheck.sh
│   │       └── sample_notify_fifo.sh
│   └── sysconfig
│       └── keepalived
├── sbin
│   └── keepalived
└── share
    ├── doc
    │   └── keepalived
    │       └── README
    ├── man
    │   ├── man1
    │   │   └── genhash.1
    │   ├── man5
    │   │   └── keepalived.conf.5
    │   └── man8
    │       └── keepalived.8
    └── snmp
        └── mibs

15 directories, 38 files
[root@master keepalived]#

2、初始化及启动

# keepalived启动脚本变量引用文件，默认文件路径是/etc/sysconfig/，也可以不做软链接，直接修改启动脚本中文件路径即可（安装目录下）
[root@master/]# cp /usr/local/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/keepalived 
 
# 将keepalived主程序加入到环境变量（安装目录下）
[root@master/]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/keepalived
 
# keepalived启动脚本（源码目录下），放到/etc/init.d/目录下就可以使用service命令便捷调用
[root@master/]# cp /usr/local/src/keepalived-2.0.7/keepalived/etc/init.d/keepalived  /etc/init.d/keepalived
 
# 将配置文件放到默认路径下
[root@master/]# mkdir /etc/keepalived
[root@master /]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
  • 加为系统服务：chkconfig –add keepalived
  • 开机启动：chkconfig keepalived on
  • 查看开机启动的服务：chkconfig –list
  • 启动、关闭、重启service keepalived start|stop|restart

###至此keepalived 就安装完成了，后续配置完后再启动。

2.2、主从节点准备mysql.sh脚本

### master节点 MySQL 脚本

[root@master ~]# vim /etc/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
[root@master ~]# chmod +x !$

### slave 节点 MySQL 脚本

[root@slave~]# vim /etc/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
[root@slave~]# chmod +x !$

2.2、准备Keepalived配置文件

## master 配置 Keepalived

[root@master keepalived]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 5
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.110.130
    }
}

virtual_server 192.168.110.130 3306 {
    delay_loop 2
   # lb_algo rr
   # lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 192.168.110.128 3306 {
        weight 3
	notify_down /etc/keepalived/mysql.sh
        TCP_CHECK {
            connect_timeout 3
            retry 3
            delay_before_retry 3
        }
    }
}
[root@master keepalived]#

## slave 配置 Keepalived

[root@slave keepalived]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    advert_int 5
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.110.130
    }
}

virtual_server 192.168.110.130 3306 {
    delay_loop 2
   # lb_algo rr
   # lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 192.168.110.129 3306 {
        weight 3
	notify_down /etc/keepalived/mysql.sh
        TCP_CHECK {
            connect_timeout 3
            retry 3
            delay_before_retry 3
        }
    }
}
[root@master keepalived]#

！Tip：master节点和slave节点配置不同，请仔细查看以上配置。

2.3、启动两个节点的Keepalived进程

### master 启动 Keepalived

[root@master ~]# systemctl start keepalived
[root@master ~]# ps -ef | grep keepalived | grep -v grep
root     9041     1  0 07:30 ?        00:00:00 /usr/sbin/keepalived -D
root     9042  9041  0 07:30 ?        00:00:00 /usr/sbin/keepalived -D

### slave 启动 Keepalived

[root@slave ~]# systemctl start keepalived
[root@slave ~]# ps -ef | grep keepalived | grep -v grep
root     9078     1  0 07:30 ?        00:00:00 /usr/sbin/keepalived -D
root     9079  9041  0 07:30 ?        00:00:00 /usr/sbin/keepalived -D

2.4、查看Keepalived状态以及VIP绑定情况

### master 查看启动日志
       master 的优先级高，Keepalived 的状态为 MASTER，并且发布的广播协议。192.168.110.130（VIP）已经在本台机器上，其他机器不能再使用了

[root@master ~]# tail -50 /var/log/messages
..
..
Mar 29 09:22:15 master Keepalived_vrrp[2652]: (VI_1) Entering MASTER STATE
Mar 29 09:22:15 master Keepalived_vrrp[2652]: (VI_1) setting VIPs.
Mar 29 09:22:15 master Keepalived_vrrp[2652]: (VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.110.130
Mar 29 09:22:15 master Keepalived_vrrp[2652]: Sending gratuitous ARP on ens33 for 192.168.110.130
Mar 29 09:22:15 master Keepalived_vrrp[2652]: Sending gratuitous ARP on ens33 for 192.168.110.130
Mar 29 09:22:15 master Keepalived_vrrp[2652]: Sending gratuitous ARP on ens33 for 192.168.110.130
Mar 29 09:22:15 master Keepalived_vrrp[2652]: Sending gratuitous ARP on ens33 for 192.168.110.130
Mar 29 09:22:15 master Keepalived_vrrp[2652]: Sending gratuitous ARP on ens33 for 192.168.110.130

### master 上查看 IP，查看 VIP 绑定情况
       ### VIP 在 master 上

[root@master keepalived]# ip addr show
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:b0:66:7c brd ff:ff:ff:ff:ff:ff
    inet 192.168.110.128/24 brd 192.168.110.255 scope global noprefixroute dynamic ens33
       valid_lft 1709sec preferred_lft 1709sec
    inet 192.168.110.130/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::4a92:c0e2:79b6:eaa1/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:b5:96:7a brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:b5:96:7a brd ff:ff:ff:ff:ff:ff
[root@master keepalived]#

### slave 上查看启动日志
       ### slave 上 Keepalived 的状态是 backup

[root@slave ~]# tail -50 /var/log/messages
Mar 29 11:27:39 slave avahi-daemon[895]: Withdrawing workstation service for ens33.
Mar 29 11:27:39 slave avahi-daemon[895]: Withdrawing workstation service for lo.
Mar 29 11:27:39 slave avahi-daemon[895]: Host name conflict, retrying with linux-407
Mar 29 11:27:39 slave avahi-daemon[895]: Registering new address record for 192.168.122.1 on virbr0.IPv4.
Mar 29 11:27:39 slave avahi-daemon[895]: Registering new address record for fe80::4a92:c0e2:79b6:eaa1 on ens33.*.
Mar 29 11:27:39 slave avahi-daemon[895]: Registering new address record for fe80::65b7:6eb1:d5ff:2d55 on ens33.*.
Mar 29 11:27:39 slave avahi-daemon[895]: Registering new address record for 192.168.110.129 on ens33.IPv4.
Mar 29 11:27:39 slave avahi-daemon[895]: Registering HINFO record with values 'X86_64'/'LINUX'.
Mar 29 11:27:59 slave avahi-daemon[895]: Server startup complete. Host name is linux-407.local. Local service cookie is 882538680.

### slave 上查看 IP，查看 VIP 绑定情况
       ### slave 上没有 VIP

[root@slave ~]# ip addr show
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:50:41:f3 brd ff:ff:ff:ff:ff:ff
    inet 192.168.110.129/24 brd 192.168.110.255 scope global noprefixroute dynamic ens33
       valid_lft 1788sec preferred_lft 1788sec
    inet6 fe80::4a92:c0e2:79b6:eaa1/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::65b7:6eb1:d5ff:2d55/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:b5:96:7a brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:b5:96:7a brd ff:ff:ff:ff:ff:ff
[root@slave ~]#

2.5、测试使用 VIP 连接 master 

###在 master 上使用 vip 连接 master ,正常连接

[root@master keepalived]# mysql -uroot -p123 -h192.168.110.130
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 226
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2.6、模拟 master 宕机时的故障切换

### master 关闭MySQL服务

[root@master ~]# service mysqld stop 
Shutting down MySQL............ SUCCESS!

## master 查看 /var/log/messages 日志输出
       这时候会发现，Keepalived 服务被 stop，并且移除了 VIP

Mar 29 11:36:03 master Keepalived_healthcheckers[8687]: TCP_CHECK on service [192.168.110.128]:tcp:3306 failed after 3 retries.
Mar 29 11:36:03 master Keepalived_healthcheckers[8687]: Removing service [192.168.110.128]:tcp:3306 to VS [192.168.110.130]:tcp:3306
Mar 29 11:36:03 master Keepalived_healthcheckers[8687]: Lost quorum 1-0=1 > 0 for VS [192.168.110.130]:tcp:3306
Mar 29 11:36:03 master Keepalived[8686]: Stopping
Mar 29 11:36:03 master Keepalived_healthcheckers[8687]: Shutting down service [192.168.110.128]:tcp:3306 from VS [192.168.110.130]:tcp:3306
Mar 29 11:36:03 master Keepalived_healthcheckers[8687]: Stopped - used 0.003598 user time, 0.120556 system time
Mar 29 11:36:03 master Keepalived_vrrp[8688]: (VI_1) sent 0 priority
Mar 29 11:36:03 master Keepalived_vrrp[8688]: (VI_1) removing VIPs.
Mar 29 11:36:03 master avahi-daemon[743]: Withdrawing address record for 192.168.110.130 on ens33.
Mar 29 11:36:04 master Keepalived_vrrp[8688]: Stopped - used 0.002016 user time, 0.031257 system time
Mar 29 11:36:04 master Keepalived[8686]: CPU usage (self/children) user: 0.000000/0.005625 system: 0.000983/0.152722
Mar 29 11:36:04 master Keepalived[8686]: Stopped Keepalived v2.2.2 (03/05,2021)

### slave 上查看 /var/log/messages 日志输出
       此时发现 slave 上的 Keepalived 状态变成了 MASTER

Mar 29 11:38:39 slave avahi-daemon[895]: Registering new address record for 192.168.110.129 on ens33.IPv4.
Mar 29 11:38:39 slave avahi-daemon[895]: Registering HINFO record with values 'X86_64'/'LINUX'.
Mar 29 11:38:59 slave avahi-daemon[895]: Server startup complete. Host name is linux-440.local. Local service cookie is 882538680.
Mar 29 11:38:59 slave avahi-daemon[895]: Withdrawing workstation service for virbr0-nic.
Mar 29 11:38:59 slave avahi-daemon[895]: Withdrawing address record for 192.168.122.1 on virbr0.
Mar 29 11:38:59 slave avahi-daemon[895]: Withdrawing workstation service for virbr0.
Mar 29 11:38:59 slave avahi-daemon[895]: Withdrawing address record for fe80::65b7:6eb1:d5ff:2d55 on ens33.
Mar 29 11:38:59 slave avahi-daemon[895]: Withdrawing address record for 192.168.110.130 on ens33.
Mar 29 11:38:59 slave avahi-daemon[895]: Withdrawing address record for 192.168.110.129 on ens33.
Mar 29 11:38:59 slave avahi-daemon[895]: Withdrawing workstation service for ens33.
Mar 29 11:38:59 slave avahi-daemon[895]: Withdrawing workstation service for lo.
Mar 29 11:38:59 slave avahi-daemon[895]: Host name conflict, retrying with linux-441
Mar 29 11:38:59 slave avahi-daemon[895]: Registering new address record for 192.168.122.1 on virbr0.IPv4.
Mar 29 11:38:59 slave avahi-daemon[895]: Registering new address record for fe80::4a92:c0e2:79b6:eaa1 on ens33.*.
Mar 29 11:38:59 slave avahi-daemon[895]: Registering new address record for fe80::65b7:6eb1:d5ff:2d55 on ens33.*.
Mar 29 11:38:59 slave avahi-daemon[895]: Registering new address record for 192.168.110.130 on ens33.IPv4.
Mar 29 11:38:59 slave avahi-daemon[895]: Registering new address record for 192.168.110.129 on ens33.IPv4.
Mar 29 11:38:59 slave avahi-daemon[895]: Registering HINFO record with values 'X86_64'/'LINUX'.
Mar 29 11:39:19 slave avahi-daemon[895]: Server startup complete. Host name is linux-441.local. Local service cookie is 882538680.
.....

### slave 查看 IP，查看 VIP 绑定情况
       此处发现 VIP 已经转移到 slave 上面

[root@slave ~]# ip addr show
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:50:41:f3 brd ff:ff:ff:ff:ff:ff
    inet 192.168.110.129/24 brd 192.168.110.255 scope global noprefixroute dynamic ens33
       valid_lft 1084sec preferred_lft 1084sec
    inet 192.168.110.130/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::4a92:c0e2:79b6:eaa1/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::65b7:6eb1:d5ff:2d55/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:b5:96:7a brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:b5:96:7a brd ff:ff:ff:ff:ff:ff
[root@slave ~]#

###至此整个过程就搭建完成了，可以使用Navicat客户端连接MySQL服务，连接时将主机名或IP地址设置为 VIP 的 IP 地址，端口设置为 3306， 用户名密码使用root用户的账户密码即可。此过程不影响客户端连接状态，客户端可以重连，继续使用VIP连接。
Master to Slave + Keepalived架构比较简单，主节点出现故障后，利用Keepalived的高可用机制可以快速的切换到另一个节点，原来的Backup Master变成了主节点。Keepalived架构在设置两个节点状态的时候都要设置成Backup而且是noprermpt（非抢占）模式，通过优先级的高低来决定谁是主库，避免发生冲突现象。另外，在服务器异常判断时，可以修改判断脚本，通过对第三方节点补充检测来决定是否进行切换，这样可以降低“脑裂”的风险。Backup Master的配置要尽量和Master保持一致，不可以降低太多性能标准。

这里贴一个 Keepalived 的配置文件详解：

[root@localhost ~]# cat /usr/local/keepalived/etc/keepalived/keepalived.conf
! Configuration File for keepalived
 
global_defs {					#全局配置
	notification_email {		#指定keepalived在发生切换时需要发送email到的对象，一行一个
		acassen@firewall.loc	#指定收件人邮箱
		failover@firewall.loc
		sysadmin@firewall.loc
	}
	notification_email_from Alexandre.Cassen@firewall.loc #指定发件人
	smtp_server 192.168.200.1	#指定smtp服务器地址
	smtp_connect_timeout 30		#指定smtp连接超时时间
	router_id LVS_DEVEL			#此处注意router_id为负载均衡标识，在局域网内应该是唯一的。
	vrrp_skip_check_adv_addr
	vrrp_strict
	vrrp_garp_interval 0
	vrrp_gna_interval 0
}
 
vrrp_sync_group VG_1{				#监控多个网段的实例
	group {
		inside_network				#实例名
		outside_network
	}
	notify_master /path/xx.sh		#指定当切换到master时，执行的脚本
	netify_backup /path/xx.sh		#指定当切换到backup时，执行的脚本
	notify_fault "path/xx.sh VG_1" 	#故障时执行的脚本
	notify /path/xx.sh
	smtp_alert 						#使用global_defs中提供的邮件地址和smtp服务器发送邮件通知
}
 
vrrp_instance inside_network {
	state BACKUP 			#指定那个为master，那个为backup，如果设置了nopreempt这个值不起作用，主备考priority决定
	interface eth0 			#设置实例绑定的网卡
	dont_track_primary 		#忽略vrrp的interface错误（默认不设置）
	track_interface{ 		#设置额外的监控，里面那个网卡出现问题都会切换
		eth0
		eth1
	}
	mcast_src_ip			#发送多播包的地址，如果不设置默认使用绑定网卡的primary ip
	garp_master_delay		#在切换到master状态后，延迟进行gratuitous ARP请求
	virtual_router_id 50	#VPID标记
	priority 99				#优先级，高优先级竞选为master
	advert_int 1			#检查间隔，默认1秒
	nopreempt				#设置为不抢占 注：这个配置只能设置在backup主机上，而且这个主机优先级要比另外一台高
	preempt_delay			#抢占延时，默认5分钟
	debug					#debug级别
	authentication {		#设置认证
		auth_type PASS		#认证方式，类型主要有PASS、AH 两种
		auth_pass 111111	#认证密码
	}
	virtual_ipaddress {		#设置vip
		192.168.36.200
	}
}
 
vrrp_instance VI_1 {		#虚拟路由的标识符
	state MASTER			#状态只有MASTER和BACKUP两种，并且要大写，MASTER为工作状态，BACKUP是备用状态
	interface eth0			#通信所使用的网络接口
    lvs_sync_daemon_inteface eth0  #这个默认没有，相当于心跳线接口，DR模式用的和上面的接口一样，也可以用机器上的其他网卡eth1，用来防止脑裂。
    virtual_router_id 51	#虚拟路由的ID号，是虚拟路由MAC的最后一位地址
    priority 100			#此节点的优先级，主节点的优先级需要比其他节点高
    advert_int 1			#通告的间隔时间
    nopreempt				#设置为不抢占 注：这个配置只能设置在backup主机上，而且这个主机优先级要比另外一台高
    preempt_delay			#抢占延时，默认5分钟
    authentication {		#认证配置
		auth_type PASS		#认证方式
        auth_pass 1111		#认证密码
    }
    virtual_ipaddress {		#虚拟ip地址,可以有多个地址，每个地址占一行，不需要子网掩码，同时这个ip 必须与我们在lvs 客户端设定的vip 相一致！
        192.168.200.16
        192.168.200.17
        192.168.200.18
    }
}
 
virtual_server 192.168.200.100 443 { #集群所使用的VIP和端口
    delay_loop 6					#健康检查间隔，单位为秒
    lb_algo rr						#lvs调度算法rr|wrr|lc|wlc|lblc|sh|dh
    nat_mask 255.255.255.0			#VIP掩码
    lb_kind NAT						#负载均衡转发规则。一般包括DR,NAT,TUN 3种
    persistence_timeout 50			#会话保持时间，会话保持，就是把用户请求转发给同一个服务器，不然刚在1上提交完帐号密码，就跳转到另一台服务器2上了
    protocol TCP					#转发协议，有TCP和UDP两种，一般用TCP，没用过UDP
    persistence_granularity <NETMASK> #lvs会话保持粒度
 
    real_server 192.168.201.100 443 { #真实服务器，包括IP和端口号
        weight 1					#默认为1,0为失效
        inhibit_on_failure			#在服务器健康检查失效时，将其设为0，而不是直接从ipvs中删除
        notify_up <string> | <quoted-string> #在检测到server up后执行脚本
        notify_down <string> | <quoted-string> #在检测到server down后执行脚本
 
		TCP_CHECK {					#通过tcpcheck判断RealServer的健康状态
            connect_timeout 3		#连接超时时间
            nb_get_retry 3			#重连次数
            delay_before_retry 3	#重连间隔时间
            connect_port 23			健康检查的端口的端口
            bindto <ip>  
        }
           
        HTTP_GET | SSL_GET {		#健康检测方式，可选有 SSL_GET、TCP_CHECK、HTTP_GET
            url {					#检查url，可以指定多个
              path /				#检查的url路径
              digest ff20ad2481f97b1754ef3e12ecd3a9cc  #需要检查到的内容。检查后的摘要信息。
              status_code 200		#检查的返回状态码
            }
            url {
              path /mrtg/
              digest 9b3a0c85a887a256d6939da88aabd8cd
            }
            connect_timeout 3		#连接超时时间
            nb_get_retry 3			#检测尝试几次
            delay_before_retry 3	#检测的时间间隔
        }
    }
}
 
virtual_server 10.10.10.2 1358 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP
 
    sorry_server 192.168.200.200 1358
 
    real_server 192.168.200.2 1358 {
        weight 1
        HTTP_GET {
            url {
              path /testurl/test.jsp
              digest 640205b7b0fc66c1ea91c463fac6334d
            }
            url {
              path /testurl2/test.jsp
              digest 640205b7b0fc66c1ea91c463fac6334d
            }
            url {
              path /testurl3/test.jsp
              digest 640205b7b0fc66c1ea91c463fac6334d
            }
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
 
    real_server 192.168.200.3 1358 {
        weight 1
        HTTP_GET {
            url {
              path /testurl/test.jsp
              digest 640205b7b0fc66c1ea91c463fac6334c
            }
            url {
              path /testurl2/test.jsp
              digest 640205b7b0fc66c1ea91c463fac6334c
            }
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}

以上MySQL的安装请自行度娘，注意事项也挺多的，期间出现问题可以留言一起探讨！

加油！

波儿爸
关注
专栏目录
MySQL双主+主从复制+keepalived
qq_19001575的博客
07-30 709
MySQL双主+主从复制+keepalived （主主）架构方案思路是: 1.两台mysql都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用； 2.masterA是masterB的主库，masterB又是masterA的主库，它们互为主从； 3.两台主库之间做高可用,可以采用keepalived等方案（使用VIP对外提供服务）； 4.所有提供服务的从服务器与masterB进行主从同步（双主多从）; 5.建议采用高可用策略的时候，masterA或master
使用keepalived实现对MySQL主从复制的主备自动切换
03-27
使用MySQL+keepalived是一种非常好的解决方案，在MySQL-HA环境中，MySQL互为主从关系，这样就保证了两台 MySQL数据的一致性，然后用keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换
mysql互为主从 keep_KeepAlived+MySQL互为主从
weixin_29958123的博客
02-02 321
版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/socho/article/details/51804720解决Master单点问题，两台mysql互为主备，双向replication。当一master挂了，则将slave切换为master，继续工作。好，给定两台机器主机A：192.168.1.201主机B：192.168.1.202一、互为主从1...
配置MySQL主从，配置MySQL主主 +keeplive高可用
最新发布
李文昊的博客
08-14 1669
注意：这个是我两年前的word文档，可以当作参考文档有个思路参考一下，但是里面可能有些地方有误
MySQL主从高可用之Keepalived
只是甲的博客
08-31 1212
文章目录前言一.环境准备二.Keepalived安装三.主从配置文件3.1 keepalived配置文件3.2 keepalived配置文件参数介绍3.3 检查及启停脚本3.4 启动keepalived四.测试4.1 通过vip远程登录mysql4.2 模拟mysql 主库 crash4.3 切换后的处理参考文献 前言 MySQL配置主从后，从库作为容灾或只读库使用，如果主库宕机，需要DBA手工执行主从切换，然后通知应用层修改数据库配置。 宕机时间长，需要人工干预，严重影响用户的使用。 keepalived
mysql主从keeplived_mysql主从+keepalived高可用
weixin_30326905的博客
01-19 499
两台机子，一台是192.168.223.143做主，一台是192.168.223.144主从，现在要实现的需求是两台机子做主从+高可用，VIP这里设置为192.168.223.100首先在两台机子上装好mysql，做好主从复制，二、在两台机子上安装keepalivedtar –zxvf keepalived-1.2.7.tar.gzcdkeepalived-1.2.7yuminstall...
mysql主从复制&mysql+keepalived实现高可用
qq_28197005的博客
11-03 503
一、mysql主从复制 1. mysql安装 另一篇博客已经介绍mysql的安装教程，请移步： https://blog.csdn.net/qq_28197005/article/details/120763590?spm=1001.2014.3001.5501 2. mysql主从复制配置 2.1 mysql主从复制原理概述 Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop
mysql主从同步+MyCat读写分离+分库分表+Haproxy高可用+Keepalived心跳机制+虚拟IP.rar
09-07
MySQL主从同步是一种数据库复制技术，它允许一个MySQL服务器（主服务器）的数据被实时地复制到其他服务器（从服务器）上。这种同步可以确保数据的一致性，并在主服务器出现问题时提供故障转移的能力。实现主从同步的...
mysql主从+keepalived实现高可用
10-11
在当前互联网业务场景中，为了确保数据库服务的连续性和可靠性，采用 MySQL 的主从复制架构结合 Keepalived 来实现高可用性是非常常见的方案。本文档将详细介绍如何通过配置 MySQL 双主双从集群，并借助 Keepalived ...
mysql+keepalived 主从高可用
loosed的博客
03-23 768
一，mysql 安装 centos 7 离线安装 mysql 5.7.31 1，[主]：配置 vi /etc/my.cnf 内容如下： [client] port = 3306 socket = /usr/local/mysql/tmp/mysql.sock [mysqld] port = 3306 basedir = /usr/local/mysql datadir = /usr/local/mysql/data tmpdir = /usr/local/mysql/tmp socke
33.keepalived应用部署
我的博客
01-25 645
谨慎参考，图和原理抄过来的，只有实验是自己做的 一、keepalived概述： 概述：keepalived起初为Lvs设计的一款强大的辅助工具，保证Lvs负载调度器的故障切换以及web节点的健康状态检查，后续被应用到很多需要容错的场景下，keepalived自身基于VRRP协议--虚拟路由冗余协议，思科公有协议； 二、设计原理： 应用场景： 设计模块：1.core模块：为keepal...
kubeadm,kubevip,containerd部署高可用的kubernetes集群
阿白，
05-28 1285
文章目录环境准备安装 Containerd负载均衡器(kube-vip)初始化控制平面添加control plane node添加work node测试高可用Dashboard重新配置前提 单 master 的集群对于生产环境风险太大了，非常有必要做一个高可用的集群，这里的高可用主要是针对控制面板(master节点)来说的，比如 kube-apiserver、etcd、kube-controller-manager、kube-scheduler 这几个组件，其中 kube-controller-manage
解决oracle客户端可以连接11g rac vip，但是不能连接scan ip问题
weixin_34349320的博客
03-02 892
local不用动，remote_listern按我说的设置一下，应该就可以了！ # Public Network - (eth0) 10.4.56.39 syquadb1 10.4.56.40 syquadb2 # Public Virtual IP (VIP) addresses - (eth1) 1...
问题解决：CDH6.3.1初始化scm_prepare_database数据库scm为空
NeverGiveup54的专栏
10-31 5658
问题重现： /opt/cloudera/cm/schema/scm_prepare_database.sh -hhadoop01 --scm-host hadoop01 mysql scm scm 123456 执行后数据库中的scm库没有任何表和数据 原因分析 刚开始报这个错误，心想增加scm的对hadoop01.xx.com访问即可 重新执行： ...
Mysql - 配置Mysql主从复制-keepalived高可用-读写分离集群
lpfstudy的博客
08-11 3122
VRRP（Virtual Router Redundancy Protocol，虚拟路由器冗余协议）是一种网络协议用于提高路由器系统的可用性和可靠性。它通过将多个路由器组合成一个虚拟路由器，只有一个路由器处于活动状态，其他路由器都是备份状态，从而达到提供冗余和故障转移的目的。vip：virtual ip 虚拟IP地址，这个地址是对外提供服务的VRRP原理及配置 - 榕霖 - 博客园 (cnblogs.com)提供冗余。
KeepAlived故障切换VIP邮件通知
m0_38102561的博客
09-10 1137
这是KeepAlived故障切换邮件通知方案一 KeepAlived故障切换邮件通知方案二见:https:// 1、在keepalived配置文件中添加配置 当前节点成为主节点时就会触发/usr/local/sendmail.sh脚本 2、编写邮件通知脚本/usr/local/sendmail.sh #! /usr/bin/perl -w use Net::SMTP_auth; use stri...
RHEL5.4 两个网段通过双网卡，DHCP配置成功
张同光 (Tongguang Zhang)：Hello everyone !
03-30 1109
http://www.net527.cn/a/caozuoxitong/Linux/16205.html 今天设置了dhcpd.conf 两个机房通过一台服务器双网卡的方式，设置成功！ 配置文件和记录如下，该配置文件已上传到126邮箱。 [root@yeger etc]# cat /etc/dhcpd.conf ddns-update-style interim; ignore c
MySQL主从复制+LVS+Keepalived：构建高性能高可用数据库集群
本文主要探讨如何利用MySQL主从复制技术与Linux Virtual Server (LVS) 和 Keepalived 实现数据库系统的高可用性和负载均衡。在这一环境中，我们有两个物理服务器，主机A（192.168.5.171）和主机B（192.168.5.172），...
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值