SpringBoot + Spring Cloud +Vue 管理系统后台搭建（十、数据备份还原）

在很多时候我们需要数据备份还原，实际生产是通过数据库专业维护人员通过命令来操作的。这里提供通过代码进行数据备份还原，主要方便日常的数据恢复

第一步、新建Maven工程（也可以直接新建spring boot项目比较方便）

这是一个可独立部署运行的项目

新建mango-backup项目

第二步、添加依赖

在pom文件中添加web、Swagger、common依赖

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.louis</groupId>
    <artifactId>mango-backup</artifactId>
    <version>1.0.0</version>
    <packaging>jar</packaging>

    <name>mango-backup</name>
    <description>mango-backup</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <!-- spring boot -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- swagger -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>com.louis</groupId>
            <artifactId>mango-common</artifactId>
            <version>1.0.0</version>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

第三步、添加YML配置

定义启动端口、应用名称、系统备份还原的数据库连接信息

# tomcat
server:
  port: 8002
spring:
  application:
    name: mango-backup
# backup datasource
mango:
  backup:
    datasource:
      host: localhost
      userName: root
      password: root
      database: mango

第四步、配置启动类

在启动类指定扫描包路径

/**
 * 启动器
 * @author Louis
 * @date Jan 15, 2019
 */
@SpringBootApplication(scanBasePackages={"com.louis.mango"})
public class MangoBackupApplication {

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

第五步、CORS跨域配置

在config下添加跨域配置

这个我们前面已经说过了就不多说了

/**
 * 跨域配置
 * @author Louis
 * @date Jan 15, 2019
 */
@Configuration
public class CorsConfig implements WebMvcConfigurer {

    @Override
    public void addCorsMappings(CorsRegistry registry) {
        registry.addMapping("/**") // 允许跨域访问的路径
        .allowedOrigins("*")   // 允许跨域访问的源
        .allowedMethods("POST", "GET", "PUT", "OPTIONS", "DELETE") // 允许请求方法
        .maxAge(168000)    // 预检间隔时间
        .allowedHeaders("*")  // 允许头部设置
        .allowCredentials(true);   // 是否发送cookie
    }
}

第六步、Swagger配置

在config下新增Swagger配置类，前面我们也说过

/**
 * Swagger配置
 * @author Louis
 * @date Jan 15, 2019
 */
@Configuration
@EnableSwagger2
public class SwaggerConfig {
   
   @Bean
   public Docket createRestApi() {
      return new Docket(DocumentationType.SWAGGER_2).select()
            .apis(RequestHandlerSelectors.any()).paths(PathSelectors.any()).build();
   }
   
}

第七步、数据源属性配置类

在datasource包下新建BackupDataSourceProperties.java

在类上添加注解

@ConfigurationProperties(prefix = "mango.backup.datasource")  

这样就可以通过注入BackupDataSourceProperties读取数据源属性了

/**
 * 数据源
 * @author Louis
 * @date Jan 15, 2019
 */
@Component  
@ConfigurationProperties(prefix = "mango.backup.datasource")  
public class BackupDataSourceProperties {
   
   private String host;
   private String userName;
   private String password;
   private String database;
   public String getHost() {
      return host;
   }
   public void setHost(String host) {
      this.host = host;
   }
   public String getUserName() {
      return userName;
   }
   public void setUserName(String userName) {
      this.userName = userName;
   }
   public String getPassword() {
      return password;
   }
   public void setPassword(String password) {
      this.password = password;
   }
   public String getDatabase() {
      return database;
   }
   public void setDatabase(String database) {
      this.database = database;
   }
   
}  

第八步、配置Service

在service包下添加MysqlBackupService接口

在接口中添加备份backup和还原restore两个接口

MysqlBackupService.java

 

/**
 * MySql命令行备份恢复服务
 * @author Louis
 * @date Jan 15, 2019
 */
public interface MysqlBackupService {

   /**
    * 备份数据库
    * @param host host地址，可以是本机也可以是远程
    * @param userName 数据库的用户名
    * @param password 数据库的密码
    * @param backupFolderPath 备份的路径
    * @param fileName 备份的文件名
    * @param database 需要备份的数据库的名称
    * @return
    * @throws IOException 
    */
   boolean backup(String host, String userName, String password, String backupFolderPath, String fileName, String database) throws Exception;

    /**
     * 恢复数据库
     * @param restoreFilePath 数据库备份的脚本路径
     * @param host IP地址
     * @param database 数据库名称
     * @param userName 用户名
     * @param password 密码
     * @return
     */
   boolean restore(String restoreFilePath, String host, String userName, String password, String database) throws Exception;

}

在impl包下实现该接口方法

MysqlBackupServiceImpl.java

 

@Service
public class MysqlBackupServiceImpl implements MysqlBackupService {

   @Override
   public boolean backup(String host, String userName, String password, String backupFolderPath, String fileName,
         String database) throws Exception {
      return MySqlBackupRestoreUtils.backup(host, userName, password, backupFolderPath, fileName, database);
   }

   @Override
   public boolean restore(String restoreFilePath, String host, String userName, String password, String database)
         throws Exception {
      return MySqlBackupRestoreUtils.restore(restoreFilePath, host, userName, password, database);
   }

}

为方便复用，我们把备份还原逻辑封装到了MySqlBackupRestoreUtils,代码如下

主要是通过代码调用Mysql的数据库备份和还原命令实现

MySqlBackupRestoreUtils.java

 

/**
 * MySQL备份还原工具类
 * @author Louis
 * @date Jan 15, 2019
 */
public class MySqlBackupRestoreUtils {

   /**
    * 备份数据库
    * @param host host地址，可以是本机也可以是远程
    * @param userName 数据库的用户名
    * @param password 数据库的密码
    * @param backupFolderPath 备份的路径
    * @param fileName 备份的文件名
    * @param database 需要备份的数据库的名称
    * @return
    * @throws IOException 
    */
   public static boolean backup(String host, String userName, String password, String backupFolderPath, String fileName,
         String database) throws Exception {
      File backupFolderFile = new File(backupFolderPath);
      if (!backupFolderFile.exists()) {
         // 如果目录不存在则创建
         backupFolderFile.mkdirs();
      }
      if (!backupFolderPath.endsWith(File.separator) && !backupFolderPath.endsWith("/")) {
         backupFolderPath = backupFolderPath + File.separator;
      }
      // 拼接命令行的命令
      String backupFilePath = backupFolderPath + fileName;
      StringBuilder stringBuilder = new StringBuilder();
      stringBuilder.append("mysqldump --opt ").append(" --add-drop-database ").append(" --add-drop-table ");
      stringBuilder.append(" -h").append(host).append(" -u").append(userName).append(" -p").append(password);
      stringBuilder.append(" --result-file=").append(backupFilePath).append(" --default-character-set=utf8 ").append(database);
      // 调用外部执行 exe 文件的 Java API
      Process process = Runtime.getRuntime().exec(getCommand(stringBuilder.toString()));
      if (process.waitFor() == 0) {
         // 0 表示线程正常终止
         System.out.println("数据已经备份到 " + backupFilePath + " 文件中");
         return true;
      }
      return false;
   }

    /**
     * 还原数据库
     * @param restoreFilePath 数据库备份的脚本路径
     * @param host IP地址
     * @param database 数据库名称
     * @param userName 用户名
     * @param password 密码
     * @return
     */
   public static boolean restore(String restoreFilePath, String host, String userName, String password, String database)
         throws Exception {
      File restoreFile = new File(restoreFilePath);
      if (restoreFile.isDirectory()) {
         for (File file : restoreFile.listFiles()) {
            if (file.exists() && file.getPath().endsWith(".sql")) {
               restoreFilePath = file.getAbsolutePath();
               break;
            }
         }
      }
      StringBuilder stringBuilder = new StringBuilder();
      stringBuilder.append("mysql -h").append(host).append(" -u").append(userName).append(" -p").append(password);
      stringBuilder.append(" ").append(database).append(" < ").append(restoreFilePath);
      try {
         Process process = Runtime.getRuntime().exec(getCommand(stringBuilder.toString()));
         if (process.waitFor() == 0) {
            System.out.println("数据已从 " + restoreFilePath + " 导入到数据库中");
         }
      } catch (IOException e) {
         e.printStackTrace();
         return false;
      }
      return true;
   }

   private static String[] getCommand(String command) {
      String os = System.getProperty("os.name");  
      String shell = "/bin/bash";
      String c = "-c";
      if(os.toLowerCase().startsWith("win")){  
         shell = "cmd";
         c = "/c";
      }  
      String[] cmd = { shell, c, command };
      return cmd;
   }

   public static void main(String[] args) throws Exception {
      String host = "localhost";
      String userName = "root";
      String password = "123456";
      String database = "mango";
      
      System.out.println("开始备份");
      String backupFolderPath = "c:/dev/";
      String fileName = "mango.sql";
      backup(host, userName, password, backupFolderPath, fileName, database);
      System.out.println("备份成功");
      
      System.out.println("开始还原");
      String restoreFilePath = "c:/dev/mango.sql";
      restore(restoreFilePath, host, userName, password, database);
      System.out.println("还原成功");

   }

}

第九步、配置Controller

在controller新建控制器MySqlBackupController

在控制器中添加数据备份接口backup

数据还原接口restore

查找备份接口findBackupRecords

删除备份接口deleteBackupRecord

MySqlBackupController.java

/**
 * 系统数据备份还原
 * @author Louis
 * @date Jan 15, 2019
 */
@RestController
@RequestMapping("/backup")
public class MySqlBackupController {

   @Autowired
   MysqlBackupService mysqlBackupService;
   @Autowired
   BackupDataSourceProperties properties;

   @GetMapping("/backup")
   public HttpResult backup() {
      String backupFodlerName = BackupConstants.DEFAULT_BACKUP_NAME + "_" + (new SimpleDateFormat(BackupConstants.DATE_FORMAT)).format(new Date());
      return backup(backupFodlerName);
   }

   private HttpResult backup(String backupFodlerName) {
      String host = properties.getHost();
      String userName = properties.getUserName();
      String password = properties.getPassword();
      String database = properties.getDatabase();
      String backupFolderPath = BackupConstants.BACKUP_FOLDER + backupFodlerName + File.separator;
      String fileName = BackupConstants.BACKUP_FILE_NAME;
      try {
         boolean success = mysqlBackupService.backup(host, userName, password, backupFolderPath, fileName, database);
         if(!success) {
            HttpResult.error("数据备份失败");
         }
      } catch (Exception e) {
         return HttpResult.error(500, e.getMessage());
      }
      return HttpResult.ok();
   }
   
   @GetMapping("/restore")
   public HttpResult restore(@RequestParam String name) throws IOException {
      String host = properties.getHost();
      String userName = properties.getUserName();
      String password = properties.getPassword();
      String database = properties.getDatabase();
      String restoreFilePath = BackupConstants.RESTORE_FOLDER + name;
      try {
         mysqlBackupService.restore(restoreFilePath, host, userName, password, database);
      } catch (Exception e) {
         return HttpResult.error(500, e.getMessage());
      }
      return HttpResult.ok();
   }
   
   @GetMapping("/findRecords")
   public HttpResult findBackupRecords() {
      if(!new File(BackupConstants.DEFAULT_RESTORE_FILE).exists()) {
         // 初始默认备份文件
         backup(BackupConstants.DEFAULT_BACKUP_NAME);
      }
      List<Map<String, String>> backupRecords = new ArrayList<>();
      File restoreFolderFile = new File(BackupConstants.RESTORE_FOLDER);
      if(restoreFolderFile.exists()) {
         for(File file:restoreFolderFile.listFiles()) {
            Map<String, String> backup = new HashMap<>();
            backup.put("name", file.getName());
            backup.put("title", file.getName());
            if(BackupConstants.DEFAULT_BACKUP_NAME.equalsIgnoreCase(file.getName())) {
               backup.put("title", "系统默认备份");
            }
            backupRecords.add(backup);
         }
      }
      // 排序，默认备份最前，然后按时间戳排序，新备份在前面
      backupRecords.sort((o1, o2) -> BackupConstants.DEFAULT_BACKUP_NAME.equalsIgnoreCase(o1.get("name")) ? -1
            : BackupConstants.DEFAULT_BACKUP_NAME.equalsIgnoreCase(o2.get("name")) ? 1 : o2.get("name").compareTo(o1.get("name")));
      return HttpResult.ok(backupRecords);
   }
   
   @GetMapping("/delete")
   public HttpResult deleteBackupRecord(@RequestParam String name) {
      if(BackupConstants.DEFAULT_BACKUP_NAME.equals(name)) {     
         return HttpResult.error("系统默认备份无法删除!");
      }
      String restoreFilePath = BackupConstants.BACKUP_FOLDER + name;
      try {
         FileUtils.deleteFile(new File(restoreFilePath));
      } catch (Exception e) {
         return HttpResult.error(500, e.getMessage());
      }
      return HttpResult.ok();
   }

}

这里我们把一些常量配置封装到了BackupConstants类中

BackupConstants.java

public interface BackupConstants {
   
   /** 备份目录名称 */
   public static final String BACKUP_FOLDER_NAME = "_mango_backup";
   /** 备份目录 */
   public static final String BACKUP_FOLDER = System.getProperty("user.home") + File.separator + BACKUP_FOLDER_NAME + File.separator;
   /** 还原目录，默认就是备份目录 */
   public static final String RESTORE_FOLDER = BACKUP_FOLDER;
   /** 日期格式 */
   public static final String DATE_FORMAT = "yyyy-MM-dd_HHmmss";
   /** SQL拓展名 */
   public static final String SQL_EXT = ".sql";
   /** 默认备份文件名 */
   public static final String BACKUP_FILE_NAME = "mango" + SQL_EXT;
   /** 默认备份还原目录名称 */
   public static final String DEFAULT_BACKUP_NAME = "backup";
   /** 默认备份还原文件 */
   public static final String DEFAULT_RESTORE_FILE = BACKUP_FOLDER + DEFAULT_BACKUP_NAME + File.separator + BACKUP_FILE_NAME;
   
}

控制器中我们对文件进行操作，我们需要新建FileUtils类

因为是公共的操作，所以我把该类放到mango-common模块中的utils包下

相关代码如下

FileUtils.java

/**
 * 文件相关操作
 * @author Louis
 * @date Jan 14, 2019
 */
public class FileUtils {

   /**
    * 下载文件
    * @param response
    * @param file
    * @param newFileName
    */
   public static void downloadFile(HttpServletResponse response, File file, String newFileName) {
      try {
         response.setHeader("Content-Disposition", "attachment; filename=" + new String(newFileName.getBytes("ISO-8859-1"), "UTF-8"));
         BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
         InputStream is = new FileInputStream(file.getAbsolutePath());
         BufferedInputStream bis = new BufferedInputStream(is);
         int length = 0;
         byte[] temp = new byte[1 * 1024 * 10];
         while ((length = bis.read(temp)) != -1) {
            bos.write(temp, 0, length);
         }
         bos.flush();
         bis.close();
         bos.close();
         is.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
   
   /**
    * 读取txt文件的内容
    * @param file 想要读取的文件路径
    * @return 返回文件内容
    */
   public static String readFile(String file){
      return readFile(new File(file));
   }
   
    /**
     * 读取txt文件的内容
     * @param file 想要读取的文件对象
     * @return 返回文件内容
     */
    public static String readFile(File file){
        StringBuilder result = new StringBuilder();
        try{
            BufferedReader br = new BufferedReader(new FileReader(file));//构造一个BufferedReader类来读取文件
            String s = null;
            while((s = br.readLine())!=null){
               //使用readLine方法，一次读一行
                result.append(System.lineSeparator() + s);
            }
            br.close();    
        }catch(Exception e){
            e.printStackTrace();
        }
        return result.toString();
    }
    
    /**
    * 递归删除文件
    * @param file
    */
   public static void deleteFile(File file) {
       // 判断是否是一个目录, 不是的话跳过, 直接删除; 如果是一个目录, 先将其内容清空.
       if(file.isDirectory()) {
           // 获取子文件/目录
           File[] subFiles = file.listFiles();
           // 遍历该目录
           for (File subFile : subFiles) {
               // 递归调用删除该文件: 如果这是一个空目录或文件, 一次递归就可删除. 
              // 如果这是一个非空目录, 多次递归清空其内容后再删除
               deleteFile(subFile);
           }
       }
       // 删除空目录或文件
       file.delete();
   }
   
   /**
    * 获取项目根路径
    * @return
    */
   public static String getProjectPath() {
      String classPath = getClassPath();
      return new File(classPath).getParentFile().getParentFile().getAbsolutePath();
   }

   /**
    * 获取类路径
    * @return
    */
   public static String getClassPath() {
      String classPath = FileUtils.class.getClassLoader().getResource("").getPath();
      return classPath;
   }
   
   public static void main(String[] args){
//        File file = new File("D:/errlog.txt");
//        System.out.println(readFile(file));
        System.out.println(getClassPath());
        System.out.println(getProjectPath());
    }
}

需要在mango-common的pom文件中添加apache依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.louis</groupId>
    <artifactId>mango-common</artifactId>
    <packaging>jar</packaging>
    <version>1.0.0</version>
    <name>mango-common</name>
    <description>mango-common</description>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- commons-beanutils -->
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>1.9.3</version>
        </dependency>
        <!-- poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
    </dependencies>
</project>

到此我们已经完成了相关配置启动项目后，用Swagger进行测试

访问http://localhost:8002/swagger-ui.html#/

进入首页我们可以看到相关接口

 

 

可以直接执行一下查询，如果没有备份文件，系统会默认生成一份

 

在配置的系统目录下生成了备份Sql

 

执行备份接口

可以看到在系统目录下按照时间戳生成的备份文件

还原就不说了，你可以把库里的数据删除，直接输入要还原文件的目录名

说明：要运行这个Process 的进程，首先要配置好数据库的环境变量