JAVA calls the console to implement MYSQL incremental backup and recovery (mysqlbinlog)

Posted by Omri16 on Fri, 25 Oct 2019 18:42:46 +0200

Knowledge background

Background reference website: https://www.cnblogs.com/luoyx/p/5896419.html (I'll copy it below if I have time)

Open MySQL binlog

Barbara

Full backup and recovery in dump mode

Barbara

Incremental backup and recovery using binlog

Barbara

JAVA implementation

Realize full backup in dump mode

The system automatically backs up every Sunday to restore the data granularity of a week according to the full backup plus incremental backup.

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

    //System storage file relative path
    @Value("${application.file.path}")
    private String filepath;

    //The path of MySql binlog (MySQL bin. Index)
    @Value("${application.mysqlbinlog.path}")
    private String binLogPath;

    //Connection database user name
    @Value("${spring.datasource.username}")
    private String DBUserName;

    //Connection database password
    @Value("${spring.datasource.password}")
    private String DBPassWord;

    @Autowired
    private BackUpListService backUpListService;

    //Run every Sunday at 1:00 a.m.
    @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("Automatic system backup every Sunday morning");
        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 {
            //Splicing mysqldump execution statement - F is to refresh and start a new binlog log file demo for the database name
            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;
        //Read the last line of mysql-bin.index file (that is, the name of binlog file newly started after dump, which is used for subsequent incremental recovery)
        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);
    }
}

Recovery based on dump full backup plus binlog increment

Here, the granularity of database recovery is only days, which can be further subdivided into hours, minutes and seconds.

The overall idea is: first restore the full backup to the desired time node according to the binlog log file.

//Simply write an interface to pass the time point when you want to recover 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();
        }
        //Take database backup records
        List<BackUpListDTO> all = backUpListExtraService.findAll(new BackUpListDTO());
        for (int i = all.size(); i > 0; i--) {
            BackUpListDTO backUpListDTO = all.get(i - 1);
            //Determine whether it is the full backup information closest to the return date
            if (backUpListDTO.getTime().isBefore(parse.toInstant())) {
                String path = backUpListDTO.getPath();
                String binlog = backUpListDTO.getBinlog();
                String fullpath = filepath + path;
                String binlogfullpath = binLogPath + "/" + binlog;

                //Perform full recovery first
                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();
                }

                //Then perform incremental restore (convert binlog to. sql file and run. 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;
                //Read mysql-bin.index file (judge whether a new binlog is generated and restored)
                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");
    }
}