springboot -- 每天自动备份 mysql 数据

缺乏、安全感 2023-01-06 13:00 566阅读 0赞

## 一、说明 ##

使用技术： mysqldump mysql 自带工具

### mysqldump备份简单操作： ###

*  mysqldump -h\[ip\] -P\[端口号\] -u\[用户名\] -p\[密码\] 数据库名 表名 >导出的文件名.sql
 *  mysqldump -h\[ip\] -P\[端口号\] -u\[用户名\] -p\[密码\] 数据库名 表名1 表名2 表名3 | gzip >导出的文件名.sql.gz

gzip 为直接压缩导出，需安装gzip 命令可使用  
参考：[https://blog.csdn.net/yuxisanno139/article/details/83016520][https_blog.csdn.net_yuxisanno139_article_details_83016520]

## 二、使用 ##

### 安装mysqldump ###

1、**win**

安装了mysql 数据库即可打开 cmd 执行备份命令，mysqldump 版本同 mysql 版本

2、**Linux**

1、安装mysql 即可使用或 2(建议)  
2、单独安装 mysqldump 安装命令如下

# 此命令安装的mysqldump 版本为 8- , 和实际使用的数据库无关
    yum -y install holland-mysqldump.noarch

3、**其他说明**

*  win 和 cmd 的区别在最后的文件保存目录, 注意更换
 *  mysqldump 8+版本 需要 --column-statistics=0 参数，mysqldump 8- 版本请移除该参数
 *  出现 `sql_mode=...` 的错误看错误一处理方法

### 使用 ###

1、**不压缩备份**

mysqldump --column-statistics=0 -u账号 -p密码 -h域名或ip -P3306 --databases 数据库名  > F:/sql/spring-boot-plus2.sql

2、**压缩备份 (增加 | gzip)**

需安装 gzip 命令， 参考：参考：[https://blog.csdn.net/yuxisanno139/article/details/83016520][https_blog.csdn.net_yuxisanno139_article_details_83016520]

mysqldump --column-statistics=0  -u账号 -p密码 -h域名或ip -P3306 --databases 数据库名  | gzip > F:/sql/spring-boot-plus2.gz

## 三、错误说明 ##

### 错误1 ###

错误信息

mysqldump: [ERROR] unknown variable 'sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'.

处理方法：  
打开mysql 找到目录： my.ini 文件, 注释 `sql_mode=....` 的那一行数据

### 错误2 ###

错误信息：

Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$. ..............

处理方法：  
添加： --column-statistics=0

### 错误3 ###

如果出现已下错误，请尝试使用 root 账号进行操作，注意查看/分配操作权限

mysqldump: Got error: 1045: "Access denied for user 'lpzs'@'106.15.57.160' (using password: YES)" when trying to connect

## 三、springboot 集成使用 ##

### 3.1、使用 ###

该备份使用流, 获取备份数据在写入文件，和直接使用命令有些小区别

本次没有写 sql文件压缩和自动删除 n天前数据，可自行处理

注意：此代码无法使用命令压缩，只能代码压缩

package com.ws.ldy.task.sys;
    
    import com.ws.ldy.common.utils.LocalDateTimeUtil;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.scheduling.annotation.EnableScheduling;
    import org.springframework.scheduling.annotation.Scheduled;
    import org.springframework.stereotype.Component;
    
    import java.io.*;
    
    
    /** * MYSQL 数据自动备份 * <P> * 请参考文章： https://blog.csdn.net/qq_41463655/article/details/112628365 * </P> * @author wangsong * @date 2021/1/14 0014 19:12 * @return * @version 1.0.0 */
    @Component
    @Configuration
    @EnableScheduling
    @Slf4j
    public class MysqlDataBackup { 
    
    
        /** * 备份 sql 存放目录(相对路径, 注意可能需要在 MvcConfig 配置访问权限) */
        String filePath = "File/sql/";
    
        /** * 数据库版本是否为 8.0 + （false=否 true=是）， mysql8+ 需要参数 --column-statistics=0 ， mysql8- 不需要 */
        Boolean isDbVersion8 = false;
    
        /** * 备份命令 * USERNAME 账号 * PASSWORD 密码 * SERVERPATH 服务器IP/域名 * DBNAME 数据库名称 * FILEPATH 备份文件存放地址+名称 * 说明 * cmdCompression ： 需压缩 （本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + gzip 命令(独立安装)） * cmd ： 不压缩 (本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) * --column-statistics=0 mysql8 添加该参数, 非mysql8 不添加, 否则将出错 */
        String cmdMysql8 = "mysqldump --column-statistics=0 -u{USERNAME} -p{PASSWORD} -h{SERVERPATH} -P3306 --databases {DBNAME}"; // > {FILEPATH}.sql
        String cmd = "mysqldump -u{USERNAME} -p{PASSWORD} -h{SERVERPATH} -P3306 --databases {DBNAME}";      // > {FILEPATH}.sql
    
    
        @Value("${spring.datasource.dynamic.datasource.db1.url}")
        private String dbUrl;
    
        @Value("${spring.datasource.dynamic.datasource.db1.username}")
        private String dbUserName;
    
        @Value("${spring.datasource.dynamic.datasource.db1.password}")
        private String dbPassWord;
    
    
        /** * 每天凌晨4点 【 0 0 4 1/1 * ? 】 * 测试 20 秒一次【 0/20 * * * * ? 】 */
        @Scheduled(cron = "0 0 4 1/1 * ?")
        private void configureTasks() { 
            log.info("【备份数据库】--START");
            String dbUrl2 = dbUrl.replace("jdbc:mysql://", "");
    
            // 获取数据库名称
            String dbName = dbUrl2.substring(dbUrl2.lastIndexOf("/") + 1, dbUrl2.indexOf("?"));
            // 获取数据库地址
            String serverPath = dbUrl2.substring(0, dbUrl2.lastIndexOf("/"));
            // 数据库账号
            String username = dbUserName;
            // 数据库密码
            String password = dbPassWord;
    
            // 备份文件目录+名称 备份文件存放目录+名称(名称 = 数据库名+时间字符串.sql)
            String timeStr = LocalDateTimeUtil.convertLDTToStr(LocalDateTimeUtil.now())
                    .replaceAll("-", "_")
                    .replaceAll(" ", "_")
                    .replaceAll(":", "");
            timeStr = timeStr.substring(0, 15);
            String pathFileName = filePath + dbName + "_" + timeStr + ".sql";
            String newCmd = "";
            if (isDbVersion8) { 
                newCmd = cmdMysql8;
            } else { 
                newCmd = cmd;
            }
            // 执行命令
            newCmd =  newCmd.replace("{USERNAME}", username)
                    .replace("{PASSWORD}", password)
                    .replace("{SERVERPATH}", serverPath)
                    .replace("{DBNAME}", dbName)
                    .replace("{FILEPATH}", pathFileName);
            System.out.println(newCmd);
            PrintWriter printWriter = null;
            BufferedReader bufferedReader = null;
            try { 
                // 创建存放sql的文件
                existsFile(new File(pathFileName));
                printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(pathFileName), "utf8"));
                Process process = null;
                String property = System.getProperty("os.name");
                System.out.println(property);
                if (property.indexOf("Linux") != -1) { 
                    // linux
                    process = Runtime.getRuntime().exec(new String[]{ "bash", "-c", newCmd});
                } else { 
                    // 本地win
                    process = Runtime.getRuntime().exec(newCmd);
                }
                InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");
                bufferedReader = new BufferedReader(inputStreamReader);
                String line;
                while ((line = bufferedReader.readLine()) != null) { 
                    printWriter.println(line);
                }
                // 此次会执行过长时间,直到备份完成
                printWriter.flush();
                printWriter.close();
                //0 表示线程正常终止。
                if (process.waitFor() == 0) { 
                    // 线程正常执行
                    log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName);
                }
            } catch (Exception e) { 
                e.printStackTrace();
                log.info("【备份数据库】FAILURE");
            } finally { 
                try { 
                    if (bufferedReader != null) { 
                        bufferedReader.close();
                    }
                    if (printWriter != null) { 
                        printWriter.close();
                    }
                } catch (IOException e) { 
                    e.printStackTrace();
                }
            }
            log.info("【备份数据库】--END");
        }
    
    
        /** * 判断文件是否存在，不存在创建 */
        private static void existsFile(File file) { 
            // 判断文件路径是否存在,不存在新建
            if (!file.getParentFile().exists()) { 
                file.getParentFile().mkdirs();
            }
            if (!file.exists()) { 
                try { 
                    file.createNewFile();
                } catch (IOException e) { 
                    e.printStackTrace();
                }
            }
        }
    }

备份目录展示，win系统 在项目下，linux在部署项目下

![在这里插入图片描述][20210114191826356.png]  
linux 将在项目部署根路径下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNDYzNjU1_size_16_color_FFFFFF_t_70]

### 3.2、失败说明 ###

如果生成的文件中没有数据，可以在控制台获取到 log.info(“当前执行命令: \{\}”, newCmd); 打印的执行命令

win在cmd中执行，linux直接运行，可以看到具体的错误信息

*  个人开源项目（通用后台管理系统）–> [ https://gitee.com/wslxm/spring-boot-plus2][https_gitee.com_wslxm_spring-boot-plus2] , 喜欢的可以看看
 *  ***本文到此结束，如果觉得有用，动动小手点赞或关注一下呗，将不定时持续更新更多的内容…，感谢大家的观看！***

[https_blog.csdn.net_yuxisanno139_article_details_83016520]: https://blog.csdn.net/yuxisanno139/article/details/83016520
[20210114191826356.png]: /images/20221119/f340b4f143934599be8131e1b3c75634.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNDYzNjU1_size_16_color_FFFFFF_t_70]: /images/20221119/0d2f86aba989427094422d452405b32f.png
[https_gitee.com_wslxm_spring-boot-plus2]: https://gitee.com/wslxm/spring-boot-plus2