mysql数据库备份

mysql数据库备份

Java代码实现

有使用到hutool包进行实现

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.List;
import java.util.Map;

/**
 * 数据库备份服务
 */
@Service
public class DatabaseBackupServer {
    private static final Logger log = LoggerFactory.getLogger(DatabaseBackupServer.class);

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public DatabaseBackupServer(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

     /**
     * 将数据库备份到指定文件。
     * 
     * 此方法首先检查备份文件所在的目录是否存在，如果不存在，则创建该目录。
     * 然后，它将当前用户的用户名和当前日期写入备份文件的头部，以供将来参考。
     * 接下来，它设置数据库的字符集和外键检查为关闭状态，这是进行数据库备份的常见操作。
     * 方法通过查询数据库中的所有表，并对每个表调用backupTable方法来实际执行备份过程。
     * 备份完成后，日志记录备份操作的成功完成。
     * 如果在备份过程中发生异常，方法将抛出一个运行时异常，指示备份失败。
     * 
     * @param filePath 备份文件的路径。
     */
    public void backupDatabaseToFile(String filePath) {
        // 创建文件对象，用于后续操作。
        File file = FileUtil.file(filePath);
        // 检查文件父目录是否存在，不存在则创建。
        if (!FileUtil.isDirectory(file.getParentFile())) {
            FileUtil.mkdir(file.getParentFile());
        }
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            // 写入备份文件头部信息，包括备份者和备份时间。
            writer.write("/*\n" +
                    " by: " + "XXX" + "\n" +
                    " Date: " + DateUtil.now() + "\n" +
                    "*/\n\n");
            // 设置数据库备份环境，如字符集和外键检查。
            writer.write("SET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS = 0;\n\n");
            // 查询数据库中的所有表，准备进行备份。
            List<String> tableNames = jdbcTemplate.queryForList("SHOW TABLES", String.class);
            for (String tableName : tableNames) {
                // 对每个表进行备份操作。
                backupTable(writer, tableName);
            }
            // 记录备份成功的日志信息。
            log.info("Backup completed to: {}", filePath);
        } catch (IOException e) {
            // 抛出运行时异常，指示备份失败。
            throw new RuntimeException("Backup failed", e);
        }
    }

    /**
     * 备份指定数据库表的结构和数据到文件。
     * 
     * @param writer 用于写入备份SQL语句的BufferedWriter对象。
     * @param tableName 需要备份的表名。
     * @throws IOException 如果写入过程中发生IO错误。
     */
    private void backupTable(BufferedWriter writer, String tableName) throws IOException {
        // 写入表结构注释
        writer.write("-- Table structure for table `" + tableName + "`\n");
        // 查询表的创建语句
        List<Map<String, Object>> createTableResult = jdbcTemplate.queryForList("SHOW CREATE TABLE `" + tableName + "`");
        // 如果表存在
        if (!createTableResult.isEmpty()) {
            // 写入删除表的SQL语句，用于备份文件中重建表
            writer.write("DROP TABLE IF EXISTS `" + tableName + "`;\n");
            // 刷新writer，确保写入文件
            writer.flush();
            // 获取表的创建语句
            Map<String, Object> resultMap = createTableResult.get(0);
            String createTableSql = (String) resultMap.get("Create Table");
            // 写入创建表的SQL语句
            writer.write(createTableSql + ";\n\n");

            // 写入数据备份注释
            writer.write("-- Dumping data for table `" + tableName + "`\n");
            // 查询表的所有数据
            List<Map<String, Object>> rows = jdbcTemplate.queryForList("SELECT * FROM `" + tableName + "`");
            // 遍历每行数据，生成INSERT语句
            for (Map<String, Object> row : rows) {
                writer.write("INSERT INTO `" + tableName + "` VALUES (");
                boolean first = true;
                // 处理每列数据，转义单引号并处理NULL值
                for (Object value : row.values()) {
                    if (!first) writer.write(", ");
                    writer.write(value != null ? "'" + value.toString().replace("'", "''") + "'" : "NULL");
                    first = false;
                }
                writer.write(");\n");
            }
            // 写入空行，区分不同表的备份数据
            writer.write("\n");
        }
    }
}

Windows系统服务器实用脚本备份

在Windows系统上创建批处理脚本，使用Windows定时任务实现自动化备份

echo 取日期、时间变量值
set yy=%date:~0,4%
set mm=%date:~5,2%
set dd=%date:~8,2%
if /i %time:~0,2% lss 10 set hh=0%time:~1,1%
if /i %time:~0,2% geq 10 set hh=%time:~0,2%
set mn=%time:~3,2%
set ss=%time:~6,2%
set date=%yy%-%mm%-%dd%
set time=%hh%%mn%%ss%

%这里不要具体时分数据，要的话可以追加time%
set filename=%date%_%time%

%赋值等号两边不能有空格%
%mysqldump的具体路径%
set mysqldumpPath="C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe"
set dbName=数据库名
set dbUser=账号
set dbPwd=密码
set outpurDir=D:/project/项目路径/db_backup/
set delDir="D:\project\项目路径\db_backup"

set outputFileName=%outpurDir%%dbName%_%filename%.sql

echo %outputFileName%

echo 开始备份

%mysqldumpPath% -u%dbUser% -p%dbPwd% --opt --default-character-set=utf8 -e --triggers -R --hex-blob --flush-logs -x %dbName% > %outputFileName%

echo 删除10天以前的
forfiles /p %delDir% /m %dbName%_*.sql -d -10 /c "cmd /c del /f @path"

Linux系统服务器实用脚本备份

在linux系统上创建bash脚本，使用使用cron来安排脚本每天定时执行

#!/bin/bash

# 获取日期和时间
yy=$(date +%Y)
mm=$(date +%m)
dd=$(date +%d)
hh=$(date +%H)
mn=$(date +%M)
ss=$(date +%S)
date_str="${yy}-${mm}-${dd}"
time_str="${hh}${mn}${ss}"

# 定义变量
mysqldump_path="/usr/bin/mysqldump"  # MySQL dump的路径，请根据实际情况调整
dbname="数据库名"
dbuser="账号"
dbpwd="密码"
output_dir="/path/to/your/backup/directory/"  # 备份文件存放目录，请替换为实际路径
filename="${dbname}_${date_str}_${time_str}.sql"
output_file="${output_dir}${filename}"

# 打印输出文件名
echo "$output_file"

# 开始备份
echo "开始备份..."
${mysqldump_path} -u${dbuser} -p${dbpwd} --opt --default-character-set=utf8 -e --triggers -R --hex-blob --flush-logs -x ${dbname} > "${output_file}"
echo "备份完成."

# 删除10天前的备份文件
find "${output_dir}" -name "${dbname}_*.sql" -mtime +10 -exec rm -f {} \;
echo "删除10天前的备份文件完成."