MySQL5.7 mysqldump备份与恢复的实现

 更新时间:2020年11月3日 14:19  点击:2348

MySQL 备份

冷备份:
停止服务进行备份,即停止数据库的写入

热备份:
不停止服务进行备份(在线)

mysql 的 MyIsam 引擎只支持冷备份,InnoDB 支持热备份,原因:

InnoDB引擎是事务性存储引擎,每一条语句都会写日志,并且每一条语句在日志里面都有时间点,那么在备份的时候,mysql可以根据这个日志来进行redo和undo,将备份的时候没有提交的事务进行回滚,已经提交了的进行重做。但是MyIsam不行,MyIsam是没有日志的,为了保证一致性,只能停机或者锁表进行备份。

InnoDB不支持直接复制整个数据库目录和使用mysqlhotcopy工具进行物理备份:

1、直接复制整个数据库目录
因为MYSQL表保存为文件方式,所以可以直接复制MYSQL数据库的存储目录以及文件进行备份。MYSQL的数据库目录位置不一定相同,在Windows平台下,MYSQL5.6存放数据库的目录通常默认为~\MySQL\MYSQL Server 5.6\data,或其他用户自定义的目录。这种方法对INNODB存储引擎的表不适用。使用这种方法备份的数据最好还原到相同版本的服务器中,不同的版本可能不兼容。在恢复的时候,可以直接复制备份文件到MYSQL数据目录下实现还原。通过这种方式还原时,必须保证备份数据的数据库和待还原的数据库服务器的主版本号相同。而且这种方式只对MYISAM引擎有效,对于InnoDB引擎的表不可用。执行还原以前关闭mysql服务,将备份的文件或目录覆盖mysql的data目录,启动mysql服务。

2、使用mysqlhotcopy工具快速备份
mysqlhotcopy是一个perl脚本,最初由Tim Bunce编写并提供。他使用LOCK TABLES 、FLUSH TABLES和cp或scp来快速备份数据库。他是备份数据库或单个表的最快途径,但他只能运行在数据库目录所在机器上,并且只能备份myisam类型的表。

mysqldump 备份简述

mysqldump可产生两种类型的输出文件,取决于是否选用- -tab=dir_name选项。

不使用- -tab=dir_name选项,mysqldump产生的数据文件是纯文本的SQL文件,又CREATE(数据库、表、存储路径等)语句和INSERT(记录)语句组成。输出结果以一个文件保存,可以用mysql命令去恢复备份文件。

使用- -tab=dir_name选项,mysqldump对于每一个需备份的数据表产生两个输出文件:一个是带分隔符的文本文件,备份的数据表中的每行存储为文本中的一行,以“表名.txt”保存;另一个输出文件为数据表的CREATE TABLE语句,以“表名.sql”保存。

mysqldump 语法和选项

【命令】shell> mysqldump -help

在这里插入图片描述

- -all-databases 表示备份系统中所有数据库,使用- -databases参数之后,必须指定至少一个数据库的名称,多个数据库名称之间用空格隔开

常用的选项

- -add-drop-table
这个选项将会在每一个表的前面加上DROP TABLE IF EXISTS语句,这样可以保证导回MySQL数据库的时候不会出错,因为每次导回的时候,都会首先检查表是否存在,存在就删除

- -add-locks
这个选项会在INSERT语句中捆上一个LOCK TABLE和UNLOCK TABLE语句。这就防止在这些记录被再次导入数据库时其他用户对表进行的操作

- -tab
这个选项将会创建两个文件,一个是带分隔符的文本文件,备份的数据表中的每行存储为文本中的一行,以“表名.txt”保存;另一个输出文件为数据表的CREATE TABLE语句,以“表名.sql”保存。

- -quick或者- -opt
如果你未使用- -quick或者- -opt选项,那么mysqldump将在转储结果之前把全部内容载入到内存中。这在你转储大数据量的数据库时将会有些问题。该选项默认是打开的,但可以使用- -skip-opt来关闭它。

- -skip-comments
使用–skip-comments可以去掉导出文件中的注释语句

- -compact
使用- -compact选项可以只输出最重要的语句,而不输出注释及删除表语句等等

以SQL格式备份数据

如果备份文件名.sql没有指定所放置的路径,则默认放在~\MySQL\MySQL Server 5.6\bin目录下。

指定数据备份路径:
mysqldump –h 主机名 –u 用户名 –p - -all-databases > C:\备份文件名.sql

调用mysqldump带有- -all-databases选项备份所有的数据库
mysqldump –h 主机名 –u 用户名 –p - -all-databases > C:\备份文件名.sql

调用mysqldump带有- -databases选项备份指定的数据库
mysqldump –u 用户名 –p - -databases db1 db2 db3 … > C:\备份文件名.sql

调用mysqldump备份一个指定的数据库:
mysqldump –u 用户名 –p - -databases db > 备份文件名.sql

用mysqldump不带有- -databases选项备份指定的一个数据库,生成的备份文件中是没有CREATE DATABASE和USE语句的:
mysqldump –u 用户名 –p db > 备份文件名.sql

注意: 当对一个数据库进行备份时- -databases允许省略,但是省略后导致的是备份文件名.sql中没有CREATE DATABASE 和USE语句,那么恢复备份文件时,必须指定一个默认的数据库名,由此服务器才知道备份文件恢复到哪个数据库中;由此可以导致你可以使用一个和原始数据库名称不同的数据库名。

调用mysqldump备份某个数据库中的某几张表:
mysqldump –u用户名 –p 数据库名 表名1 表名2 表名3… > 备份文件名.sql

恢复SQL格式的备份文件

通过mysqldump备份的文件,如果用了- -all-databases或- -databases选项,则在备份文件中包含CREATE DATABASE和USE语句,故并不需要指定一个数据库名去恢复备份文件。

在Shell命令下:

shell> mysql –u 用户名 –p < 备份文件.sql

在mysql命令下,用source命令导入备份文件:

mysql> source备份文件.sql; //已登录mysql,用source命令

如果通过mysqldump备份的是单个数据库,且没有使用- -databases选项,则备份文件中不包含CREATE DATABASE和USE语句,那么在恢复的时候必须先创建数据库。

在shell命令下:

shell> mysqladmin –u 用户名 –p create 数据库名 //创建数据库
shell> mysql –u 用户名 –p数据库名 < 备份文件.sql

在mysql命令下:

mysql> CREATE DATABASE IF NOT EXIST 数据库名;
mysql> USE 数据库名;
mysql> source备份文件.sql;

注意: 只能在cmd界面下执行source命令,不能在mysql工具里面执行source命令,会报错,因为cmd是直接调用mysql.exe来执行命令的。

以带分隔符的文本文件格式备份数据

调用mysqldump带有- -tab=dir_name选项去备份数据库,则dir_name表示输出文件的目录,在这个目录中,需备份的每个表将会产生两个文件。如对于一个名为t1的表,包含两个文件:t1.sql和t1.txt。.sql文件中包含CREATE TABLE语句,.txt文件中一行为数据表中的一条记录,列值与列值之间以‘tab'分隔。

注意: 使用带- -tab=dir_name选项的mysqldump最好只被用于本地服务器上。因为如果用在远程服务器上,- -tab产生的目录将会既存在本地主机也会存在于远程主机上,.txt文件将会被服务器写在远程主机的目录中,而.sql文件将会被写在本地主机目录中。

调用mysqldump带有- -tab=dir_name选项备份数据库
mysqldump –u 用户名 –p - -tab=dir_name 数据库名

【例子】用mysqldump带有- -tab=dir_name选项备份数据库test,放在D盘下:

数据库test中的表:

在这里插入图片描述

执行备份命令:

在这里插入图片描述

所输出的结果:

在这里插入图片描述

恢复带分隔符的文本文件格式的备份文件 用mysql命令处理.sql文件去还原表结构,然后处理.txt文件去载入记录。

shell> mysql –u 用户名 –p 数据库名 < 表名.sql //还原表结构
shell> mysqlimport –u 用户名 –p 数据库名 表名.txt //还原记录

或者:可用LOAD DATA INFILE 去代替mysqlimport命令,不过此时得在mysql命令下:

mysql> use 数据库名; //选中数据库
mysql> LOAD DATA INFILE ‘表名.txt' INTO TABLE表名; //还原记录

【例子】 恢复数据库test里面的数据表stucou表: 查看test数据库里面的表,没有stucou表:

在这里插入图片描述

用stucou.sql文件恢复stucou表结构:

在这里插入图片描述

stucou数据表恢复成功:

在这里插入图片描述

stucou数据表中没有任何记录:

在这里插入图片描述

用stucou.txt文件恢复stucou表记录:

在这里插入图片描述

在这里插入图片描述

用mysql命令将查询的中间结果导出

将查询结果导入到文本文件中

mysql是一个功能丰富的工具命令,使用mysql还可以在命令行模式下执行SQL指令,将查询结果导入到文本文件中。相比mysqldump,mysql工具导出的结果可读性更强。如果mysql服务器是单独的机器,用户是在一个client上进行操作,用户要把数据结果导入到client机器上,可以使用mysql -e语句。

【命令】:
shell> mysql -u root -p --execute="SELECT 语句" dbname > filename.txt

该命令使用–execute 选项,表示执行该选项后面的语句并退出,后面的语句必须用双引号括起来

dbname为要导出的数据库名称,导出的文件中不同列之间使用制表符分隔,第一行包含了字段名称

【例子】使用mysql命令,导出test库的person表记录到文本文件:
shell> mysql -u root -p --execute="SELECT * FROM person;" test > C:\person3.txt

person3.txt的内容如下

ID     Name    Age    job

1       green    29      lawer

2       suse    26      dancer

3       evans    27      sports man

4       mary    26      singer

可以看到,person3.txt文件中包含了每个字段的名称和各条记录,如果某行记录字段很多,可能一行不能完全显示,可以使用 - -vertical参数,将每条记录分为多行显示

【例子】使用mysql命令导出test库的person表使用–vertical参数显示:
shell> mysql -u root -p --vertical --execute="SELECT * FROM person;" test > C:\person4.txt

*************************** 1. row ***************************

ID: 1

Name: green

Age: 29

job: lawer

*************************** 2. row ***************************

ID: 2

Name: suse

Age: 26

job: dancer

*************************** 3. row ***************************

ID: 3

Name: evans

Age: 27

job: sports man

*************************** 4. row ***************************

ID: 4

Name: mary

Age: 26

job: singer

如果person表中记录内容太长,这样显示将会更加容易阅读

将查询结果导入到html文件中

使用mysql命令导出test库的person表记录到html文件,输入语句如下

shell> mysql -u root -p --html --execute="SELECT * FROM PERSON;" test > C:\person5.html

在这里插入图片描述

将查询结果导入到xml文件中

如果要导出为xml文件,那么使用–xml选项
使用mysql命令导出test库的person表的中记录到xml文件

shell> mysql -u root -p --xml --execute="SELECT * FROM PERSON;" test > C:\person6.xml

<?xml version="1.0"?>

<resultset statement="SELECT * FROM PERSON" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <row>

  <field name="ID">1</field>

  <field name="Name">green</field>

  <field name="Age">29</field>

  <field name="job">lawer</field>

 </row>

 <row>

  <field name="ID">2</field>

  <field name="Name">suse</field>

  <field name="Age">26</field>

  <field name="job">dancer</field>

 </row>

 <row>

  <field name="ID">3</field>

  <field name="Name">evans</field>

  <field name="Age">27</field>

  <field name="job">sports man</field>

 </row>

 <row>

  <field name="ID">4</field>

  <field name="Name">mary</field>

  <field name="Age">26</field>

  <field name="job">singer</field>

 </row>

</resultset>

到此这篇关于MySQL5.7 mysqldump备份与恢复的实现的文章就介绍到这了,更多相关MySQL mysqldump备份恢复内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!

[!--infotagslink--]

相关文章

  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
  • 华为手机怎么恢复已卸载的应用?华为手机恢复应用教程

    华为手机怎么恢复已卸载的应用?有时候我们在使用华为手机的时候,想知道卸载的应用怎么恢复,这篇文章主要介绍了华为手机恢复应用教程,需要的朋友可以参考下...2020-06-29
  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15
  • mysqldump命令导入导出数据库方法与实例汇总

    mysqldump命令的用法1、导出所有库系统命令行mysqldump -uusername -ppassword --all-databases > all.sql 2、导入所有库mysql命令行mysql>source all.sql; 3、导出某些库系统命令行mysqldump -uusername -ppassword...2015-10-21
  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
  • mysql的3种分表方案

    一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
  • Linux下MySQL数据库的备份与还原

    深入解析Linux下MySQL数据库的备份与还原...2013-09-11
  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
  • MySQL数据库备份还原方法

    MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
  • Navicat for MySQL 11注册码\激活码汇总

    Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
  • Mysql命令大全(详细篇)

    一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
  • 基于PostgreSQL和mysql数据类型对比兼容

    这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
  • Mysql中 show table status 获取表信息的方法

    这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12
  • mysql IS NULL使用索引案例讲解

    这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
  • 20分钟MySQL基础入门

    这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02
  • mysql备份的三种方式详解

    一、备份的目的做灾难恢复:对损坏的数据进行恢复和还原需求改变:因需求改变而需要把数据还原到改变以前测试:测试新功能是否可用二、备份需要考虑的问题可以容忍丢失多长时间的数据;恢复数据要在多长时间内完; 恢复的时候...2013-10-04