• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    浅析mysql 定时备份任务

    简介

    在生产环境上,为了避免数据的丢失,通常情况下都会定时的对数据库进行备份。而Linux的crontab指令则可以帮助我们实现对数据库定时进行备份。首先我们来简单了解crontab指令,如果你会了请跳到下一个内容mysql备份。
    本文章的mysql数据库是安装在docker容器当中,以此为例进行讲解。没有安装到docker容器当中也可以参照参照。

    contab定时任务

    使用crontab -e来编写我们的定时任务。

    0 5 * * 1 [command]

    前面的5个数字分别代表分、时、日、月、周,后面的 command为你的执行命令。
    假如你需要在每天晚上8点整执行定时任务,那么可以这么写

    0 8 * * * [command]

    扩展:

    mysql备份

    快速上手

    这里我的mysql数据库是docker容器。假如你需要在每天晚上8点整执行定时任务,那么可以这么写。
    首先执行命令crontab -e。

    0 8 * * * docker exec mysql_container mysqldump -uroot -proot_password database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

    mysql_container 为你的数据库容器名
    mysqldump 是mysql数据库导出数据的指令
    -u 填写root账号
    -p 填写root密码
    database_name 需要备份的数据库名
    /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql 备份文件,后面是文件名的格式

    如果你没什么要求,单纯的只是想要备份,那么上面那个命令就可以帮你进行定时备份。

    小坑: mysql备份的时候我使用了docker exec -it mysqldump ... 这样的命令去做bash脚本,因为-i参数是有互动的意思,导致在crontab中执行定时任务的时候,没有输出数据到sql文件当中。所以使用crontab定时的对docker容器进行备份命令的时候不要添加-i参数。

    crontab优化

    我不建议直接在crontab -e里面写要执行的命令,任务多了就把这个文件写的乱七八招了。
    建议把数据库备份的命令写成一个bash脚本。在crontab这里调用就好了
    如:建立一个/var/backups/mysql/mysqldump.sh文件,内容如下

    docker exec mysql_container mysqldump -uroot -pmypassword database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

    然后把文件改为当前用户可执行的:

    chmod 711 /var/backups/mysql/mysqldump.sh

    执行crontab -e命令修改成如下:

    0 20 * * * /var/backups/mysql/mysqldump.sh

    那么这样就比较规范了。

    mysql备份优化

    因为sql文件比较大,所以一般情况下都会对sql文件进行压缩,不然的话磁盘占用就太大了。
    假设你做了上面这一步 crontab优化,我们可以把mysqldump.sh脚本改成下面这样:

    export mysqldump_date=$(date +%Y%m%d_%H%M%S)  \
    
    docker exec mysql_container mysqldump -uroot -pmypassword database_name> /var/backups/mysql/$mysqldump_date.sql  \
    
    gzip /var/backups/mysql/$mysqldump_date.sql
    find /var/backups/mysql/ -name "*.sql" -mtime +15 -exec rm -f {} \;

    export 在系统中自定义了个变量mysqldump_date,给备份和压缩命令使用
    gzip 为压缩命令,默认压缩了之后会把源文件删除,压缩成.gz文件
    find ... 这行命令的意思为,查询/var/backups/mysql/目录下,创建时间15天之前(-mtime +15),文件名后缀为.sql的所有文件 执行删除命令-exec rm -f {} \;。总的意思就是:mysql的备份文件只保留15天之内的。15天之前的都删除掉。

    数据恢复

    若一不小心你执行drop database,稳住,淡定。我们首先要创建数据库被删除的数据库。

    >mysql create database database_name;

    然后恢复最近备份的数据。恢复备份的命令:

    docker exec -i mysql_container mysql -uroot -proot_password database_name  /var/backups/mysql/20200619_120012.sql

    虽然恢复了备份文件的数据,但是备份时间点之后的数据我们却没有恢复回来。
    如:晚上8点进行定时备份,但是却在晚上9点drop database,那么晚上8点到晚上9点这一个小时之内的数据却没有备份到。这时候就要使用binlog日志了。

    binlog日志

    binlog 是mysql的一个归档日志,记录的数据修改的逻辑,如:给 ID = 3 的这一行的 money 字段 + 1。
    首先登录mysql后查询当前有多少个binlog文件:

    > mysql show binary logs;
    +---------------+-----------+-----------+
    | Log_name   | File_size | Encrypted |
    +---------------+-----------+-----------+
    | binlog.000001 |    729 | No    |
    | binlog.000002 |   1749 | No    |
    | binlog.000003 |   1087 | No    |
    +---------------+-----------+-----------+

    查看当前正在写入的binlog

    mysql> show master status\G;

    生成新的binlog文件,mysql的后续操作都会写入到新的binlog文件当中,一般在恢复数据都时候都会先执行这个命令。

    mysql> flush logs

    查看binlog日志

    mysql> show binlog events in 'binlog.000003';

    小知识点:初始化mysql容器时,添加参数--binlog-rows-query-log-events=ON。或者到容器当中修改/etc/mysql/my.cnf文件,添加参数binlog_rows_query_log_events=ON,然后重启mysql容器。这样可以把原始的SQL添加到binlog文件当中。

    恢复数据

    拿回上面例子的这段话。

    晚上8点进行定时备份,但是却在晚上9点drop database,那么晚上8点到晚上9点这一个小时之内的数据却没有备份到。。

    首先进入到mysql容器后,切换到/var/lib/mysql目录下,查看binlog文件的创建日期

    cd /var/lib/mysql
    ls -l
    ...
    -rw-r----- 1 mysql mysql   729 Jun 19 15:54 binlog.000001
    -rw-r----- 1 mysql mysql   1749 Jun 19 18:45 binlog.000002
    -rw-r----- 1 mysql mysql   1087 Jun 19 20:58 binlog.000003
    ...

    从文件日期可以看出:当天时间为2020-06-21,binlog.000002文件的最后更新时间是 18:45 分,那么晚上8点的备份肯定包含了binlog.000002的数据;
    binlog.000003的最后更新日期为 20:58 分,那么我们需要恢复的数据 = 晚上8点的全量备份 + binlog.000003的 20:00 - 执行drop database命令时间前的数据。

    恢复命令格式:

    mysqlbinlog [options] file | mysql -uroot -proot_password database_name

    mysqlbinlog常用参数:

    --start-datetime 开始时间,格式 2020-06-19 18:00:00
    --stop-datetime 结束时间,格式同上
    --start-positon 开始位置,(需要查看binlog文件)
    --stop-position 结束位置,同上
    ...

    恢复备份数据和binlog数据前建议先登录mysql后执行flush logs生成新的binlog日志,这样可以专注需要恢复数据的binlog文件。
    首先我们需要查看binlog日志,在哪个位置进行了drop database操作:

    mysql> show binlog events in 'binlog.000003';
    +---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
    | Log_name   | Pos | Event_type   | Server_id | End_log_pos | Info                                                                    |
    +---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
    | binlog.000003 |  4 | Format_desc  |     1 |     125 | Server ver: 8.0.20, Binlog ver: 4                                                      |
    | binlog.000003 | 125 | Previous_gtids |     1 |     156 |                                                                       |
    | binlog.000003 | 156 | Anonymous_Gtid |     1 |     235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                    |
    | binlog.000003 | 235 | Query     |     1 |     318 | BEGIN                                                                    |
    | binlog.000003 | 318 | Rows_query   |     1 |     479 | # INSERT INTO `product_category` SET `name` = '床上用品' , `create_time` = 1592707634 , `update_time` = 1592707634 , `lock_version` = 0   |
    | binlog.000003 | 479 | Table_map   |     1 |     559 | table_id: 139 (hotel_server.product_category)                                                |
    | binlog.000003 | 559 | Write_rows   |     1 |     629 | table_id: 139 flags: STMT_END_F                                                       |
    | binlog.000003 | 629 | Xid      |     1 |     660 | COMMIT /* xid=2021 */                                                            |
    | binlog.000004 | 660 | Anonymous_Gtid |     1 |     739 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                    |
    | binlog.000004 | 739 | Query     |     1 |     822 | drop database hotel_server /* xid=26 */                                                   |
    +---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------

    根据上面的日志,我们可以看到,在End_log_pos= 822 的位置执行了drop database操作,那么使用binlog恢复的范围就在2020-06-19 20:00:00 - 660 的位置。为什么是660?因为drop database的上一个事务的提交是660的位置,命令如下:

    mysqlbinlog --start-datetime=2020-06-19 20:00:00 --stop-position=660 /var/lib/mysql/binlog.000003 | mysql -uroot -proot_password datbase_name

    如果你的范围包括了822的位置,那么就会帮你执行drop database命令了。不信你试试?
    执行完上面的命令,你的数据就会恢复到drop database前啦!开不开心,激不激动!

    总结

    因为mysql定时备份是在生产环境上必须的任务。是很常用的。所以我就迫不及待的写博客。当然也很感谢我同事的帮助。这篇文章已经写了三天了,因为我也是在不断地试错,不断的更新文章。避免把错误的知识点写出来。如果帮到你了,关注我一波呗!谢谢。

    以上就是浅析mysql 定时备份任务的详细内容,更多关于mysql 定时备份任务的资料请关注脚本之家其它相关文章!

    您可能感兴趣的文章:
    • shell脚本实现mysql定时备份、删除、恢复功能
    • CentOS下mysql定时备份Shell脚本分享
    • MySQL定时备份方案(利用Linux crontab)
    • MySQL定时备份数据库操作示例
    • MySQL数据库定时备份的实现方法
    • 定时备份 Mysql并上传到七牛的方法
    • linux实现定时备份mysql数据库的简单方法
    • linux实现mysql数据库每天自动备份定时备份
    • Mysql数据库定时备份脚本分享
    • Windows下MySQL定时备份脚本的实现
    • mysql自动定时备份数据库的最佳方法(windows服务器)
    • 在Windows环境下使用MySQL:实现自动定时备份
    上一篇:两种方法实现mysql分组计数,范围汇总
    下一篇:一篇文章掌握MySQL的索引查询优化技巧
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    浅析mysql 定时备份任务 浅析,mysql,定时,备份,任务,