文章目录
MySQL双主一从 + keepalived高可用 + NFS 部署
主机名 | ip | 数据库主从 | 高可用服务器 | NFS | 系统版本 |
---|---|---|---|---|---|
mysql-slave | 192.168.234.33 | 从 | 无 | 无 | CentOS8 |
mysql-master01 | 192.168.234.22 | 主 | 主 | 无 | CentOS8 |
mysql-master02 | 192.168.234.123 | 主 | 备 | 无 | CentOS8 |
nfs | 192.168.234.100 | 无 | 无 | 有 | CentOS8 |
vip设置为192.168.234.234
1. 部署NFS
1.1 安装nfs
[root@nfs ~]# yum install -y nfs-utils
//启动服务
[root@nfs ~]# systemctl start rpcbind nfs-server
1.2 创建MySQL用户
//注:主主与NFS的用户id需保持一致
[root@nfs ~]# groupadd -r -g 234 mysql
[root@nfs ~]# useradd -r -M -s /sbin/nologin -g 234 -u 234 mysql
1.3 关闭防火墙
[root@nfs ~]# systemctl disable --now 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 ~]# sed -i 's/^SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
1.4 创建共享目录
[root@nfs ~]# mkdir -p /data/nfs/mysql
[root@nfs ~]# chown -R mysql:mysql /data/nfs/mysql/
1.5 配置export
[root@nfs ~]# vim /etc/exports
[root@nfs ~]# cat /etc/exports
/data/nfs/mysql 192.168.234.22(rw,no_root_squash) 192.168.234.123(rw,no_root_squash)
//在mysql-master01装nfs服务
[root@mysql-master01 ~]# yum install -y nfs-utils
[root@mysql-master01 ~]# systemctl enable --now nfs-server
//在mysql-master02装nfs服务
[root@mysql-master02 ~]# yum install -y nfs-utils
[root@mysql-master02 ~]# systemctl enable --now nfs-server
//验证是否nfs是否搭建成功
[root@nfs ~]# showmount -e 192.168.234.22
Export list for 192.168.234.22:
[root@nfs ~]# showmount -e 192.168.234.22
Export list for 192.168.234.22:
[root@nfs ~]# showmount -e 192.168.234.123
Export list for 192.168.234.123:
[root@nfs ~]# showmount
Hosts on nfs:
2. 部署主主MySQL
2.1 mysql-master01配置
//创建用户与目录
[root@mysql-master01 ~]# mkdir -p /data/mysql
[root@mysql-master01 ~]# groupadd -r -g 234 mysql
[root@mysql-master01 ~]# useradd -r -M -s /sbin/nologin -g 234 -u 234 mysql
[root@mysql-master01 ~]#
[root@mysql-slave ~]# chown -R mysql.mysql /data/mysql/
//挂载到nfs目录
[root@mysql-master01 ~]# mount -t nfs 192.168.234.100:/data/nfs/mysql /data/mysql
[root@mysql-master01 ~]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
······················略
192.168.234.100:/data/nfs/mysql nfs4 17G 2.0G 16G 12% /data/mysql ##挂载成功
2.2 mysql-master02配置
//创建用户与目录
[root@mysql-master02 ~]# mkdir -p /data/mysql
[root@mysql-master02 ~]# groupadd -r -g 234 mysql
[root@mysql-master02 ~]# useradd -r -M -s /sbin/nologin -g 234 -u 234 mysql
[root@mysql-master02 ~]#
[root@mysql-slave ~]# chown -R mysql.mysql /data/mysql/
//挂载到nfs目录
[root@mysql-master02 ~]# mount -t nfs 192.168.234.100:/data/nfs/mysql /data/mysql
[root@mysql-master02 ~]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
······················略
192.168.234.100:/data/nfs/mysql nfs4 17G 2.0G 16G 12% /data/mysql ##挂载成功
2.3 将在两台主数据库上源码编译安装MySQL
源码编译安装详细步骤可参考《MySQL基础》,在这里只阐述主要步骤
//注意: msyql-master02操作与msyql-master01的区别在于:不需要再初始化了,利用主初始化好的,进行运行mysql
//mysql-master01操作
[root@mysql-master01 local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/mysql/
[root@mysql-master01 local]# cd
[root@mysql-master01 ~]# echo Xkq7?5Vu1lfT > mysql_password
//启动服务
[root@mysql-master01 ~]# service mysqld start
//设置密码
[root@mysql-master01 ~]# mysql -uroot -p
Enter password:
mysql> set password = password('1');
//停止服务
//注意:mysql-master01与msyql-master02只能同时开启一个
[root@mysql-master01 ~]# service mysqld stop
msyql-master02操作
//注意: msyql-master02操作与msyql-master01的区别在于:此处不需要在初始化了,利用主初始化好的,进行运行mysql
//可以看到密码已经同步
[root@mysql-master02 ~]# mysql -uroot -p1
[root@mysql-master02 ~]# service mysqld start
//停止服务
[root@mysql-master02 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
验证
//在master01上创建数据库,看master02是否成功同步
[root@mysql-master01 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@mysql-master01 ~]# mysql -uroot -p1
mysql> create database kiwi;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[root@mysql-master01 ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
//在master02上查看
[root@mysql-master02 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@mysql-master02 ~]# mysql -uroot -p -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| kiwi |
| mysql |
| performance_schema |
| sys |
+--------------------+
//可以看见同步成功
2.4 配置主从复制的配置文件
//master01配置文件
[root@mysql-master01 mysql]# vim /etc/my.cnf
[root@mysql-master01 mysql]# cat /etc/my.cnf
## 追加下面几行
server-id = 10
log-bin = mysql_bin
[root@mysql-master01 ~]#
//添加master02配置文件
[root@mysql-master02 ~]# vim /etc/my.cnf
[root@mysql-master02 ~]# cat /etc/my.cnf
##追加下面几行
server-id = 10
log-bin = mysql_bin
[root@mysql-master02 ~]#
//在mysql-master01上创建授权用户
[root@mysql-master01 ~]# mysql -uroot -p1
mysql> create user 'kiwi'@'192.168.234.%' identified with mysql_native_password by '1';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'kiwi'@'192.168.234.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//由于做了NFS,所以只需在master02上看有无master状态就行
[root@mysql-master02 ~]# mysql -uroot -p1 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
[root@mysql-master02 ~]#
3. 部署keepalived高可用
3.1 配置状态检查脚本与修改主配置文件
//在mysql-master01上编写
[root@mysql-master01 ~]# vim /kiwi_scripts/check_mysql.sh
[root@mysql-master01 ~]# cat /kiwi_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
//在mysql-master01上配置
[root@mysql-master01 kiwi_scripts]# vim /etc/keepalived/keepalived.conf
[root@mysql-master01 kiwi_scripts]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id msyql-master01_master
}
vrrp_script mysql_check {
script "/kiwi_scripts/check_mysql.sh"
interval 1
}
vrrp_instance VI_1 {
state MASTER
interface ens160
virtual_router_id 111
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass kiwi111
}
virtual_ipaddress {
192.168.234.234
}
track_script {
mysql_check
}
}
virtual_server 192.168.234.234 3306 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.234.22 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
real_server 192.168.234.123 3306 {
weight 1
TCP_CHECK {
connect_port 3306
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
//在mysql-master02上编写脚本
[root@mysql-master02 kiwi_scripts]# cat notify_mysql.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 start
fi
;;
*)
echo "Usage:$0 master|backup"
;;
esac
//mysql-master02主配置文件
[root@mysql-master02 ~]# vim /etc/keepalived/keepalived.conf
[root@mysql-master02 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql-master02_slave
}
vrrp_instance VI_1 {
state BACKUP
interface ens160
virtual_router_id 111
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass kiwi111
}
virtual_ipaddress {
192.168.234.234
}
notify_master "/kiwi_scripts/notify_mysql.sh master"
notify_backup "/kiwi_scripts/notify_mysql.sh backup"
}
virtual_server 192.168.234.234 3306 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.234.22 3306 {
weight 1
TCP_CHECK {
connect_port 8080
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
real_server 192.168.234.123 3306 {
weight 1
TCP_CHECK {
connect_port 8080
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
4. 部署主从
//修改配置文件
[root@mysql-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=mysql-relay-bin
//开始同步
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.234.234',
-> MASTER_USER='kiwi',
-> MASTER_PASSWORD='1',
-> MASTER_LOG_FILE='mysql-bin.000008',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
//查看状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.234.234
Master_User: kiwi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000001
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: