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

最新推荐文章于 2022-05-09 14:51:10 发布

msn250843956

最新推荐文章于 2022-05-09 14:51:10 发布

阅读量1.5k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： JAVA 文章标签： JAVA MYSQL binlog binlog增量备份与恢复

本文链接：https://blog.youkuaiyun.com/u012730318/article/details/95938637

JAVA 专栏收录该内容

1 篇文章

订阅专栏

博客介绍了MySQL开启binlog、dump方式全量备份与恢复及binlog增量备份与恢复的知识。用Java实现dump方式全量备份，可按全备加增量备份恢复一周数据；还实现基于dump全备加binlog增量的恢复，能恢复到指定时间节点，并进行了相关测试。

知识背景

背景知识参考网站: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");
    }
}