大数据量快速数据库还原程序

        最近在搞数据库备份，备份出来的sql都十几G，还原的贼慢还容易报错，就写了个Java程序来定时还原数据库，我加快还原的方法是修改数据库配置，因此程序需要重启数据库，线上项目数据库不能重启的就别用了。（加快后一小时差不多还原20G）

1.创建还原数据库的service类

/**
 * 用cmd命令将sql文件还原数据库servic类
 */
@Component
@Slf4j
public class DataBaseRestoreByCmdService {

    //需要还原的数据表名组成的集合
    public static List<String> db_names = new ArrayList<>(Arrays.asList("demo1", "demo2", "demo3", "demo4"));

    public void initiateRestore(String sqlPackagePath) {
        // 拼接 SQL 文件完整路径
        LocalDate now = LocalDate.now();
        int lastMonth = (now.getMonthValue() - 1);
        lastMonth = (lastMonth == 0) ? 12 : lastMonth;
        String lastMonthDate = now.getYear() + "_" + lastMonth;

        List<CompletableFuture<Void>> futures = new ArrayList<>();
        for (String dbName : db_names) {
            String sqlFileName = dbName + "_" + lastMonthDate;
            String sqlFilePath = sqlPackagePath + sqlFileName + ".sql";

            //执行数据还原
            CompletableFuture<Void> future = CompletableFuture.runAsync(() -> SqlImportUtil.exportSql(sqlFilePath));
            futures.add(future);
        }
        // 等待所有任务完成
        CompletableFuture<Void> allOf = CompletableFuture.allOf(futures.toArray(new CompletableFuture[0]));

        // 处理完成时的操作
        allOf.thenRun(() -> {
            // 任务完成后的操作，例如打印日志等
            log.info("数据库还原完成");
        }).join(); // 阻塞直到所有任务完成
    }
}

• 因为还原多个数据表，我就让他异步执行数据库还原

        最早我是想用ScriptUtils执行sql文件，但是这种方法由于是将sql文件直接读取到内存，因此只能用来运行小的sql，大数据量会导致内存溢出

/**
 * 用ScriptUtils执行sql文件还原数据库servic类
 */
@Component
@Slf4j
public class DatabaseRestoreService {

    //需要还原的数据表名组成的集合
    public static List<String> db_names = new ArrayList<>(Arrays.asList("demo1", "demo2", "demo3", "demo4"));

    private final JdbcTemplate jdbcTemplate;

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

    @Transactional
    public void restoreDatabase(String sqlPackagePath) {
        try {
            // 1. 获取数据库连接
            Connection connection = jdbcTemplate.getDataSource().getConnection();

            //2.拼接sql文件完整路径
            LocalDate now = LocalDate.now();
            int lastMonth = (now.getMonthValue()-1);
            lastMonth = (lastMonth == 0) ? 12 : lastMonth;
            String lastMonthDate = now.getYear()+"_"+lastMonth;

            //3.遍历数据表集合逐个执行SQL文件
            for (String dbName : db_names) {
                String sqlFileName = dbName+"_"+ lastMonthDate;
                String sqlFilePath = sqlPackagePath+sqlFileName+".sql";

                //执行SQL文件
                log.info("开始执行sql还原，Time:{}", LocalDateTime.now());
                ScriptUtils.executeSqlScript(connection, new SimpleResource(sqlFilePath));
            }
            // 4. 关闭连接
            connection.close();
        } catch (SQLException e) {
            log.info("Failed to restore database:{}",e);
            throw new RuntimeException("Failed to restore database", e);
        }
    }
}

2.创建用cmd命令执行sql的工具类

import lombok.extern.slf4j.Slf4j;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.time.LocalDateTime;

@Slf4j
public class SqlImportUtil {
    public static boolean exportSql(String sqlFilePath) {
        log.info("开始执行：{}, Time：{}", sqlFilePath, LocalDateTime.now());
        String user = "demo";
        String password = "demo";
        String host = "localhost";
        String exportDatabaseName = "demo";

        // 使用拼接的方式来完成 DOS 命令
        String command = new String("mysql -h" + host + " -u" + user + " -p" + password + " " + exportDatabaseName + " < " + sqlFilePath);
        log.info("命令：{}", command);

        try {
            ProcessBuilder processBuilder = new ProcessBuilder("cmd.exe", "/c", command);
            processBuilder.redirectErrorStream(true);
            Process process = processBuilder.start();

            // 读取命令执行结果
            BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()));
            String line;
            while ((line = reader.readLine()) != null) {
                // 输出命令执行结果，只在line不为空时打印
                if (!line.trim().isEmpty()) {
                    log.info("cmd命令框数据：{}", line);
                }
            }

            // 等待命令执行完成
            int exitCode = process.waitFor();

            if (exitCode == 0) {
                log.info("执行结束：{}, Time：{}", sqlFilePath, LocalDateTime.now());
                return true;
            } else {
                log.error("执行失败，命令返回码：{}", exitCode);
            }
        } catch (IOException | InterruptedException e) {
            log.error("执行命令时发生异常", e);
        }
        return false;
    }
}

• 用ProcessBuilder类来执行拼接出来的cmd命令
• 用Process类来阻塞从而任务完成时输出任务结果日志

3. 创建数据库切换配置并重启的service类

import com.database_reduction.event.RestartDatabaseEvent;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.ApplicationEventPublisher;
import org.springframework.context.ApplicationEventPublisherAware;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
/**
 * 切换数据库配置并重启mysql的servic类
 */
@Service
@Slf4j
public class DatabaseSwitchService implements ApplicationEventPublisherAware {

    @Value("${mysql.config-file-path}")
    private String mysqlConfigFilePath;

    @Value("${mysql.new-config-file-path}")
    private String newMysqlConfigFilePath;

    @Value("${mysql.backup-config-file-path}")
    private String backupConfigFilePath;

    private ApplicationEventPublisher eventPublisher;

    public void restoreAndRestartDatabase() {
        // 还原数据库配置文件
        restoreConfigFile();
        log.info("还原数据库配置文件");

        // 重启 MySQL 服务
        log.info("重启 MySQL 服务");
        restartMysqlService();
    }

    private void restoreConfigFile() {
        try {
            // 使用备份的配置文件还原当前配置
            Files.copy(new File(backupConfigFilePath).toPath(), new File(mysqlConfigFilePath).toPath(), StandardCopyOption.REPLACE_EXISTING);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public void switchAndRestartDatabase() {
        // 备份当前配置文件
        backupConfigFile();
        log.info("备份当前配置文件");

        // 切换到新的配置文件
        switchConfigFile();
        log.info("切换到新的配置文件");

        // 重启 MySQL 服务
        restartMysqlService();
        log.info("重启 MySQL 服务");
    }

    private void backupConfigFile() {
        try {
            Path source = new File(mysqlConfigFilePath).toPath();
            Path backup = new File(mysqlConfigFilePath + ".backup").toPath();
            Files.copy(source, backup, StandardCopyOption.REPLACE_EXISTING);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void switchConfigFile() {
        try {
            Files.copy(new File(newMysqlConfigFilePath).toPath(), new File(mysqlConfigFilePath).toPath(), StandardCopyOption.REPLACE_EXISTING);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void restartMysqlService() {
        // 发布事件，通知监听者重启 MySQL
        eventPublisher.publishEvent(new RestartDatabaseEvent(this));
    }

    @Override
    public void setApplicationEventPublisher(ApplicationEventPublisher applicationEventPublisher) {
        this.eventPublisher = applicationEventPublisher;
    }
}

• 实现ApplicationEventPublisherAware接口从而注入事件发布器，能够发布自定义的重启数据库事件来通知观察者做出相应操作（观察者模式）
• 除了为了解耦、异步执行之外，发送重启数据库的事件也是为了保证切换配置之后才重启

3.1 配置mysql配置文件地址

mysql:
  config-file-path: D:\install\MySQL\MySQL Server 8.0\my.ini
  new-config-file-path: D:\install\MySQL\my.ini
  backup-config-file-path: D:\install\MySQL\MySQL Server 8.0\my.ini.backup

•  config-file-path为mysql默认配置文件，new-config-file-path是要替换的新配置，backup-config-file-path为mysql默认配置文件的备份

3.1 创建新的mysql配置文件

        复制一个默认mysql配置文件并修改其对应参数

[mysqld]
skip-log-bin
#log-bin="USER-20220912IO-bin"
innodb_buffer_pool_size=2G
innodb_log_file_size=2G
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2

•  skip-log-bin        还原过程不生成日志文件，
  innodb_buffer_pool_size        写缓冲池内存
  innodb_log_file_size        日志大小             
• 默认配置文件中若没有的字段可以不改

4. 创建事件类

import org.springframework.context.ApplicationEvent;

public class RestartDatabaseEvent extends ApplicationEvent {

    public RestartDatabaseEvent(Object source) {
        super(source);
    }
}

5.创建观察者

import com.database_reduction.event.RestartDatabaseEvent;
import org.springframework.context.ApplicationListener;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

public class DatabaseRestartListener implements ApplicationListener<RestartDatabaseEvent> {

    @Override
    public void onApplicationEvent(RestartDatabaseEvent event) {
        // 实现重启 MySQL 服务的逻辑
        try {
            ProcessBuilder processBuilder = new ProcessBuilder("net", "stop", "MySQL80");
            processBuilder.redirectErrorStream(true);

            Process process = processBuilder.start();

            // 读取命令执行结果
            BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()));
            String line;
            while ((line = reader.readLine()) != null) {
                System.out.println("命令执行结果：" + line);
            }

            // 等待命令执行完成
            int exitCode = process.waitFor();

            if (exitCode == 0) {
                System.out.println("MySQL服务停止成功");

                // 启动MySQL服务
                startMysqlService();
            } else {
                System.err.println("MySQL服务停止失败，错误码：" + exitCode);
            }
        } catch (IOException | InterruptedException e) {
            e.printStackTrace();
        }
    }

    private void startMysqlService() {
        try {
            ProcessBuilder processBuilder = new ProcessBuilder("net", "start", "MySQL80");
            processBuilder.redirectErrorStream(true);

            Process process = processBuilder.start();

            // 读取命令执行结果
            BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()));
            String line;
            while ((line = reader.readLine()) != null) {
                System.out.println("命令执行结果：" + line);
            }

            // 等待命令执行完成
            int exitCode = process.waitFor();

            if (exitCode == 0) {
                System.out.println("MySQL服务启动成功");
            } else {
                System.err.println("MySQL服务启动失败，错误码：" + exitCode);
            }
        } catch (IOException | InterruptedException e) {
            e.printStackTrace();
        }
    }
}

• 同样用的ProcessBuilfer执行cmd命令
• 注意这里的MySQL80，是mysql的服务名，不知道自己mysql服务名的可以win+r输入services.msc 查看

 6.创建定时任务类

import com.database_reduction.service.DataBaseRestoreByCmdService;
import com.database_reduction.service.DatabaseSwitchService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.time.LocalDate;
import java.time.LocalDateTime;

/**
 * 还原数据库定时类
 */
@Component
@Slf4j
public class ReductionTask {

    //sql文件所在路径
    private String sqlPackagePath  = "E:/project/DataBaseBackup/sql/";

    //使用 Spring 的 @Value 注解注入属性值
    @Value("${LOG_MONTH}")
    private String logMonth;

    @Autowired
    private DatabaseSwitchService databaseSwitchService;
    @Autowired
    private DataBaseRestoreByCmdService dataBaseRestoreByCmdService;

    /**
     * 每月的2号0点还原数据库
     */
    @Scheduled(cron = "0 0 0 2 * ?")
    public void executeTask(){
        // 切换数据库配置并重启 MySQL
        databaseSwitchService.switchAndRestartDatabase();

        log.info("开始还原数据库：{}", LocalDateTime.now());
        dataBaseRestoreByCmdService.initiateRestore(sqlPackagePath);

        //还原数据库配置并重启
        databaseSwitchService.restoreAndRestartDatabase();
    }

    @Scheduled(cron = "0 0 0 1 * ?")  // 每月1号执行一次
    public void updateLogMonth() {
        // 生成新的月份值，例如 2023_12
        String newMonth = generateNewMonth();

        log.info("更新log文件月份：{}", newMonth);

        // 更新 LOG_MONTH 的值
        logMonth = newMonth;
    }

    private String generateNewMonth() {
        LocalDate now = LocalDate.now();
        int month = (now.getMonthValue());
        String nowMonth = now.getYear()+"_"+month;
        return nowMonth;
    }
}

6.1 主启动类上添加注解开启定时任务

@SpringBootApplication
@EnableScheduling
public class DatabaseBackupApplication {

    public static void main(String[] args) {
        SpringApplication.run(DatabaseBackupApplication.class, args);
    }

}