老板再也不用担心删库跑路了 | mysql系列（二）数据库备份

三年三月
371 阅读10分钟

前言

一名正在自由职业的程序员的独立开发之路

参考链接

mysql使用load data local infile导入数据Error 3948和Error 2068

MySQL 导出数据

MySQL 导入数据

Docker安装的MySQL没有Mysqlbinlog和Mysqlimport等工具的解决办法

说明

一个项目最核心的就是数据，所以对mysql的数据备份是重中之重

本篇文章主要实现自己的数据库备份，不会对每个指令去讲解，深扣知识点，重要的是达到我的目的

目标

  1. 数据库备份
  2. 自动化脚本实现每天固定时间自动化备份数据
  3. 自动化脚本实现 自动恢复 数据
  4. 每天备份数据的同时通过邮件备份一份数据 发到 自己邮箱中（两次备份保险）

mysql 备份语句（导入导出）

最终结果语句

# 导出 scan 这个数据库的所有数据 到 文件 /var/lib/mysql-files/scan.aql
mysqldump -u root -p密码 scan > /var/lib/mysql-files/scan.sql
#导入备份数据到 某个数据
# 如果scan 数据库不存在，需要先创建
mysqladmin -u root -p create scan
# 将备份的数据库导入
mysql -u root -p scan < var/lib/mysql-files/scan.sql

知识点总汇（关于导入导出）

# 导出为 txt格式（csv都可以）（导入也是一样的，没有什么区别）
mysqldump -u root -p scan > /var/lib/mysql-files/scan.txt
# 单独导出为一个表 （这个需要登录到mysql）
select * from Banner into outfile '/var/lib/mysql-files/banner.txt';
# 导入某一张表 （与上面是对应的，需要登录到 mysql）
LOAD DATA LOCAL INFILE '/var/lib/mysql-files/banner.txt' INTO TABLE Banner;

遇到的问题

导出数据的时候显示没有权限

报错信息如下

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

原因

需要注意的是，导出文件的地址，不是随便指定的，而是mysql的一个配置 中指定的地址

解决

通过 SHOW VARIABLES LIKE "secure_file_priv"; 查看导出的目录

image.png

当然这个配置可以通过mysql的配置文件改掉，但是此处我没有去改，直接用这个

导入的时候报错

报错信息如下

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

原因

local_infile=OFF (默认) 表示不允许使用load data local infile从客户端导入数据

解决

登录mysql的时候加参数 --local-infile

mysqlimport 命令报错

报错信息如下

mysqlimport: command not found

原因

因为 mysql 8 之后版本把很多自带的工具都放弃了，而我的版本也是8 之后的，所以用不了

解决

还是要改一些mysql级别的配置，需要改到基础文件，还是比较麻烦，此处我就不弄了

想弄的小伙伴可以参考这个文章 Docker安装的MySQL没有Mysqlbinlog和Mysqlimport等工具的解决办法

此处我直接用 mysql -u root -p scan < var/lib/mysql-files/scan.sql 就解决了导入问题，绕过去了

自动化脚本备份（导入导出）

最终结果

# 不需要输入密码了
docker exec -it mysql /bin/bash -c  'mysqldump -u root --password=你的密码 scan > /var/lib/mysql-files/scan.sql'
# 如果怕脚本文件泄漏密码，如下
docker exec -it mysql /bin/bash -c  'mysqldump -u root --p scan > /var/lib/mysql-files/scan.sql'
#同理 导入也是差不多的
docker exec -it mysql /bin/bash -c  'mysqldump -u root --password=你的密码 scan < var/lib/mysql-files/scan.sql'

本来sql的导出语句都已经写出来了，但是却是最麻烦的

原来的思路

直接把上一节中的 mysqldump -u root -p密码 scan > /var/lib/mysql-files/scan.sql 这个sql语句直接放到 shell脚本中，然后触发执行就行

太天真了，当运行的时候，才发现，一个问题接着一个问题，下面一个个列出来

1. docker中的容器的应用的目录不一样

说明

比方说我登录服务器，在服务器无法执行 docker中mysql容器中 shell 脚本

解决

通过docker命令 制定执行

docker exec -it mysql /bin/bash -c 脚本文件路径（注意此处是 mysql容器内部的地址）

2.docker 环境中的 vim指令和 apt-get指令找不到，无法写脚本文件

问题

我在进入 docker中的容器 mysql后，通过 mkdir和touch来创建了 文件和脚本文件

但是在我用 vim去编写脚本内容的时候，却发现 vim指令找不到

bash: vim: command not found

通过文章 学习

用 apt-get install vim 来安转 vim

但是又报错 bash: apt-get: command not found

又尝试了下 yum install vim

但是又报错 bash: yum: command not found

此时一头雾水，经过学习

知识点如下：

  1. 我的服务是 centos的系统，但是不代表我的 docker环境也是centos的，可能是其他的系统，docker就像在你原来的系统上面装了一个 虚拟机，系统可以任意选择的
  2. 一般 centos系统用 yum来安装
  3. 一般 ubuntu 系统用 apt-get 来安装

所以要弄清楚现在的docker运行的是什么系统

  1. 通过 cat /etc/os-release 来查看 docker运行的系统

image.png

  1. 查找到资料，关于 Oracle Linux Server 这个系统的官方资料如下 操作手册

官网上显示可以 直接使用 vim，所以我也不知道咋回事

后来又想出来两个办法，实现写脚本

3.另外两种在docker 中写 shell的脚本

第一种

在容器外面通过vim把脚本文件写好，复制到 容器里面

docker cp /mnt/hgfs/**.sql 容器id:/home/**.sql

docker中mysql执行脚本文件

第二种

通过直接写的方式

# 注意 1. 必须先进入到 docker 中
echo hello world > myfile

参考操作手册

shell文件终于建好了，下面就是执行了

4.在容器外通过 docker执行文件，显示没权限

问题

执行 docker exec -it mysgl /bin/bash -c /fang_mysgl/sgl_export_data.sh

报错 bin/bash: /fang_mysql/sql_export_data.sh: Permission denied

69c538e6b2dc68dee9d0c9580da2adb.png

原因

docker是单独的一个虚拟空间，外面的用户权限，在里面不一定能用

解决

  1. 给当前用户加到 docker组中
  2. 重启docker
  3. 重启 mysql（docker内部所有的应用都需要再重启）

docker命令permission denied

相关操作截图如下

fa61600df1b81d491d4c96c9c0a0947.png

ade3edad11e8e512c99b3645e8059ab.png

323d9820aec13597a682b48b958cfa9.png

image.png

c84cf4c8a8a9c88c7fa2df56c22614c.png

结果

权限还是没有通，后续再拿给你吧,也不是啥原因

最后通过 docker exec -it mysql /bin/bash -c 'mysqldump -u root --password=你的密码 scan > /var/lib/mysql-files/scan.sql' 来先解决

总结知识点

  1. docker 相当于虚拟机，会自己有一个系统，所以要注意
  2. 通过 cat /etc/os-release 查看docker的系统（前提是先进入docker）
  3. docker 设置完用户后需要重启，重启的话，docker内部的所有的应用 也就挂了，也是需要重启的
# 相关命令总结
# 查看docker 所有应用的状态
docker ps -a
# docker 重启
sudo service docker restart
systemctl restart  docker
# docker 重启某个应用
docker start id
# 将容器外的文件复制到容器内
docker cp /mnt/hgfs/**.sql 容器id:/home/**.sql
# 在容器外执行容器内的脚本
docker exec -it mysql /bin/bash -c /fang_mysql/sql_export_data.sh

# docker常规操作——启动、停止、重启容器实例

设置定时任务执行脚本

关于SQL备份和恢复的脚本语句我们在上一节已经写好了，而且也执行测试过，是正确的

那么下面就需要定时的去执行脚本即可

此处介绍两种方法来实现在服务器端实现定时任务

每日凌晨1点准时执行脚本备份SQL的所有数据

方法一

介绍

crontab是linux自带一个实现定时任务的命令

相关API

# 查看crontab 状态
# 查看所有任务
crontab -l
# 增加编辑任务
crontab -e
#重启服务
sudo service crond restart 
# 查看执行日志
tail -f /var/log/cron

参考文档

Linux crontab 命令

Linux Crontab命令定时任务基本语法与操作教程

实践

在实践过程中，发现 当我直接在 编辑任务中 写 shell 语句的时候，是能够成功执行的

但是当我把shell 语句放到 shell 脚本中，以文件的形式 引用执行，发现它有执行记录，但是没有得到输出的结果

  1. 手动执行脚本，能产生预想的结果，说明脚本应该没有问题
  2. 在脚本内部，已经没有相关环境变量了，应该也没有问题

实在不知道问题出在哪里 这是我执行的任务，每隔一分钟就执行 语句，将 123 写入 bbb的文件里面

# 这样写就是可以执行的，有bbb的文件输出
*/1 * * * * echo 123 >> /fang/crontab_test/bbb.txt
# 这样写就不行，test.sh 的内容就和上面的一样
*/1 * * * * /fang/crontab_test/test.sh

# 一下为 /fang/crontab_test/test.sh 脚本内容
#!/bin/bash
. /etc/profile
. ~/.bash_profile

echo 456 >> /fang/crontab_test/ccc.txt

结论

这种方法遇到了一些问题，按照网上的办法也排查过了，但是没有用，也没有什么报错信息，就不太好排查，所以就学习一下，换种其他的方式

方法二

介绍

通过node程序来实现定时任务，我们自己就有在后端实时跑的服务，就可以在里面写

node-schedule

node中可以通过三方插件node-schedule来实现 定时器任务

child_process

node中可以通过三方插件child_process来实现 执行shell

具体详细的介绍，此处省略，可以参看我下面的参考链接

实际怎么使用的直接看我下面的代码即可

参考文档

node+koa框架开发服务器之定时器

nodejs中执行Shell命令（koa+vue)

代码

# 此处实现的是 每分钟的 第 1秒钟执行一次任务
# 任务是动态的，文件名是根据日期来定的
# 账号密码也从配置里面取，不至于泄漏
# 如果你愿意，这里面的东西都可以实现动态化的实现，你可以在 web 端实现一个 命令行终端
schedule.scheduleJob('1 * * * * *', async () => {
    console.log('当前系统 =====',process.platform)
    console.log('当前时间 =====',new Date())
    if(process.platform === 'linux'){
        let cmdStr = `docker exec mysql /bin/bash -c  'mysqldump -u ${config.userName} --password=${config.password} scan > /var/lib/mysql-files/scan-${dayjs().format("YYYY-MM-DD_HH:mm:ss")}.sql'`
        console.log('cmdStr  =====',cmdStr)
        await doShellCmd(cmdStr)
    }
})
# 执行 CMD 命令
const doShellCmd = cmd =>{
  let str=cmd;
  let result={};
  return new Promise(function(resolve,reject){
    try {
        exec(str,function(err,stdout,stderr){
            if(err){
                console.log('err',err);
                result.errCode=500;
                result.data="操作失败！请重试";
                reject(result);
            }else{
                console.log('stdout ',stdout);//标准输出
                result.errCode=200;
                result.data="操作成功！";
                resolve(result);
            }
        })
    } catch (error) {
        console.log('error ==== in catch',error)
        reject(error)
    }
  })
}

实践中遇到的问题

问题一

报错如下

the input device is not a TTY

image.png

原因

linux执行定时任务的时候没有终端设备，TTY一词源于Teletypes,或teletypewriters。其实出现该错误和我们的一个习惯有关，一般来说我们启动容器后要与容器进行交互操作，这是，就要加上"-it"这个参数，而在定时任务中，如果让脚本在后台运行，就没有可交互的终端，这就会引发如题所示错误，解决办法就是去掉“-it”这个参数。

Docker初识：the input device is not a TTY

问题二

报错如下

mysqldump: Couldn't find table: "15:03:01.sql"

image.png

原因

就是文件名字不能有空格，修改一下就好了

结论

通过上面的代码以及介绍，我们来做个总结

  1. 用代码程序书写，很多动态参数，比如日期等 都更加方便的获取，拓展性很大
  2. 便于调试，服务器的crontab去执行脚本shell，调试起来非常麻烦，而在node服务中还可以debugger
  3. 也方便对外做成一个接口，方便随时调用，能够实现 web端强求调用，更加方便

自动邮件备份

介绍插件

nodemailer 模块是一个发送邮件的三方插件，简单易用

最终代码

//每天凌晨 2点执行
schedule.scheduleJob('0 0 2 * * *', async () => {
    console.log('当前系统 =====',process.platform)
    console.log('当前时间 =====',new Date())
    if(process.platform === 'linux'){
        let fileName = `scan-${dayjs().format("YYYY-MM-DD_HH:mm:ss")}.sql`
        let cmdStr = `docker exec mysql /bin/bash -c  'mysqldump -u ${config.userName} --password=${config.password} scan > /var/lib/mysql-files/${fileName}'`
        let res1 = await doShellCmd(cmdStr)
        console.log('备份sql成功 ===========',res1)
        // 成功之后，取文件把 文件发送给管理员备份
        // 先把 docker 容器中 文件拷贝到 外面
        let res2 = await doShellCmd(`docker cp mysql:/var/lib/mysql-files/${fileName} /xxx/backup/`)
        console.log('docker拷贝文件到外部服务 成功 ===========',res2)

        // testEmal()
        let mailInfo = {
            to:'发送到的邮箱',
            subject:`数据库备份成功-${dayjs().format("YYYY-MM-DD_HH:mm:ss")}`,
            text:`
            每日备份数据库任务完成
            时间：${dayjs().format("YYYY-MM-DD_HH:mm:ss")}
            路径：/var/lib/mysql-files/${fileName}
            `,
            // html:`<h1 style='color:red'>发送的代码，html的</h1>`,
            attachments:[
                {
                    filename:fileName,
                    path:文件路径
    
                }
            ]
        }
        console.log('发送邮件的参数是 ====',mailInfo)
        发送邮件方法，在下面
        let res3 =  await commonHelp.sendMail(mailInfo)
        console.log('发送邮件成功 ===========',res3)
    }
})

const transporter = nodemailer.createTransport({
    host: "smtp.qq.com",
    port: 587,
    secure: false,
    auth: {
        user: "xxx@qq.com",
        pass: 密码
    }
})
const sendMail = async ({to='',subject='',text='',html='',attachments=[]})=>{
    return new Promise(async (resolve,reject)=>{
        try {
            if(!to) reject('to 参数不对')
            if(!subject || typeof(subject) != 'string') reject('subject 参数不对')
            if(!text || typeof(text) != 'string') reject('text 参数不对')

            let mailInfo = {to,subject,text}
            if(html) mailInfo.html = html
            if(Array.isArray(attachments) && attachments.length>0)  mailInfo.attachments = attachments

            mailInfo.from = '623003648@qq.com'

            let res = await transporter.sendMail(mailInfo)
            console.log('发送邮件成功 ====',res)
            resolve()
        } catch (error) {
            reject(error)
        }
    })
}

实践步骤

申请邮箱的密码

申请邮箱的SMTP服务，需要邮箱的授权，会得到一个密码，这个密码不是你登录的密码，而是生成一串密钥

此处以QQ为例

image.png

fdeb5611073021be898d188b46270ac.png

image.png

image.png

遇到的问题

端口和加密的问题

一般 port: 587,secure: false,

如果 port 不写的话，默认 25，而25的端口 会被 腾讯云 和 阿里云默认给封了不开放，需要去申请开放

如果secure true 是指的是加密，那么就必须要有 SSl证书（这个我没有试验，应该是你服务器应该得 HTPPS的服务才可以，待求证）

84846d55996c80751913da7bc5b0b54.png

参考文档

# Node.js通过nodemailer模块发送邮件 官网

总结

这一篇虽然只是一个备份，但是中间牵扯的内容太多了，知识点太多，也耗费了很多的时间

TODO 总结

TODO 因为备份的文件太多了，还需要考虑压缩，后续优化

avatar
文章
阅读
粉丝