java实现对mysql数据备份

mysqldump 是 MySQL 自带的逻辑备份工具。

利用这个工具我们写一份工具类：

/** * @author Lxq * @version 1.0 * @date 2020/9/8 9:26 * MySQL备份还原工具类 */ public class MySqlBackupRestoreUtils { private static final Logger logger = LoggerFactory.getLogger(MySqlBackupRestoreUtils.class); /** * 备份数据库 * * @param host host地址，可以是本机也可以是远程 * @param userName 数据库用户名 * @param password 数据库密码 * @param backupFolderPath 备份路径 * @param fileName 备份文件名 * @param database 需要备份的数据库名称 * @return * @throws Exception */ public static boolean backup(String host,String port, 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 sb = new StringBuilder(); sb.append("mysqldump --opt ").append(" --add-drop-database ").append(" --add-drop-table "); sb.append(" -h").append(host).append(" -P").append(port).append(" -u").append(userName).append(" -p").append(password); sb.append(" --result-file=").append(backupFilePath).append(" --default-character-set=utf8 ").append(database); // 调用外部执行 exe 文件的 Java API System.out.println("=======================开始执行备份命令==========================="); System.out.println(sb.toString()); Process process = Runtime.getRuntime().exec(getCommand(sb.toString())); if (process.waitFor() == 0) { // 0 表示线程正常终止 System.out.println("数据已经备份到 " + backupFilePath + " 文件中"); return true; } return false; } /** * 还原数据库 * * @param restoreFilePath 数据库备份的脚本路径 * @param host IP地址 * @param userName 数据库用户名 * @param password 数据库密码 * @param database 数据库名称 * @return * @throws Exception */ public static boolean restore(String restoreFilePath, String host,String port, 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 sb = new StringBuilder(); sb.append("mysql -h").append(host).append(" -P").append(port).append(" -u").append(userName).append(" -p").append(password); sb.append(" ").append(database).append(" < ").append(restoreFilePath); try { System.out.println("=======================开始执行还原命令==========================="); System.out.println(sb.toString()); Process process = Runtime.getRuntime().exec(getCommand(sb.toString())); if (process.waitFor() == 0) { System.out.println("数据已从 " + restoreFilePath + " 导入到数据库中"); }else { return false; } } catch (IOException e) { e.printStackTrace(); return false; } return true; } /** * 执行命令 * * @param command 需要执行的命令 * @return */ 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; } }

然后我们可以定义两个接口一个是备份，一个是恢复

/** * @author Lxq * @version 1.0 * @date 2020/9/8 9:19 * MySql命令行备份恢复服务 */ public interface MysqlBackupService { /** * 备份数据库 * * @param host host地址，可以是本机也可以是远程 * @param userName 数据库的用户名 * @param password 数据库的密码 * @param backupFolderPath 备份的路径 * @param fileName 备份的文件名称 * @param database 需要备份的数据库名称 * @return * @throws Exception */ boolean backup(String host,String port, String userName, String password, String backupFolderPath, String fileName, String database) throws Exception; /** * 恢复数据库 * * @param restoreFilePath 数据库备份的路径 * @param host IP地址 * @param userName 用户名 * @param password 密码 * @param database 数据库名称 * @return * @throws Exception */ boolean restore(String restoreFilePath, String host,String port ,String userName, String password, String database) throws Exception; }

接口实现类

/** * @author Lxq * @version 1.0 * @date 2020/9/8 9:20 */ @Service public class MysqlBackupServiceImpl implements MysqlBackupService { @Override public boolean backup(String host, String port,String userName, String password, String backupFolderPath, String fileName, String database) throws Exception { return MySqlBackupRestoreUtils.backup(host, port,userName, password, backupFolderPath, fileName, database); } @Override public boolean restore(String restoreFilePath, String host,String port, String userName, String password, String database) throws Exception { return MySqlBackupRestoreUtils.restore(restoreFilePath, host,port, userName, password, database); } }

 数据库备份常量的配置

/** * @author Lxq * @version 1.0 * @date 2020/9/7 20:43 * <p> * 数据库备份常量 */ public class BackupConstants { /** * 备份目录名称 */ public static final String BACKUP_FOLDER_NAME = "arp_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 = "arp" + 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; /** * 备份的版本号 */ public static final String BACKUP_VERSION = "v.10"; }

控制层实现

/** * 数据库备份 * * @return */ @PreAuthorize("@ss.hasPermi('sql:history:backup')") @Log(title = "数据库备份") @GetMapping("/backup") public RestResponse backup() { String backupFodlerName = BackupConstants.DEFAULT_BACKUP_NAME + "_" + (new SimpleDateFormat(BackupConstants.DATE_FORMAT)).format(new Date()); backupByFodlerName(backupFodlerName); return RestResponse.success(); } /** * 数据库还原 * * @param name 还原相对路径名称 * @return */ @Log(title = "数据库备份还原") @GetMapping("/restore") public RestResponse restore(@RequestParam String name) { String host = properties.getHost(); String port = properties.getPort(); String userName = properties.getUserName(); String password = properties.getPassword(); String database = properties.getDatabase(); String restoreFilePath = BackupConstants.RESTORE_FOLDER + name; try { mysqlBackupService.restore(restoreFilePath, host, port,userName, password, database); } catch (Exception e) { return RestResponse.error(500, e.getMessage()); } return RestResponse.success(); }