mysqldump命令详解

注意：备份加入--databases 备份文件中会有创建数据库的语句，否则没有

建议：导出库加入、导出表不加此参数

一、mysqldump命令备份Mysql数据库的参数说明

在用mysqldump备份使用那些参数选项是最完美的组合呢？

--skip-opt                 ----跳过   --add-drop-table, --add-locks,--create-options, --quick, --extended-insert等

--create-option         ----添加create相关的选项

--single-transaction   ----一致性备份，保证导出数据一致性

-q                            ----采用快速的dump方式(提高导出性能)

-e                            ----采用多重insert语句形式(提高还原性能)，当一个表的数据量很大情况下不知道会不会导致死锁？

--no-autocommit      ----采用批量提交方式(提高还原性能)

-R                            ----导出存储过程，函数，和触发器

--master-data           ----如果有写log-bin且版本为5.0以上的版本，打开-lock-all-tables

                               则再加上 --master-data=1  （输出中会带change master 便于从库搭建）

                               则再加上 --master-data=2  （输出中会带注释change master便于从库搭建）

--events                    ----如果是5.1以上的版本使用，包含事件

--compress               ----客户端与服务器之间启用压缩

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=154;

mysqldump产生两种类型的输出，具体取决于是否指定了该--tab 选项：

没有--tab， mysqldump将SQL语句写入标准输出。该输出包含 CREATE用于创建转储对象（数据库，表，存储的例程等）的INSERT语句，

以及 用于将数据加载到表中的语句。可以将输出保存在文件中，并稍后使用mysql重新创建转储的对象来重新加载 。选项可用于修改SQL语句的格式，并控制转储哪些对象。

使用--tab， mysqldump为每个转储的表产生两个输出文件。服务器以制表符分隔的文本形式写入一个文件，每表行一行。该文件tbl_name.txt 在输出目录中命名 。

服务器还将CREATE TABLE表的语句发送 到mysqldump，将其写为tbl_name.sql 在输出目录中命名的文件 。

不带--databases，转储输出不包含CREATE DATABASE or USE 语句。这有几个含义：

重新加载转储文件时，必须指定默认数据库名称，以便服务器知道要重新加载的数据库。

对于重新加载，可以指定一个与原始名称不同的数据库名称，这使您可以将数据重新加载到另一个数据库中。

如果要重装的数据库不存在，则必须首先创建它。

因为输出将不包含任何 CREATE DATABASE语句，所以该--add-drop-database 选项无效。如果使用它，则不会产生任何 DROP DATABASE语句。

二、mysqldump (sql 格式输出) ，默认标准输出到屏幕

单数据库导出：

mysqldump -uroot -p'gzjpm0330' -S /tmp/mysql.sock kyriba > /home/mysql/backup/kyriba_fullback.sql

mysqldump -uroot -p'gzjpm0330' -S /tmp/mysql.sock --databases kyriba > /home/mysql/backup/kyriba_fullback01.sql

单表导出：

mysqldump -uroot -p'gzjpm0330' -S /tmp/mysql.sock kyriba  k_bank  > /home/mysql/backup/kyriba_k_bank.sql

多数据库导出：

mysqldump -uroot -p'gzjpm0330' -S /tmp/mysql.sock  --databases kyriba mysql >/home/mysql/backup/kyriba_mysql_fullback.sql

全部数据库导出：

mysqldump -uroot -p'gzjpm0330' -S /tmp/mysql.sock  --all-databases  >/home/mysql/backup/database_fullback.sql

多库导出可以使用参数：-B 代替--databases

全库导出可以使用参数：-A 代替--all-databases

三、mysqldump输出定界格式文件，并且输出为实际数据的.txt文件和对象结构的.sql 文件（注意不用于导出库）

--fields-terminated-by：指定列值的分隔符，默认值Tab符

--fields-enclosed-by：指定列值的包括符，默认值没有（对于字符中包含列分隔符的直接包括起来）例如：12#kk#2#4，2与4间包括了#，使用包括符后，”2#4“

--fields-optionally-enclosed-by: 指定非数字列的包括符，默认值没有（有就使用，没有就不使用）

--fields-escped-by：指定转义符，默认值转义符\

--lines-terminated-by:指定行结束符，默认值就是换行符

通用性强的CSV 格式导出：

mysqldump -uroot -p'gzjpm0330' -S /tmp/mysql.sock --tab=/home/mysql/backup/k_bank kyriba k_bank --fields-terminated-by=',' --fields-enclosed-by='"'

--tab ：备份输出到此位置

导出以逗号分隔，列值使用双引号引住

导出后有.txt(数据)和.sql(结构) 文件。每个表都有这两个文件

导出不包含数据：

shell> mysqldump --no-data test > dump-defs.sql

导出不包含结构：

shell> mysqldump --no-create-info test > dump-data.sql

四、使用mysqldump测试升级不兼容性

在考虑进行MySQL升级时，谨慎地将较新的版本与当前的生产版本分开安装。然后，您可以从生产服务器中转储数据库和数据库对象定义，

并将它们加载到新服务器中，以验证是否已正确处理它们。（这对于测试降级也很有用）

在生产服务器上：

shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql

在升级的服务器上：

shell> mysql < dump-defs.sql

因为转储文件不包含表数据，所以可以对其进行快速处理。这使您能够发现潜在的不兼容性，而无需等待冗长的数据加载操作。在处理转储文件时查找警告或错误。

验证定义是否正确处理后，转储数据并尝试将其加载到升级的服务器中。

在生产服务器上：

shell> mysqldump --all-databases --no-create-info > dump-data.sql

在升级的服务器上：

shell> mysql < dump-data.sql

现在检查表内容并运行一些测试查询

五、恢复

要重新加载由mysqldump编写的 包含SQL语句的转储文件，请将其用作mysql客户端的输入 。

如果转储文件是由mysqldump使用 --all-databases或 --databases选项创建的 ，

则它包含CREATE DATABASE和 USE语句，并且无需指定默认数据库以将数据加载到其中：

1、定界符格式恢复

先恢复表结构：

mysql -uroot -p'gzjpm030' -S /tmp/mysql.sock kyriba </home/mysql/backup/k_bank/k_bank.sql

恢复数据：

mysql>use kyriba;

mysql>LOAD DATA INFILE  '/home/mysql/backup/k_bank/k_bank.txt' into table k_bank;

这种方式用的很少，对于语法要求很高。

2、SQL 格式恢复(默认的方式)

2.1、使用重定义符<

恢复kyriba数据库(注意导出方式)

1、先创建数据库

mysql -uroot -p'gzjpm030' -S /tmp/mysql.sock kyriba  < /home/mysql/backup/kyriba_fullback.sql

2、直接恢复

mysql -uroot -p'gzjpm030' -S /tmp/mysql.sock  < /home/mysql/backup/kyriba_fullback01.sql

恢复k_bank 表

mysql -usystem -p -S /usr/local/mysql/data/mysql.sock kyriba <  kyriba_k_bank.sql

2.2、使用source(先创建kyriba数据库)

mysql>use kyriba;

mysql>source /home/mysql/backup/kyriba_fullback.sql;

1、mysqldump 从全备份中恢复单库、单表

恢复单库：（前提条件库是要存在的，恢复的是库里的对象）

mysql -uroot -p'gzjpm030' -S /tmp/mysql.sock  kyriba --one-database < database_fullback.sql

恢复单表：

先获取表结构：3种都可以，建议使用第3个，前面两个都需要获取备份中下一个表的名称（顺序与show tables 查看的一样）

awk '/^-- Table structure for table `t2`/,/^-- Table structure for table `t3`/{print}'  gl_test_full05.sql > t2.sql

sed -n -e '/-- Table structure for table `t2`/,/-- Table structure for table `t3`/p'  gl_test_full05.sql > t2_sed.sql

sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t4`/!d;q' gl_test_full05.sql > t4.sql

获取表的数据：

grep -i 'INSERT INTO `t4`' gl_test_full05.sql >> t4.sql

恢复：

mysql -usystem -p -S /usr/local/mysql/data/mysql.sock gl_test < t4.sql