mysql热备+全备+增备+清理旧日志rsync_mysql日志清理-优快云博客

本文链接：https://blog.youkuaiyun.com/2401_84008551/article/details/146853540

系统:centos7.9
版本:mysql8
前言:需要替换你的日志路径，每个人的情况不一样
yum -y install rsync
#远程服务器：
ssh-keygen -t rsa -b 2048
ssh-copy-id 150mysql
mkdir -p /heihei/x/mysql
chmod 755 /heihei/x/mysql
#mysql服务器：
ssh-keygen -t rsa -b 2048
ssh-copy-id 136node
mkdir -p /heihei/x/{mysql,script,logs}
chmod -R 755 /heihei/x/{mysql,script,logs}
mysql_config_editor set --login-path=backup --user=root --password #插件存储密码，用户脚本免交互登录mysql

#已经安装好mysql8，现在我们更改data目录位置
mkdir -p /data/mysqll
cp -R /var/lib/mysql/* /data/mysql

mysql服务器：
vim /etc/my.cnf

[client]

socket=/data/mysql/mysql.sock

[mysqld]
datadir=/data/mysql #不要用/var/lib/mysql默认路径更新数据库版本的时候数据会被覆盖
socket=/data/mysql/mysql.sock #不要用/var/lib/mysql默认路径更新数据库版本的时候数据会被覆盖
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid
log_bin=/data/mysql/mysql-bin #不要用/var/lib/mysql默认路径更新数据库版本的时候数据会被覆盖
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

sudo mkdir -p /var/log/mysql
sudo chown -R mysql:mysql /var/log/mysql
chown -R mysql:mysql /data/mysql
systemctl restart mysqd

#日志存放位置查看
mysql -uroot -p
show variables like '%log_error%'; #查看错误日志，报错查看这个日志
log-error=/var/log/mysql/error.log
show variables like '%log_bin%'; #binlog日志用于数据恢复，show variables like '%binlog_format%'; #查看binlog记录信息的方式 ROW行记录
log_bin=/var/lib/mysql/mysql-bin
SHOW VARIABLES LIKE 'slow_query_log'; #查看慢日志
/var/log/mysql/mysql-slow.log

【脚本】
[root@150m01 /backup/script]# cat auto_mysqldump.sh
#!/bin/bash

# 设置环境变量
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
export PATH

# 获取当前日期
DATE="$(date +%F)"

# 本地备份目录
local_backup_dir=/heihei/x/mysql
mkdir -p "${local_backup_dir}"

# 日志文件路径
log_file="/heihei/x/logs/mysql_backup.log"
mkdir -p "$(dirname "${log_file}")"

# 远程服务器信息
REMOTE_SERVER="136node" # 替换为远程服务器的IP地址或主机名
REMOTE_DIR="/heihei/x/mysql" # 替换为远程服务器的备份目录
REMOTE_SSH_PORT=22 # 替换为远程服务器的SSH端口

# MySQL配置
MYSQL_LOGIN_PATH="backup" # 使用 mysql_config_editor 设置的登录路径
SOCKET_FILE="/data/mysql/mysql.sock" # MySQL 套接字文件路径

# 清理二进制日志
echo "$(date): Cleaning up binary logs..." >> "${log_file}"
mysql --login-path=${MYSQL_LOGIN_PATH} --socket=${SOCKET_FILE} -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 30 DAY;" >> "${log_file}" 2>&1

# 清理错误日志
echo "$(date): Cleaning up error log..." >> "${log_file}"
ERROR_LOG="/var/log/mysql/error.log"
if [ -f "$ERROR_LOG" ]; then
cat /dev/null > $ERROR_LOG
echo "$(date): Error log cleaned." >> "${log_file}"
else
echo "$(date): Error log not found." >> "${log_file}"
fi

# 清理慢查询日志
echo "$(date): Cleaning up slow query log..." >> "${log_file}"
SLOW_QUERY_LOG="/var/log/mysql/mysql-slow.log"
if [ -f "$SLOW_QUERY_LOG" ]; then
cat /dev/null > $SLOW_QUERY_LOG
echo "$(date): Slow query log cleaned." >> "${log_file}"
else
echo "$(date): Slow query log not found." >> "${log_file}"
fi

# 执行 MySQL 热备份
echo "$(date): Performing MySQL hot backup..." >> "${log_file}"
mysqldump --login-path=${MYSQL_LOGIN_PATH} --socket=${SOCKET_FILE} --all-databases > "${local_backup_dir}/hot_backup-${DATE}.sql"
if [ $? -eq 0 ]; then
echo "$(date): MySQL hot backup completed successfully" >> "${log_file}"
else
echo "$(date): MySQL hot backup failed" >> "${log_file}"
exit 1
fi

# 使用 rsync 同步本地备份文件到远程服务器
echo "$(date): Syncing backup files to remote server using rsync..." >> "${log_file}"
rsync -vzrtopg --progress --delete -e "ssh -p ${REMOTE_SSH_PORT}" "${local_backup_dir}/" "${REMOTE_SERVER}:${REMOTE_DIR}/" >> "${log_file}" 2>&1
if [ $? -eq 0 ]; then
echo "$(date): Backup files synced successfully" >> "${log_file}"
else
echo "$(date): Backup files sync failed" >> "${log_file}"
exit 1
fi

# 删除本地旧的备份文件（保留最近30天的备份）
echo "$(date): Deleting old local backups..." >> "${log_file}"
find "${local_backup_dir}" -name "hot_backup-*.sql" -mtime +30 -delete
if [ $? -eq 0 ]; then
echo "$(date): Old local backups deleted successfully" >> "${log_file}"
else
echo "$(date): Old local backups deletion failed" >> "${log_file}"
fi

# 在远程服务器上删除旧的备份文件（保留最近30天的备份）
echo "$(date): Deleting old remote backups..." >> "${log_file}"
ssh -p ${REMOTE_SSH_PORT} "${REMOTE_SERVER}" "find ${REMOTE_DIR} -name 'hot_backup-*.sql' -mtime +30 -delete" >> "${log_file}" 2>&1
if [ $? -eq 0 ]; then
echo "$(date): Old remote backups deleted successfully" >> "${log_file}"
else
echo "$(date): Old remote backups deletion failed" >> "${log_file}"
fi

echo "$(date): MySQL cleanup and backup completed." >> "${log_file}"

chmod +x auto_mysqldump.sh
----定时执行
crontab -e
#*/2 * * * * /heihei/x/script/auto_mysqldump.sh #每两分钟同步一次，测试用
0 2 * * * * /heihei/x/script/auto_mysqldump.sh #实际使用，每天晚上2点钟更新

----验证查看
mysql服务器：
ls /heihei/x/mysql/
hot_backup-2025-03-31.sql
远程服务器：
ls /heihei/x/mysql
hot_backup-2025-03-31.sql

----删除测试
1.navicat登录mysql数据库删除一个库，例如test库。
2.还原
mysql -uroot -p
source /heihei/x/mysql/hot_backup-2025-03-31.sql

【原有脚本功能：加增量备份，环境不变，两个脚本不一样，直接复制】

vim auto_mysqldump.sh

#!/bin/bash

# 设置环境变量
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
export PATH

# 获取当前日期
DATE="$(date +%F)"

# 本地备份目录
local_backup_dir=/heihei/x/mysql
mkdir -p "${local_backup_dir}"

# 日志文件路径
log_file="/heihei/x/logs/mysql_backup.log"
mkdir -p "$(dirname "${log_file}")"

# 远程服务器信息
REMOTE_SERVER="136node" # 替换为远程服务器的IP地址或主机名
REMOTE_DIR="//heihei/x/mysql" # 替换为远程服务器的备份目录
REMOTE_SSH_PORT=22 # 替换为远程服务器的SSH端口

# MySQL配置
MYSQL_LOGIN_PATH="backup" # 使用 mysql_config_editor 设置的登录路径
SOCKET_FILE="/data/mysql/mysql.sock" # MySQL 套接字文件路径

# 执行 MySQL 增量备份
echo "$(date): Performing MySQL incremental backup..." >> "${log_file}"
INCREMENTAL_BACKUP_DIR="${local_backup_dir}/incremental"
mkdir -p "${INCREMENTAL_BACKUP_DIR}"

# 创建增量备份目录
INCREMENTAL_BACKUP_FILE="${INCREMENTAL_BACKUP_DIR}/incremental_backup-${DATE}.sql"
mysqldump --login-path=${MYSQL_LOGIN_PATH} --socket=${SOCKET_FILE} --single-transaction --master-data=2 --flush-logs --all-databases > "${INCREMENTAL_BACKUP_FILE}"
if [ $? -eq 0 ]; then
echo "$(date): MySQL incremental backup completed successfully" >> "${log_file}"
else
echo "$(date): MySQL incremental backup failed" >> "${log_file}"
exit 1
fi

# 使用 rsync 同步增量备份文件到远程服务器
echo "$(date): Syncing incremental backup files to remote server using rsync..." >> "${log_file}"
rsync -vzrtopg --progress --delete -e "ssh -p ${REMOTE_SSH_PORT}" "${INCREMENTAL_BACKUP_DIR}/" "${REMOTE_SERVER}:${REMOTE_DIR}/incremental/" >> "${log_file}" 2>&1
if [ $? -eq 0 ]; then
echo "$(date): Incremental backup files synced successfully" >> "${log_file}"
else
echo "$(date): Incremental backup files sync failed" >> "${log_file}"
exit 1
fi

# 删除本地旧的增量备份文件（保留最近30天的备份）
echo "$(date): Deleting old local incremental backups..." >> "${log_file}"
find "${INCREMENTAL_BACKUP_DIR}" -name "incremental_backup-*.sql" -mtime +30 -delete
if [ $? -eq 0 ]; then
echo "$(date): Old local incremental backups deleted successfully" >> "${log_file}"
else
echo "$(date): Old local incremental backups deletion failed" >> "${log_file}"
fi

# 在远程服务器上删除旧的增量备份文件（保留最近30天的备份）
echo "$(date): Deleting old remote incremental backups..." >> "${log_file}"
ssh -p ${REMOTE_SSH_PORT} "${REMOTE_SERVER}" "find ${REMOTE_DIR}/incremental -name 'incremental_backup-*.sql' -mtime +30 -delete" >> "${log_file}" 2>&1
if [ $? -eq 0 ]; then
echo "$(date): Old remote incremental backups deleted successfully" >> "${log_file}"
else

----验证查看
mysql服务器：
ls /heihei/x/mysql/
hot_backup-2025-03-31.sql#全量文件 increxxx.sql#增量文件
远程服务器：
ls /heihei/x/mysql
hot_backup-2025-03-31.sql

----删除测试
1.navicat登录mysql数据库删除一个库，例如test库。
2.还原
mysql -uroot -p
source /heihei/x/mysql/hot_backup-2025-03-31.sql #先还原全量文件
假设你有多个增量备份文件，需要按时间顺序依次应用它们。例如：
mysql -u root -p < /heihei/x/mysql/incremental/incremental_backup-2024-07-31.sql
mysql -u root -p < /heihei/x/mysql/incremental/incremental_backup-2024-08-01.sql
mysql -u root -p < /heihei/x/mysql/incremental/incremental_backup-2024-08-02.sql

其他：脚本错误路径:tail -f /var/spool/mail/root