Centos7.9备份mysql数据库

1. 备份

• 备份shell脚本

[root@iZoqvrzbtnzd6kZ local]# vi mysql_backup.sh 
#!/bin/bash
#设置MySQL登录信息
MYSQL_USER="root"
MYSQL_PASSWORD="**********"
MYSQL_DATABASE="ubox"
BACKUP_DIR="/usr/local/mysql_backup"
MONTH="`date +'%Y-%m'`"
DATE="`date +'%Y-%m-%d'`"
TIME="`date +'%H:%M'`"

#创建备份目录和文件名
mkdir -p $BACKUP_DIR/$MYSQL_DATABASE/$MONTH
FILENAME=$BACKUP_DIR/$MYSQL_DATABASE/$MONTH/$MYSQL_DATABASE-$DATE-$TIME.sql.gz
chmod +w $BACKUP_DIR/$MYSQL_DATABASE/$MONTH
#使用 mysqldump 命令备份数据库
mysqldump --default-character-set=utf8 --user=$MYSQL_USER --password=$MYSQL_PASSWORD --databases $MYSQL_DATABASE | gzip > $FILENAME
#输出备份完成信息
echo "Backup completed on $DATE $TIME for database $MYSQL_DATABASE to $FILENAME." >> /usr/local/mysql_backup/mysql_backup.log
# 删除超过7天的备份文件
find $BACKUP_DIR/$MYSQL_DATABASE/* -mtime +7 -exec rm {} \;

• 备份结果展示

[root@iZoqvrzbtnzd6kZ local]# cd mysql_backup/
[root@iZoqvrzbtnzd6kZ mysql_backup]# ls -lh
total 28K
-rw-r--r-- 1 root root  19K Mar 27 02:07 mysql_backup.log
drwxr-xr-x 3 root root 4.0K Mar 22 09:47 ubox
[root@iZoqvrzbtnzd6kZ mysql_backup]# cd ubox/
[root@iZoqvrzbtnzd6kZ ubox]# ls -lh
total 4.0K
drwxr-xr-x 2 root root 4.0K Mar 27 02:07 2024-03
[root@iZoqvrzbtnzd6kZ ubox]# cd 2024-03/
[root@iZoqvrzbtnzd6kZ 2024-03]# ls -lh
total 7.1G
-rw-r--r-- 1 root root 1.8G Mar 24 02:06 ubox-2024-03-24-02:00.sql.gz
-rw-r--r-- 1 root root 1.8G Mar 25 02:06 ubox-2024-03-25-02:00.sql.gz
-rw-r--r-- 1 root root 1.8G Mar 26 02:06 ubox-2024-03-26-02:00.sql.gz
-rw-r--r-- 1 root root 1.9G Mar 27 02:07 ubox-2024-03-27-02:00.sql.gz

2. 备份库导入\导出

• 步骤1：解压脚本文件

[root@localhost software]# ls -lh
total 2.4G
-rw-r--r--  1 root root 5.4K Mar 26 04:45 my.cnf
-rw-r--r--. 1 root root 545M Jun 22  2023 mysql-5.7.43-1.el7.x86_64.rpm-bundle.tar
drwxr-xr-x. 2 root root  106 Sep 21  2023 rabbitmq
-rw-r--r--. 1 root root 2.4M Mar 21 23:33 redis-6.2.6.tar.gz
-rw-r--r--  1 root root 1.9G Mar 27 04:01 ubox-2024-03-27-02_00.sql.gz
-rwxr-xr-x. 1 root root 2.4K Mar 21 23:29 upgrade_openssh8.9p1.sh
[root@localhost software]# gunzip ubox-2024-03-27-02_00.sql.gz

• 步骤2：执行导入脚本

#创建数据库
create database ubox default character set utf8 collate utf8_general_ci; 
#执行脚本导入
[root@localhost software]# mysql -u root -p --default-character-set=utf8 ubox < /home/sqyr/software/ubox-2024-03-27-02_00.sql