网站模板免费网盘,wordpress支付可见下载,做市场分析的网站,效果图最近在搞数据库备份#xff0c;备份出来的sql都十几G#xff0c;还原的贼慢还容易报错#xff0c;就写了个Java程序来定时还原数据库#xff0c;我加快还原的方法是修改数据库配置#xff0c;因此程序需要重启数据库#xff0c;线上项目数据库不能重启的就别用了。#… 最近在搞数据库备份备份出来的sql都十几G还原的贼慢还容易报错就写了个Java程序来定时还原数据库我加快还原的方法是修改数据库配置因此程序需要重启数据库线上项目数据库不能重启的就别用了。加快后一小时差不多还原20G
1.创建还原数据库的service类
/*** 用cmd命令将sql文件还原数据库servic类*/
Component
Slf4j
public class DataBaseRestoreByCmdService {//需要还原的数据表名组成的集合public static ListString 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;ListCompletableFutureVoid futures new ArrayList();for (String dbName : db_names) {String sqlFileName dbName _ lastMonthDate;String sqlFilePath sqlPackagePath sqlFileName .sql;//执行数据还原CompletableFutureVoid future CompletableFuture.runAsync(() - SqlImportUtil.exportSql(sqlFilePath));futures.add(future);}// 等待所有任务完成CompletableFutureVoid 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 ListString db_names new ArrayList(Arrays.asList(demo1, demo2, demo3, demo4));private final JdbcTemplate jdbcTemplate;Autowiredpublic DatabaseRestoreService(JdbcTemplate jdbcTemplate) {this.jdbcTemplate jdbcTemplate;}Transactionalpublic 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 sqlPackagePathsqlFileName.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() {// 发布事件通知监听者重启 MySQLeventPublisher.publishEvent(new RestartDatabaseEvent(this));}Overridepublic void setApplicationEventPublisher(ApplicationEventPublisher applicationEventPublisher) {this.eventPublisher applicationEventPublisher;}
}
实现ApplicationEventPublisherAware接口从而注入事件发布器能够发布自定义的重启数据库事件来通知观察者做出相应操作观察者模式除了为了解耦、异步执行之外发送重启数据库的事件也是为了保证切换配置之后才重启
3.1 配置mysql配置文件地址
mysql:config-file-path: D:\install\MySQL\MySQL Server 8.0\my.ininew-config-file-path: D:\install\MySQL\my.inibackup-config-file-path: D:\install\MySQL\MySQL Server 8.0\my.ini.backupconfig-file-path为mysql默认配置文件new-config-file-path是要替换的新配置backup-config-file-path为mysql默认配置文件的备份
3.1 创建新的mysql配置文件 复制一个默认mysql配置文件并修改其对应参数
[mysqld]
skip-log-bin
#log-binUSER-20220912IO-bin
innodb_buffer_pool_size2G
innodb_log_file_size2G
innodb_log_buffer_size8M
innodb_flush_log_at_trx_commit2 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 ApplicationListenerRestartDatabaseEvent {Overridepublic 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服务名的可以winr输入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;Autowiredprivate DatabaseSwitchService databaseSwitchService;Autowiredprivate DataBaseRestoreByCmdService dataBaseRestoreByCmdService;/*** 每月的2号0点还原数据库*/Scheduled(cron 0 0 0 2 * ?)public void executeTask(){// 切换数据库配置并重启 MySQLdatabaseSwitchService.switchAndRestartDatabase();log.info(开始还原数据库{}, LocalDateTime.now());dataBaseRestoreByCmdService.initiateRestore(sqlPackagePath);//还原数据库配置并重启databaseSwitchService.restoreAndRestartDatabase();}Scheduled(cron 0 0 0 1 * ?) // 每月1号执行一次public void updateLogMonth() {// 生成新的月份值例如 2023_12String 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);}}
