mysql双主一从高可用
环境准备
主机名 | ip地址 | VIP | 环境说明 |
---|---|---|---|
nfs | 192.168.200.30 | nfs | |
master1 | 192.168.200.10 | 192.168.200.250 | mysql、keepalived |
master2 | 192.168.200.20 | 192.168.200.250 | mysql、keepalived |
slave | 192.168.200.40 | mysql |
master1和master2为主数据库,slave为从数据库
关闭防火墙和selinux(所有主机上都要操作)
[root@master1 ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master1 ~]# setenforce 0
[root@master1 ~]# sed -ri 's/^(SELINUX=).*/\1disabled/g' /etc/selinux/config
配置yum源(所有主机上都要操作)
[root@master1 ~]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 2495 100 2495 0 0 10483 0 --:--:-- --:--:-- --:--:-- 10527
[root@master1 ~]#
在需要部署keepalived服务的两台主机上配置epel源
[root@master1 ~]# yum -y install https://mirrors.aliyun.com/epel/epel-release-latest-8.noarch.rpm
[root@master1 ~]# sed -i 's|^#baseurl=https://download.example/pub|baseurl=https://mirrors.aliyun.com|' /etc/yum.repos.d/epel*
[root@master1 ~]#
[root@master1 ~]# sed -i 's|^metalink|#metalink|' /etc/yum.repos.d/epel*
[root@master1 ~]#
部署nfs
安装nfs
[root@nfs ~]# yum -y install nfs-utils
...
libtirpc.x86_64 0:0.2.4-0.16.el7 libverto-libevent.x86_64 0:0.2.5-4.el7 quota.x86_64 1:4.01-19.el7
quota-nls.noarch 1:4.01-19.el7 rpcbind.x86_64 0:0.2.0-49.el7 tcp_wrappers.x86_64 0:7.6-77.el7
Complete!
[root@nfs ~]#
创建公共目录
[root@nfs ~]# mkdir /opt/data
配置export,添加可挂载的客户端
[root@nfs ~]# vi /etc/exports
[root@nfs ~]# cat /etc/exports
/opt/data 192.168.200.10(rw,sync,no_root_squash)
/opt/data 192.168.200.20(rw,sync,no_root_squash)
[root@nfs ~]#
创建mysql用户和组
[root@nfs ~]# groupadd -r -g 306 mysql
[root@nfs ~]# useradd -r -M -s /sbin/nologin -g 306 -u 306 mysql
[root@nfs ~]#
[root@nfs ~]# id mysql
uid=306(mysql) gid=306(mysql) groups=306(mysql)
[root@nfs ~]#
给data目录添加mysql属主和属组
[root@nfs ~]# chown -R mysql.mysql /opt/data/
[root@nfs ~]#
[root@nfs ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Oct 17 17:55 data
[root@nfs ~]#
设置nfs服务开机自启
[root@nfs ~]# systemctl enable --now nfs
Created symlink from /etc/systemd/system/multi-user.target.wants/nfs-server.service to /usr/lib/systemd/system/nfs-server.service.
[root@nfs ~]#
[root@nfs ~]# systemctl status nfs
● nfs-server.service - NFS server and services
Loaded: loaded (/usr/lib/systemd/system/nfs-server.service; enabled; vendor preset: disabled)
Drop-In: /run/systemd/generator/nfs-server.service.d
└─order-with-mounts.conf
Active: active (exited) since Tue 2023-10-17 22:15:35 CST; 2h 26min ago
Process: 955 ExecStartPost=/bin/sh -c if systemctl -q is-active gssproxy; then systemctl reload gssproxy ; fi
...
[root@nfs ~]#
部署mysql
安装所需的依赖包
[root@master1 ~]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel
创建mysql用户和组
[root@master1 ~]# groupadd -r -g 306 mysql
[root@master1 ~]#
[root@master1 ~]# useradd -r -M -s /sbin/nologin -g 306 -u 306 mysql
[root@master1 ~]#
[root@master1 ~]# id mysql
uid=306(mysql) gid=306(mysql) groups=306(mysql)
[root@master1 ~]#
二进制安装mysql
下载mysql二进制包
[root@master1 ~]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz
[root@master1 ~]# ls
anaconda-ks.cfg mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz
[root@master1 ~]#
[root@master1 ~]# scp mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz 192.168.200.20:/root/
[root@master1 ~]# scp mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz 192.168.200.40:/root/
解压mysql二进制包
[root@master1 ~]# tar xf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@master1 ~]#
[root@master1 ~]# ls /usr/local/
bin etc games include lib lib64 libexec mysql-5.7.39-linux-glibc2.12-x86_64 sbin share src
[root@master1 ~]#
建立软链接
[root@master1 ~]# ln -sv /usr/local/mysql-5.7.39-linux-glibc2.12-x86_64 /usr/local/mysql
'/usr/local/mysql' -> '/usr/local/mysql-5.7.39-linux-glibc2.12-x86_64'
[root@master1 ~]#
[root@master1 ~]# ll /usr/local/
...
drwxr-xr-x. 2 root root 6 Jun 22 2021 libexec
lrwxrwxrwx. 1 root root 46 Oct 17 18:46 mysql -> /usr/local/mysql-5.7.39-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 Oct 17 18:43 mysql-5.7.39-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Jun 22 2021 sbin
drwxr-xr-x. 5 root root 49 Jul 28 10:32 share
drwxr-xr-x. 2 root root 6 Jun 22 2021 src
[root@master1 ~]#
修改目录/usr/local/mysql的属主属组
[root@master1 ~]# chown -R mysql.mysql /usr/local/mysql
[root@master1 ~]#
[root@master1 ~]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 46 Oct 17 18:46 /usr/local/mysql -> /usr/local/mysql-5.7.39-linux-glibc2.12-x86_64
[root@master1 ~]#
添加环境变量
[root@master1 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master1 ~]#
[root@master1 ~]# source /etc/profile.d/mysql.sh
[root@master1 ~]#
[root@master1 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@master1 ~]#
创建存放数据的目
[root@master1 ~]# mkdir /opt/data
[root@master1 ~]#
[root@master1 ~]# chown -R mysql.mysql /opt/data/
[root@master1 ~]#
[root@master1 ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Oct 17 19:01 data
[root@master1 ~]#
安装nfs,并设置开机自启
[root@master1 ~]# yum -y install nfs-utils
[root@master1 ~]# systemctl enable --now nfs-server
Created symlink /etc/systemd/system/multi-user.target.wants/nfs-server.service → /usr/lib/systemd/system/nfs-server.service.
[root@master1 ~]#
[root@master1 ~]# systemctl status nfs-server
● nfs-server.service - NFS server and services
Loaded: loaded (/usr/lib/systemd/system/nfs-server.service; enabled; vendor preset: disabled)
Active: active (exited) since Tue 2023-10-17 19:06:55 CST; 1s ago
...
[root@master1 ~]#
挂载nfs
[root@master1 ~]# vi /etc/fstab
[root@master1 ~]# cat /etc/fstab
...
192.168.200.30:/opt/data /opt/data nfs defaults 0 0
[root@master1 ~]#
[root@master1 ~]# mount -a
[root@master1 ~]# df -Th
...
192.168.200.30:/opt/data nfs4 37G 1.5G 36G 5% /opt/data
[root@master1 ~]#
配置mysql软链接
[root@master1 ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
'/usr/local/include/mysql' -> '/usr/local/mysql/include/'
[root@master1 ~]#
[root@master1 ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@master1 ~]# ldconfig
[root@master1 ~]#
[root@master1 ~]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@master1 ~]#
生成my.cnf配置文件
[root@master1 ~]# vi /etc/my.cnf
[root@master1 ~]# 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@master1 ~]#
配置服务启动脚本
[root@master1 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@master1 ~]#
[root@master1 ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@master1 ~]#
[root@master1 ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
[root@master1 ~]#
添加软链接
[root@master1 ~]# ln -s libncurses.so.6.1 /usr/lib64/libncurses.so.5
[root@master1 ~]# ln -s libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
注意:以上部署mysql的操作要在需要安装mysql的主机上进行,从数据库无需安装和挂载nfs
初始化数据库
由于配了nfs文件共享,只需要初始化一台主数据库,另外一台主数据库会自动同步文件
[root@master1 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
查看两个主数据库目录中的文件是否一致
[root@master1 ~]# ls /opt/data/
auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 performance_schema public_key.pem server-key.pem
ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 mysql private_key.pem server-cert.pem sys
[root@master1 ~]#
[root@master2 ~]# ls /opt/data/
auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 performance_schema public_key.pem server-key.pem
ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 mysql private_key.pem server-cert.pem sys
[root@master2 ~]#
启动数据库
[root@master1 ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/master1.err'.
SUCCESS!
[root@master1 ~]# service mysqld status
SUCCESS! MySQL running (16890)
[root@master1 ~]#
使用临时密码登录,修改数据库密码
[root@master1 ~]# mysql -uroot -p'lXi!Bilur2bo'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql> set password = password('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> quit;
Bye
[root@master1 ~]#
使用修改后的密码登录master2上的数据库
注意:由于两台主机的主数据库文件一样,只能同时启动一个mysql服务
先将master1上的mysql服务停掉
[root@master1 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@master1 ~]#
启动master2上的mysql服务,登录mysql
[root@master2 ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/master2.err'.
SUCCESS!
[root@master2 ~]#
[root@master2 ~]# mysql -uroot -predhat123!
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.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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
[root@master2 ~]#
[root@master2 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@master2 ~]#
登陆成功,说明master1和master2上的主数据库是同步的
mysql主从数据库配置
在主数据库里创建一个同步账号授权给从数据库使用
[root@master1 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@master1 ~]#
[root@master1 ~]# mysql -uroot -predhat123!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql>
mysql> grant replication slave on *.* to 'repl'@'192.168.200.40' identified by 'repl123!';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> quit;
Bye
[root@master1 ~]#
配置主数据库
在master1上修改my.cnf配置文件添加内容,重启mysql服务
[root@master1 ~]# vi /etc/my.cnf
[root@master1 ~]# cat /etc/my.cnf
...
server-id = 10
log-bin = mysql_bin
[root@master1 ~]#
[root@master1 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@master1 ~]#
[root@master1 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@master1 ~]#
先将master1上的mysql服务停掉,然后在master2上修改my.cnf配置文件添加内容,重启mysql服务
[root@master2 ~]# vi /etc/my.cnf
[root@master2 ~]# cat /etc/my.cnf
...
server-id = 11
log-bin = mysql_bin
[root@master2 ~]#
[root@master2 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@master2 ~]#
[root@master2 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@master2 ~]#
部署keepalived高可用
安装keepalived
[root@master1 ~]# yum -y install keepalived
[root@master2 ~]# yum -y install keepalived
配置keepalived脚本监控负载均衡机状态
在master1上创建脚本并加上执行权限
[root@master1 ~]# vi /scripts/check_mysql.sh
[root@master1 ~]# cat /scripts/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@master1 ~]#
[root@master1 ~]# chmod +x /scripts/check_mysql.sh
[root@master1 ~]#
[root@master1 ~]# ll /scripts/
total 4
-rwxr-xr-x. 1 root root 140 Oct 17 21:24 check_mysql.sh
[root@master1 ~]#
编辑keepalived.conf配置文件,将端口改为3306
[root@master1 ~]# mkdir /scripts
[root@master1 ~]# cp /etc/keepalived/keepalived.conf /opt/
[root@master1 ~]# vi /etc/keepalived/keepalived.conf
[root@master1 ~]#
[root@master1 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql01
}
vrrp_script check_mysql {
script "/scripts/check_mysql.sh"
interval 1
weight -50
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 80
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
192.168.200.250
}
track_script {
check_mysql
}
}
virtual_server 192.168.200.250 3306 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.200.10 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
real_server 192.168.200.20 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
[root@master1 ~]#
设置keepalived服务开机自启,并查看VIP
[root@master1 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@master1 ~]#
[root@master1 ~]# systemctl enable --now keepalived
Created symlink /etc/systemd/system/multi-user.target.wants/keepalived.service → /usr/lib/systemd/system/keepalived.service.
[root@master1 ~]#
[root@master1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 00:0c:29:80:df:b8 brd ff:ff:ff:ff:ff:ff
inet 192.168.200.10/24 brd 192.168.200.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.200.250/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe80:dfb8/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[root@master1 ~]#
在master2上创建脚本并加上执行权限
[root@master2 ~]# cat /scripts/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 ~]#
[root@master2 ~]# chmod +x /scripts/notify.sh
[root@master2 ~]#
[root@master2 ~]# ll /scripts/
total 4
-rwxr-xr-x. 1 root root 422 Oct 17 21:47 notify.sh
[root@master2 ~]#
编辑keepalived.conf配置文件,将端口改为3306
[root@master2 ~]# mkdir /scripts
[root@master2 ~]#
[root@master2 ~]# cp /etc/keepalived/keepalived.conf /opt/
[root@master2 ~]#
[root@master2 ~]# rm -rf /etc/keepalived/keepalived.conf
[root@master2 ~]#
[root@master2 ~]# scp 192.168.200.10:/etc/keepalived/keepalived.conf /etc/keepalived/
...
root@192.168.200.10's password:
keepalived.conf 100% 1012 871.0KB/s 00:00
[root@master2 ~]#
[root@master2 ~]# vi /etc/keepalived/keepalived.conf
[root@master2 ~]#
[root@master2 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql02
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 80
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
192.168.200.250
}
notify_master "/scripts/notify.sh master"
notify_backup "/scripts/notify.sh backup"
}
virtual_server 192.168.200.250 3306 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.200.10 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
real_server 192.168.200.20 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
[root@master2 ~]#
设置keepalived服务开机自启
[root@master2 ~]# systemctl enable --now keepalived
Created symlink /etc/systemd/system/multi-user.target.wants/keepalived.service → /usr/lib/systemd/system/keepalived.service.
[root@master2 ~]#
[root@master2 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2023-10-17 22:03:59 CST; 6s ago
Process: 21649 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
...
[root@master2 ~]#
配置从数据库
修改my.cnf配置文件
[root@slave ~]# vi /etc/my.cnf
[root@slave ~]#
[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
server-id = 20
relay-log = myrelay
[root@slave ~]#
初始化从数据库,并启动数据库
[root@slave ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
[root@slave ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/slave.err'.
SUCCESS!
[root@slave ~]#
使用临时密码登录,修改数据库密码
[root@slave ~]# mysql -uroot -p'1&ryUdCkg&09'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql>
mysql> set password = password('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit;
Bye
[root@slave ~]#
配置并启动主从数据库复制
[root@slave ~]# mysql -uroot -predhat123!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql> change master to
-> master_host='192.168.200.250',
-> master_user='repl',
-> master_password='repl123!',
-> master_log_file='mysql_bin.000005',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.250
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Slave_IO_Running和Slave_SQL_Running这两处必须为yes
测试验证
在master1上创建一个数据库
[root@master1 ~]# mysql -uroot -predhat123!
mysql: [Warning] Using a password on the command line interface can be insecure.
...
mysql> create database xinge;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xinge |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> quit;
Bye
[root@master1 ~]#
在从数据库查看,数据同步成功
[root@slave ~]# mysql -uroot -predhat123! -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xinge |
+--------------------+
[root@slave ~]#
模拟宕机
当手动关闭master1上的mysqld服务,脚本会自动执行停掉keepalived服务
[root@master1 ~]# service mysqld stop
Shutting down MySQL............ SUCCESS!
[root@master1 ~]#
[root@master1 ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Tue 2023-10-17 23:28:46 CST; 11s ago
Process: 38343 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
...
[root@master1 ~]#
[root@master1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 00:0c:29:80:df:b8 brd ff:ff:ff:ff:ff:ff
inet 192.168.200.10/24 brd 192.168.200.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe80:dfb8/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[root@master1 ~]#
此时VIP被master2抢了过来,同时mysql服务自动开启
[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: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 00:0c:29:18:11:8c brd ff:ff:ff:ff:ff:ff
inet 192.168.200.20/24 brd 192.168.200.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.200.250/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe18:118c/64 scope link noprefixroute
valid_lft forever preferred_lft forever
[root@master2 ~]#
[root@master2 ~]# ss -anlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
...
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:20048 [::]:*
[root@master2 ~]#
在master2上创建数据库
[root@master2 ~]# mysql -uroot -predhat123! -e "create database mhy;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 ~]#
[root@master2 ~]# mysql -uroot -predhat123! -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mhy |
| mysql |
| performance_schema |
| sys |
| xinge |
+--------------------+
[root@master2 ~]#
数据同步成功
[root@slave ~]# mysql -uroot -predhat123! -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mhy |
| mysql |
| performance_schema |
| sys |
| xinge |
+--------------------+
[root@slave ~]#