MySQL 主主同步+Keepalived 高可用部署

一江春水向东流.

已于 2024-10-08 16:23:36 修改

阅读量615

点赞数 15

文章标签： mysql 数据库 linux

于 2024-10-08 16:17:52 首次发布

本文链接：https://blog.csdn.net/weixin_41620953/article/details/142760833

版权

一、概述
1.1前言
本文详细描述MySQL+Keepalived高可用安装步骤。供运维人员参考。
1.2释义和缩略语
缩写、术语   解释
主节点   当前VIP及mysql正在对外提供服务的节点
备节点   Mysql存活，但是没有VIP的节点
Master   初始默认为主节点
Slave   初始默认为备节点
HA（High Available）   高可用性集群

1.3机器列表
节点类型   IP地址   VIP   主机名   操作系统
Slave ip1 db01 Centos 7.9
Master ip2 db 02
仲裁节点   VIP   任意可以ping通的网络地址即可
1.4文件列表
文件名
mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz   MySQL二进制文件
dbeaver   数据库连接工具
KeepLived
二、内核参数优化
2.1追加以下内容
# vim /etc/sysctl.conf
vm.swappiness = 10
fs.aio-max-nr = 1048576
#sysctl -p 使上述配置生效

# vim /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
* soft nproc 65535
* hard nproc 65535

2.2修改20-nproc.conf
# vim /etc/security/limits.d/20-nproc.conf
(centos 6 为90-nproc.conf)
* soft nproc 65535
重新登录机器生效
2.3验证
# sysctl -a|grep -E "vm.swappiness|fs.aio-max-nr"

三、部署MySQL
3.1安装MySQL
3.1.1 创建用户
# useradd mysql
# echo "mysql:Password2024"|chpasswd

3.1.2 上传文件
#su - mysql
将mysql-5.7.25-linux-glibc-x86_64.tar.gz用mysql上传至/home/mysql/
$ tar zxvf mysql-5.7.25-linux-glibc-x86_64.tar.gz
$ mv mysql-5.7.25-linux-glibc-x86_64 mysql-5.7.25
$ exit
3.2配置环境变量
# vim /etc/profile
export PATH=/home/mysql/mysql-5.7.25/bin:$PATH
立即生效
# source /etc/profile
# mysql -V

3.3部署实例
3.3.1 创建数据目录
# mkdir -p /data/mysql/mysqldb
# chown mysql:mysql /data/mysql/mysqldb
# mkdir -p /data/mysql/mysqldbtmpdir
# chown mysql:mysql /data/mysql/mysqldbtmpdir
# mkdir -p /data/logs/mysqldb
# chown mysql:mysql /data/logs/mysqldb

3.4修改配置文件
$ su - mysql
$ cd /home/mysql/
$vim my.cnf
[mysqld]
##general
basedir = /home/mysql/mysql-5.7.25
datadir = /data/mysql/mysqldb
port = 3306
user = mysql
server-id = 11 #在两台机器上不能一样，一般为IP最后8位
socket = /home/mysql/mysql.sock
pid-file = /home/mysql/mysql.pid
tmpdir = /data/mysql/mysqldbtmpdir
default_storage_engine = InnoDB
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
character-set-server = utf8mb4

##cache
table_definition_cache = 4000
table_open_cache = 4000
table_open_cache_instances = 8
thread_cache_size = 1000
query_cache_type = OFF
query_cache_size = 0
join_buffer_size = 1M
sort_buffer_size = 2M

##limit
wait_timeout = 1800
interactive_timeout = 3600
max_connections = 10000
max_user_connections = 2500
open_files_limit = 65535
skip_name_resolve = ON
host_cache_size = 0
lower_case_table_names = 1 #为建表是否区分大小写，默认为0区分大小写，1为不区分大小写
##innodb
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 20G #根据物理内存及实际需求修改innodb_buffer_pool_size，总大小不要超过物理内存的70%
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 8M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_open_files = 10000
innodb_purge_threads = 4
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_print_all_deadlocks = ON

## log
general_log = OFF
slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = OFF
slow_query_log_file = /data/logs/mysqldb/slow-query.log
log_error = /data/logs/mysqldb/error.log
#log_warnings = 2
log_error_verbosity = 2
log-bin = /data/logs/mysqldb/binlog
sync_binlog = 1
max_binlog_size = 1G
expire_logs_days = 7
binlog_rows_query_log_events = ON
binlog_cache_size = 4M
max_binlog_cache_size = 2G
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_slave_updates
relay_log = /data/logs/mysqldb/relaylog
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1
gtid_mode = on
enforce_gtid_consistency = 1
slave_preserve_commit_order = 1
slave_parallel_workers = 16
slave_parallel_type = LOGICAL_CLOCK
report_host = IP1 #修改report_host为当前节点IP

##ssl证书配置
ssl-ca=/data/mysql/mysqldb/ca.pem
ssl-cert=/data/mysql/mysqldb/server-cert.pem
ssl-key=/data/mysql/mysqldb/server-key.pem

###validate_password密码策略配置，用以提高数据库的安全性
validate_password_policy=1
validate_password_length=12

3.5初始化数据
$/home/mysql/mysql-5.7.25/bin/mysqld --defaults-file=/home/mysql/my.cnf --initialize
$ cat /data/logs/mysqldb/error.log | grep 'temporarysdfsf'
获取root用户本地登录临时密码
启动数据库：
$/home/mysql/mysql-5.7.25/bin/mysqld --defaults-file=/home/mysql/my.cnf &
使用临时密码登录数数据库：
$/home/mysql/mysql-5.7.25/bin/mysql -u root -p -S /home/mysql/mysql.sock
mysql>set password = password('xxxxxxxx');设置本地登录密码
mysql> use mysql
mysql> grant all privileges on *.* to root@'192.168.0.%' with grant option;
mysql> create user rpl@'192.168.0.%' identified by 'xxxxxxxx';
mysql> grant replication slave on *.* to rpl@'192.168.0.%';
mysql> create user keepalived@'127.0.0.1' identified by 'xxxxxxxx';
mysql> grant super,replication client on *.* to keepalived@'127.0.0.1';
mysql> select Host,User,authentication_string from user;
mysql> flush privileges;
mysql> reset master;
mysql>shutdown; 停止数据库
mysql>\q
$ /home/mysql/mysql-5.7.25/bin/mysql_ssl_rsa_setup --datadir=/data/mysql/mysqldb/
$ /home/mysql/mysql-5.7.25/bin/mysqld --defaults-file=/home/mysql/my.cnf &
$/home/mysql/mysql-5.7.25/bin/mysql -u dba -p -S /home/mysql/mysql.sock
生成ssl证书并启动数据库
>mysql> show variables like "%ssl%";
查询ssl状态

192.168.0.%为同网段业务IP段，10.10.10.%为Windows管理机器IP段，用于安装数据库连接工具便于操作。请各位管理员根据业务系统安全需求合理划分用户权限（信息安全会检查）。
3.6建立复制关系
执行此操作前，请确认两个数据库实例均部署完成。
On IP1
mysql> change master to master_host='IP2', master_port=3306, master_user='rpl', master_password='xxxxxxxx',master_auto_position=1;
mysql> start slave;
On IP2
mysql> change master to master_host='IP1', master_port=3306, master_user='rpl', master_password='xxxxxxxx',master_auto_position=1;
mysql> start slave;
On IP1
mysql> show slave status\G

mysql> show master status\G;

mysql> \q
On IP2
mysql> show slave status\G

mysql> show master status\G;

mysql> \q
3.7配置mysql开机启动
$ exit
# vim /etc/rc.local
su - mysql -c '/home/mysql/mysql-5.7.25/bin/mysqld --defaults-file=/home/mysql/my.cnf &'

四、部署keepalived
4.1.1 安装keepalived
# yum install -y keepalived
#vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id oa-test07 #机器识别名，建议设置为主机名
script_user root
enable_script_security
}

vrrp_script check_mysql {
#MySQL实例健康检查定义，每个实例一个
script "/etc/keepalived/check_mysql.sh"
interval 3
weight -5
fall 3
rise 2
}

vrrp_instance VI_qygqmysql { #虚拟实例定义，每套实例一个
state MASTER #节点状态，backu节点修改为BACKUP
interface eth0 #根据实际网卡名修改
virtual_router_id 99 #虚拟路由id，每套实例不一样
priority 101 #优先级，backup节点修改为100
advert_int 1
authentication {
auth_type PASS
auth_pass qazWSXed #认证密码，8位，建议每套实例不一样
}
track_script {
check_mysql #引用MySQL实例健康检查脚本
}
virtual_ipaddress {
VIP/24 #MySQL实例VIP
}

}

#vim /etc/keepalived/check_mysql.sh
#!/bin/bash

username=keepalived
password=xxxxxxxx #MySQL为keepalive脚本创建的用户密码
bindir="/home/mysql/mysql-5.7.25/bin"
Quoram_IP="VIP" #仲裁节点IP
Brother_IP="IP1" #对端节点IP
MySQL_Port="3306" #MySQL实例端口
mysql_check() { ${bindir}/mysql -u${username} -p${password} -h127.0.0.1 -P${MySQL_Port} -e "show variables like 'read_only';"; }
check_PingNode()
{
if ping -c 1 -w 2 ${Quoram_IP} > /dev/null 2>&1 ; then
echo yes
else
if ping -c 1 -w 2 ${Brother_IP} >/dev/null 2>&1 ; then
echo yes
else
echo no
fi
fi
}

if [ -n "$(mysql_check)" ] && [[ "$(check_PingNode)" = "yes" ]] ; then
exit 0
else
systemctl stop keepalived
exit 1
fi
~
~
#chmod 755 /etc/keepalived/check_mysql.sh
五、启动keepalived以及设置开机自启
#systemctl start keepalived
#systemctl enable keepalived
#systemctl status keepalived 查看keepalived状态
#ip a 查看IP

六、验证
6.1主从验证
使用数据库管理工具在VIP所在库写入数据，查看另一台数据数据是否同步，反向操作一遍。
6.2高可用验证
停止VIP所在服务器的keepalived服务，查看VIP是否漂移，启动后是否抢占回来。