双主+keepalived高可用：话不多说直接开干
1.准备工作
1.1两台centos7.9服务器
mysql-1：192.168.0.113
mysql-2：192.168.0.111
1.2mysql安装包
版本：mysql-5.7.26-el7-x86_64.tar.gz
下载地址：https://cdn.mysql.com/archives/mysql-5.7/mysql-test-5.7.26-el7-x86_64.tar.gz
1.3keepalived安装包
版本：keepalived-2.2.7.tar.gz
下载地址：https://www.keepalived.org/software/keepalived-2.2.7.tar.gz
2.安装mysql
2.1下载并上传软件至 ~目录
2.2解压软件
[root@test2 ~]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@test2 ~]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /opt/mysql
2.3创建用户处理原始环境
#卸载系统自带mariadb
[root@test2 mysql] yum remove mariadb-libs-5.5.68-1.el7.x86_64 -y
#查看系统自带mariadb
[root@test2 mysql] rpm -qa |grep mariadb
#创建mysql系统用户
[root@test2 mysql] useradd -s /sbin/nologin mysql
2.4 设置环境变量
vim /etc/profile
export PATH=/opt/mysql/bin:$PATH
[root@test2 ~]# source /etc/profile
[root@test2 ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper
2.5创建数据路径并授权（无新磁盘跳过）
#查看磁盘信息
[root@test2 mysql]# fdisk -l
磁盘 /dev/sdb：107.4 GB, 107374182400 字节，209715200 个扇区
#格式化
[root@test2 ~]# mkfs.xfs /dev/sdb
#创建数据目录
[root@test2 ~]# mkdir /data
#查看磁盘UUID
[root@test2 ~]# blkid
#修改磁盘管理文件
[root@test2 ~]# vim /etc/fstab
#修改文件将sdb磁盘 挂载到/data目录
[root@test2 ~]# UUID="eac66d23-c5db-4d22-8e4e-e5e30e8ae37c" /data xfs defaults 0 0
#挂载
[root@test2 ~]# mount -a
[root@test2 data]# df -h
文件系统 容量 已用 可用 已用% 挂载点
devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs 1.9G 0 1.9G 0% /dev/shm
tmpfs 1.9G 12M 1.9G 1% /run
tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 5.7G 12G 34% /
/dev/sda1 1014M 151M 864M 15% /boot
tmpfs 378M 0 378M 0% /run/user/0
/dev/sdb 100G 33M 100G 1% /data
2.6授权
#mysql默认用户是mysql
chown -R mysql.mysql /opt/mysql* #软件路径
chown -R mysql.mysql /data/mysql* #数据路径
2.7数据初始化(创建系统数据)
#创建数据初始化目录
[root@test2 ~]# mkdir /data/mysql/data -p
[root@test2 ~]# mkdir -p /data/mysql/log
#授权
[root@test2 ~]# chown -R mysql.mysql /data
[root@test2 ~]#mysqld --initialize --user=mysql --basedir=/opt/mysql --datadir=/data/mysql/data
#初始化完成生成临时密码 用于第一次登录
2023-02-20T08:33:58.573076Z 1 [Note] A temporary password is generated for root@localhost: 3a;PJB&m7tze
2.8配置文件的准备(两边一致 server-id唯一)
#用于启动MySQL
cat >/etc/my.cnf <<EOF
[client]
port=3306
socket=/data/mysql/log/mysql.sock
default-character-set=utf8
[mysqld]
port=3306
basedir=/opt/mysql
datadir=/data/mysql/data
socket=/data/mysql/log/mysql.sock
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/log/mysql.pid
#server-id
server-id=1
log-bin=mysql-bin
binlog_do_db=test_db
#binlog_ignore_db=mysql
#binlog_ignore_db=information_schema
#binlog_ignore_db=sys
#binlog_ignore_db=performance_schema
binlog_format=row
character-set-server=utf8
2.9启动数据库
#systemd 方式启动
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
#启动命令
[root@test2 data]# systemctl start mysqld
[root@test2 data]# systemctl stop mysqld
[root@test2 ~]# systemctl enable mysqld.service #开启自启
2.10修改密码
#使用临时密码登录
mysql -uroot -p
Enter password: 输入临时密码
#修改密码
mysql> alter user root@'localhost' identified by '123123';
#更新策略
mysql> flush privileges;
3.mysql数据库双主配置
3.1服务器113
#在主服务器设置允许root用户在所有IP访问数据库
mysql>grant replication slave on *.* to 'root'@'%' identified by '123123';
#刷新MySQL的系统权限相关表
mysql> flush privileges;
#锁定数据库，此时不允许更改任何数据
mysql> flush tables with read lock;
#查看状态，这些数据是要记录的
mysql> show master status;
3.2登录服务器111 配置113主库信息
#允许root用户在所有IP访问数据库
grant replication slave on *.* to 'root'@'%' identified by '123123';
#刷新MySQL的系统权限相关表
mysql> flush privileges;
#在另外的服务器上测试连通性
[root@test2 mysql]# mysql -h192.168.0.111 -uroot -p123123
#配置113主库信息
mysql> change master to
-> master_host='192.168.0.113',
-> master_port=3306,
-> master_user='root',
-> master_password='123123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=1137;
#启动从库配置
mysql> start slave;
#查看状态，这些数据是要记录的
mysql> show master status;
#查看Slave_IO_Running、Slave_SQL_Running都为yes则成功
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.113
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1137
Relay_Log_File: node1-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#注释： 注意是英文单引号
mysql> change master to
-> master_host='192.168.0.113', #113服务器mysql ip
-> master_port=3306, #113服务器mysql端口
-> master_user='root', #113服务器mysql从库同步用户
-> master_password='123123', #113服务器mysql从库同步用户密码
-> master_log_file='mysql-bin.000001', #前面记录的113服务mysql file值
-> master_log_pos=1137; #前面记录的113服务mysql psition值
3.3登录113服务器，配置111的库信息
mysql> change master to
-> master_host='192.168.0.111',
-> master_port=3306,
-> master_user='root',
-> master_password='123123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=985;
#启动从库配置
mysql> start slave;
#查看状态，这些数据是要记录的
mysql> show master status;
#查看Slave_IO_Running、Slave_SQL_Running都为yes则成功
show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.111
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 985
Relay_Log_File: test2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.4双主模式搭建完成测试
#连接113服务器 创建test_db数据库，t_test表，插入数据id=1,name='Raptao'
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
mysql> use test_db;
mysql> create table t_test(id int primary key, name varchar(30));
Query OK, 0 row affected (0.01 sec)
mysql> insert into t_test values (1, 'zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_test;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 rows in set (0.00 sec)
#连接111服务器，查看test_db数据库、t_test表以及数据是否同步，并插入id=2，name=‘lisi’
mysql> show databases;
| test_db | #test_db数据库已同步过来
mysql> use test_db;
mysql> show tables;
| t_test | #t_test表已同步过来
mysql> select * from t_test;
| 1 | zhangsan | #数据已同步过来
mysql> insert into t_test values (2, 'lisi'); #插入数据
Query OK, 1 row affected (0.01 sec)
#113服务器查看数据已同步
mysql> select * from t_test;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+
4.keepalived配置
4.1两台服务器安装步骤相同，配置文件不同
#安装相关依赖包，并下载keepalived安装包，解压，配置，编译
[root@test2 ~]# cd /opt
[root@test2 opt]# yum -y install gcc openssl-devel popt-devel psmisc
[root@test2 opt]# wget https://www.keepalived.org/software/keepalived-2.2.7.tar.gz
[root@test2 opt]# tar -zxvf keepalived-2.2.7.tar.gz
[root@test2 opt]# cd keepalived-2.2.7
[root@test2 keepalived-2.2.7]# ./configure --prefix=/opt/keepalived2.2.7
[root@test2 keepalived-2.2.7]# make && make install
#将文件复制到对应目录下
[root@test2 keepalived-2.2.7]# mkdir /etc/keepalived
[root@test2 keepalived-2.2.7]# cp keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf
[root@test2 keepalived-2.2.7]# cp keepalived/etc/init.d/keepalived /etc/init.d/
[root@test2 keepalived-2.2.7]# cp keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@test2 keepalived-2.2.7]# cp bin/keepalived /usr/sbin/
4.2修改113服务器/etc/keepalived/keepalived.conf配置文件
! Configuration File for keepalived
global_defs {
notification_email { #邮件告警
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_script check_httpd {
script "killall -0 mysqld" #返回值判断mysql服务是否正常
interval 2
}
vrrp_instance HA_1 {
state BACK
interface ens33
virtual_router_id 80
priority 100 #值越大 优先级越高 主库
advert_int 2
nopreempt
authentication {
auth_type PASS
auth_pass qwaszx
}
track_script {
check_httpd
}
virtual_ipaddress {
192.168.0.222/24 dev ens33 #VIP 地址
}
}
4.3修改111服务器/etc/keepalived/keepalived.conf配置文件
! Configuration File for keepalived
global_defs {
notification_email { #邮件告警
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_script check_httpd {
script "killall -0 mysqld" #返回值判断mysql服务是否正常
interval 2
}
vrrp_instance HA_1 {
state BACK
interface ens33
virtual_router_id 80
priority 80 #值越大 优先级越高
advert_int 2
nopreempt
authentication {
auth_type PASS
auth_pass qwaszx
}
track_script {
check_httpd
}
virtual_ipaddress {
192.168.0.222/24 dev ens33
}
}
4.4将113、111服务器keepalived加入开机自启，并启动服务
[root@test2 keepalived-2.2.7]# systemctl enable keepalived
[root@test2 keepalived-2.2.7]# systemctl start keepalived
5.测试keeaplived
5.1启动后相当于虚拟出一个vip 192.168.0.222，可使用远程连接工具，连接该服务器，连接进去后使用ifconfig查看该虚拟vip实际上使用的实体服务器是113服务器。
5.2将111服务器的keepalived应用停止，再次查看222服务ifconfig，可以看到，222服务器自动将实体机ip漂移到了113服务器上
测试成功！
6.mysql双主双活+keepalived高可用整体测试
6.1首先将113、111两台服务器mysql、keepalived应用全部启动，然后用mysql连接工具连接keepalived虚拟出来的192.168.0.222服务器
6.2再222数据库test_db库t_test表插入id=3，name=‘Raptao’
6.3当一台mysql服务挂掉后会自己切换至另一台MySQL
已完成测试mysql高可用，当113mysql服务挂掉即时切换至111mysql服务
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
