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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 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

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
安全 关系型数据库 MySQL
如何将数据从MySQL同步到其他系统
【10月更文挑战第17天】如何将数据从MySQL同步到其他系统
525 0
|
3月前
|
SQL 前端开发 关系型数据库
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
75 0
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
|
3月前
|
关系型数据库 MySQL 数据库
mysql 里创建表并插入数据
【10月更文挑战第5天】
205 1
|
1月前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
126 6
|
2月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
175 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
1月前
|
SQL 关系型数据库 MySQL
mysql分页读取数据重复问题
在服务端开发中,与MySQL数据库进行数据交互时,常因数据量大、网络延迟等因素需分页读取数据。文章介绍了使用`limit`和`offset`参数实现分页的方法,并针对分页过程中可能出现的数据重复问题进行了详细分析,提出了利用时间戳或确保排序规则绝对性等解决方案。
|
2月前
|
关系型数据库 MySQL 数据库
GBase 数据库如何像MYSQL一样存放多行数据
GBase 数据库如何像MYSQL一样存放多行数据
|
2月前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
78 14
|
2月前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
82 9
|
2月前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下,实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
197 3