Java定时备份MySql数据库

Java定时备份MySql数据库 1、定时任务类

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.linlinjava.litemall.db.util.DbUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.IOException;
import java.time.LocalDate;


@Component
public class DbJob {
    private final Log logger = LogFactory.getLog(DbJob.class);

	// Environment  environment 是用于获取spring配置文件值的接口
    @Autowired
    private Environment environment;

    
    @Scheduled(cron = "0 0 5 * * ?")
    public void backup() throws IOException {
        logger.info("系统开启定时任务数据库备份");
		
		// 数据库用户名
        String user = environment.getProperty("spring.datasource.druid.username");
        // 数据库密码
        String password = environment.getProperty("spring.datasource.druid.password");
        // 数据库地址
        String url = environment.getProperty("spring.datasource.druid.url");
        // 动态截取数据库url中的数据库名称
        int index1 = url.indexOf("3306/");
        int index2 = url.indexOf("?");
        String db = url.substring(index1+5, index2);
		
		// 当前时间 年月日格式
        LocalDate localDate = LocalDate.now();
        // 备份文件名称
        String fileName = localDate.toString() + ".sql";
        // 输出
        File file = new File("backup", fileName);
        file.getParentFile().mkdirs();
        file.createNewFile();

        // 备份今天数据库
        DbUtil.backup(file, user, password, db);
        // 删除七天前数据库备份文件
        LocalDate before = localDate.minusDays(7);
        String fileBeforeName = before.toString()+".sql";
        File fileBefore = new File("backup", fileBeforeName);
        if (fileBefore.exists()) {
            fileBefore.delete();
        }
        logger.info("系统结束定时任务数据库备份");
    }

}

2、备份工具类

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DbUtil {

    public static void backup(File file, String user, String password, String db) {
        try {
            Runtime rt = Runtime.getRuntime();
            String command = "mysqldump -u" + user + " -p" + password + " --set-charset=utf8 " + db;
            Process child = rt.exec(command);
            InputStream inputStream = child.getInputStream();
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8));
            OutputStreamWriter outputStreamWriter = new OutputStreamWriter( new FileOutputStream(file), StandardCharsets.UTF_8);
            String str;
            while ((str = bufferedReader.readLine()) != null) {
                outputStreamWriter.write(str + "rn");
            }
            outputStreamWriter.flush();
            inputStream.close();
            bufferedReader.close();
            outputStreamWriter.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void load(File file, String user, String password, String db) {
        try {
            Runtime rt = Runtime.getRuntime();
            String command = "mysql -u" + user + " -p" + password + " --default-character-set=utf8 " + db;
            Process child = rt.exec(command);
            OutputStream outputStream = child.getOutputStream();
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(file), StandardCharsets.UTF_8));
            OutputStreamWriter outputStreamWriter = new OutputStreamWriter(outputStream, StandardCharsets.UTF_8);
            String str;
            while ((str = bufferedReader.readLine()) != null) {
                outputStreamWriter.write(str + "rn");
            }
            outputStreamWriter.flush();
            outputStream.close();
            bufferedReader.close();
            outputStreamWriter.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}