Vue springboot 备份数据库

后端 Springboot

Controller

@RestController
@RequestMapping("api")
public class BackupController {
	
    @Autowired
    private BackupService backupService;
	// 获取备份记录
    @GetMapping(value = "/backup/backup")
    public ResponseEntity getBackup(){
        return new ResponseEntity(backupService.queryBackup(),HttpStatus.OK);
    }
	// 备份
    @PostMapping(value = "/backup/backup")
    public ResponseEntity backup(@Validated @RequestBody String name){
        backupService.backup(name);
        return new ResponseEntity(HttpStatus.CREATED);
    }
	// 还原
    @PutMapping(value = "/backup/restore")
    public ResponseEntity restore(@Validated @RequestBody String name){
        backupService.restore(name);
        return new ResponseEntity(HttpStatus.OK);
    }
	// 删除备份记录
    @DeleteMapping(value = "/backup/del")
    public ResponseEntity delBackup(@Validated @RequestBody String name){
        backupService.delBackup(name);
        return new ResponseEntity(HttpStatus.OK);
    }
}

BackupService

public interface BackupService {
    List<Map<String, String>> queryBackup();
    void backup(String name);
    void restore(String name);
    void delBackup(String name);
}

BackupServiceImpl

功能实现代码如下，BackupConstants为常量接口，此外还需要FileUtils、MySqlBackupRestoreUtils用于文件操作（删除备份的sql文件）、数据库sql语句执行（备份和还原）

public class BackupServiceImpl implements BackupService {
    @Override
    public List<Map<String, String>> queryBackup(){
        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("title", file.getName());
                backupRecords.add(backup);
            }
        }
        // 按时间戳排序，新备份在前面
        backupRecords.sort((o1, o2) -> o2.get("title").compareTo(o1.get("title")));
        return backupRecords;
    }

    @Override
    public void backup(String name){
        String host = BackupConstants.HOST;
        String userName = BackupConstants.USER_NAME;
        String password = BackupConstants.PASSWORD;
        String database = BackupConstants.DATABASE;
        String backupFolderPath = BackupConstants.BACKUP_FOLDER + name + File.separator;
        String fileName = name;
        try {
            boolean success = MySqlBackupRestoreUtils.backup(host, userName, password, backupFolderPath, fileName, database);
            if(!success) {
                System.out.println("数据备份失败");
            }
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    @Override
    public void restore(String name){
        String host = BackupConstants.HOST;
        String userName = BackupConstants.USER_NAME;
        String password = BackupConstants.PASSWORD;
        String database = BackupConstants.DATABASE;
        String restoreFilePath = BackupConstants.RESTORE_FOLDER + name;
        try {
            MySqlBackupRestoreUtils.restore(restoreFilePath, host, userName, password, database);
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    @Override
    public void delBackup(String name){
        String restoreFilePath = BackupConstants.BACKUP_FOLDER + name;
        try {
            FileUtils.deleteFile(new File(restoreFilePath));
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

BackupConstants

用于存储一些常量

public interface BackupConstants {
    /** 备份目录名称 */
    public static final String BACKUP_FOLDER_NAME = "_backup";
    /** 备份目录 */
    public static final String BACKUP_FOLDER = Constants.FOLDER_NAME + BACKUP_FOLDER_NAME + File.separator;
    /** 还原目录，默认就是备份目录 */
    public static final String RESTORE_FOLDER = BACKUP_FOLDER;
    /** SQL拓展名 */
    public static final String SQL_EXT = ".sql";

    /** 目标备份数据库信息 */
    public static final String HOST = "127.0.0.1";
    public static final String USER_NAME = "root";
    public static final String PASSWORD = "123456";
    public static final String DATABASE = "database";
}

FileUtils

public class FileUtils {
    /**
     * 递归删除文件
     * @param file
     */
    public static void deleteFile(File file) {
        // 判断是否是一个目录, 不是的话跳过, 直接删除; 如果是一个目录, 先将其内容清空.
        if(file.isDirectory()) {
            // 获取子文件/目录
            File[] subFiles = file.listFiles();
            // 遍历该目录
            for (File subFile : subFiles) {
                // 递归调用删除该文件: 如果这是一个空目录或文件, 一次递归就可删除.
                // 如果这是一个非空目录, 多次递归清空其内容后再删除
                deleteFile(subFile);
            }
        }
        // 删除空目录或文件
        file.delete();
    }
}

MySqlBackupRestoreUtils

/**
 * MySQL备份还原工具类
 * @author Louis
 * @date Sep 21, 2018
 */
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(".sql").append(" --default-character-set=utf8 ").append(database);
        System.out.println(stringBuilder.toString());
        // 调用外部执行 exe 文件的 Java API
        Process process = Runtime.getRuntime().exec(getCommand(stringBuilder.toString()));
        if (process.waitFor() == 0) {
            // 0 表示线程正常终止
            System.out.println("数据已经备份到 " + backupFilePath + " 文件中");
            return true;
        }
        System.out.println(process.getInputStream());
        System.out.println(process.getErrorStream());

        System.out.println(process.exitValue());
        byte[] bytes = new byte[process.getInputStream().available()];
        process.getInputStream().read(bytes);
        System.out.println(new String(bytes));
        process.getErrorStream().read(bytes);
        System.out.println(new String(bytes));
        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);
        System.out.println(stringBuilder.toString());
        try {
            Process process = Runtime.getRuntime().exec(getCommand(stringBuilder.toString()));
            if (process.waitFor() == 0) {
                System.out.println("数据已从 " + restoreFilePath + " 导入到数据库中");
                return true;
            }
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        System.out.println("数据从 " + restoreFilePath + " 导入到数据库失败");
        return false;
    }

    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;
    }
}

前端VUE

backup.vue

<script>
// 封装好的axios请求
import { getbackup, delbackup } from '@/api/backup'
export default {
  data() {
    return {
      // 略
    }
  },
  created() {
    this.$nextTick(() => {
      this.init()
    })
  },
  methods: {
    // 获取备份记录
    async init() {
      return new Promise((resolve, reject) => {
        this.loading = true
        getbackup().then(res => {
          this.tableData = res
          this.loading = false
          resolve(res)
        }).catch(err => {
          this.loading = false
          reject(err)
        })
      })
    },
    // 删除备份
    subDelete(title) {
      this.delLoading = true
      delbackup(title).then(res => {
        this.delLoading = false
        this.$refs[title].doClose()
        this.init()
        this.$notify({
          title: '删除成功',
          type: 'success',
          duration: 2500
        })
      }).catch(err => {
        this.delLoading = false
        this.init()
        console.log(err.response.data.message)
      })
    },
    // 备份
    backup() {
      this.isBackup = true
      this.timeFormat()
      const _this = this.$refs.form
      _this.form = { title: this.nowTime }
      _this.dialog = true
    },
    // 还原
    restore(data) {
      this.isBackup = false
      const _this = this.$refs.form
      _this.form = { title: data.title }
      _this.dialog = true
    },
    timeFormat() {
      const timeStamp = new Date().getTime()
      const year = new Date(timeStamp).getFullYear()
      const month = new Date(timeStamp).getMonth() + 1 < 10 ? '0' + (new Date(timeStamp).getMonth() + 1) : new Date(timeStamp).getMonth() + 1
      const date = new Date(timeStamp).getDate() < 10 ? '0' + new Date(timeStamp).getDate() : new Date(timeStamp).getDate()
      const hh = new Date(timeStamp).getHours() < 10 ? '0' + new Date(timeStamp).getHours() : new Date(timeStamp).getHours()
      const mm = new Date(timeStamp).getMinutes() < 10 ? '0' + new Date(timeStamp).getMinutes() : new Date(timeStamp).getMinutes()
      const ss = new Date(timeStamp).getSeconds() < 10 ? '0' + new Date(timeStamp).getSeconds() : new Date(timeStamp).getSeconds()
      this.nowTime = year + '-' + month + '-' + date + '_' + hh + mm + ss
    }
  }
}
</script>