第7章、备份与恢复

使用mysqldump导出SQL格式的备份

默认情况下mysqldump导出的是标准的SQL语法,因此你可以使用标准输出写入文件。

shell> mysqldump [arguments] > file_name

若需要导出所有数据,如下:

shell> mysqldump --all-databases > dump.sql

若仅导出特定数据库,请在命令行中命名它们,然后使用--databases选项。--databases后的所有名称都将视为数据库名称,若省略该选项则默认取第一个字符串作为数据库的名称。

shell> mysqldump --databases db1 db2 db3 > dump.sql

当使用--all-databases--databases选项时,在具体的数据库导出之前会添加CREATE DATABASEUSE语句。这样可以确保导出的备份在下次被加载之前,若发现库没有创建可以实施创建,不会因此影响数据导入。如果你需要在导入时删除之前的库,可以在导出时添加选项--add-drop-database。这样做可以在声明CREATE DATABASE 之前添加语句DROP DATABASE

导出单个数据库:

shell> mysqldump --databases test > dump.sql

也可以省略--databases标签:

shell> mysqldump test > dump.sql

他们两者的区别是省略--databases标签在导出的数据库脚本中,不会自动添加CREATE DATABASEUSE语句。

省略--databases这意味着你在执行导入时:

  • 需要指定数据库。
  • 你可以将数据导入到与导出指定数据库名称不同的数据库中。
  • 如果你指定的数据库不存在,你需要手动创建。
  • 因为输出将不包含CREATE DATABASE语句,所以--add-drop-database选项不起作用。如果使用它,它不会生成DROP DATABASE语句。

如果仅想导出指定数据库中的指定数据表,可以这样做:

shell> mysqldump test t1 t3 t7 > dump.sql

用mysqldump以分隔文本格式转储数据

若你使用mysqldump --tab={dir-name}时,它会使用{dir-name}作为输出目录。并且每张表将会有两份文件,比如数据库表名称是t1,则对应的输出文件为t1.sqlt1.txt*.txt存放表的数据,一行一条数据。

以下命令将db1数据库的内容转储到/ tmp数据库中的文件:

shell> mysqldump --tab=/tmp db1

*.txt是由mysql的运行的用户持有,因为mysql的用户调用SELECT ... INTO OUTFILE语句写入该文件,所以你需要确保执行该方法的用户具备FILE权限。另外,如果对应的*.txt文件已存在时,导出操作将会报错。

Tips:FILE 权限是一个比较危险的权限,SQL注入之后利用FILE权限可以完成提权,所以处于安全考虑不对外开放该权限。

选项--tab最好仅应用于本地服务器。

要使用不同的格式写入数据文件,mysqldump支持以下选项:

--fields-terminated-by=str
# 分割列的符号 (default: tab).
--fields-enclosed-by=char
# 包围列的字符 (default: no character).
--fields-optionally-enclosed-by=char
# 包围非数据的列字符 (default: no character).
--fields-escaped-by=char
# 用于转义的特殊字符 (default: no escaping).
--lines-terminated-by=str
# 终止符 (default: newline).

在实际使用时,你可以指定Hex也可以使用字符。

--fields-enclosed-by='"'
# 上下是同一个意思
--fields-enclosed-by=0x22

制作数据库备份

3行代码备份一个数据库。

# 不使用 --databases 是避免CREATE 语句导致导入到db2会失败
shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql

导出存储过程、计划事件、触发器

  • --events : 导出计划事件
  • --routines : 导出存储过程
  • --triggers : 导出触发器

--triggers默认是开启的,其它两个是默认关闭的。如果需要导出,则需要显示的指定。如果明确无需导出,可以设置:--skip-events,--skip-routines,--skip-triggers。

定时备份

有了上面的概念对导出导入有了基本认识,接下去借助crontab即可完成定时备份的效果。

不了解crontab可以参考文档:Configuring Cron Tasks

1. 创建并授权备份用户

CREATE USER 'backup'@'%' IDENTIFIED BY '你的密码'
GRANT SELECT,LOCK TABLES ON *.* TO 'backup'@'%' IDENTIFIED BY '你的密码';

2. 编写备份脚本

#!/bin/bash   

#定义有备份的数据库名 定义远程数据库
dbname="你要备份的数据库名"  dbhost="数据库HOST"

#定义远程数据库端口
dbport=3306

#定义备份数据库时使用的用户名和密码 
dbuser="你的mysql用户名" dbpasswd="你的密码"   
#数据库备份的路径 
backuppath=/home/mysql/${dbhost}/ 
 
#数据库备份日志文件存储的路径 
logfile=/home/mysql/${dbhost}/logs/bak.log   


#以当前的时间作为备份的数据库命名。 
dumpfile=${dbname}-$(date +%Y%m%d%H%M)   


#这个函数用来备份数据库 
back_db() {    
#将备份的时间、数据库名存入日志    
echo "------"$(date +%Y-%m-%d%t%A%t%T)" Beginning database "${dbname}" backup--------" >>${logfile}     


#备份数据库,如果有错误信息也记入日志。   默认utf8编码
/usr/bin/mysqldump -u${dbuser} -p${dbpasswd} -h ${dbhost} -P ${dbport} --default-character-set=utf8 --databases ${dbname} >${backuppath}${dumpfile}.sql 2>> ${logfile}     

#开始压缩数据文件   
echo $(date +%Y-%m-%d%t%A%t%T)" Beginning zip ${backuppath}${dumpfile}.sql" >>${logfile} 
    
#将备份数据库文件库压成ZIP文件,并删除先前的SQL文件。如果有错误信息也记入日志。   
tar zcvf ${dumpfile}.tar.gz ${dumpfile}.sql && rm ${dumpfile}.sql 2>> ${logfile}     

#将压缩后的文件名存入日志。   
echo "backup file name:"${dumpfile}".tar.gz" >>${logfile}   
echo -e "-------"$(date +%Y-%m-%d%t%A%t%T)" Ending database "${dbname}" backup-------\n" >>${logfile}  
}

#发送邮件  
#cat ${logfile} | mutt -s "Blog数据库备份" -a ${dumpfile}.tar.gz 8chf@163.com }   

rm_oldfile() {   
#查找出当前目录下7天前生成的文件,并将之删除   
find ${backuppath} -type f -mtime +7 -exec rm {} \; 
}   

#切换到数据库备份的目录。如果不做这个操作,压缩文件时有可能会错误 
cd ${backuppath}   

#运行备份数据函数 
back_db   

#运行删除文件函数 
rm_oldfile 

3. 设置备份目录

  • 220.*.*.*
    • 约定以mysql服务器host为文件夹,每个host下备份只有该host下的备份。
    • logs 目录存放备份过程的日志。
  • shell 目录下则是备份脚本。

设置备份目录

4. 启用计划任务

若服务器没有安装crontab服务,则使用yum -y install crontab安装即可。

shell > crontab -e 

编辑文件并保存。

15 14 * * *  /home/mysql/shell/bak-220.*.*.*.sh > /dev/null

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏实用工具入门教程

如何部署 ZABBIX 监控系统

Zabbix是用于网络和应用程序的开源监控软件。它提供对从服务器,虚拟机和任何其他类型的网络设备收集的数千个指标的实时监控。这些指标可帮助您确定IT基础架构当前...

33920
来自专栏数据和云

SQL Server for Linux 下一版本的公共预览

当微软宣布即将发布SQL Server for Linux版本的时候,有些人觉得很兴奋,有些人觉得然并卵,但是既然Gartner在2016年的数据库管理系统魔...

34390
来自专栏高爽的专栏

增量接口的设计及实现

引言 在应用开发过程中,我们总会碰到这样的场景:某系统需要同步我们系统的数据去做一些业务逻辑,当数据量较小的时候,可以全量的提供,但当数据量很大时,全量提供就显...

45000
来自专栏云计算教程系列

如何使用Mytop监控MySQL性能

Mytop是一个用于监控MySQL性能的开源命令行工具。它受到名为top的Linux系统监视工具的启发,在外观和感觉上类似于它。Mytop连接到MySQL服务器...

48100
来自专栏程序员的SOD蜜

Oracle 免费的数据库--Database 快捷版 11g 安装使用与"SOD框架"对Oracle的CodeFirst支持

一、Oracle XE 数据库与连接工具安装使用 Oracle数据库历来以价格昂贵出名,当然贵有贵的道理,成为一个Oracle DBA也是令人羡慕的事情,如果程...

51670
来自专栏Samego开发资源

Thinkphp3.2多语言配置

19550
来自专栏L宝宝聊IT

SQL Server数据库介绍

23250
来自专栏决胜机器学习

《高性能MySQL》读书笔记(二) ——MySQL存储引擎概述

《高性能MySQL》读书笔记(二)——MySQL存储引擎概述 (原创内容,转载请注明来源,谢谢) 一、基础信息 mysql将数据库保存在数据目录下...

40650
来自专栏杨建荣的学习笔记

由小见大-MySQL脚本部署中的一些策略

在线上环境中部署脚本,可谓是常在河边走,哪有不湿鞋,所以大大小小的案例总结下来,还是会发现一些有趣的地方,这些可以作为操作时的一些参考,仅供参考而已。 第一类...

34860
来自专栏散尽浮华

mysql数据库误删除后的数据恢复操作说明

在日常运维工作中,对于mysql数据库的备份是至关重要的!数据库对于网站的重要性使得我们对mysql数据的管理不容有失! 然后,是人总难免会犯错误,说不定哪天大...

507110

扫码关注云+社区

领取腾讯云代金券