MySQL双主一从高可用

最新推荐文章于 2024-05-22 15:20:28 发布

摆烂cc

最新推荐文章于 2024-05-22 15:20:28 发布

阅读量67

点赞数

文章标签： mysql adb 数据库

本文链接：https://blog.csdn.net/nmlg6665352/article/details/134179223

版权

MySQL双主一从高可用

环境说明：

主机名	IP	角色	安装的服务	操作系统
nfs	192.168.16.128	nfs服务器	nfs	centos-8
master(vip)	192.168.16.130	mysql主服务器（主节点）	mysql+keepalived	centos-8
master2（vip）	192.168.16.131	mysql主服务器（备节点）	mysql+keepalived	rh8
slave	192.168.16.132	mysql主服务器	mysql	rh8

基本配置

所有主机都要配置

# 关闭防火墙和selinux

1. 在nfs主机上
[root@nfs ~]# systemctl stop firewalld
[root@nfs ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@nfs ~]# setenforce 0
[root@nfs ~]# vim /etc/yum.repos.d
[root@nfs ~]# cat /etc/selinux/config 

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

2.在master主机上
[root@master ~]# systemctl stop firewalld
[root@master ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
se[root@master ~]# setenforce 0
[root@master ~]# vim /etc/selinux/config 
[root@master ~]# cat /etc/selinux/config 

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

3. 在master2主机上
[root@master2 ~]# systemctl stop firewalld
[root@master2 ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master2 ~]# setenforce 0
[root@master2 ~]# vim /etc/selinux/config 
[root@master2 ~]# cat /etc/selinux/config 

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

4. 在slave主机上
[root@slave ~]# systemctl stop firewalld
[root@slave ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@slave ~]# setenforce 0
[root@slave ~]# vim /etc/selinux/config 
[root@slave ~]# cat /etc/selinux/config 

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

配置阿里云源并且master主机和master2主机还要配置epel源

# 在nfs主机上
[root@nfs yum.repos.d]# rm -rf *
[root@nfs yum.repos.d]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo

[root@nfs yum.repos.d]# sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentOS-Base.repo
[root@nfs yum.repos.d]# yum clean all
27 files removed
[root@nfs yum.repos.d]# yum makecache

# 在master主机上
[root@master yum.repos.d]# rm -rf *
[root@master yum.repos.d]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo

[root@master yum.repos.d]# sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentOS-Base.repo
[root@master yum.repos.d]# yum clean all
27 files removed
[root@master yum.repos.d]# yum makecache
[root@master yum.repos.d]# yum install -y https://mirrors.aliyun.com/epel/epel-release-latest-8.noarch.rpm
[root@master yum.repos.d]# sed -i 's|^#baseurl=https://download.example/pub|baseurl=https://mirrors.aliyun.com|' /etc/yum.repos.d/epel*
[root@master yum.repos.d]# sed -i 's|^metalink|#metalink|' /etc/yum.repos.d/epel*
[root@master yum.repos.d]# yum makecache


# 在master2主机上
[root@master2 yum.repos.d]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo

[root@master2 yum.repos.d]# sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentOS-Base.repo
[root@master2 yum.repos.d]# yum clean all
27 files removed
[root@master2 yum.repos.d]# yum makecache
[root@master2 yum.repos.d]# yum install -y https://mirrors.aliyun.com/epel/epel-release-latest-8.noarch.rpm
[root@master2 yum.repos.d]# sed -i 's|^#baseurl=https://download.example/pub|baseurl=https://mirrors.aliyun.com|' /etc/yum.repos.d/epel*
[root@master2 yum.repos.d]# sed -i 's|^metalink|#metalink|' /etc/yum.repos.d/epel*
[root@master2 yum.repos.d]# yum makecache


# 在slave主机上
[root@slave yum.repos.d]# rm -rf *
[root@slave yum.repos.d]# ls
[root@slave yum.repos.d] curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo

[root@slave yum.repos.d]# sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentOS-Base.repo
[root@slave yum.repos.d]# yum clean all
已加载插件：fastestmirror
正在清理软件源： base extras updates
Cleaning up everything
Maybe you want: rm -rf /var/cache/yum, to also free up space taken by orphaned data from disabled or removed repos
yCleaning up list of fastest mirrors
[root@slave yum.repos.d]# yum makecache

部署nfs服务

在nfs主机上

# 安装nfs
[root@nfs ~]# yum list all |grep nfs
...........省略
nfs-utils.x86_64                                       1:2.3.3-46.el8                                         base      
nfs4-acl-tools.x86_64                                  0.3.5-3.el8                                            base      
pcp-pmda-nfsclient.x86_64                              5.3.1-5.el8           

[root@nfs ~]# yum -y install nfs-utils.x86_64

# 创建公共目录
[root@nfs ~]# mkdir /opt/data
[root@nfs ~]# vim /etc/exports
[root@nfs ~]# cat /etc/exports
/opt/data 192.168.136.140(rw,sync,no_root_squash)
/opt/data 192.168.136.142(rw,sync,no_root_squash)

# 创建MySQL用户，并修改属主
[root@nfs ~]# groupadd -r -g 306 mysql
[root@nfs ~]# useradd -r -M -s /sbin/nologin -g 306 -u 306 mysql
[root@nfs ~]# chown -R mysql.mysql /opt/data/

# 启动服务
[root@nfs ~]# systemctl restart rpcbind.service
[root@nfs ~]# systemctl restart nfs-server.service
[root@nfs ~]# systemctl enable nfs-server.service 
Created symlink /etc/systemd/system/multi-user.target.wants/nfs-server.service → /usr/lib/systemd/system/nfs-server.service.
[root@nfs ~]#

安装主数据库的数据库服务，并挂载nfs

在master 和 master2 安装数据库

# 在master上配置

1.创建mysql用户
[root@master ~]# groupadd -r -g 306 mysql
[root@master ~]# useradd -r -M -s /sbin/nologin -g 306 -u 306 mysql

2.下载mysql包
[root@master ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.42-el7-x86_64.tar.gz

3.解压mysql包，并指定解压路径
[root@master ~]# tar -xf mysql-5.7.42-el7-x86_64.tar.gz -C /usr/local/

4.创建软链接
[root@master ~]# ln -sv /usr/local/mysql-5.7.42-el7-x86_64/ /usr/local/mysql
'/usr/local/mysql' -> '/usr/local/mysql-5.7.42-el7-x86_64/'

5.修改目录/usr/local/mysql的属主和属组
[root@master ~]# chown -R mysql:mysql /usr/local/mysql

6.添加环境变量
[root@master ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master ~]# source /etc/profile.d/mysql.sh

7.建立数据存放目录
[root@master ~]# mkdir /opt/data
[root@master ~]# chown -R mysql.mysql /opt/data/

8.挂载nfs上的/opt/data目录，方便同步
[root@master ~]# yum -y install nfs-utils.x86_64
[root@master ~]# systemctl enable --now nfs-server.service 
[root@master ~]# mount -t nfs 192.168.136.139:/opt/data /opt/data //ip为nfs主机的ip

9.永久挂载nfs共享目录
[root@master ~]# vim /etc/fstab
[root@master ~]# cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Thu Jul 27 13:52:45 2023
#
# Accessible filesystems, by reference, are maintained under '/dev/disk/'.
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info.
#
# After editing this file, run 'systemctl daemon-reload' to update systemd
# units generated from this file.
#
/dev/mapper/cs-root     /                       xfs     defaults        0 0
UUID=8ed9e77d-0bb9-4723-a705-21f3d6c10813 /boot                   xfs     defaults        0 0
/dev/mapper/cs-swap     none                    swap    defaults        0 0
192.168.136.139:/opt/data /opt/data nfs defaults,_netdev 0 0 //添加这行
[root@master ~]# mount -a

10.配置mysql
[root@master ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
'/usr/local/include/mysql' -> '/usr/local/mysql/include/'
[root@master ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@master ~]# ldconfig

11.生成配置文件
[root@master ~]# vim /etc/my.cnf
[root@master ~]# 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
[root@master ~]# 

12.配置服务启动脚本
[root@master ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@master ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@master ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

# 在master2 上配置
[root@master2 ~]# groupadd -r -g 306 mysql
[root@master2 ~]# useradd -r -M -s /sbin/nologin -g 306 -u 306 mysql

[root@master2 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.42-el7-x86_64.tar.gz

[root@master2 ~]# tar -xf mysql-5.7.42-el7-x86_64.tar.gz -C /usr/local/
[root@master2 ~]# ln -sv /usr/local/mysql-5.7.39-linux-glibc2.12-x86_64/ /usr/local/mysql

[root@master2 ~]# chown -R mysql:mysql /usr/local/mysql
[root@master2 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master2 ~]#  source /etc/profile.d/mysql.sh

[root@master2 ~]# mkdir /opt/data
[root@master2 ~]# chown -R mysql.mysql /opt/data/

[root@master2 ~]# yum -y install nfs-utils.x86_64
[root@master2 ~]# systemctl enable --now nfs-server.service
[root@master2 ~]# mount -t nfs 192.168.136.139:/opt/data /opt/data

[root@master2 ~]# vim /etc/fstab 
[root@master2 ~]# cat /etc/fstab 

#
# /etc/fstab
# Created by anaconda on Wed Aug  2 15:02:18 2023
#
# Accessible filesystems, by reference, are maintained under '/dev/disk/'.
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info.
#
# After editing this file, run 'systemctl daemon-reload' to update systemd
# units generated from this file.
#
/dev/mapper/cs-root     /                       xfs     defaults        0 0
UUID=51d399ea-e737-4afd-9b79-95498239b392 /boot                   xfs     defaults        0 0
/dev/mapper/cs-swap     none                    swap    defaults        0 0
192.168.136.139:/opt/data /opt/data nfs defaults,_netdev 0 0 //添加这行
[root@master2 ~]# mount -a

[root@master2 ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
'/usr/local/include/mysql' -> '/usr/local/mysql/include/'
[root@master2 ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@master2 ~]# ldconfig

[root@master2 ~]# vim /etc/my.cnf
[root@master2 ~]# 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
[root@master2 ~]# 

[root@master2 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@master2 ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@master2 ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

初始化数据库

因为配置了nfs同步两台主机的/opt/data目录，所以只需在其中一台上初始化数据库即可，另一台会同步文件

# 只在master主机上初始化数据库

[root@master ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2023-10-19T13:26:16.367785Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-10-19T13:26:19.178193Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-10-19T13:26:19.463371Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-10-19T13:26:19.546624Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 16aff5fb-6e83-11ee-b298-000c29a4aaa2.
2023-10-19T13:26:19.550919Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-10-19T13:26:19.831613Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-10-19T13:26:19.831644Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-10-19T13:26:19.835883Z 0 [Warning] CA certificate ca.pem is self signed.
2023-10-19T13:26:20.213621Z 1 [Note] A temporary password is generated for root@localhost: P%s)ycs8=96z

# 查看master和master2主机/opt/data目录的内容
[root@master ~]# ls /opt/data
auto.cnf    client-cert.pem  ibdata1      mysql               public_key.pem   sys
ca-key.pem  client-key.pem   ib_logfile0  performance_schema  server-cert.pem
ca.pem      ib_buffer_pool   ib_logfile1  private_key.pem     server-key.pem

# master2主机
[root@master2 ~]# ls /opt/data/
auto.cnf         client-key.pem  ib_logfile1         public_key.pem
ca-key.pem       ib_buffer_pool  mysql               server-cert.pem
ca.pem           ibdata1         performance_schema  server-key.pem
client-cert.pem  ib_logfile0     private_key.pem     sys
[root@master2 ~]#

配置主数据库服务器

# 先在master 和 master2 安装一个库文件包
[root@master ~]# yum -y install ncurses-compat-libs

[root@master2 ~]# yum -y install ncurses-compat-libs

# 在master主机
启动MySQL服务，不可以与master2同时启动，避免冲突
[root@master ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/master.err'.
 SUCCESS! 
[root@master ~]#

进入数据库并设置密码

[root@master ~]mysql -uroot -p 
Enter password:  //这里密码是用的临时密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.42

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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

关闭master的数据库服务，使用修改过后的密码登录master2的数据库

# 关闭masterMySQL服务
[root@master ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@master ~]# 

# 开启master2 mysql服务
[root@master2 ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/master2.err'.
. SUCCESS! 
[root@master2 ~]# mysql -uroot -p123456 //这里的密码是用的master修改的密码
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.42 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> quit
Bye

# 关闭master2的MySQL服务
[root@master2 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@master2 ~]#

配置master 和 master2 为主数据库

# 在master主机上
[root@master ~]# service mysqld start 
Starting MySQL. SUCCESS! 
[root@master ~]# mysql -uroot -p123456
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.42 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 'tq'@'%' identified by 'tq123';
Query OK, 0 rows affected (0.02 sec)

mysql> grant replication slave on *.* to 'tq'@'%';  
Query OK, 0 rows affected (0.01 sec)

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

mysql> quit
Bye
[root@master ~]# 
# 修改配置文件
[root@master ~]# vim /etc/my.cnf 
[root@master ~]# 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@master ~]# 

# 重启服务
[root@master ~]# service mysqld stop 
Shutting down MySQL.. SUCCESS! 
[root@master ~]# service mysqld start
Starting MySQL. SUCCESS! 
[root@master ~]# 

# 然后关闭，开启master2
[root@master ~]# service mysqld stop 
Shutting down MySQL.. SUCCESS!

# 在master2主机
配置m2，由于/opt/data的是同步的，所以不需要再创建用户授权，只用改配置文件
[root@master2 ~]# vim /etc/my.cnf 
[root@master2 ~]# 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=11 //添加 
[root@master2 ~]# service mysqld start 
Starting MySQL. SUCCESS! 
[root@master2 ~]# service mysqld stop 
Shutting down MySQL.. SUCCESS! 
[root@master2 ~]#

在master 和master2 安装keepalived

# 在master主机
[root@master ~]# yum -y install keepalived.x86_64

# 创建目录并写脚本
[root@master ~]# mkdir /scripts
[root@master ~]# cd /scripts/
[root@master scripts]# vim check_mysql.sh
[root@master 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@master scripts]# chmod +x check_mysql.sh 

# 把原配置备份，然后修改
[root@master ~]# cp /etc/keepalived/keepalived.conf /opt/
[root@master ~]# vim /etc/keepalived/keepalived.conf
[root@master ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id mysql01 //唯一，路由的id
}

vrrp_script check_mysql {
    script "/scripts/check_mysql.sh" //脚本路径
    interval 1
    fall 3
    weight -20
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160 
    virtual_router_id 90 #虚拟路由器id，两台主机要一样
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456 //密码
    }
    virtual_ipaddress {
        192.168.16.200 //配置vip
    }
    track_script {        
        check_mysql #追踪脚本
    }
}

virtual_server 192.168.16.200 { //vip配置
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 192.168.16.130 3306 { //master的ip
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
    real_server 192.168.16.131 3306 {   //maste2的IP
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}

# 重启keepalived
[root@master ~]# systemctl restart keepalived.service

# 在主机master2主机
[root@master2 ~]# yum -y install keepalived.x86_64

# 写脚本
[root@master2 scripts]# vim notify.sh
[root@master2 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
            service mysqld start
        fi
  ;;
  backup)
        mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
        if [ $mysql_status -gt 0 ];then
            service mysqld stop
        fi
  ;;
  *)
        echo "Usage:$0 master|backup"
  ;;
esac
[root@master2 scripts]# chmod +x notify.sh 
[root@master2 scripts]# 

# 先将原配置文件备份一下，然后修改，生成一个新的配置文件
[root@master2 keepalived]# mv keepalived.conf /opt/
[root@master2 keepalived]# ll
total 0
[root@master2 keepalived]# scp root@192.168.16.130:/etc/keepalived/keepalived.conf .
root@192.168.16.130's password: 
keepalived.conf                              100% 1040   884.2KB/s   00:00    
[root@master2 keepalived]# ll
total 4
-rw-r--r--. 1 root root 1040 Oct 19 17:16 keepalived.conf
[root@master2 keepalived]# vim keepalived.conf 
[root@master2 keepalived]# cat keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id mysql02 //修改路由id
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens160
    virtual_router_id 90
    priority 90 //修改优先级
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456 //密码
    }
    virtual_ipaddress {
        192.168.136.250
    }
    notify_master "/scripts/notify.sh master"   #追踪脚本
    notify_backup "/scripts/notify.sh backup"   #追踪脚本
}

virtual_server 192.168.16.200 {  //vip
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 192.168.16.130 3306 {  master的ip
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
    real_server 192.168.16.131 3306 {   master2的ip
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}
[root@master2 keepalived]# 

# 重启keepalived服务
[root@master2 keepalived]# systemctl restart keepalived.service

查看vip

//手动启动master的数据库服务，当前应该是keepalived服务和mysql服务都是启动的，有VIP
[root@master ~]#  service mysqld start 
[root@master ~]# systemctl start keepalived.service 
[root@master ~]# systemctl is-active keepalived.service 
active
[root@master ~]#  ss -anlt | grep 3306
LISTEN 0      80                 *:3306             *:*   
[root@master ~]# 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:a4:aa:a2 brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.16.130/24 brd 192.168.136.255 scope global dynamic noprefixroute ens160
       valid_lft 1763sec preferred_lft 1763sec
    inet 192.168.16.200/32 scope global ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fea4:aaa2/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master ~]#

[root@master2 ~]# systemctl start keepalived.service 
[root@master2 ~]# systemctl is-active keepalived.service 
active
[root@master2 ~]# ss -antl | grep 3306
[root@master2 ~]# 

[root@master2 scripts]# 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:70:02:d8 brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.16.132/24 brd 192.168.136.255 scope global dynamic noprefixroute ens160
       valid_lft 986sec preferred_lft 986sec
    inet6 fe80::20c:29ff:fe70:2d8/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master2 scripts]#

安装从数据库

# 在slave主机

[root@slave ~]# groupadd -r -g 306 mysql
[root@slave ~]# useradd -r -M -s /sbin/nologin -g 306 -u 306 mysql
 
 # 解压MySQL
 [root@slave ~]# tar -xf mysql-5.7.42-el7-x86_64.tar.gz -C /usr/local/
[root@slave ~]# ln -sv /usr/local/mysql-5.7.42-el7-x86_64/ /usr/local/mysql
"/usr/local/mysql" -> "/usr/local/mysql-5.7.42-el7-x86_64/"

#修改目录/usr/local/mysql的属主属组
[root@slave ~]# chown -R mysql:mysql /usr/local/mysql

# 添加环境变量
[root@slave ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@slave ~]# source /etc/profile.d/mysql.sh
[root@slave ~]# mkdir /opt/data
[root@slave ~]# chown -R mysql.mysql /opt/data/

# 初始化数据库
[root@slave ~]#  /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2023-10-19T14:44:35.988200Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-10-19T14:44:36.367939Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-10-19T14:44:36.414747Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-10-19T14:44:36.428872Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 063f5185-6e8e-11ee-96ed-000c291180d7.
2023-10-19T14:44:36.429814Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-10-19T14:44:36.689881Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-10-19T14:44:36.689904Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-10-19T14:44:36.691267Z 0 [Warning] CA certificate ca.pem is self signed.
2023-10-19T14:44:36.900223Z 1 [Note] A temporary password is generated for root@localhost: e>)yKI?Pv5tr
[root@slave ~]# 

# 配置mysql
[root@slave ~]#  ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
"/usr/local/include/mysql" -> "/usr/local/mysql/include/"
[root@slave ~]#  echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@slave ~]# ldconfig
[root@slave ~]# 

# 生成配置文件
[root@slave etc]# vim my.cnf
[root@slave etc]# cat 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=mysql-relay-bin 
server-id=20
[root@slave etc]# 

配置服务启动脚本
[root@slave etc]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@slave etc]#  sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@slave etc]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
[root@slave etc]#

设置密码

[root@slave etc]# service mysqld start 
Starting MySQL.Logging to '/opt/data/slave.err'.
 SUCCESS! 
[root@slave etc]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.42

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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> quit
Bye
[root@slave etc]#

配置从服务器

# 认vip的那台主机为主

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

# 在slave主机
[root@slave ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.42 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.136.250',
    ->  master_user='rl',
    ->  master_password='rl123',
    ->  master_log_file='mysql-bin.000005',
    ->  master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave；
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.200
                  Master_User: tq
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes //这里为yes
            Slave_SQL_Running: Yes //这里为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
 .........省略

测试效果

# master为主，master2为备

1. 查看主机master
[root@master ~]# systemctl is-active keepalived.service
active
[root@master ~]#  ss -anlt | grep 3306
LISTEN 0      80                 *:3306             *:*          
[root@master ~]# ip addr show ens160
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:a4:aa:a2 brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.16.130/24 brd 192.168.136.255 scope global dynamic noprefixroute ens160
       valid_lft 1423sec preferred_lft 1423sec
    inet 192.168.16.200/32 scope global ens160 //vip在master主机
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fea4:aaa2/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master ~]# 

2. 查看主机master2

[root@master2 ~]# systemctl is-active keepalived.service 
active //服务也是启动的
[root@master2 ~]# ss -anlt | grep 3306
[root@master2 ~]# ip addr show ens160
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:70:02:d8 brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.16.131/24 brd 192.168.136.255 scope global dynamic noprefixroute ens160
       valid_lft 1366sec preferred_lft 1366sec
    inet6 fe80::20c:29ff:fe70:2d8/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master2 ~]# 

# 在master主机数据库中创建数据库，查看是否同步
[root@master ~]# mysql -uroot -p123456 -e "create database ttq;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 344
Server version: 5.7.42-log 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                |
| ttq                |
+--------------------+
5 rows in set (0.03 sec)

mysql> 

# 在从服务器slave主机上查看，看到ttq库表示同步成功
[root@slave ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.42 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                |
| ttq                |
+--------------------+
5 rows in set (0.00 sec)

2.当原来的主（m1）发生故障，那么m1会自动关闭keepalived服务，释放资源。备节点（m2）就会启动mysql服务，并且抢来vip。接替工作

# 手动关闭master的数据库，模拟宕机
[root@master ~]# service mysqld stop 
Shutting down MySQL............ SUCCESS! 
[root@master ~]#

# 在主机master查看MySQL和keepalived服务
[root@master ~]#  systemctl is-active keepalived.service 
inactive
[root@master ~]# ss -anlt | grep 3306
[root@master ~]# 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:a4:aa:a2 brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.16.130/24 brd 192.168.136.255 scope global dynamic noprefixroute ens160
       valid_lft 1689sec preferred_lft 1689sec
    inet6 fe80::20c:29ff:fea4:aaa2/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master ~]# 

# 再看master2主机的两个服务是启动的，并且有vip
[root@master2 ~]# systemctl is-active keepalived.service 
active
[root@master2 ~]# ss -anlt | grep 3306
LISTEN 0      80                 *:3306             *:*          
[root@master2 ~]# 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:70:02:d8 brd ff:ff:ff:ff:ff:ff
    altname enp3s0
    inet 192.168.16.131/24 brd 192.168.136.255 scope global dynamic noprefixroute ens160
       valid_lft 1573sec preferred_lft 1573sec
    inet 192.168.16.200/32 scope global ens160
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe70:2d8/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

# 在master2登录mysql，创建数据库，然后查看从服务器是否同步
[root@master2 ~]# mysql -uroot -p123456 -e "create database ttq1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 ~]# mysql -uroot -p123456
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 52
Server version: 5.7.42-log 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                |
| ttq                |
| ttq1     //创建成功           |
+--------------------+
6 rows in set (0.04 sec)

mysql> 


# 在slave主机从服务器查看
[root@slave ~]# mysql -uroot -p123456
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 7
Server version: 5.7.42 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                |
| ttq                |
| ttq1       //同步成功        |
+--------------------+
6 rows in set (0.00 sec)

mysql>