时间戳

首页 > 技术文章 > mysqldump的用法

mysqldump的用法

ct20150811 2018-08-06 14:25 原文

1、mysqldump 是文本备份还是二进制备份

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

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

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

l  使用- -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

5、使用 mysqldump 怎样备份指定的数据库？

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

6、使用 mysqldump 怎样备份指定的表？

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

7、我不想要数据，怎样仅获取 DDL？

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

8、一次 mysqldump 备份花费多长时间？

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

9、怎样备份位于其他服务器的远程数据库？

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

10、–routines 选项的含义是什么？

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

11、怎样列出 mysqldump 中的所有选项？

    mysqldump –help

12、mysqldump 中常用的选项是？

    All-databases
    Databases 
    Routines
    Single-transaction （它不会锁住表） – 一直在 innodb databases 中使用
    Master-data – 复制 （现在忽略了）
    No-data – 它将 dump 一个没有数据的空白数据库

13、默认所有的 triggers 都会备份吗？

    是的

14、single transaction 选项的含义是什么？

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

15、使用 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 &

16、使用 mysqldump 怎样压缩一个备份？

    注意: 压缩会降低备份的速度
    Mysqldump [options] | gzip > backup.sql.gz

17、mysqldump 备份大数据库是否是理想的？

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

18、怎样通过使用 mysqldump 来恢复备份？
        使用来源数据的方法
        Mysql –u root –p < backup.sql

19、在恢复期间我想记录错误到日志中，我也想看看恢复的执行时间？

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

20、怎样知道恢复是否正在进行？

    显示完整的进程列表

21、如果数据库是巨大的，你不得不做的事情是？

    使用 nohup 在后台运行它

22、我是否可以在 windows 上使用 mysqldump 备份然后在 linux 服务器上恢复？

    是的

23、我怎么传输文件到目标服务器上去？
        使用 scp
        使用 sftp
        使用 winscp

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

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

25、默认情况下，mysqldump 包含 drop 数据库吗？

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

26、怎样从一个多数据库备份中提取一个数据库备份（假设数据库名字是 test）？

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

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

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

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语法和选项实例

【命令】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>





推荐阅读