mysql 5.5 mysqldump 原文翻译

根据mysql 5.5第6.4章节理解和自己翻译水平有限如有纰漏请指教,原文如下.

http://dev.mysql.com/doc/refman/5.5/en/using-mysqldump.html

 

6.4 使用mysqldump备份(Using mysqldump for Backups)

首先多余的不用说了备份用来干什么大家都清楚。

mysqldump备份分两种输出形式：

1. 无--tab选项，输出标准的SQL格式。输出包含CREATE语句(databases,tables,stored routines,and so forth),INSERT语句插入数据到表。输出可以保存成一个文件，之后可以用mysql再次创建。选项可以控制输出SQL语句格式，文件类型。

2. 含--tab选项，每个表对应两个备份文件。一个文件为由tab分割的文本，一行对应一条数据记录，在目录中这个文件输出名为tb1_name.txt。同样会创建一个含有CREATE TABLE语句，名为tb1_name.sql的文件。

 

6.4.1 使用mysqldump备份SQL数据(Dumping Data in SQL Format with mysqldump)

默认语法

shell>mysqldump [arguments] > file_name

 

备份所有数据库

shell>mysqldump --all-databases > dump.sql

如果你数据库有密码上面那样当然是不行的加上用用户密码选项

shell>mysqldump -uroot -p --all-databases > dump.sql

 

选择性的备份数据库

shell>mysqldump --databases db1 db2 db3 > dump.sql

 

--databases选项会把后面几个名称作为数据库名。没有这个选项mysqldump会把第一个当成数据库名，后面的当成表名。

 

--all-databases或--databases，mysqldump会为每一个数据库写入CREATE DATABASE和USE。以确保当备份文件被再次载入的时候，如果数据库不存在则创建数据库，然后设置为当前数据库(USE DATABASES)，当INSERT的时候数据库的内容均会加载到同一个数据库中。

 

如果想要备份文件载入时强制删除数据库，可以使用--add-drop-database。这样mysqldump会在CREATE DATABASE前写入DROP DATABASE。

 

备份单个数据库

shell>mysqldump --databases test > dump.sql

在单个数据库时可以这样简写

shell>mysqldump test > dump.sql

省略--databases时备份文件没有CREATE DATABASE,USE。有下面几方面含义。

1.当你导入备份文件时，你必须选择一个默认数据库名这样程序才知道要导入到哪个数据库。

2.当导入到时候你可以选择一个不同的数据库名。

3.如果要导入到数据库不存在，你必须提前创建。

4.因为输出文件没有CREATE DATABASE，所以--add-drop-database选项没有效果，如果你使用也没有DROP DATABASE。

 

选择表备份，表名跟在数据库名之后

shell>mysqldump test t1 t3 t7 > dump.sql

 

6.4.2 导入SQL备份文件(Reloading SQL-Format Backups)

导入由mysqldump备份的文件，如果使用了--all-databases或--databases选项，包含CREATE DATABASE和USE。且不需要导入到不同的数据库中，可以这样写。

shell>mysql < dump.sql

 

另外在mysql内部你可以这样写

mysql>source dump.sql;

 

如果是简写单个数据库导出没有CREATE DATABASE和USE，如果需要则先创建数据库。

shell>mysqladmin create db1

接着选择具体的数据库

shell>mysql db1 < dump.sql

 

另外在mysql内部创建数据库，选择数据库，导入备份：

mysql>CREATE DATABASE IF NOT EXISTS db1;
mysql>USE db1;
mysql>source dump.sql

 

6.4.3 使用mysqldump以分割文本方式备份(Dumping Data in Delimited-Text Format with mysqldump)

mysqldump备份时使用--tab=dir_name，使用dir_name作为备份文件输出目录，每一个表对应两个文件，文件名为表名。例如表名为t1，文件名则为t1.sql和t1.txt。.sql文件含表的CREATE TABLE语句。.txt文件含表的数据，一行对应一条数据记录。

例如备份数据库db1到/tmp目录

shell>mysqldump --tab=/tmp db1

 

.txt文件被服务器创建其中包含表数据，为系统用户所有。当程序运行SELECT ... INTO OUTFILE写入文件时你必须拥有相应权限，.txt存在时会发生错误。

 

服务器发送创建CREATE备份表命令给mysqldump写入.sql，因此文件是mysqldump所有。

 

--tab最好的用法是本地备份。如果你用来备份远程服务器，--tab的目录必须本地和远程服务器都存在，.txt文件将会写入到远程服务器(on the server host)目录中，.sql文件将会写入到本地目录中(on the client host)。

 

对于mysql --tab，默认情况下服务器将表数据写入.txt一行一条记录两个值之间tab分割，没有引号，新的一行在行的末尾。(这些都为相同的默认值SELECT ... INTO OUTFILE)

 

通过选项输出不同的格式，mysqldump支持如下:

--fields-terminated-by=str

字段值之间的分隔符默认为tab

 

--fields-enclosed-by=char

字段值两边的分隔符默认为空

(PS这个翻译过来真不知道说的对不对.效果可以参考INSERT INTO中如果VALUE是字符型就要加上双引号中的双引号)

 

--fields-optionally-enclosed-by=char

这个效果同上但只有字符型的字段值才会加默认值为空

 

--fields-escaped-by=char

转义特殊字符的字符默认值无

 

--lines-terminated-by=str

记录之间的分隔符默认值是换行

 

根据这些选项你可以指定任何值，可能需要在命令行中对值进行适当的引用或转义。另外可以使用16进制表示值，假设想要mysqldump输出的值引用双引号。可以添加 --fields-enclosed-by 选项指定值为双引号。但是这个值往往是特殊的转义字符需要处理一下。例如在unix上可以这样使用双引号：

--fields-enclosed-by='"'

在其他的平台可以使用16进制表示：

--fields-enclosed-by=0x22

 

这是几个选项的同时使用的例子，记录以逗号分隔多条记录之间用换行/回车：

shell> mysqldump --tab=/tmp --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0adb1

(windows平台--fields-enclosed-by要等于0x22。换行符也没有出来变成了乱码，这个可能也需要改)

当设置了数据输出格式，在导入备份数据的时候同样也要设置相同的格式，以保证内容正确导入。

 

6.4.4 导入以分割文本方式的备份(Reloading Delimited-Text Format Backups)

使用mysqldump --tab文件备份，每一个表都被存储成包含CREATE TABLE语句的.sql文件和保护表数据的.txt文件。导入表之前先定位到备份文件的目录。这样.sql文件先创建空的表，然后.txt文件导入数据:

shell> mysql db1 < t1.sql
shell> mysqlimport db1 t1.txt

 

另外在mysql端导入需要使用LOAD DATA INFILE:

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

(PS:范例中t1.txt的路径为当前选中数据库的路径，可以修改为绝对路径'C:/t1.txt')

 

如果在备份文件时使用了控制数据格式的选项，在使用mysqlimport或LOAD DATA INFILE导入时也需设置相同的选项:

shell>mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

或

mysql>USE db1;
mysql>LOAD DATA INFILE 't1.txt' INTO TABLE t1
    ->FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'
    ->LINES TERMINATED BY '\r\n';

 

6.4.5 mysqldump技巧(mysqldump Tips)

这一章解决些常见问题所需的技术

如何创建备份数据库

如何从一个服务器将数据库拷贝到另一个服务器

如何备份一个存储程序(存储过程函数,触发器,日志)

如何备份数据和创建分离

 

6.4.5.1 创建数据库备份文件(Making a Copy of a Database)

shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql

在使用db1备份文件覆盖db2时不要使用--databases选项因为那样会在备份文件中写入USE db1.

 

6.4.5.2 从一个服务器复制数据库到另一个服务器(Copy a Database from one Server to Another)

服务器1:

shell>mysqldump --databases db1 > dump.sql

将备份文件复制到服务器2

 

服务器2:

shell>mysql < dump.sql

 

使用mysqldump时添加--database选项备份文件会包含CREATE DATABASE和USE语句，如果不存在则会创建同时设为默认数据库在导入数据。

 

你可以忽略--database选项，但在导入数据库的时候需要创建一个数据库(如果需要)然后设置为当前数据库。

 

服务器1:

shell>mysqldump db1 > dump.sql

服务器2:

shell> mysqladmin create db1
shell> mysql db1 < dump.sql

所以忽略--database选项就可以选择不同的数据库导入。

 

6.4.5.3 备份存储程序(Dumping Stored Programs)

以下是存储程序几个可选选项(存储过程函数,触发器,日志)

--events:调度事件

--routines:存储过程和函数

--triggers:触发器

 

triggers默认是备份的routines，events需要选择性备份默认是不备份的可以选择跳过--skip-events, --skip-routines, --skip-triggers。

 

6.4.5.4 备份表定义和内容分开(Dumping Table Definitions and Content Separately)

--no-data选项告诉mysqldump不备份表数据，备份文件仅包括创建表。相对的--no-create-info选项告诉mysqldump备份仅包含数据。

示例：

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

 

一个只备份CREATE存储和事件的例子：

shell> mysqldump --no-data --routines --event stest > dump-defs.sql

 

6.4.5.5 用备份来测试mysql升级后兼容的问题(Using mysqldump to test for Upgrade Incompatibilities)

当考虑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

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