三种方法备份 MySQL 数据库（含docker）

超、凢脫俗 2023-10-03 12:01 41阅读 0赞

#### 文章目录 ####

*   *   *   *  一、备份MySQL
             *  二、还原 MySQL
             *  三、3 种实现备份的方法
             *   *  1、shell 脚本
                 *   *  ①、描述
                     *  ②、脚本
                     *  ③、执行 shell 脚本
                     *  ④、docker 容器
                     *  ⑤、开启定时任务
                     *  ⑥、重点：使用 scp 传输备份文件
                 *  2、Java 代码
                 *  3、bat 批处理

##### 一、备份MySQL #####

备份 MySQL 数据库核心就是使用 mysqldump 命令，如;

mysqldump -h127.0.0.1 -P3306 -utest -p123456 student > I:\sql\2021-07-27_14-19-14\student.sql

注意：使用备份命令需要开启相关数据库权限，如下：我的数据库比较简单，只需要开启三个权限  
备份 MySQL 必要权限，参考我的另一篇博客 [https://blog.csdn.net/qq\_41538097/article/details/118928032][https_blog.csdn.net_qq_41538097_article_details_118928032]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70]

##### 二、还原 MySQL #####

还原 mysql 方法比较多，可以使用 可视化工具导入，或者使用 source 命令导入，下面说说和备份命令相对的还原命令

mysql -h127.0.0.1 -P3306 -utest -p123456 student < 2021-07-27_14-19-04\student.sql

注意以下几点

*  需要还原的 sql 文件存在
 *  还原需要保证数据库存在
 *  还原需要开启必要的权限，具体的权限根据数据库内容不同，可自行开启，如果想偷个懒，使用 root 用户即可（一般 root 用户开启了所有的权限）

如果要使用脚本实现，可参考备份脚本自行实现

##### 三、3 种实现备份的方法 #####

###### 1、shell 脚本 ######

shell需要用刚学的，小白一个，可能考虑不够全面，望见谅

###### ①、描述 ######

*  备份 mysql 数据库
 *  凌晨一点执行 shell 脚本
 *  删除超过七天的备份

###### ②、脚本 ######

#!/bin/bash
    dateBackup=$(date +%Y-%m-%d_%H:%M:%S)
    dir="/root/backupDB/sql/${dateBackup}"
    if [ ! -d "${dir}" ]
    then
            mkdir ${dir}
            echo "创建文件夹成功"
    else
            echo "文件夹已经存在"
    fi
    # 需要备份的数据库名
    dbNames=(mybatis mybatis-plus)
    
    for dbName in ${dbNames[@]}
    do
    		echo "-----------------> 备份 ${dbName} 数据库 <-----------------" >> /root/backupDB/log/${dateBackup}.log
    		# 备份MySQL， 1>> 为正常输出，2>> 为错误或者警告输出到日志文件
            mysqldump -utest -p123456 ${dbName} 1>> ${dir}/${dbName}.sql 2>> /root/backupDB/log/${dateBackup}.log
            # 压缩文件 解压为 gzip -d mybatis.sql.gz
            gzip ${dir}/${dbName}.sql
    done
    
    echo -e " \n ----------------> 删除过期文件 <---------------------------" >> /root/backupDB/log/${dateBackup}.log
    # 判断文件夹数量是否大于7，防止程序意外停止，删除所有备份
    dirCount=`ls -l /root/backupDB/sql/|grep "^d"|wc -l`
    if [ ${dirCount} -gt 7 ]
    then
    	# 删除超过七天的 sql 备份
    	find /root/backupDB/sql -mtime +6 -name "*_*" -exec rm -rf {
        } \;
    	#删除超过七天的 日志
    	find /root/backupDB/log -mtime +6 -name "*.log" -exec rm -rf {
        } \;
    	echo -e " 删除过期文件成功" >> /root/backupDB/log/${dateBackup}.log
    else 
    	echo "删除过期文件失败，文件数量小于 7 " >> /root/backupDB/log/${dateBackup}.log
    fi

###### ③、执行 shell 脚本 ######

> 可以使用 chmod 777 backup.sh 添加执行权限， ./backup.sh 启动  
> 或者  
> 使用 sh ./backup.sh 启动
> 
> 执行脚本生成目录如下  
> ![在这里插入图片描述][73791c7f084d4d4ab20eff21c29ce66b.png]

###### ④、docker 容器 ######

如果你的 mysql 是 docker 容器，则需要修改上面的 mysqldump 备份命令如下

docker exec -i 111122223333 /bin/bash -c 'mysqldump -utest -p123456 ${dbName} 1>> ${dir}/${dbName}.sql 2>> /root/backupDB/log/${dateBackup}.log'

可以在启动时加一个挂载目录 ,我备份 sql 的挂载目录为 `-v /opt/docker/mysql/backup:/opt/backup` ,/opt/docker/mysql/backup宿主机目录，/opt/backup容器目录

#!/bin/bash
    dateBackup=$(date +%Y-%m-%d_%H:%M:%S)
    dir=/opt/docker/mysql/backup/${dateBackup}
    # 宿主机新建目录，通过挂载会自动添加到容器
    if [ ! -d "${dir}"]
    then
    		mkdir ${dir}
    		echo "创建文件夹 ${dir} 成功" >> ${dir}/error.log
    else
    		echo "创建文件夹 ${dir} 失败，文件夹已存在" >> ${dir}/error.log
    fi
    # 需要备份的数据库名		
    dbNames=(student teacher car dog cat)
    for dbName in ${dbNames[@]}
    do
    		echo "-----------------> 备份 ${dbName} 数据库 <-----------------" >> ${dir}/error.log
            docker exec -i 642c89599d9b sh -c "mysqldump -ubackup -pAdmin@123 -h127.0.0.1 -P32773 ${dbName} 1>> /opt/backup/${dateBackup}/${dbName}.sql 2>> /opt/backup/${dateBackup}/error.log"
            gzip ${dir}/${dbName}.sql
    done
    
    echo -e " \n ----------------> 删除过期文件 <---------------------------" >> ${dir}/error.log
    # 判断文件夹数量是否大于7，防止程序意外停止，删除所有备份
    dirCount=`ls -l /opt/docker/mysql/backup/|grep "^d"|wc -l`
    if [ ${dirCount} -gt 7 ]
    then
    	# 删除超过七天的带"_"的目录
    	find /opt/docker/mysql/backup/ -mtime +6 -name "*_*" -exec rm -rf {
        } \;
    	echo -e " 删除过期文件成功" >> ${dir}/error.log
    else 
    	echo "删除过期文件失败，文件数量小于 7 " >> ${dir}/error.log
    fi

###### ⑤、开启定时任务 ######

如果上面的脚本可以正常执行，下面开启定时任务，固定在每天凌晨一点执行

如果没有 crontab 命令自行百度，cron 表达式不清楚的自行百度

>  *  1、添加定时任务  
>     crontab -e
>  *  2、凌晨 01：00 分执行 /root/backupDB/backup.sh 文件  
>     0 1 \* \* \* /root/backupDB/backup.sh
>  *  3、查看定时任务  
>     crontab -l
>  *  4、查看定时任务日志  
>     tail -f /var/log/cron
>  *  5、crond 服务  
>     开启、关闭、重启 可以使用 systemctl start、stop、restart crond

添加定时任务，记得重启 crond 服务，`systemctl restart crond`

如果手动启动脚本可以执行，定时任务报错找不到命令，则大概是环境变量问题，我们可以添加环境变量，你需要判断你用到的命令是在用户环境变量配置还是系统环境变量配置，添加即可

shell 脚本中添加

#!/bin/bash
    # 当前用户环境变量
    source ~/.bashrc
    #  系统环境变量
    source /etc/profile

定时任务中添加

# 当前用户环境变量
    0 1 * * *  . ~/.bashrc;/root/backupDB/backup.sh
    # 系统环境变量
    0 1 * * *  . /etc/profile;/root/backupDB/backup.sh

**其他应该注意的问题**

参考：[https://blog.csdn.net/coolhe21cn/article/details/117445973][https_blog.csdn.net_coolhe21cn_article_details_117445973]

*  1）新创建的 cron job，不会马上执行，至少要过2分钟才执行。如果重启 crond 则马上执行。
 *  2）每条 JOB 执行完毕之后，系统会自动将输出发送邮件给当前系统用户。日积月累，非常的多，甚至会撑爆整个系统。所以每条 JOB 命令后面进行重定向处理是非常必要的： > /dev/null 2>&1 。前提是对 Job 中的命令需要正常输出已经作了一定的处理, 比如追加到某个特定日志文件。
 *  3）当 crontab 突然失效时，可以尝试 systemctl restart crond 解决问题。或者查看日志看某个job有没有执行/报错 tail -f /var/log/cron。
 *  4）千万别乱运行 crontab -r。它从 Cronta b目录（/var/spool/cron）中删除用户的 Crontab 文件。删除了该用户的所有 crontab 都没了。
 *  5）在 crontab 中 % 是有特殊含义的，表示换行的意思。如果要用的话必须进行转义 %，如经常用的date ‘+%Y%m%d’ 在crontab里是不会执行的，应该换成 date ‘+%Y%m%d’\`。

**crontab中的输出配置**

crontab中经常配置运行脚本输出为：> /dev/null 2>&1，来避免 crontab 运行中有内容输出。

*  shell 命令的结果可以通过‘> ’的形式来定义输出
 *  /dev/null 代表空设备文件
 *  1 表示 stdout 标准输出，系统默认值是1，所以 “> /dev/null” 等同于"1 > /dev/null"
 *  2 表示 stderr 标准错误, & 表示等同于的意思，2>&1，表示2的输出重定向等同于1
 *  1>/dev/null 首先表示标准输出重定向到空设备文件，也就是不输出任何信息到终端，不显示任何信息。
 *  2>&1 表示标准错误输出重定向等同于标准输出，因为之前标准输出已经重定向到了空设备文件，所以标准错误输出也重定向到空设备文件。

###### ⑥、重点：使用 scp 传输备份文件 ######

一般数据库备份都不是一份且不放在同一个服务器上，如果 mysql 直接在 Linux 服务器，则可以不用 scp 传输，因为备份命令可以指定另一台服务器 ip ，但是如果是 docker 容器中 mysql ，需要将备份文件拷贝到远程主机（或许 docker 有一些高端操作，暂时没遇到），因为 docker 容器备份 MySQL 只能在当前主机，scp 拷贝 docker 中 备份的 mysql 到另一台服务器看我另一篇博客 [scp 传输文件到另一台服务器][scp]

###### 2、Java 代码 ######

这种用的不多

public class TestBackup {
        
        public static void main(String[] args) throws Exception {
        
            dbBackUp("111.111.111.111", "test", "123456", "3306", "mybatis,mybatis-plus", "I:\\spring\\sendsms\\");
            //reduction("111.111.111.111", "3306", "root", "123456", "mybatis-plus", "2021-07-27_14-19-04/mybatis-plus.sql");
        }
    
        public static void dbBackUp(String host, String userName, String password, String port, String dbNames, String backupPath) {
        
            String format = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(new Date());
            boolean mkdir = new File(format).mkdir();
            if (!mkdir) return;
            String[] dbNameArray = dbNames.split(",");
            for (String dbName : dbNameArray) {
        
                String pathSql = backupPath + format + "\\" + dbName + ".sql";
                String cmd = "cmd /c mysqldump" + " -h" + host + " -P" + port + " -u" + userName + " -p" + password + " " + dbName + " > " + pathSql;
                System.out.println("cmd命令为：" + cmd);
                try {
        
                    Process process = Runtime.getRuntime().exec(cmd);
                    if (process.waitFor() == 0) {
        
                        System.out.println(dbName + "数据库备份成功!");
                    } else {
        
                        System.out.println(dbName + "数据库备份失败!");
                    }
                } catch (IOException | InterruptedException e) {
        
                    e.printStackTrace();
                }
            }
        }
    
        public static void reduction(String host, String port, String userName, String password, String databaseName, String fileName) throws Exception {
        
            File datafile = new File(fileName);
            if (!datafile.exists()) {
        
                System.out.println(fileName + "文件不存在，请检查");
                return;
            }
            String cmd = "cmd /c mysql -h" + host + " -P" + port + " -u" + userName + " -p" + password + " " + databaseName + " < " + datafile;
            System.out.println(cmd);
            //拼接cmd命令
            Process exec = Runtime.getRuntime().exec(cmd);
            if (exec.waitFor() == 0) {
        
                System.out.println(databaseName + "数据库还原成功，还原的文件为：" + datafile);
            } else {
        
                System.out.println(databaseName + "数据库还原失败");
            }
        }
    }

运行结果如下，备份成功  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 1]

###### 3、bat 批处理 ######

核心命令和上面类似，适用与 window系统服务器

①、新建 backup.bat 文件，输入下面脚本

d:
    ::格式化时间，如20210808
    set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
    ::新建文件夹
    md D:\备份mysql\%ymd%
    ::循环数据库，备份数据库到指定目录，打印日志到指定文件
    for %%I in (mybatis,mybatis-plus) do mysqldump -utest -h1.*.*.107 -p123456 -P3306 %%I > D:\备份mysql\%ymd%\%%I.sql 2> D:\备份mysql\%ymd%\error.log

备份成功，结果如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 2]  
②、添加定时任务  
在 window 搜索中搜索管理工具，或者任务计划，打开任务计划添加定时任务  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 3]  
右键任务计划程序库创建任务

> 配置权限![这里是引用][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 4]  
> 配置触发器  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 5]  
> 配置刚才写的批处理文件  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 6]  
> 最后出入密码，定时任务配置成功  
> ![在这里插入图片描述][2acb60dabdff4d9abd905337a80f1bf6.png]

定时任务成功执行  
![在这里插入图片描述][9896fc26cbe741de87673590518fb3b7.png]

[https_blog.csdn.net_qq_41538097_article_details_118928032]: https://blog.csdn.net/qq_41538097/article/details/118928032
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/aefc0938105b4d3aa459500fc7ea1696.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3,size_16,color_FFFFFF,t_70
[73791c7f084d4d4ab20eff21c29ce66b.png]: https://img-blog.csdnimg.cn/73791c7f084d4d4ab20eff21c29ce66b.png
[https_blog.csdn.net_coolhe21cn_article_details_117445973]: https://blog.csdn.net/coolhe21cn/article/details/117445973
[scp]: https://blog.csdn.net/qq_41538097/article/details/119729610
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/7557fd1d13b44033873270307598574e.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/91180b1ba7f84980a418c2b30b8bfef0.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/bc32b7080ec3471eb74346b78a61f302.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/1ef3a2ec6a4748f38c49574a3256d7b8.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/b68d6febaa4d4a9bab74a0c526a5eb5c.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/256af70b80d44a95b8934c7af26bf3cd.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3,size_16,color_FFFFFF,t_70
[2acb60dabdff4d9abd905337a80f1bf6.png]: https://img-blog.csdnimg.cn/2acb60dabdff4d9abd905337a80f1bf6.png
[9896fc26cbe741de87673590518fb3b7.png]: https://img-blog.csdnimg.cn/9896fc26cbe741de87673590518fb3b7.png