mysqldump 用法（快查）

1 关于 mysqldump

问题1：mysqldump 是文本备份还是二进制备份？

它是文本备份，如果你打开备份文件你将看到所有的语句，可以用于重新创建表和对象。它也有 insert 语句来使用数据构成表。

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

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

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

问题2：mysqldump 的语法是什么？

mysqldump -u [uname] -p[pass] –-databases [dbname][dbname2] > [backupfile.sql]

问题3：使用 mysqldump 怎样备份所有数据库？

mysqldump -u root -p –-all-databases > backupfile.sql

问题4：使用 mysqldump 怎样备份指定的数据库？

mysqldump -u root -p –-databases school hospital > backupfile.sql

问题5：使用 mysqldump 怎样备份指定的表？

mysqldump –-user=root –-password=mypassword -h localhost databasename table_name_to_dump table_name_to_dump_2 > dump_only_two_tables_file.sql

问题6：我不想要数据，怎样仅获取 DDL？

mysqldump -u root -p –-all-databases –-no-data > backupfile.sql

问题7：一次 mysqldump 备份花费多长时间？

这依赖于数据库大小，100 GB 大小的数据库可能花费两小时或更长时间

问题8：怎样备份位于其他服务器的远程数据库？

mysqldump -h 172.16.25.126 -u root -ppass dbname > dbname.sql

问题9：–-routines 选项的含义是什么？

通过使用 `--routines` 产生的输出包含 `CREATE PROCEDURE` 和 `CREATE FUNCTION` 语句用于重新创建 `routines`。如果你有 `procedures` 或 `functions` 你需要使用这个选项

问题10：怎样列出 mysqldump 中的所有选项？

mysqldump –-help

问题11：mysqldump 中常用的选项是？

--all-databases
--databases 
--routines
--single-transaction（它不会锁住表）：一直在 innodb databases 中使用
--master-data ： 复制（现在忽略了）
--no-data ：它将 dump 一个没有数据的空白数据库

问题12：默认所有的 triggers 都会备份吗？

是的

问题13、single transaction 选项的含义是什么？

`–-single-transaction` 选项避免了 innodb databases 备份期间的任何锁，如果你使用这个选项，在备份期间，没有锁

问题14：使用 mysqldump 备份的常用命令是什么？

nohup mysqldump –-socket=mysql.sock –-user=user1 –-password=pass -–single-transaction -–flush-logs –-master-data=2 –-all-databases -–extended-insert –-quick –-routines > market_dump.sql 2> market_dump.err &

问题15：使用 mysqldump 怎样压缩一个备份？

注意: 压缩会降低备份的速度

mysqldump [options] | gzip > backup.sql.gz

问题16：mysqldump 备份大数据库是否是理想的？

依赖于你的硬件，包括可用的内存和硬盘驱动器速度，一个在 5GB 和 20GB 之间适当的数据库大小。 虽然有可能使用  mysqldump 备份 200GB 的数据库，这种单一线程的方法需要时间来执行。

问题17：怎样通过使用 mysqldump 来恢复备份？

使用来源数据的方法

mysql –u root –p < backup.sql

问题18：在恢复期间我想记录错误到日志中，我也想看看恢复的执行时间？

time Mysql –u root –p < backup.sql > backup.out 2>&1

问题19：怎样知道恢复是否正在进行？

显示完整的进程列表

问题20：如果数据库是巨大的，你不得不做的事情是？

使用 nohup 在后台运行它

问题21：是否可以在 windows 上使用 mysqldump 备份然后在 linux 服务器上恢复？

是的

问题22：我怎么传输文件到目标服务器上去？

使用 scp
使用 sftp
使用 winscp

问题23：如果我使用一个巨大的备份文件来源来恢复会发生什么？

如果你的一个数据库备份文件来源，它可能需要很长时间运行。处理这种情况更好的方式是使用 nohup 来在后台运行。也可使用在 unix 中的 screen 代替

问题24：默认情况下，mysqldump 包含 drop 数据库吗？

你需要添加 –-add-drop-database 选项

问题25：怎样从一个多数据库备份中提取一个数据库备份（假设数据库名字是 test）？

sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql

2 冷备份 vs. 热备份

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

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

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

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

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

• 直接复制整个数据库目录

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

• 使用mysqlhotcopy工具快速备份

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

3 mysqldump 语法和选项实例

查看选项

shell> mysqldump --help

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

3a. 参数列表

4 备份与恢复

4a. 以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  > 备份文件名.sql

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

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

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

mysqldump –u 用户名 –p  --databases db > 备份文件名.sql  

或

mysqldump –u 用户名 –p  db > 备份文件名.sql

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

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

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

4b. 恢复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来执行命令的。

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

调用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 数据库名

恢复带分隔符的文本文件格式的备份文件
用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表名;    //还原记录

4d. 用 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>

参考文章：

• mysqldump的用法
• mysqldump 常用命令以及参数详情