0
  • 聊天消息
  • 系统消息
  • 评论与回复
登录后你可以
  • 下载海量资料
  • 学习在线课程
  • 观看威廉希尔官方网站 视频
  • 写文章/发帖/加入社区
会员中心
创作中心

完善资料让更多小伙伴认识你,还能领取20积分哦,立即完善>

3天内不再提示

mysql定时备份任务

马哥Linux运维 来源:博客园Johnson木木 2024-10-31 10:07 次阅读

简介#

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

contab定时任务#

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


	
Copy
0 5 * * 1 [command]

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


	
Copy
0 8 * * * [command]

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

mysql备份#

快速上手#

这里我的mysql数据库是docker容器。假如你需要在每天晚上8点整执行定时任务,那么可以这么写。 首先创建数据库备份目录


	
Copy
mkdir -p /var/backups/mysql

然后先尝试执行数据库备份命令


	
Copy
docker exec mysql_container mysqldump --single-transaction -uroot -proot_password database_name > /var/backups/mysql/test.sql

如果你看到了/var/backups/mysql/test.sql文件,那么应该就没啥问题了(最好还是看看文件大小,文件太小可能说明会有问题)。 mysql_container为你的数据库容器名 --single-transaction不会锁表,也确保数据的一致性 mysqldump是mysql数据库导出数据的指令 -u填写mysql的root账号 -p填写mysql的root密码 database_name需要备份的数据库名

最后执行命令crontab -e,把mysql的备份命令写到定时任务中。


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

/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文件,内容如下


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

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


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

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


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

那么这样就比较规范了。

mysql备份优化#

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


	
Copy
export mysqldump_date=$(date +%Y%m%d_%H%M%S) && docker exec mysql_container mysqldump --single-transaction -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,稳住,淡定。我们首先要创建数据库被删除的数据库。


	
Copy
>mysql create database database_name;

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


	
Copy
docker exec -i mysql_container mysql --single-transaction -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文件:


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

查看当前正在写入的binlog


	
Copy
mysql> show master statusG;

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


	
Copy
mysql> flush logs

查看binlog日志


	
Copy
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文件的创建日期


	
Copy
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命令时间前的数据。

恢复命令格式:


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

mysqlbinlog常用参数:

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

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


	
Copy
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 2000- 660 的位置。为什么是660?因为drop database的上一个事务的提交是660的位置,命令如下:


	
Copy
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前啦!开不开心,激不激动!

链接:https://www.cnblogs.com/Johnson-lin/p/13178028.html


声明:本文内容及配图由入驻作者撰写或者入驻合作网站授权转载。文章观点仅代表作者本人,不代表电子发烧友网立场。文章及其配图仅供工程师学习之用,如有内容侵权或者其他违规问题,请联系本站处理。 举报投诉
  • Linux
    +关注

    关注

    87

    文章

    11302

    浏览量

    209416
  • 指令
    +关注

    关注

    1

    文章

    607

    浏览量

    35704
  • 容器
    +关注

    关注

    0

    文章

    495

    浏览量

    22061
  • 日志
    +关注

    关注

    0

    文章

    138

    浏览量

    10641

原文标题:binlog日志#

文章出处:【微信号:magedu-Linux,微信公众号:马哥Linux运维】欢迎添加关注!文章转载请注明出处。

收藏 人收藏

    评论

    相关推荐

    MySQL零基础入门教程!

    和DCL第6节 SQL语句课堂实践精确授权优先第7节 复习MySQL基础第8节 破解MariaDB 5.5 root密码第9节 MySQL备份的分类和备份的两大要素第10节 Innod
    发表于 06-17 14:54

    0基础学Mysql:mysql入门视频教程!

    DDL语句第5节 SQL语句之DML语句和DCL第6节 SQL语句课堂实践精确授权优先第7节 复习MySQL基础第8节 破解MariaDB 5.5 root密码第9节 MySQL备份的分类和
    发表于 07-08 10:51

    mysql零基础入门视频教程免费分享!

    SQL语句之DML语句和DCL第6节 SQL语句课堂实践精确授权优先第7节 复习MySQL基础第8节 破解MariaDB 5.5 root密码第9节 MySQL备份的分类和备份的两大
    发表于 07-13 11:08

    基于linux的mysql数据库每天自动备份定时备份的实现

    linux下如何实现mysql数据库每天自动备份定时备份
    发表于 05-10 17:10

    如何用labview对数据库进行备份/如何在MySql中使用命令的方式进行数据库备份(非cmd窗口非手动保存)

    ,不能登录到 MySQL 服务中执行。2:手动点击鼠标保存。想在不想用打开cmd窗口的方式去备份有没有一种语句,可以在MySQL 服务中执行,输入语句,进行备份
    发表于 07-15 16:48

    MySQL基础架构自动化测试分析

    MySQL对于GitHub的重要性不言而喻,本文作者从MySQL备份、自动测试能否成功从备份恢复数据、模拟各种 master 可能挂掉的情况、自动测试 failover 是否正常、自
    发表于 09-28 16:47 0次下载
    <b class='flag-5'>MySQL</b>基础架构自动化测试分析

    Linux教程之linux下如何备份还原mysql数据库

    本文介绍了linux下如何备份与恢复mysql数据库。数据库备份是非常重要的。如果定期做好备份,这样就可以在发生系统崩溃时恢复数据到最后一次正常的状态,把损失减小到最少。
    发表于 10-19 17:18 4次下载

    如何完成Mysql主从复制的在线配置

    Percona XtraBackup(简称PXB)是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具,支持 MySQl(Oracle)、Percona Server 和 MariaDB,并且全部开源,可
    的头像 发表于 07-29 18:27 2159次阅读
    如何完成<b class='flag-5'>Mysql</b>主从复制的在线配置

    PHP的Mysql数据库备份脚本详细资料免费下载

    本文档的主要内容详细介绍的是PHP的Mysql数据库备份脚本详细资料免费下载。
    发表于 06-19 17:15 5次下载

    PHP的Mysql数据库备份脚本的程序免费下载

    本文档的主要内容详细介绍的是PHP的Mysql数据库备份脚本的程序免费下载。
    发表于 06-28 15:37 2次下载

    为什么MySQL备份很重要?MySQL备份类型有哪些?

    随着企业和应用程序越来越依赖 MySQL 数据库来管理其关键数据,确保数据可靠性和可用性变得至关重要。在这个数字信息时代,强大的备份和恢复策略是应用程序稳定性的支柱。 本文中,我们将回顾所有常用
    的头像 发表于 11-14 10:20 683次阅读

    mysql数据库备份与还原

    MySQL是一种常见的关系型数据库管理系统,用于存储和管理数据。备份和还原数据库是非常重要的操作,可以保护数据免受丢失或损坏的影响。本文将详细介绍MySQL数据库的备份和还原过程,包括
    的头像 发表于 11-23 14:32 1193次阅读

    mysql备份还原哪些方法

    MySQL是一个开源的关系型数据库管理系统,备份和还原是保证数据安全性和可恢复性的重要措施。本文将详细介绍MySQL备份和还原的方法,包括物理备份
    的头像 发表于 11-23 14:35 987次阅读

    mysql中表分区的备份与恢复

    MySQL的表分区是一种将大型表分成更小段的威廉希尔官方网站 ,这样可以提高查询效率、降低维护成本和减少数据备份恢复时间。在进行表分区的过程中,我们也需要了解如何备份和恢复这些分区,以确保数据的安全性。 一、表
    的头像 发表于 11-23 14:39 1145次阅读

    Linux计划任务介绍

    定时备份数据。比如:11点开启网站抢购接口,12点关闭网站抢购接口。 3.计划任务主要分为以下两种使用情况: 1.系统级别的定时任务: 临时文件清理、系统信息采集、日志文件切割 2.
    的头像 发表于 11-24 15:49 284次阅读