mysql使用脚本定时进行数据热备份

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: mysql使用脚本定时进行数据热备份

按照数据库状态划分


热备

热备份(Hot Backup)是指在数据库运行中直接备份,对正在运行的数据库没有任何影响。


冷备

冷备份(Cold Backup)是指在数据库停止的情况下进行备份,这种备份最为简单,一般只需要拷贝相关的数据库物理文件即可

(由于要在数据库停止的情况下进行备份,所以一般用的非常少)


冷备份的优点:

1.是非常快速的备份方法(只需拷贝文件)

2.容易归档(简单拷贝即可)

3.容易恢复到某个时间点上(只需将文件再拷贝回去)

4.能与归档方法相结合,作数据库“最新状态”的恢复。

5.低度维护,高度安全。


冷备份的缺点:

1.单独使用时,只能提供到“某一时间点上”的恢复。

2.在实施备份的全过程中,数据库必须要作备份而不能作其它工作。也就是说,在冷备份过程中,数据库必须是关闭状态。

3.若磁盘空间有限,只能拷贝到磁带等其它外部存储设备上,速度会很慢。

4.不能按表或按用户恢复。

值得注意的是冷备份必须在数据库关闭的情况下进行,当数据库处于打开状态时,执行数据库文件系统备份是无效的 。而且在恢复后一定要把数据库文件的属组和属主改为mysql。


热备份 (on, 快)

热备份是在数据库运行的情况下,备份数据库操作的sql语句,当数据库发生问题时,可以重新执行一遍备份的sql语句。


热备份的优点:

1.可在表空间或数据文件级备份,备份时间短。

2.备份时数据库仍可使用。

3.可达到秒级恢复(恢复到某一时间点上)。

4.可对几乎所有数据库实体作恢复。

5.恢复是快速的,在大多数情况下在数据库仍工作时恢复。


按照备份方式划分

有两种常见的备份方式

物理备份

直接复制数据文件进行的备份。

优点:无需额外工具,直接copy即可,恢复直接复制备份文件即可

缺点:与存储引擎有关,跨平台能力较弱


逻辑备份

从数据库中“导出”数据另存而进行的备份

优点:能使用编辑器处理,恢复简单,能基于网络恢复,有助于避免数据损坏

缺点:备份文件较大,备份较慢,无法保证浮点数的精度,使用逻辑备份数据恢复后,还需手动重建索引,十分消耗CPU资源。


mysqldump热备份的操作步骤:


一、创建备份目录


mkdir   /data/mysql_bak
cd /data/mysql_bak


二、创建脚本


vi mysql_auto_back.sh

#!/usr/bin/env bash
#########数据库基础信息#############
DB_HOST=127.0.0.1
DB_NAME=$1
DB_USER=root
DB_PASS=123456
DATE=`date +%Y%m%d%H%M%S`
MYSQL_PATH=/data/mysql_bak/
#保存备份个数
BACK_NUM=3
##########################
back_up(){
#bakup file absolute path
BAKUP_FILE=${MYSQL_PATH}${DB_NAME}${DATE}.sql
#执行mysqldump命令的参数
SQL_OPT="-u${DB_USER} -p${DB_PASS} -h ${DB_HOST} ${DB_NAME}"
#执行备份,整个DB所有表的备份,SQL语句的形式
mysqldump ${SQL_OPT} > ${BAKUP_FILE}
# 删除30天之前的就备份文件
#find ${MYSQL_PATH}/* -mtime +10 -exec rm {} \;
#写创建备份日志
echo "create ${BAKUP_FILE}"  >> ${MYSQL_PATH}log.txt
#找出需要删除的备份
delfile=`ls -l -crt  ${MYSQL_PATH}$1*.sql | awk '{print $9 }' | head -1`
#echo "${delfile}"
#判断现在的备份数量是否大于$number
count=`ls -l -crt  ${MYSQL_PATH}$1*.sql | awk '{print $9 }' | wc -l`
if [[ $count -gt $BACK_NUM ]];then
     #删除最早生成的备份,只保留number数量的备份
     rm $delfile
     #写删除文件日志
     echo "delete $delfile" >> ${MYSQL_PATH}log.txt
fi
}
back_up;
#############脚本结束##############


3、脚本赋予执行权限


chmod  a+x    mysql_auto_back.sh


4、测试脚本正确性,备份mysql库,执行4次,查看日志,看是否正确


./    mysql_auto_back.sh   mysql


执行4次,查看结果

18.png


5、配置定时任务

crontab -e

#每晚5点备份一次


30 5 * * * /data/mysql_bak/mysql_auto_back.sh   mysql


说明:

最好本机和选一台远程机器都配置一个这样的定时备份任务。

这样,当数据库服务器出现了故障,至少数据不会大面积的丢失。

当然,如果你的mysql配置了主从,备份的数据更加实时。当主机出现故障后,可以直接切到备机上。


热备份也可以防止对数据库的误操作(由于主从同步,从机上的数据也会被删除)

比如有同事故意或者粗心,将生产库误操作删除,那最起码我可以追回前一天的数据,而不会所有数据都丢失。


所以,针对线上环境的数据库,一般都是主从备份 + mysqldump多点热备份


到这里使用mysql使用脚本定时进行数据冷备就配置完成了。


6、sql文件还原方法

1、

(-f 参数表示在导出过程中忽略出现的SQL错误)


mysql -uroot -p"db_password"   -f dbname < backup.sql


(在导入较大的sql文件时可以放到后台执行)


nohup mysql -uroot -p"db_password" -f dbname < backup.sql > /dev/null 2>&1 &


2、

mysql -uroot -p 先登录到数据库,

use dbname; 切换到需要导入的库中

然后执行 source /opt/backup.sql; 这样就可以导入了


mysqldump备份常见用法

1、导出整个数据库(包括数据库中的数据)

mysqldump -u username -p dbname > dbname.sql


2、导出数据库结构(不含数据)

mysqldump -u username -p -d dbname > dbname.sql


–databases, -B 导出多少数据库,参数后面所有参量都被看作数据库名

–no-data, -d 不导出任何数据,只导出数据库表结构

–no-create-info, -t 只导出数据,而不添加CREATE TABLE 语句

–no-create-db, -n 只导出数据,而不添加CREATE DATABASE 语句


MySQL 5.6 警告信息 command line interface can be insecure 修复

https://blog.51cto.com/880314/1348413

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
5月前
|
缓存 NoSQL 关系型数据库
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
|
3月前
|
SQL 人工智能 关系型数据库
如何实现MySQL百万级数据的查询?
本文探讨了在MySQL中对百万级数据进行排序分页查询的优化策略。面对五百万条数据,传统的浅分页和深分页查询效率较低,尤其深分页因偏移量大导致性能显著下降。通过为排序字段添加索引、使用联合索引、手动回表等方法,有效提升了查询速度。最终建议根据业务需求选择合适方案:浅分页可加单列索引,深分页推荐联合索引或子查询优化,同时结合前端传递最后一条数据ID的方式实现高效翻页。
163 0
|
2月前
|
存储 关系型数据库 MySQL
在CentOS 8.x上安装Percona Xtrabackup工具备份MySQL数据步骤。
以上就是在CentOS8.x上通过Perconaxtabbackup工具对Mysql进行高效率、高可靠性、无锁定影响地实现在线快速全量及增加式数据库资料保存与恢复流程。通过以上流程可以有效地将Mysql相关资料按需求完成定期或不定期地保存与灾难恢复需求。
173 10
|
4月前
|
存储 关系型数据库 MySQL
【赵渝强老师】使用select...into outfile语句备份MySQL
本文介绍了MySQL中使用`SELECT...INTO OUTFILE`语句将表数据导出为文本文件的方法。通过示例演示了如何备份员工表(emp)的数据,包括创建存储目录、设置权限、配置参数`secure_file_priv`以及解决相关错误的过程。字段分隔符和行终止符可自定义,确保数据格式符合需求。最后展示了备份文件的内容,验证操作成功。
288 36
|
3月前
|
SQL 存储 缓存
MySQL 如何高效可靠处理持久化数据
本文详细解析了 MySQL 的 SQL 执行流程、crash-safe 机制及性能优化策略。内容涵盖连接器、分析器、优化器、执行器与存储引擎的工作原理,深入探讨 redolog 与 binlog 的两阶段提交机制,并分析日志策略、组提交、脏页刷盘等关键性能优化手段,帮助提升数据库稳定性与执行效率。
|
4月前
|
存储 SQL 关系型数据库
【赵渝强老师】使用mysqldump备份MySQL
本文介绍了 MySQL 自带的逻辑备份工具 mysqldump 的使用方法。通过 mysqldump,可以将数据库中的数据转换为对应的 SQL 插入语句,便于备份和还原。文章详细说明了如何备份所有数据库、指定数据库及特定表,排除某些表不备份的操作,以及删除数据库后如何通过备份文件恢复数据。同时提供了视频讲解和具体命令示例,帮助用户更好地理解和应用该工具。
183 5
|
5月前
|
关系型数据库 MySQL Linux
实现MySQL数据库的定时自动备份脚本。
拿走,不谢,这个脚本配方(指引)保证你的数据库数据像蛋糕店一样地天天更新,还能确保老旧的蛋糕(数据)不会堆积满仓库。这下可好,数据安全有保障,数据库管理员也能轻松一点,偶尔闲下来的时候,煮杯咖啡,看个剧岂不美哉?别忘了偶尔检查一下你的自动备份是否正常工作,以防万一蛋糕机器出了点小差错。
262 20
|
4月前
|
存储 SQL 关系型数据库
【赵渝强老师】使用mydumper备份MySQL
本文介绍了使用mydumper工具进行MySQL数据库备份与恢复的操作方法。相比单线程工作的mysqldump,mydumper支持多线程,速度提升可达10倍。其功能包括事务性表快照、快速压缩、导出binlog等,并提供详细的参数说明和操作步骤。文章通过实例演示了安装mydumper、创建存储目录、全库备份、指定数据库及表备份、删除数据库以及使用myloader恢复数据的完整流程,并附带视频讲解,帮助用户更好地理解和应用该工具。
121 0
|
6月前
|
关系型数据库 MySQL Linux
在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾
以上就是在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾的步骤。这个过程就像是一场接力赛,数据从MySQL数据库中接力棒一样传递到备份文件,再从备份文件传递到其他服务器,最后再传递回MySQL数据库。这样,即使在灾难发生时,我们也可以快速恢复数据,保证业务的正常运行。
285 28
|
6月前
|
存储 关系型数据库 MySQL
利用Cron表达式实现MySQL数据库的定时备份
以上就是如何使用Cron表达式和mysqldump命令实现MySQL数据库的定时备份。这种方法的优点是简单易用,而且可以根据需要定制备份的时间和频率。但是,它也有一些限制,例如,它不能备份MySQL服务器的配置文件和用户账户信息,也不能实现增量备份。如果需要更复杂的备份策略,可能需要使用专门的备份工具或服务。
138 15

推荐镜像

更多