浅析mysql 定时备份任务

简介

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

contab定时任务

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

0 5 * * 1 [command]

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

0 8 * * * [command]

扩展:

  • crontab -l 可以查看自己的定时任务
  • crontab -r 删除当前用户的所有定时任务

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 定时备份任务的资料请关注我们其它相关文章!

(0)

相关推荐

  • MySQL定时备份数据库操作示例

    本文实例讲述了MySQL定时备份数据库操作.分享给大家供大家参考,具体如下: 1. 查看mysqldump root@laowang:/# which mysqldump /usr/bin/mysqldump 2. 编写脚本 编辑my.cnf文件, 指定账号与密码, 然后在脚本中引用 root@laowang:/# vim /etc/my.cnf [mysqldump] user=root password=root 脚本文件 root@laowang:/var/backups# vim mys

  • Linux下Mysql定时任务备份数据的实现方法

    前言 备份是容灾的基础,是指为防止系统出现操作失误或系统故障导致数据丢失,而将全部或部分数据集合从应用主机的硬盘或阵列复制到其它的存储介质的过程.而对于一些网站.系统来说,数据库就是一切,所以做好数据库的备份是至关重要的! 备份是什么? 本实例将创建目录放置于/mnt目录下,可根据具体情况放置于其他目录: cd /mnt mkdir dbback pwd /mnt/dbback 创建shell脚本 脚本名称可根据自己规范进行自定义: vim bcmysql.sh 进入编辑器,输入以下内容: #!

  • shell脚本实现mysql定时备份、删除、恢复功能

    mysql备份脚本: 脚本实现:按照数据库名称,全量备份mysql数据库并定期删除 #!/bin/bash #全备方式,一般在从机上执行,适用于小中型mysql数据库 #删除15天以前备份 #作者:lcm_linux #时间:2019.08.06 source ~/.bash_profile #加载用户环境变量 set -o nounset #引用未初始化变量时退出 set -o errexit #执行shell命令遇到错误时退出 #备份用户---需要在mysql中提前创建并授权 #GRANT

  • Linux实现定时备份MySQL数据库并删除30天前的备份文件

    1. MySQL5.6以上版本 2. 修改 /etc/my.cnf 文件 # vim /etc/my.cnf [client] host=localhost user=你的数据库用户 password='你的数据库密码' 3. 编写数据库脚本 mysql-backup.sh # vim mysql-backup.sh #!/bin/bash backupDir=数据库备份目录 backupTime=`date +%Y%m%d%H%M%S` mysqldump 你的数据库 | gzip > $ba

  • CentOS7下 MySQL定时自动备份的实现方法

    生产环境遇到得最幸福得事情就是,某些场景没办法避免去update或者delete的时候,某个参数没有注意.完蛋了 完蛋了,数据被我搞崩了怎么办,赶紧去运营找备份呀!运营说:狗屁 咱们系统从来不备份,你说:那把日志给我吧,运营说:狗屁 每天上G的日志,我都没给你开,你说:怎么办,数据库被我搞崩了.运营说:那是你的事,跟我没关系........这是某公司的一段写照,所以今天我们来把这个情况给杜绝 今天要给客户部署一套系统,使用的MySQL5.7,那客户那边的开发人员我可不敢保证他们的能力咋样.我只能

  • linux定时备份MySQL数据库并删除以前的备份文件(推荐)

    备份数据库,这是必须会用到的,今天上午花了几个小时整理了一下,结果不错.下面详细的走一遍 一丶选择一个比较大位置来保存备份文件(创建文件就行) 使用mkdir来创建文件夹,这个简单. 二丶检查有没安装crond,如果没有,先安装 rpm -qa|grep cron  安装后会是 一般没有安装,所以先 yum -y install vixie-cron 然后再 yum -y install crontabs 这样服务就安装好了. 三丶写备份脚本 vi  /var/spool/cron/name.s

  • 浅析mysql 定时备份任务

    简介 在生产环境上,为了避免数据的丢失,通常情况下都会定时的对数据库进行备份.而Linux的crontab指令则可以帮助我们实现对数据库定时进行备份.首先我们来简单了解crontab指令,如果你会了请跳到下一个内容mysql备份. 本文章的mysql数据库是安装在docker容器当中,以此为例进行讲解.没有安装到docker容器当中也可以参照参照. contab定时任务 使用crontab -e来编写我们的定时任务. 0 5 * * 1 [command] 前面的5个数字分别代表分.时.日.月.

  • Navicat for MySQL定时备份数据库及数据恢复详解

    在做数据库修改或删除操作中,可能会导致数据错误,甚至数据库奔溃,而有效的定时备份能很好地保护数据库.本篇文章主要讲述Navicat for MySQL定时备份数据库和数据恢复等功能,同时可以定时播放电影等设置,希望对您有所帮助,如果文章中存在错误或不足之处,还请海涵~ 一. 设置计划任务定时备份数据库 计划任务就是让电脑在指定的时间内执行指定的动作,这些动作可以是一个程序,也可以是一个批处理,但是至少是可以运行的!其实再通俗一点也就是相当于你在那个时间里面进行了对某个东西对鼠标双击的操作. 1.

  • MySQL定时备份方案(利用Linux crontab)

    前言 虽说现在这世道有些爱情是有价的,但是数据是无价的,数据备份是尤为的重要,可以在你未来的某一天不小心删库了,不用着急跑路. 本片文章介绍的方案是利用Linux自身的crontab定时任务功能,定时执行备份数据库的脚本. 技术要点: 数据库备份dump命令 shell脚本 Linux定时任务crontab 数据备份dump 数据库都有一个导出数据库内数据和结构的命令,就是备份. 将备份的数据还原会将原来的数据中的表删了重建,再插入备份中的数据,这是恢复. 这一点需要注意,如果恢复之前的数据比备

  • Windows下MySQL定时备份脚本的实现

    在windows服务器上,想要定时备份数据库数据,可采用windows的任务计划程序+数据库备份脚本组合.其中,MySQL数据库备份,起到关键作用是mysqldump.有关于mysqldump命令的用法,可以找MySQL的官方文档了解. 1 备份脚本 @echo off echo 设置MySql数据库的连接信息 set host=127.0.0.1 set port=3306 set user=root set pass=admin echo 设置要备份MySql数据库名称 set dbname

  • MySQL定时备份数据库(全库备份)的实现

    目录 一.MySQL数据备份 1.1. mysqldump命令备份数据 1.2. mysqldump常用操作示例 1.3. 还原mysql备份内容 二. 编写脚本维护备份的数据库文件 2.1. 编写BASH维护固定数量备份文件 2.2. 使用crontab定期执行备份脚本 一.cron服务 二.crontab语法 三.创建cron脚本 三.执行效果截图 本文参考: 一.MySQL数据备份 1.1. mysqldump命令备份数据 在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具m

  • centos7中如何利用crontab进行mysql定时备份

    目录 centos7用crontab进行mysql定时备份 备注 附crontab文件的一些例子 总结 centos7用crontab进行mysql定时备份 1.建立 mysql3306_backup.sh 文件 文件名及路径可以自定义,个人习惯将脚本.脚本日志.备份的数据放在mysql的data路径下,方便查找 mysql3306_backup.sh内容: #!/bin/bash # 以下配置信息请自己修改 mysql_user="USER" #MySQL备份用户 mysql_pas

  • MySQL定时备份之使用Linux下的crontab定时备份实例

    1. Mysql备份某个数据库的命令 复制代码 代码如下: ##################################################################  # 备份某个数据库  ################################################################## # root 用户,创建备份目录  mkdir -p /usr/local/cncounter/mysql_dump  cd /usr/local/

  • CentOS下mysql定时备份Shell脚本分享

    1.备份语句 复制代码 代码如下: # /usr/local/mysql/bin/mysqldump -utest -ptest test --socket=/tmp/mysql.3306.sock > /usr/local/mysql_backup/3306/test-`date +%Y%m%d`.sql.gz 语句说明:   /usr/local/mysql/bin/mysqldump:MySql安装目录下 -utest:分为两块,一块为-u一块为test:其中-u表示其为用户名,test表

随机推荐