JAVA调用控制台 实现MYSQL增量备份与恢复(mysqlbinlog)

知识背景

背景知识参考网站:https://www.cnblogs.com/luoyx/p/5896419.html(有时间的话 我会抄在下面的)

开启MySQLbinlog

巴拉巴拉

dump方式全量备份与恢复

巴拉巴拉

使用binlog进行增量备份与恢复

巴拉巴拉

 

 

JAVA实现

实现dump方式的全量备份

系统每周日进行自动备份 做到可以根据全备加增量备份 恢复一周的数据的粒度.

@Component
public class Timer {
    private final Logger log = LoggerFactory.getLogger(Timer.class);

    //系统存储文件相对路径
    @Value("${application.file.path}")
    private String filepath;

    //MySql binlog 所在路径(mysql-bin.index)
    @Value("${application.mysqlbinlog.path}")
    private String binLogPath;

    //连接数据库用户名
    @Value("${spring.datasource.username}")
    private String DBUserName;

    //连接数据库密码
    @Value("${spring.datasource.password}")
    private String DBPassWord;

    @Autowired
    private BackUpListService backUpListService;

    //每周日的凌晨一点运行
    @Scheduled(cron = "0 0 1 * * 7")
    public void TimerBackUp() {
        log.debug("It's time to run TimerBackUp" + Instant.now().toString());

        BackUpListDTO backUpListDTO = new BackUpListDTO();
        backUpListDTO.setName("系统每周日凌晨自动备份");
        String realPath = filepath;
        String fpath = "BackUp";
        Calendar calendar = Calendar.getInstance();
        String dir = calendar.get(Calendar.YEAR) + "/"
            + calendar.get(Calendar.MONTH) + "/"
            + calendar.get(Calendar.DAY_OF_MONTH);

        String fileName = System.currentTimeMillis() + ".sql";

        String fullPath = realPath + "/" + fpath + "/" + dir + "/" + fileName;
        String returnPath = "/" + fpath + "/" + dir + "/" + fileName;

        File dirFile = new File(realPath + "/" + fpath + "/" + dir);
        boolean mkdirs = dirFile.mkdirs();

        try {
            //拼接mysqldump执行语句 -F 是为了刷新并开始一个新的binlog日志文件 demo为数据库名称
            String dumpEXE = "mysqldump -F -u" + DBUserName + " -p" + DBPassWord + " demo>" + fullPath;
            log.debug("dumpEXE String : " + dumpEXE);
            Process process = Runtime.getRuntime().exec("cmd /c " + dumpEXE);
            process.waitFor();
        } catch (Exception e) {
            e.getMessage();
        }

        File file = new File(fullPath);

        File binlogIndex = new File(binLogPath + "/mysql-bin.index");

        FileReader fileReader = null;
        try {
            fileReader = new FileReader(binlogIndex);
        } catch (Exception e) {
            e.getMessage();
        }
        Scanner scanner = new Scanner(fileReader);
        String line = null;
        //读取mysql-bin.index文件最后一行(即dump后所新开始的binlog文件名称,用于后续增量恢复)
        while ((scanner.hasNextLine() && (line = scanner.nextLine()) != null)) {
            if (!scanner.hasNextLine()) {
                backUpListDTO.setBinlog(line.substring(2, line.length()));
            }
        }

        backUpListDTO.setTime(Instant.now());
        backUpListDTO.setPath(returnPath);
        backUpListDTO.setSize(file.length() / 1024 / 1024 + "");

        BackUpListDTO save = backUpListService.save(backUpListDTO);
    }
}

实现基于dump全备加binlog增量的恢复

这里数据库的恢复的粒度只到达了天 可以更细分到小时分钟秒

整体思路是 : 先恢复全量备份 在根据binlog日志文件进行增量恢复到想要的时间节点.

//简单写一个接口 传递想要恢复mysql的时间点
    @GetMapping("/binlogResume")
    public ResponseEntity binlogResume(String yyyy, String MM, String dd) {
        String date = yyyy + "-" + MM + "-" + dd;
        Date parse = null;
        try {
            parse = new SimpleDateFormat("yyyy-MM-dd").parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //拿取数据库备份记录
        List<BackUpListDTO> all = backUpListExtraService.findAll(new BackUpListDTO());
        for (int i = all.size(); i > 0; i--) {
            BackUpListDTO backUpListDTO = all.get(i - 1);
            //判断是否是距离回档日期最近的全量备份信息
            if (backUpListDTO.getTime().isBefore(parse.toInstant())) {
                String path = backUpListDTO.getPath();
                String binlog = backUpListDTO.getBinlog();
                String fullpath = filepath + path;
                String binlogfullpath = binLogPath + "/" + binlog;

                //首先执行全量恢复
                String dumpEXE = "mysql -u" + DBUserName 
                    + " -p" + DBPassWord + " demo<" + fullpath;
                log.debug("dumpEXE String : " + dumpEXE);

                try {
                    Process dumpprocess = Runtime.getRuntime()
                        .exec("cmd /c " + dumpEXE);
                    dumpprocess.waitFor();
                } catch (Exception e) {
                    e.printStackTrace();
                }

                //然后进行增量还原(先将binlog转换成.sql文件 之后运行.sql 即可)
                String binlogEXE = "mysqlbinlog --database=\"demo\" --stop-datetime=\"" 
                    + date + " 00:00:00\"" + binlogfullpath + ">" + binLogPath + "/temp.sql";
                String sqlEXE = "mysql -u" + DBUserName 
                    + " -p" + DBPassWord + " < " + binLogPath + "/temp.sql";
                log.debug("binlogEXE String : " + binlogEXE);
                log.debug("sqlEXE String : " + sqlEXE);

                try {
                    Process binlogprocess = Runtime.getRuntime()
                        .exec("cmd /c " + binlogEXE);
                    binlogprocess.waitFor();
                    Process sqlprocess = Runtime.getRuntime()
                        .exec("cmd /c " + sqlEXE);
                    sqlprocess.waitFor();
                } catch (Exception e) {
                    e.printStackTrace();
                }

                //
                File binlogIndex = new File(binLogPath + "/mysql-bin.index");
                FileReader fileReader = null;
                try {
                    fileReader = new FileReader(binlogIndex);
                } catch (Exception e) {
                    e.getMessage();
                }
                Scanner scanner = new Scanner(fileReader);
                String line = null;
                //读取mysql-bin.index文件(判断是否有新的binlog生成 并还原)
                while ((scanner.hasNextLine() && (line = scanner.nextLine()) != null)) {
                    line = line.substring(2, line.length());
                    if (line.equals(binlog)) {
                        while ((scanner.hasNextLine() && (line = scanner.nextLine()) != null)) {
                            line = line.substring(2, line.length());
                            log.debug("" + line);

                            String binlog2EXE = "mysqlbinlog --database=\"demo\" --stop-datetime=\"" 
                                + date + " 00:00:00\"" + binLogPath + "/" 
                                + line + ">" + binLogPath + "/temp.sql";
                            String sql2EXE = "mysql -u" + DBUserName 
                                + " -p" + DBPassWord + " < " + binLogPath + "/temp.sql";
                            log.debug("binlog2EXE String : " + binlog2EXE);
                            log.debug("sql2EXE String : " + sql2EXE);

                            try {
                                Process binlog2process = Runtime.getRuntime()
                                    .exec("cmd /c " + binlog2EXE);
                                binlog2process.waitFor();
                                Process sql2process = Runtime.getRuntime()
                                    .exec("cmd /c " + sql2EXE);
                                sql2process.waitFor();
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
            break;
        }
        return ResponseEntity.ok().body("success");
    }
}

测试

首先将数据库进行全量备份

 

全量备份后增加一条数据

 

并删除之前的一条信息

 

首先恢复全备信息 获得如下结果

 

再根据binlog增量恢复 获得如下结果