定时备份mysql数据库

应用系统中最重要的东西就是 “数据”，定期备份数据的重要性就不言而喻了。

本文将会带你了解如何实现定期备份 MySQL 数据库。

mysqldump

MYSQL本身提供了一个工具 mysqldump，通过它可以完成数据库的备份。

简单来说就是一个命令，可以把数据库中的表结构和数据，以 SQL 语句的形式输出到标准输出：

mysqldump -u[用户名] -p[密码] [数据库] > [备份的SQL文件]

注意，命令中的 > 符号在linux下是重定向符，在这里的意思就是把标准输出重定向到文件。

例如，备份 demo 库到 ~/mysql.sql，用户名和密码都是 root：

mysqldump -uroot -proot demo  > ~/mysql.sql

mysqldump的详细文档：https://dev.mysql.com/doc/refman/en/mysqldump.html

题外知识：如果是导入则可以使用 <

mysql -u [用户名] -p [密码] -h [数据库] < [导入的SQL文件]

SpringBoot定时备份

1、创建应用

创建任意 Spring Boot 应用，并添加 commons-exec 依赖。

<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-exec -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-exec</artifactId>
    <version>1.3</version>
</dependency>

由于我们的备份是通过新启动一个子进程调用 mysqldump 来完成，所以建议使用 apache 的 commons-exec 库。它的使用比较简单，且设计合理，包含了子进程超时控制，异步执行等等功能。

应用配置

spring:
  # 基本的数据源配置
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&allowMultiQueries=true
    username: root
    password: root

app:
  # 备份配置
  backup:
    # 备份数据库
    db: "demo"
    # 备份文件存储目录
    dir: "backups"
    # 备份文件最多保留时间。如，5分钟：5m、12小时：12h、1天：1d
    max-age: 3m

如上，我们配置了基本的数据源。以及自定义的 “备份配置”，其中指定了备份文件的存储目录，要备份的数据库以及备份文件滚动存储的最大保存时间。

2、数据备份BackupService

创建 BackupService 服务类，用于备份服务。如下：

import java.io.OutputStream;
import java.io.BufferedOutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.nio.file.attribute.BasicFileAttributes;
import java.time.Duration;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import org.apache.commons.exec.CommandLine;
import org.apache.commons.exec.DefaultExecutor;
import org.apache.commons.exec.ExecuteWatchdog;
import org.apache.commons.exec.PumpStreamHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

/**
 * 
 *  数据库备份服务
 * 
 */
@Component
public class BackupService {

    static final Logger log = LoggerFactory.getLogger(BackupService.class);

    // 用户名
    @Value("${spring.datasource.username}")
    private String username;

    // 密码
    @Value("${spring.datasource.password}")
    private String password; 

    // 备份数据库
    @Value("${app.backup.db}")
    private String db;

    // 备份目录
    @Value("${app.backup.dir}")
    private String dir;

    // 最大备份文件数量
    @Value("${app.backup.max-age}")
    private Duration maxAge;

    // 锁，防止并发备份
    private Lock lock = new ReentrantLock();

    // 日期格式化
    private DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd-HHmmss.SSS");

    /**
     * 备份文件
     * @return
     * @throws Exception 
     */
    public Path backup() throws Exception {
        
        if (!this.lock.tryLock()) {
            throw new Exception("备份任务进行中！");
        }
        
        try {
            
            LocalDateTime now = LocalDateTime.now();
            
            Path dir = Paths.get(this.dir);

            // 备份的SQL文件
            Path sqlFile = dir.resolve(Path.of(now.format(formatter) + ".sql"));
            
            if (Files.exists(sqlFile)) {
                // 文件已经存在，则添加后缀
                for (int i = 1; i >= 1; i ++) {
                    sqlFile = dir.resolve(Path.of(now.format(formatter) + "-" + i + ".sql"));
                    if (!Files.exists(sqlFile)) {
                        break;
                    }
                }
            }
            
            // 初始化目录
            if (!Files.isDirectory(sqlFile.getParent())) {
                Files.createDirectories(sqlFile.getParent());
            }
            
            // 创建备份文件文件
            Files.createFile(sqlFile);

            // 标准流输出的内容就是 SQL 的备份内容
            try (OutputStream stdOut = new BufferedOutputStream(
                    Files.newOutputStream(sqlFile, StandardOpenOption.CREATE, StandardOpenOption.TRUNCATE_EXISTING))) {

                // 监视狗。执行超时时间，1小时
                ExecuteWatchdog watchdog = new ExecuteWatchdog(TimeUnit.HOURS.toMillis(1));

                // 子进程执行器
                DefaultExecutor defaultExecutor = new DefaultExecutor();
                // defaultExecutor.setWorkingDirectory(null); // 工作目录
                defaultExecutor.setWatchdog(watchdog);
                defaultExecutor.setStreamHandler(new PumpStreamHandler(stdOut, System.err));

                // 进程执行命令
                CommandLine commandLine = new CommandLine("mysqldump");
                commandLine.addArgument("-u" + this.username); 	// 用户名
                commandLine.addArgument("-p" + this.password); 	// 密码
                commandLine.addArgument(this.db); 				// 数据库

                log.info("备份 SQL 数据");

                // 同步执行，阻塞直到子进程执行完毕。
                int exitCode = defaultExecutor.execute(commandLine);

                if (defaultExecutor.isFailure(exitCode)) {
                    throw new Exception("备份任务执行异常：exitCode=" + exitCode);
                }
            }

            
            if (this.maxAge.isPositive() && !this.maxAge.isZero()) {
                
                for (Path file : Files.list(dir).toList()) {
                    // 获取文件的创建时间
                    LocalDateTime createTime = LocalDateTime.ofInstant(Files.readAttributes(file, BasicFileAttributes.class).creationTime().toInstant(), ZoneId.systemDefault());
                    
                    if (createTime.plus(this.maxAge).isBefore(now)) {
                        
                        log.info("删除过期文件：{}", file.toAbsolutePath().toString());
                        
                        // 删除文件
                        Files.delete(file);
                    }
                }
            }
            
            return sqlFile;
        } finally {
            this.lock.unlock();
        }
    }
}

如上，我们在 Service 类中通过 @Value 注入了 application.yaml 文件中的配置信息。使用 ReentrantLock 锁来保证备份任务不会被并发执行。备份文件的名称使用 yyyy-MM-dd-HHmmss.SSS 格式，包含了年月日时分秒以及毫秒，如：2023-10-22-095300.857.sql。如果文件名称冲突，则在末尾递增编号。

使用 commons-exec 启动新进程，调用 mysqldump 执行备份，备份成功后，尝试删除备份目录下那些已经 “过期” 的备份文件，从而达到滚动存储的目的。

备份成功后，返回 SQL 备份文件的 Path 对象。

3、定时备份

配合 spring-task 就可以实现定时备份，在启动类上添加注解 @EnableScheduling 以启用定时任务。

启用定时任务

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableScheduling;

@SpringBootApplication
@EnableScheduling
public class DemoApplication {

    public static void main(String[] args) {

        SpringApplication.run(DemoApplication.class, args);

    }
}

创建 BackupTask 任务类，定时执行备份任务。

import java.nio.file.Path;
import java.util.concurrent.TimeUnit;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import cn.springdoc.demo.service.BackupService;

@Component
public class BackupTask {

    static final Logger log = LoggerFactory.getLogger(BackupTask.class);

    @Autowired
    private BackupService backupService;

    // 1 分钟执行一次
    @Scheduled(fixedRate = 1, timeUnit = TimeUnit.MINUTES)
    public void backup () {
        
        try {
            
            Path file = this.backupService.backup();
            
            log.info("备份成功：{}", file.toAbsolutePath().toString());
            
        } catch (Exception e) {
            log.error("备份任务执行异常：{}", e.getMessage());
        }
    }
}

通过 @Scheduled 注解指定执行周期。在这里为了简单，设置的是 1 分钟执行一次。如果你需要在具体的时间执行备份任务，可以使 cron 表达式，如：@Scheduled(cron = "0 0 2 1/1 * ? ") 表示每天凌晨 2 点执行备份任务。

4、总结限制

通过 mysqldump 进行备份的 「限制」 就是 「应用和数据库必须在同一个机器上」，适用于资源有限、刚起步的小项目。

Linux服务器定时备份

登录到数据库服务器： 打开终端并使用SSH或其他适当的方式登录到你的Linux数据库服务器。

1、创建备份目录

首先，创建一个用于存储备份的目录

mkdir /backup

2、编写备份脚本

创建一个备份脚本，该脚本将负责执行备份操作。在脚本中，你需要包括以下内容：

#!/bin/bash
# 设置备份目录和日期
backup_dir="/backup"
date=$(date +%Y-%m-%d)

# 使用mysqldump命令备份数据库，替换YOUR_DB_NAME、YOUR_DB_USER和YOUR_DB_PASSWORD为实际数据库信息
mysqldump -u YOUR_DB_USER -p'YOUR_DB_PASSWORD' YOUR_DB_NAME > "$backup_dir/$date.sql"

# 压缩备份文件
gzip "$backup_dir/$date.sql"

# 删除一周前的备份文件，只保留最近一周的备份
find "$backup_dir" -type f -name "*.gz" -mtime +7 -delete

#请注意，在这个脚本中，你需要替换YOUR_DB_NAME、YOUR_DB_USER和YOUR_DB_PASSWORD为实际的数据库名称、用户名和密码

3、设置脚本权限

使用chmod命令为脚本赋予执行权限

chmod +x your_backup_script.sh

4、创建定时任务

使用cron或其他调度工具创建一个定时任务，以便每周执行备份脚本。编辑定时任务配置文件：

crontab -e

然后添加以下行来每周执行备份脚本（每周一早上3点，你可以根据需求调整时间）：

#/path/to/your_backup_script.sh 为自己脚本的实际路径
0 3 * * 1 /path/to/your_backup_script.sh

保存并退出： 在编辑器中保存并退出配置文件。

现在，你已经设置了一个每周备份数据库的定时任务。每周一早上3点，系统将执行备份脚本，将数据库备份保存到/backup目录，并只保留最近一周的备份。你可以根据需要自定义备份脚本和定时任务的设置，以满足你的具体需求和安全策略。