Keepalived+MySQL主从搭建
目录
实验环境
虚拟机两台操作系统版本：centos7.3
数据库版本：5.7.37
node1（主节点）：192.168.92.141
node2（从节点）：192.168.92.142
VIP：192.168.92.140
MySQL安装
软件包上传
分别在node1、node2使用scp工具上传mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
注意：分别在node1 和node2 上执行
创建数据库用户和组
[root@node1 ~]# groupadd mysql
[root@node1 ~]# useradd mysql -g mysql解压压缩包
[root@node1 ~]# tar xvfz mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/重命名
[root@node1 ~]# mv /usr/local/mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql创建数据目录
[root@node1 ~]# mkdir -p /data/mysql/创建日志目录
[root@node1 ~]# mkdir -p /data/log/修改目录权限
[root@node1 ~]# chown mysql.mysql /data/初始化MySQL数据库，记录数据库密码
[root@node1 ~]# cd /usr/local/mysql
[root@node1 mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
2022-10-09T06:43:27.426299Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-10-09T06:43:33.552521Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-10-09T06:43:34.450688Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-10-09T06:43:34.624646Z 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: b25d3ec2-479d-11ed-a0c4-000c29c00593.
2022-10-09T06:43:34.649311Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-10-09T06:43:35.449087Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-10-09T06:43:35.449107Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-10-09T06:43:35.450086Z 0 [Warning] CA certificate ca.pem is self signed.
2022-10-09T06:43:36.274669Z 1 [Note] A temporary password is generated for root@localhost: L)0A5kZtfat!修改MySQL配置文件
[root@node1 mysql]# vim /etc/my.cnf
[client]
port = 3306
[mysqld]
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/
socket=/tmp/mysql.sock
log-bin=/data/log/mysql-bin
log-error=/data/log/err.log
slow_query_log=on
slow_query_log_file =/data/log/slow.log
character-set-server = utf8mb4
init_connect='SET NAMES utf8mb4'
default_storage_engine = innodb
default_authentication_plugin=mysql_native_password
server-id=1 #注意server-id要不一致
default_authentication_plugin=mysql_native_password修改环境变量
[root@node1 ~]# vim .bash_profile
添加 export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
[root@node1 ~]# source .bash_profile更改启动文件
[root@node1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@node1 ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/data/mysql使用mysql用户启动数据库
[root@node1 log]# su - mysql
上一次登录：日 10月 9 15:24:26 CST 2022pts/0 上
[mysql@node1 ~]$ /etc/init.d/mysqld start
Starting MySQL. SUCCESS!登录数据库修改初始root密码
[root@node1 log]# 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.37-log
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> alter user 'root'@'localhost' identified by '12345';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)搭建mysql主从
修改node2的mysql配置文件
[root@node2 log]# vim /etc/my.cnf
添加relay_log=/data/log/mysql-relay重启数据库
[mysql@node2 ~]$ /etc/init.d/mysqld stop
[mysql@node2 ~]$ /etc/init.d/mysqld start在主库node1上创建用于同步的用户并赋权，密码为：repl
mysql> grant replication slave,replication client on *.* to 'repl'@'%' identified by 'repl';
Query OK, 0 rows affected, 1 warning (0.01 sec)查看node1的binlog和pos位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 853 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)在node2上执行同步配置
mysql> change master to master_host='192.168.92.141',
-> master_user='repl',
-> master_port=3306,
-> master_password='repl',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=853;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)查看从库状态，均为YES同步成功
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.92.141
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1157
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 624
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: 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: 1157
Relay_Log_Space: 827
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b25d3ec2-479d-11ed-a0c4-000c29c00593
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)安装keepalived
注意：在node1和 node2上分别执行
下载keepalived
[root@node1 log]# yum install keepalived编写keepalived的配置文件
[root@node1 log]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
HA_MYSQL
}
vrrp_script check_run_mysql
{
script "/root/keepalived_shutdown.sh" #检查mysql状态脚本
interval 2 #每两秒检测一下
}
vrrp_instance VI_1 {
state BACKUP #都设置为BACKUP，设置非抢占模式，防止主库故障重起之后数据不同步，就进行VIP飘移
interface ens32 #设置网卡
virtual_router_id 51
priority 100 # 优先级，node2要比100低
advert_int 1
nopreempt #不主动抢占资源，非抢占模式
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_run_mysql ##执行mysql监控服务脚本
}
virtual_ipaddress {
192.168.92.140 #VIP
}
}
virtual_server 192.168.92.140 3306 {
delay_loop 2 #每2秒检查一次
# lb_algo rr
# lb_kind NAT LVS模式，如果不关闭，服务器不能通过VIP连接MySQL
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 192.168.92.141 3306 { #IP为当前主机IP，3306为数据库端口
weight 3
notify_down /root/keepalived_shutdown.sh #当mysql服务宕了时，执行杀死keepalieved脚本
TCP_CHECK{
connect_timeout 3 #连接超时时间
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
connect_port 3306 #检查的端口
}
}
}编写/root/keepalived_shutdown.sh脚本
[root@node2 log]# vim /root/keepalived_shutdown.sh
#!/bin/sh
check=$(/usr/local/mysql/bin/mysql -uroot -p12345 -e 'select 1' |sed -n '2p')
function error_query(){
systemctl stop keepalived
echo "192.168.92.142 mysql down, keepalived 切换"
}
echo "$check"
if [ "$check" != "1" ]
then
error_query
fi启动keepalived并设置开机自启
[root@node1 log]# systemctl start keepalived.service
[root@node1 log]# systemctl enable keepalived.service查看VIP，当前VIP在主节点node1上
[root@node1 log]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
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: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:c0:05:93 brd ff:ff:ff:ff:ff:ff
inet 192.168.92.141/24 brd 192.168.92.255 scope global ens32
valid_lft forever preferred_lft forever
inet 192.168.92.140/32 scope global ens32
valid_lft forever preferred_lft forever
inet6 fe80::7fc9:696b:7c6b:794d/64 scope link
valid_lft forever preferred_lft forever测试VIP切换情况
在slave1上通过VIP连接数据库，查看当前连接数据库为server_id为1是node1主机
[root@slave1 ~]# mysql -uroot -p12345 -h192.168.92.140
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 3
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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.
db01 [(none)]>show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.09 sec)在node1 停止mysql
[root@node1 ~]# pkill mysql在slave1上查看当前数据库的server_id为2，从库提供服务
db01 [(none)]>show variables like 'server_id';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 28
Current database: *** NONE ***
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.11 sec)在node2上执行ip a 查看VIP已飘移
[root@node2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
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
3: ens37: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:35:01:7a brd ff:ff:ff:ff:ff:ff
inet 192.168.92.142/24 brd 192.168.92.255 scope global ens37
valid_lft forever preferred_lft forever
inet 192.168.92.140/32 scope global ens37
valid_lft forever preferred_lft forever
inet6 fe80::ba09:6c26:28e4:1289/64 scope link
valid_lft forever preferred_lft forever
本文含有隐藏内容，请 开通VIP 后查看