MYSQL5.6学习——mysqldump备份与恢复

MYSQL备份

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

热备份：不停止服务进行备份（在线）

l  mysql的MyIsam引擎只支持冷备份，InnoDB支持热备份，原因：

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

l  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选项。

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

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

mysqldump语法和选项

【命令】shell> mysqldump -help

 

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

【常用的选项】

1)        - -add-drop-table

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

2)        - -add-locks

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

3)        - -tab

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

4)        --quick或者—opt

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

5)        --skip-comments

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

6)        –compact

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

以SQL格式备份数据

       如果备份文件名.sql没有指定所放置的路径，则默认放在~\MySQL\MySQL Server 5.6\bin目录下。但可以通过以下方式指定其备份文件的路径：

mysqldump –h 主机名 –u 用户名 –p  - -all-databases  > C:\备份文件名.sql

l  调用mysqldump带有- -all-databases选项备份所有的数据库

【命令】mysqldump –h 主机名 –u 用户名 –p  - -all-databases  > 备份文件名.sql

【例子】以’test’@’%’用户为例，查看其数据库：

 

用mysqldump带有- -all-databases选项备份所有的数据库（test，test1）：

 

 

 

l  调用mysqldump带有- -databases选项备份指定的数据库

【命令】mysqldump –u 用户名 –p  - -databases db1 db2 db3 …  > 备份文件名.sql

【例子】用mysqldump带有- -databases选项备份指定的数据库（如test，test1）

 

 

 

l  调用mysqldump备份一个指定的数据库：

【命令1】mysqldump –u 用户名 –p  - -databases db > 备份文件名.sql  

【例子1】用mysqldump带有- -databases选项备份指定的一个数据库（如test）

 

 

 

或【命令2】 mysqldump –u 用户名 –p  db > 备份文件名.sql

【例子2】用mysqldump不带有- -databases选项备份指定的一个数据库（如test）

 

 

注意生成的备份文件中是没有CREATE DATABASE和USE语句的：

 

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

 

l  调用mysqldump备份某个数据库中的某几张表：

【命令】mysqldump –u用户名 –p 数据库名 表名1 表名2 表名3… > 备份文件名.sql

【例子】test数据库中的表：

 

用mysqldump备份数据库test中的course表和student表：

 

 

scdump.sql文件中只有CREATE TABLE，INSERT course，student的信息。

恢复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文件将会被写在本地主机目录中。

l  调用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命令将查询的中间结果导出

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

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表中记录内容太长，这样显示将会更加容易阅读

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

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

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

 

l  将查询结果导入到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>

 

【参考】http://www.cnblogs.com/lyhabc/p/3842015.html