浅析mysql 定时备份任务
作者:返回主页Johnson木木 发布时间:2024-01-17 07:21:01
简介
在生产环境上,为了避免数据的丢失,通常情况下都会定时的对数据库进行备份。而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定时备份是在生产环境上必须的任务。是很常用的。所以我就迫不及待的写博客。当然也很感谢我同事的帮助。这篇文章已经写了三天了,因为我也是在不断地试错,不断的更新文章。避免把错误的知识点写出来。如果帮到你了,关注我一波呗!谢谢。
来源:https://www.cnblogs.com/Johnson-lin/p/13178028.html


猜你喜欢
- 导出单个表mysqldump -u user -p dbname tablename > db.sql导入单个表mysql>dr
- 1、解决方案mysql是不支持跨库连接的,如果我们实在要连接的话可以用dblink方式。解释:dblink就是我们在创建表的时候连接到我们的
- 安装pip install pyshp引入import shapefile读取sf=shapefile.Reader("{路径名}
- 前言CSDN博客好久没有换过头像了,想换个新头像,在相册里面翻来翻去,然后就找到以前养的小宠物的一些照片,有一张特别有意思惊恐到站起来的金丝
- 常用快捷键1、Ctrl + Enter:在下方新建行但不移动光标;2、Shift + Enter:在下方新建行并移到新行行首;3、Ctrl
- 内容摘要:本文介绍了对数据库的基本操作:数据记录筛选(select),更新数据库(update),删除记录(delete),添加数据记录(i
- 界面文件 Ui_ControlBoard.py# -*- coding: utf-8 -*-# Form implementation ge
- 事务特性1、原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。2、一致性(Consiste
- 我们将要来学习python的重要概念迭代和迭代器,通过简单实用的例子如列表迭代器和xrange。可迭代一个对象,物理或者虚拟存储的序列。li
- python数据类型之间怎么转换?数据如果类型不对,在运行中有交集的话就会出现错误,那怎么让两个类型的数据变成同一个类型的呢首先是字符串,在
- 本文实例为大家分享了用python画一朵花的具体代码,供大家参考,具体内容如下第一种,画法from turtle import *impor
- order by 查询语句使用也是非常频繁,有时候数据量大了会发现排序查询很慢,本文就介绍一下 MySQL 是如何进行排序的,以及如何利用其
- 本文整理了关于Javascript表单验证的所有涉及到的,大家仔细阅读一定会有所收获的 //验证字符串非空 var Validat
- SQL(结构化查询语言)是一种通用数据库查询语言。SQL具有数据定义、数据操作和数据控制功能,可以完成数据库的全部工作。SQL语言使用时只需
- 我认为多选列表具有完美的功能——只需按下Ctrl键,同时点击鼠标从列表中选择多个项目。以下是一个典型的多选列表框:选出你最喜爱的快餐:&nb
- 前言:任何一个编程者都少不了要去调试代码,不管你是高手还是菜鸟,调试程序都是一项必不可少的工作。一般来说调试程序是在编写代码之后或测试期修改
- 终于构建出了第一个神经网络,Keras真的很方便。之前不知道Keras这么方便,在构建神经网络的过程中绕了很多弯路,最开始学的TensorF
- 一、旧式的字符串格式化% 操作符参考以下示例:>>> name = "Eric">>>
- 一、分块查找算法分块查找是二分法查找和顺序查找的改进方法,分块查找要求索引表是有序的,对块内结点没有排序要求,块内结点可以是有序的也可以是无
- 前言本文主要给大家介绍了关于python中MethodType方法的相关内容,分享出来供大家参考学习,话不多说,来一起看看详细的介绍吧示例代