基于SpringBoot實現MySQL備份與還原,需求是在頁面上對所有的平臺數據執行備份和恢復操作,那么就需要使用代碼去調用MySQL備份和恢復的指令,下面是具體實現步驟;
MySQL備份表設計
CREATE TABLE IF NOT EXISTS `mysql_backups` (`id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',`mysql_ip` VARCHAR ( 15 ) DEFAULT NULL COMMENT '數據庫IP',`mysql_port` VARCHAR ( 5 ) DEFAULT NULL COMMENT '數據庫端口',`mysql_cmd` VARCHAR ( 230 ) DEFAULT NULL COMMENT '備份命令',`mysql_back_cmd` VARCHAR ( 230 ) DEFAULT NULL COMMENT '恢復命令',`database_name` VARCHAR ( 20 ) DEFAULT NULL COMMENT '數據庫名稱',`backups_path` VARCHAR ( 50 ) DEFAULT NULL COMMENT '備份數據地址',`backups_name` VARCHAR ( 50 ) DEFAULT NULL COMMENT '備份文件名稱',`operation` INT ( 11 ) DEFAULT NULL COMMENT '操作次數',`status` INT ( 1 ) DEFAULT NULL COMMENT '數據狀態(1正常,-1刪除)',`recovery_time` DATETIME DEFAULT NULL COMMENT '恢復時間',`create_time` DATETIME DEFAULT NULL COMMENT '備份時間',PRIMARY KEY ( `id` ),
INDEX baskups_index ( mysql_ip, mysql_port, backups_path, database_name,backups_name) USING BTREE COMMENT '索引'
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARSET = UTF8 ROW_FORMAT = COMPACT COMMENT = 'MySQL數據備份表';
數據狀態這個字段可加可不加,我的視角是這個備份屬于永久存儲,不可刪除的,所以前端界面上不能有刪除按鈕,但是后臺可能會對一些數據做操作,就加了status這個字段。
實體類設計
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import lombok.Data;import java.util.Date;/*** ClassName:SystemMysqlBackups* 類描述: MySQL備份實體*/
@Data
@TableName("mysql_backups")
public class SystemMysqlBackups {/*** 主鍵id*/@TableId(value = "id", type = IdType.AUTO)private Long id;/*** MySQL服務器IP地址*/@TableField("mysql_ip")private String mysqlIp;/*** MySQL服務器端口號*/@TableField("mysql_port")private String mysqlPort;/*** MySQL服務器端口號*/@TableField("database_name")private String databaseName;/*** MySQL備份指令*/@TableField("mysql_cmd")private String mysqlCmd;/*** MySQL恢復指令*/@TableField("mysql_back_cmd")private String mysqlBackCmd;/*** MySQL備份存儲地址*/@TableField("backups_path")private String backupsPath;/*** MySQL備份文件名稱*/@TableField("backups_name")private String backupsName;/*** 操作次數*/@TableField("operation")private Integer operation;/*** 數據狀態*/@TableField("status")private Integer status;/*** 恢復時間*/@TableField("recovery_time")private Date recoveryTime;/*** 備份時間*/@TableField("create_time")private Date createTime;}
注解說明:
@Data:Lombok簡化實體類注解
@TableName:MybatisPlus的注解,標識表名
@TableId:標識主鍵,設置主鍵增長類型
@TableField:標識表字段
mapper和映射文件
mapper 接口代碼:
import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.zj.module.repository.model.system.backups.SystemMysqlBackups;
import org.apache.ibatis.annotations.Param;import java.util.List;/*** InterfaceName:SystemMysqlBackupsMapper* 接口描述: MySQL備份接口*/
public interface SystemMysqlBackupsMapper extends BaseMapper<SystemMysqlBackups> {/*** 查詢所有備份數據*/List<SystemMysqlBackups> selectBackupsList();/*** 根據ID查詢*/SystemMysqlBackups selectListId(@Param("id") Long id);
}
以上兩個方法可以不寫,但是我們公司使用的 MybatisPlus 版本明明都配置好了邏輯刪除,無法使用,所以只能寫這些重復的輪子。
映射文件代碼:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mobaijun.module.dao.system.backups.SystemMysqlBackupsMapper"><resultMap id="Base_Result_Map" type="com.mobaijun.module.model.system.backups.SystemMysqlBackups"><id column="id" property="id"/><result column="mysql_ip" property="mysqlIp"/><result column="mysql_port" property="mysqlPort"/><result column="mysql_cmd" property="mysqlCmd"/><result column="mysql_back_cmd" property="mysqlBackCmd"/><result column="database_name" property="databaseName"/><result column="backups_path" property="backupsPath"/><result column="backups_name" property="backupsName"/><result column="operation" property="operation"/><result column="status" property="status"/><result column="recovery_time" property="recoveryTime"/><result column="create_time" property="createTime"/></resultMap><sql id="Base_Column_List">idas id,`mysql_ip` as mysqlIp,`mysql_port` as mysqlPort,`mysql_cmd` as mysqlCmd,`mysql_back_cmd` as mysqlBackCmd,`database_name` as databaseName,`backups_path` as backupsPath,`backups_name` as backupsName,`operation` as operation,`status` as status,`recovery_time` as recoveryTime,`create_time` as createTime</sql><select id="selectListId" resultMap="Base_Result_Map">SELECT *FROM `mysql_backups`WHERE `status` != 0AND id = #{id}</select><select id="selectBackupsList" resultMap="Base_Result_Map">SELECT *FROM `mysql_backups`WHERE `status` != 0ORDER BY create_time DESC</select>
</mapper>
Service 接口和實現類
service 接口
import com.baomidou.mybatisplus.service.IService;
import com.mobaijun.module.model.system.backups.SystemMysqlBackups;import java.util.List;/*** ClassName:SystemMysqlBackupsservice* 類描述: MySQL備份接口*/
public interface SystemMysqlBackupsService extends IService<SystemMysqlBackups> {/*** 查詢所有備份數據*/List<SystemMysqlBackups> selectBackupsList();/*** mysql備份接口*/Object mysqlBackups(String filePath, String url, String userName, String password);/*** 根據ID查詢*/SystemMysqlBackups selectListId(Long id);/*** 恢復數據庫** @param smb 恢復對象* @param userName 數據庫用戶名* @param password 數據庫密碼* @return*/Object rollback(SystemMysqlBackups smb, String userName, String password);
}
實現類:
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.mobaijun.module.core.constant.common.Constants;
import com.mobaijun.module.core.tips.ErrorTip;
import com.mobaijun.module.dao.system.backups.SystemMysqlBackupsMapper;
import com.mobaijun.module.model.system.backups.SystemMysqlBackups;
import com.mobaijun.module.service.system.backups.SystemMysqlBackupsService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;import javax.annotation.Resource;
import java.util.Date;
import java.util.List;/*** ClassName:SystemMysqlBackupsService* 類描述: MySQL備份實現*/
@Slf4j
@Service
public class SystemMysqlBackupsServiceImpl extends ServiceImpl<SystemMysqlBackupsMapper, SystemMysqlBackups> implements SystemMysqlBackupsService {@Resourceprivate SystemMysqlBackupsMapper systemMysqlBackupsMapper;@Overridepublic List<SystemMysqlBackups> selectBackupsList() {return systemMysqlBackupsMapper.selectBackupsList();}@Overridepublic Object mysqlBackups(String filePath, String url, String userName, String password) {// 獲取ipfinal String ip = url.substring(13, 22);// 獲取端口號final String port = url.substring(23, 27);// 獲取數據庫名稱final String database_name = url.substring(28, 42);// 數據庫文件名稱StringBuilder mysqlFileName = new StringBuilder().append(Constants.DATA_BASE_NAME).append("_").append(DateUtil.format(new Date(), "yyyy-MM-dd-HH-mm-ss")).append(Constants.FILE_SUFFIX);// 備份命令StringBuilder cmd = new StringBuilder().append("mysqldump ").append("--no-tablespaces ").append("-h").append(ip).append(" -u").append(userName).append(" -p").append(password)// 排除MySQL備份表.append(" --ignore-table ").append(database_name).append(".mysql_backups ").append(database_name).append(" > ").append(filePath).append(mysqlFileName);// 判斷文件是否保存成功if (!FileUtil.exist(filePath)) {FileUtil.mkdir(filePath);return new ErrorTip(HttpStatus.REQUEST_HEADER_FIELDS_TOO_LARGE.value(), "備份失敗,文件保存異常,請查看文件內容后重新嘗試!");}// 獲取操作系統名稱String osName = System.getProperty("os.name").toLowerCase();String[] command = new String[0];if (Constants.isSystem(osName)) {// Windowscommand = new String[]{"cmd", "/c", String.valueOf(cmd)};} else {// Linuxcommand = new String[]{"/bin/sh", "-c", String.valueOf(cmd)};}SystemMysqlBackups smb = new SystemMysqlBackups();// 備份信息存放到數據庫smb.setMysqlIp(ip);smb.setMysqlPort(port);smb.setBackupsName(String.valueOf(mysqlFileName));smb.setDatabaseName(database_name);smb.setMysqlCmd(String.valueOf(cmd));smb.setBackupsPath(filePath);smb.setCreateTime(DateTime.now());smb.setStatus(1);smb.setOperation(0);systemMysqlBackupsMapper.insert(smb);log.error("數據庫備份命令為:{}", cmd);// 獲取Runtime實例Process process = null;try {process = Runtime.getRuntime().exec(command);if (process.waitFor() == 0) {log.info("Mysql 數據庫備份成功,備份文件名:{}", mysqlFileName);} else {return new ErrorTip(HttpStatus.INTERNAL_SERVER_ERROR.value(), "網絡異常,數據庫備份失敗");}} catch (Exception e) {e.printStackTrace();return new ErrorTip(HttpStatus.INTERNAL_SERVER_ERROR.value(), "網絡異常,數據庫備份失敗");}return smb;}@Overridepublic SystemMysqlBackups selectListId(Long id) {return systemMysqlBackupsMapper.selectListId(id);}@Overridepublic Object rollback(SystemMysqlBackups smb, String userName, String password) {// 備份路徑和文件名StringBuilder realFilePath = new StringBuilder().append(smb.getBackupsPath()).append(smb.getBackupsName());if (!FileUtil.exist(String.valueOf(realFilePath))) {return new ErrorTip(HttpStatus.NOT_FOUND.value(), "文件不存在,恢復失敗,請查看目錄內文件是否存在后重新嘗試!");}StringBuilder cmd = new StringBuilder().append("mysql -h").append(smb.getMysqlIp()).append(" -u").append(userName).append(" -p").append(password).append(" ").append(smb.getDatabaseName()).append(" < ").append(realFilePath);String[] command = new String[0];log.error("數據庫恢復命令為:{}", cmd);// 獲取操作系統名稱String osName = System.getProperty("os.name").toLowerCase();if (Constants.isSystem(osName)) {// Windowscommand = new String[]{"cmd", "/c", String.valueOf(cmd)};} else {// Linuxcommand = new String[]{"/bin/sh", "-c", String.valueOf(cmd)};}// 恢復指令寫入到數據庫smb.setMysqlBackCmd(String.valueOf(cmd));// 更新操作次數smb.setRecoveryTime(DateTime.now());smb.setOperation(smb.getOperation() + 1);// 獲取Runtime實例Process process = null;try {process = Runtime.getRuntime().exec(command);if (process.waitFor() == 0) {log.error("Mysql 數據庫恢復成功,恢復文件名:{}", realFilePath);} else {return new ErrorTip(HttpStatus.GATEWAY_TIMEOUT.value(), "網絡異常,恢復失敗,請稍后重新嘗試!");}} catch (Exception e) {e.printStackTrace();return new ErrorTip(HttpStatus.GATEWAY_TIMEOUT.value(), "網絡異常,恢復失敗,請稍后重新嘗試!");}return smb;}
}
寫的工具類方法如下:
/*** 文件后綴*/
public static final String FILE_SUFFIX = ".sql";/*** 判斷操作系統類型、Linux|Windows
*/
public static boolean isSystem(String osName) {Boolean flag = null;if (osName.startsWith("windows")) {flag = true;} else if (osName.startsWith("linux")) {flag = false;}return flag;
}
控制器
import com.mobaijun.module.core.constant.common.Constants;
import com.mobaijun.module.core.tips.ErrorTip;
import com.mobaijun.module.core.tips.SuccessTip;
import com.mobaijun.module.model.system.backups.SystemMysqlBackups;
import com.mobaijun.module.service.system.backups.SystemMysqlBackupsService;
import com.mobaijun.module.web.annotion.ApiJsonObject;
import com.mobaijun.module.web.annotion.ApiJsonProperty;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;
import java.util.Map;/*** ClassName:SystemMysqlBackupsController* 類描述: MySQL數據備份接口*/
@RestController
@Api(description = "MySQL數據備份")
@RequestMapping(value = "/api/system/baskups")
public class SystemMysqlBackupsController {/*** 數據庫用戶名*/@Value("${spring.datasource.username}")private String userName;/*** 數據庫密碼*/@Value("${spring.datasource.password}")private String password;/*** 數據庫url*/@Value("${spring.datasource.url}")private String url;/*** Windows數據庫備份地址*/@Value("${spring.datasource.win-path}")private String windowsPath;/*** Linux數據庫備份地址*/@Value("${spring.datasource.linux-path}")private String linuxPath;@Autowiredprivate SystemMysqlBackupsService systemMysqlBackupsService;@ApiOperation(value = "獲取所有備份數據列表")@GetMapping("/backupsList")public Object backupsList() {List<SystemMysqlBackups> systemMysqlBackups = systemMysqlBackupsService.selectBackupsList();return new SuccessTip(systemMysqlBackups);}@ApiOperation(value = "MySQL備份")@PostMapping("/mysqlBackups")public Object mysqlBackups() {String path = null;// 獲取操作系統名稱String osName = System.getProperty("os.name").toLowerCase();if (Constants.isSystem(osName)) {// Windowspath = this.windowsPath;} else {// Linuxpath = this.linuxPath;}// 數據庫用戶名String userName = this.userName;// 數據庫密碼String password = this.password;// 數據庫地址String url = this.url;// 調用備份Object systemMysqlBackups = systemMysqlBackupsService.mysqlBackups(path, url, userName, password);return new SuccessTip(systemMysqlBackups);}@ApiOperation(value = "恢復數據庫")@PutMapping("/rollback")public Object rollback(@ApiJsonObject(name = "恢復數據庫", value = {@ApiJsonProperty(name = "id", example = "1", value = "數據id", dataType = "long", required = true)})@ApiParam(value = "恢復數據庫") @RequestBody Map<String, Object> map) {Long id = Long.valueOf(map.get("id").toString());if (id == null) {return new ErrorTip(HttpStatus.INTERNAL_SERVER_ERROR.value(), "id不能為null,請重新嘗試!");}// 數據庫用戶名String userName = this.userName;// 數據庫密碼String password = this.password;// 根據id查詢查詢已有的信息SystemMysqlBackups smb = systemMysqlBackupsService.selectListId(id);// 恢復數據庫Object rollback = systemMysqlBackupsService.rollback(smb, userName, password);// 更新操作次數systemMysqlBackupsService.updateById(smb);return new SuccessTip(rollback);}
}