mysql8.0版本部署+日志清理+rsync备份策略

mysql安装：https://blog.csdn.net/qq_39399966/article/details/120205461

系统：centos7.9
数据库版本：mysql8.0.28

1.卸载旧的mysql,保证环境纯净
rpm -qa | grep mariadb
mariadb-5....
rpm -e --nodeps 软件
rpm -e --nodeps mariadb-5...
rpm -qa | grep -i mysql  #查看是否安装过mysql

#查找mysql文件，并将其删除
find / -name mysql
whereis mysql
rm -rf 查询到的mysql路径
rm /etc/my.cnf
rpm -qa | grep -i mysql

#uname -m
arch
x86_64  #我的是x86

#拉取包,如果你是arch，下载https://mirrors.aliyun.com/mysql/MySQL-8.0/mysql-8.0.28-1.el7.aarch64.rpm-bundle.tar
wget https://mirrors.aliyun.com/mysql/MySQL-8.0/mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar 

#安装依赖
yum install openssl-devel perl-Test-Simple perl-JSON.noarch perl.x86_64 perl-devel.x86_64 autoconf numactl openssl-devel.x86_64 openssl.x86_64 -y

#rpm安装顺序
#1梯队
rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm --nodeps
rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm --nodeps
rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm --nodeps
rpm -ivh mysql-community-server-8.0.26-1.el7.x86_64.rpm --nodeps
#2梯队
rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm --nodeps
rpm -ivh mysql-community-embedded-compat-8.0.26-1.el7.x86_64.rpm --nodeps
rpm -ivh mysql-community-devel-8.0.26-1.el7.x86_64.rpm --nodeps
rpm -ivh mysql-community-test-8.0.26-1.el7.x86_64.rpm --nodeps

#数据库初始化
mysqld --initialize --console
#目录授权
chown -R mysql:mysql /var/lib/mysql/

#启动
systemctl start mysqld && systemctl enable --now mysqld && systemctl status mysqld

#查看密码
cat /var/log/mysqld.log | grep password

#登录
mysql -u root -p

#改密码
alter USER 'root'@'localhost' IDENTIFIED BY 'W!@f2rfsdf;';

#登录授权
use mysql;
select host, user, authentication_string, plugin from user;
update user set host = "%" where user='root';
flush privileges;

#密码验证方式更改
use mysql;
alter USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'heihei3431@：';
flush privileges;

#创建用户并授权

CREATE USER 'xixi'@'%' IDENTIFIED BY 'heihei@13のqednasdo11';
GRANT ALL PRIVILEGES ON *.* TO 'xixi'@'%' WITH GRANT OPTION;
ALTER USER 'xixi'@'%' IDENTIFIED WITH mysql_native_password BY 'heihei@13のqednasdo11';
FLUSH PRIVILEGES;

UPDATE mysql.user SET authentication_string = PASSWORD('qwc@113.'), plugin = 'mysql_native_password' WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

----------------------mysql：热备份，清理日志数据，注意：mysql服务器需要和远程服务器是免密登录，创建公私钥,这个脚本只是实现了全备
#脚本功能：
#1.清理二进制日志binlog
#2.清理错误日志（Error Log）。
#3.清理慢查询日志（Slow Query Log）。
#4.执行全量备份。
#5.将备份文件上传到远程服务器。

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

#日志存放位置查看
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

vim /etc/my.cnf
[client]
sock=/data/mysql/mysql.sock
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid
log_bin=/data/mysql/mysql-bin
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
systemctl restart mysqld

---【全量备份+热备份+rsync】
[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=/backup/mysql
mkdir -p "${local_backup_dir}"

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

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

# MySQL配置
MYSQL_LOGIN_PATH="backup"  # 使用 mysql_config_editor 设置的登录路径
SOCKET_FILE="/var/lib/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 * * * * /backup/script/auto_mysqldump.sh   #每两分钟同步一次，测试用
0 2 * * * * /backup/script/auto_mysqldump.sh   #实际使用，每天晚上2点钟更新

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

----删除测试
1.navicat登录mysql数据库删除一个库，例如test库。
2.还原
mysql -uroot -p
source /backup/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=/backup/mysql
mkdir -p "${local_backup_dir}"

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

# 远程服务器信息
REMOTE_SERVER="136node"  # 替换为远程服务器的IP地址或主机名
REMOTE_DIR="/store/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

# 执行 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 /backup/mysql/
hot_backup-2025-03-31.sql#全量文件   increxxx.sql#增量文件
远程服务器：
ls /store/mysql
hot_backup-2025-03-31.sql

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

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

================！！！！！！！！！！！！！！更换data目录，不要默认的，不然更新mysql的时候，data所有数据会被覆盖消失=================
#直接把脚本粘贴上去把，免得错了

mkdir -p /data/mysql
chown mysql:mysql -R /data/mysql
cp -R /var/lib/mysql/* /data/mysql/

1.my.cnf配置文件的datadir，socket，log-bin目录也要切换为/data/mysql目录下
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid
log_bin=/data/mysql/mysql-bin
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

2.脚本里的socket路径更改/data/mysql/
SOCKET_FILE="/data/mysql/mysql.sock"

systemctl restart mysqld

#还有报错查看
tail -f /var/spool/mail/root
journalctl -xe | grep mysqld