keepalived--MySQL双主一从高可用

实现MySQL双主一从的高可用集群

环境

环境说明

主机角色	IP	主机名	版本
主数据库1	192.168.227.134/24	master1	centos8
主数据库2	192.168.227.153/24	master2	rockylinux9
从数据库	192.168.227.141/24	slave	rockylinux9
NFS服务器	192.168.227.147/24	nfs	rockylinux9

永久关闭所有主机防火墙，

分别在三台数据库主机上源码安装MySQL，版本：mysql-8.0.35

部署NFS服务器

NFS服务端
[root@nfsserver ~]# yum -y install nfs-utils

启动服务端nfs
[root@nfsserver ~]# systemctl start rpcbind nfs-server
[root@nfsserver ~]# systemctl enable --now nfs-server.service
Created symlink /etc/systemd/system/multi-user.target.wants/nfs-server.service → /usr/lib/systemd/system/nfs-server.service.
[root@nfsserver ~]# systemctl status rpcbind
● rpcbind.service - RPC Bind
     Loaded: loaded (/usr/lib/systemd/system/rpcbind.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2024-03-05 15:05:20 CST; 2min 38s ago
TriggeredBy: ● rpcbind.socket
       Docs: man:rpcbind(8)
   Main PID: 11808 (rpcbind)
      Tasks: 1 (limit: 7101)
     Memory: 1.6M
        CPU: 12ms
     CGroup: /system.slice/rpcbind.service
             └─11808 /usr/bin/rpcbind -w -f

3月 05 15:05:20 nfsserver systemd[1]: Starting RPC Bind...
3月 05 15:05:20 nfsserver systemd[1]: Started RPC Bind.

创建共享目录
[root@nfsserver ~]# mkdir /data
[root@nfsserver ~]# chmod 777 /data/

[root@nfsserver ~]# vi /etc/exports
[root@nfsserver ~]# cat /etc/exports
/data 192.168.227.134(rw)
/data 192.168.227.153(rw)
[root@nfsserver ~]# systemctl restart nfs-server.service 



"数据库master01/02客户端"
[root@master01 ~]#: yum -y install nfs-utils
......
[root@master02 ~]#: yum -y install nfs-utils
.....
"master01查看共享目录"
[root@master01 ~]#: showmount -e 192.168.227.147
Export list for 192.168.227.147:
/data 192.168.227.153,192.168.227.134
"master02查看
[root@master02 ~]#: showmount -e 192.168.227.147
Export list for 192.168.227.147:
/data 192.168.227.153,192.168.227.134

配置主数据库

"master01上永久挂载nfs共享目录"
[root@master01 ~]#: vi /etc/fstab 
[root@master01 ~]#: tail -n 1 /etc/fstab 
192.168.227.147:/data /opt/data  nfs defaults,_netdev 0 0

[root@master01 ~]#: mount -a
[root@master01 ~]#: df -h
Filesystem             Size  Used Avail Use% Mounted on
devtmpfs               1.8G     0  1.8G   0% /dev
tmpfs                  1.9G     0  1.9G   0% /dev/shm
tmpfs                  1.9G  9.0M  1.9G   1% /run
tmpfs                  1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/mapper/cs-root     17G  4.6G   13G  28% /
/dev/sda1             1014M  209M  806M  21% /boot
tmpfs                  371M     0  371M   0% /run/user/0
192.168.227.147:/data   17G  1.5G   16G   9% /opt/data

"master02上永久挂载nfs共享目录"
[root@master02 ~]#: vi /etc/fstab
[root@master02 ~]#: tail -n 1 /etc/fstab 
192.168.227.147:/data /opt/data  nfs defaults,_netdev 0 0
[root@master02 ~]#: systemctl daemon-reload 
[root@master02 ~]#: mount -a
[root@master02 ~]#: df -h
Filesystem             Size  Used Avail Use% Mounted on
devtmpfs               4.0M     0  4.0M   0% /dev
tmpfs                  872M     0  872M   0% /dev/shm
tmpfs                  349M  8.3M  341M   3% /run
/dev/mapper/rl-root     17G  4.0G   14G  24% /
/dev/sda1             1014M  218M  797M  22% /boot
tmpfs                  175M     0  175M   0% /run/user/0
192.168.227.147:/data   17G  1.5G   16G   9% /opt/data

修改mysql用户属组主

环境中mysql用户的id要保持一致
[root@master01 ~]#: id mysql 
uid=900(mysql) gid=991(mysql) groups=991(mysql)
[root@master01 ~]#: ll /opt/
total 0
drwxrwxrwx 2 mysql mysql 82 Mar  5 17:19 data

[root@master02 ~]#: id mysql 
uid=900(mysql) gid=991(mysql) groups=991(mysql)
[root@master02 ~]#: ll /opt/
total 0
drwxrwxrwx 2 mysql mysql 82 Mar  5 17:19 data

[root@slave ~]# id mysql 
用户id=900(mysql) 组id=991(mysql) 组=991(mysql)
[root@slave ~]# ll /opt/
总用量 4
drwxr-xr-x. 7 mysql mysql 4096  3月  5 17:11 data

[root@nfsserver ~]# id mysql
用户id=900(mysql) 组id=991(mysql) 组=991(mysql)

[root@nfsserver ~]# ll /
总用量 24
......
drwxrwxrwx.   2 mysql mysql   82  3月  5 17:19 data


初始化数据库
启动数据库并修改密码
设置数据同步

[root@master01 ~]#: scp /usr/local/mysql/support-files/mysql.server 192.168.227.153:/usr/local/mysql/support-files/
The authenticity of host '192.168.227.153 (192.168.227.153)' can't be established.
ECDSA key fingerprint is SHA256:IKwHBb96fOjkLV4Zc8E3mCU/fT40gtWzpzOiWX4rKYs.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.227.153' (ECDSA) to the list of known hosts.
root@192.168.227.153's password: 
mysql.server                                                100%   10KB  13.5MB/s   00:00    

[root@master01 ~]#: scp /usr/lib/systemd/system/mysqld.service 192.168.227.153:/usr/lib/systemd/system/
root@192.168.227.153's password: 
mysqld.service       

配置从数据库

初始化数据库
启动数据库并修改密码

配置双主高可用

"master01节点"
安装keepalived
[root@master01 ~]#: yum -y install keepalived
[root@master01 ~]#: cd /etc/keepalived/
[root@master01 keepalived]#: ls
keepalived.conf
[root@master01 keepalived]#: mv keepalived.conf{,.bak}
[root@master01 keepalived]#: ls
keepalived.conf.bak
[root@master01 keepalived]#: vim keepalived.conf
[root@master01 keepalived]#: cat keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id db01
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160
    virtual_router_id 77
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1
    }
    virtual_ipaddress {
        192.168.227.200
    }
}

virtual_server 192.168.227.200 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

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

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

[root@master01 keepalived]#: scp keepalived.conf 192.168.227.153:/etc/keepalived/
root@192.168.227.153's password: 
keepalived.conf                                             100%  882   901.9KB/s   00:00   
[root@master01 keepalived]#: systemctl enable --now keepalived.service
Created symlink /etc/systemd/system/multi-user.target.wants/keepalived.service → /usr/lib/systemd/system/keepalived.service.





"master02节点"
[root@master02 ~]#: cd /etc/keepalived/
[root@master02 keepalived]#: 
[root@master02 keepalived]#: vi keepalived.conf 
[root@master02 keepalived]#: cat keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id db02
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160
    virtual_router_id 77
    priority 80
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass wangqing
    }
    virtual_ipaddress {
        192.168.227.200
    }
}

virtual_server 192.168.227.200 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

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

    real_server 192.168.227.153 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}
[root@master02 keepalived]#: systemctl enable --now keepalived.service 
Created symlink /etc/systemd/system/multi-user.target.wants/keepalived.service → /usr/lib/systemd/system/keepalived.service.

查看VIP

此时master01节点上有VIP
[root@master01 ~]#: ss -antl
State     Recv-Q    Send-Q        Local Address:Port          Peer Address:Port    Process    
LISTEN    0         128                 0.0.0.0:111                0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:22                 0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:9000               0.0.0.0:*                  
LISTEN    0         128                    [::]:111                   [::]:*                  
LISTEN    0         128                    [::]:22                    [::]:*                  
LISTEN    0         70                        *:33060                    *:*                  
LISTEN    0         128                       *:3306                     *:*                  

[root@master01 ~]#: 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: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:f7:fe:01 brd ff:ff:ff:ff:ff:ff
    inet 192.168.227.134/24 brd 192.168.227.255 scope global dynamic noprefixroute ens160
       valid_lft 1177sec preferred_lft 1177sec
    inet 192.168.227.200/32 scope global ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fef7:fe01/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

数据库授权

[root@master01 ~]#: mysql -uroot -p'Pssword@_'
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 98
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> CREATE USER root@'192.168.227.%'  IDENTIFIED with mysql_native_password BY  'Pssword@_';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO root@'192.168.227.%';
Query OK, 0 rows affected (0.00 sec)

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

授权后可以使用VIP登录MySQL
[root@master02 ~]#: mysql -uroot -p'Pssword@_' -h 192.168.227.200 
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 181
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> 

编写脚本

主节点上MySQL服务要设置开机自启，关闭状态
备节点上MySQL服务不能设置开机自启，关闭状态

[root@master01 ~]#: mkdir /scripts
[root@master01 ~]#: cd /scripts/
[root@master01 scripts]#: vim check_mysql.sh
[root@master01 scripts]#: cat check_mysql.sh 
#!/bin/bash

mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
if [ $mysql_status -lt 1 ];then
    systemctl stop keepalived
fi
[root@master01 scripts]#: ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l
0
[root@master01 scripts]#: chmod +x /scripts/ 
[root@master01 scripts]#: chmod +x check_mysql.sh 
[root@master01 ~]#: systemctl start mysqld
[root@master01 ~]#: ss -antl
State     Recv-Q    Send-Q        Local Address:Port          Peer Address:Port    Process    
LISTEN    0         128                 0.0.0.0:111                0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:22                 0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:9000               0.0.0.0:*                  
LISTEN    0         128                    [::]:111                   [::]:*                  
LISTEN    0         128                    [::]:22                    [::]:*                  
LISTEN    0         70                        *:33060                    *:*                  
LISTEN    0         128                       *:3306                     *:*                  


"master02"
[root@master02 scripts]#: vim notify.sh
[root@master02 scripts]#: cat notify.sh 
#!/bin/bash

case "$1" in
  master)
        mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
        if [ $mysql_status -lt 1 ];then
            systemctl start mysqld
        fi
  ;;
  backup)
        mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
        if [ $mysql_status -gt 0 ];then
            systemctl stop mysqld
        fi
  ;;
  *)
        echo "Usage:$0 master|backup"
  ;;
esac
[root@master01 scripts]#: chmod +x notify.sh

配置keepalived加入监控脚本的配置

配置主keepalived

配置主master01
[root@master01 ~]#: vim /etc/keepalived/keepalived.conf
[root@master01 ~]#: cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id db01
}

vrrp_script mysql_check {                 
    script "/scripts/check_mysql.sh"
    interval 1
    weight -30                
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160
    virtual_router_id 77
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass wangqing
    }
    virtual_ipaddress {
        192.168.227.200
    }

    track_script {                   
        mysql_check
    }

}

virtual_server 192.168.227.200 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

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

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

[root@master01 ~]#: systemctl restart keepalived

配置之后查看VIP在主节点上，MySQL服务启动状态
[root@master01 ~]#: ss -antl
State     Recv-Q    Send-Q        Local Address:Port          Peer Address:Port    Process    
LISTEN    0         128                 0.0.0.0:111                0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:22                 0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:9000               0.0.0.0:*                  
LISTEN    0         128                    [::]:111                   [::]:*                  
LISTEN    0         128                    [::]:22                    [::]:*                  
LISTEN    0         70                        *:33060                    *:*                  
LISTEN    0         128                       *:3306                     *:*                  

[root@master01 ~]#: 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: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:f7:fe:01 brd ff:ff:ff:ff:ff:ff
    inet 192.168.227.134/24 brd 192.168.227.255 scope global dynamic noprefixroute ens160
       valid_lft 1507sec preferred_lft 1507sec
    inet 192.168.227.200/32 scope global ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fef7:fe01/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

配置备keepalived

配置master02
[root@master02 ~]#: vim /etc/keepalived/keepalived.conf
[root@master02 ~]#: cat  /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id db02
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160
    virtual_router_id 77
    priority 80
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1
    }
    virtual_ipaddress {
        192.168.227.200
    }

    notify_master "/scripts/notify.sh master"     #添加两行notify信息
    notify_backup "/scripts/notify.sh backup"

}

virtual_server 192.168.227.200 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

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

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

[root@master02 ~]#: systemctl restart keepalived.service

配置完成后VIP不会在备节点启动，MySQL服务关闭状态
[root@master02 ~]#: 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: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:92:c0:51 brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.227.153/24 brd 192.168.227.255 scope global dynamic noprefixroute ens160
       valid_lft 1395sec preferred_lft 1395sec
    inet6 fe80::20c:29ff:fe92:c051/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master02 ~]#: systemctl status mysqld.service 
● mysqld.service - mysql server daemon
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disable>
     Active: activating (start) since Thu 2024-03-07 09:38:19 CST; 23s ago
Cntrl PID: 19812 (mysql.server)
      Tasks: 15 (limit: 10890)
     Memory: 255.7M
        CPU: 389ms
     CGroup: /system.slice/mysqld.service
             ├─19812 /bin/sh /usr/local/mysql/support-files/mysql.server start
             ├─19825 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=>
             ├─20015 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/da>
             └─20081 sleep 1

Mar 07 09:38:19 master02 systemd[1]: Starting mysql server daemon...

测试keepalived环境

模拟主点MySQL宕机

[root@master01 ~]#: ss -antl
State     Recv-Q    Send-Q        Local Address:Port          Peer Address:Port    Process    
LISTEN    0         128                 0.0.0.0:111                0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:22                 0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:9000               0.0.0.0:*                  
LISTEN    0         128                    [::]:111                   [::]:*                  
LISTEN    0         128                    [::]:22                    [::]:*                  
LISTEN    0         70                        *:33060                    *:*                  
LISTEN    0         128                       *:3306                     *:*                  
[root@master01 ~]#: systemctl stop mysqld.service 
[root@master01 ~]#: ss -antl
State     Recv-Q    Send-Q         Local Address:Port         Peer Address:Port    Process    
LISTEN    0         128                  0.0.0.0:111               0.0.0.0:*                  
LISTEN    0         128                  0.0.0.0:22                0.0.0.0:*                  
LISTEN    0         128                  0.0.0.0:9000              0.0.0.0:*                  
LISTEN    0         128                     [::]:111                  [::]:*                  
LISTEN    0         128                     [::]:22                   [::]:*                  
[root@master01 ~]#: 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: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:f7:fe:01 brd ff:ff:ff:ff:ff:ff
    inet 192.168.227.134/24 brd 192.168.227.255 scope global dynamic noprefixroute ens160
       valid_lft 1656sec preferred_lft 1656sec
    inet6 fe80::20c:29ff:fef7:fe01/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

再查看备节点的MySQL服务状态和VIP是否存在（若满足两条件，则keepalived配置成功）

[root@master02 ~]#: 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: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:92:c0:51 brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.227.153/24 brd 192.168.227.255 scope global dynamic noprefixroute ens160
       valid_lft 1687sec preferred_lft 1687sec
    inet 192.168.227.200/32 scope global ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe92:c051/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master02 ~]#: ss -antl
State     Recv-Q    Send-Q        Local Address:Port          Peer Address:Port    Process    
LISTEN    0         4096                0.0.0.0:111                0.0.0.0:*                  
LISTEN    0         511                 0.0.0.0:80                 0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:22                 0.0.0.0:*                  
LISTEN    0         4096              127.0.0.1:9000               0.0.0.0:*                  
LISTEN    0         4096                   [::]:111                   [::]:*                  
LISTEN    0         128                    [::]:22                    [::]:*                  
LISTEN    0         70                        *:33060                    *:*                  
LISTEN    0         151                       *:3306                     *:*                  

配置主从

在主数据库里创建一个同步账号授权给从数据库使用

[root@master01 ~]#: mysql -uroot -p'Pssword@_'
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 72
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> CREATE USER 'admin'@'192.168.227.141'  IDENTIFIED with mysql_native_password BY  'admin
123';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'admin'@'192.168.227.141';
Query OK, 0 rows affected (0.00 sec)

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

配置主数据库

"master01"
[root@master01 ~]#: vim /etc/my.cnf
[root@master01 ~]#: cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

log-bin = mysql_bin           #添加此行，启用binlog日志
server-id = 10                #添加此行，数据库服务器唯一标识符


"master02上的my.cof文件要与master01一致"
[root@master02 ~]#: vim /etc/my.cnf
[root@master02 ~]#: cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

log-bin = mysql_bin
server-id = 10

重启
[root@master01 ~]#: systemctl restart mysqld

查看主数据库的状态
[root@master01 ~]#: mysql -uroot -p'Pssword@_'
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 42
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从数据库

[root@slave ~]# vim /etc/my.cnf
[root@slave ~]# cat  /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

relay-log=myrelay
server-id=20

[root@slave ~]# systemctl restart mysqld
[root@slave ~]# ss -antl
State     Recv-Q    Send-Q        Local Address:Port          Peer Address:Port    Process    
LISTEN    0         4096              127.0.0.1:9000               0.0.0.0:*                  
LISTEN    0         4096                0.0.0.0:111                0.0.0.0:*                  
LISTEN    0         511                 0.0.0.0:80                 0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:22                 0.0.0.0:*                  
LISTEN    0         70                        *:33060                    *:*                  
LISTEN    0         151                       *:3306                     *:*                  
LISTEN    0         4096                   [::]:111                   [::]:*                  
LISTEN    0         128                    [::]:22                    [::]:*                  
[root@slave ~]# mysql -uroot -p'Pssword@_'
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 8
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> CHANGE MASTER TO
    -> MASTER_HOST='192.168.227.200',
    -> MASTER_USER='admin',
    -> MASTER_PASSWORD='admin123',
    -> MASTER_LOG_FILE='mysql_bin.000001',
    -> MASTER_LOG_POS=157;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.227.200
                  Master_User: admin
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ......
            ......

测试验证

测试主从状态

在主库中创建内容test
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

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

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

从库上已同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.02 sec)

测试主从高可用

模拟主数据库宕机，再确认从库上依旧能获取到主库内容
[root@master01 ~]#: systemctl stop mysqld
[root@master01 ~]#: 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: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:f7:fe:01 brd ff:ff:ff:ff:ff:ff
    inet 192.168.227.134/24 brd 192.168.227.255 scope global dynamic noprefixroute ens160
       valid_lft 1421sec preferred_lft 1421sec
    inet6 fe80::20c:29ff:fef7:fe01/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
       
此时再查看高可用备节点的数据库状态，vip
[root@master02 ~]#: 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: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:92:c0:51 brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.227.153/24 brd 192.168.227.255 scope global dynamic noprefixroute ens160
       valid_lft 1545sec preferred_lft 1545sec
    inet 192.168.227.200/32 scope global ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe92:c051/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master02 ~]#: ss -antl
State     Recv-Q    Send-Q        Local Address:Port          Peer Address:Port    Process    
LISTEN    0         4096                0.0.0.0:111                0.0.0.0:*                  
LISTEN    0         511                 0.0.0.0:80                 0.0.0.0:*                  
LISTEN    0         128                 0.0.0.0:22                 0.0.0.0:*                  
LISTEN    0         4096              127.0.0.1:9000               0.0.0.0:*                  
LISTEN    0         4096                   [::]:111                   [::]:*                  
LISTEN    0         128                    [::]:22                    [::]:*                  
LISTEN    0         70                        *:33060                    *:*                  
LISTEN    0         151                       *:3306                     *:*          
在备节点登录，删除’test‘库
[root@master02 ~]#: mysql -uroot -p'Pssword@_'
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 70
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.02 sec)

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)


在从数据库slave端查看，已被删除
[root@slave ~]# mysql -uroot -p'Pssword@_'
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 15
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)