工具类:
public class MySqlBackupRestoreUtils {
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);
Process process = Runtime.getRuntime().exec(getCommand(stringBuilder.toString()));
if (process.waitFor() == 0) {
System.out.println("数据已经备份到 " + backupFilePath + " 文件中");
return true;
}
return false;
}
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("还原成功");
}
}
service层:
@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);
}
}
@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;
}
}
常量:
public interface BackupConstants {
public static final String BACKUP_FOLDER_NAME = "_my_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";
public static final String SQL_EXT = ".sql";
public static final String BACKUP_FILE_NAME = "my" + 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;
}
controller:
@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();
}
}