mysqldump实现数据库的备份

利用mysql的工具mysqldump可以实现备份的，生成一个sql文件，例如

mysqldump -h 127.0.0.1 -P 3306 -uroot -pmysql --databases dbname > F:\test.sql

mysqldump的使用可以参考MySQL之mysqldump的使用
java代码中使用mysqldump实现数据库的备份，跟在cmd里边儿执行普通命令差不多得，不过要确保mysqldump和mysql所在的目录配置环境变量，例子在windows上面操作的，linux部署和docker部署也一样，要配置环境变量

备份的代码

public class MysqlDataUtils {

    private static final String USERNAME = "root";
    private static final String PASSWORD = "mysql";
    private static final String HOST = "localhost";
    private static final String PORT = "3306";

    public static void backup(String database) throws IOException {
        String parentPath = "F:" + File.separator + database;
        File parentDir = new File(parentPath);
        if (!parentDir.exists())
            parentDir.mkdirs();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        File fileSql = new File(parentPath + File.separator + database + "_" + sdf.format(new Date())+".sql");
        if (!fileSql.exists())
            fileSql.createNewFile();
        StringBuilder builder = new StringBuilder();
        builder.append("mysqldump");
        builder.append(" -h " + HOST);
        builder.append(" -P " + PORT);
        builder.append(" -u" + USERNAME);
        builder.append(" -p" + PASSWORD);
        builder.append(" --databases " + database + " > ");
        builder.append(fileSql.getAbsolutePath());
        System.out.println("cmd命令:" + builder.toString());
        Runtime runtime = Runtime.getRuntime();
        runtime.exec("cmd /c" + builder.toString());
        System.out.println("备份成功");
    }
    public static void main(String[] args) throws IOException {
        backup("spring_security");
    }
}

测试



还原的代码

public static void reduction(String database, File file) {
        if (!file.exists())
            return;
        StringBuilder builder = new StringBuilder();
        builder.append("mysql");
        builder.append(" -h " + HOST);
        builder.append(" -P " + PORT);
        builder.append(" -u" + USERNAME);
        builder.append(" -p" + PASSWORD);
        builder.append(" " + database + " < ");
        builder.append(file.getAbsolutePath());
        System.out.println("cmd命令为" + builder.toString());
        Runtime runtime = Runtime.getRuntime();
        System.out.println("开始还原数据");
        try {
            Process process = runtime.exec("cmd /c" + builder.toString());
            InputStream inputStream = process.getInputStream();
            BufferedReader bf = new BufferedReader(new InputStreamReader(inputStream, "utf8"));
            String line = null;
            while ((line = bf.readLine()) != null) {
                System.out.println(line);
            }
            inputStream.close();
            bf.close();
        } catch (IOException e) {
            System.out.println("还原失败");
        }
        System.out.println("还原成功");
    }

测试
先修改下数据库

利用刚刚备份的sql文件，具体生产环境使用的时候另外写逻辑咯

public static void main(String[] args) throws IOException {
        //backup("spring_security");
        reduction("spring_security", new File("F:\\spring_security\\spring_security_20201123164614.sql"));
    }