【MySQL学生手册】建立文本备份

本文地址：https://www.askmac.cn/archives/mysql-generate-text-backup.html

 

11.4 建立文本备份

 

11.4.1 通过SQL建立文件备份

 

SELECT命令可以和INTO OUTFILE语法一起使用来将返回结果直接写入文件中。在使用中，需要将INTO OUTFILE语法放在FROM语法之前。例如，将Country表中数据写入Country.txt文件中，执行以下语句：

 

mysql> select * from into outfile 'Country.txt' from Country;

 

 

其中文件名指定了你希望写入的位置，这里也可以写路径，如果没有写明路径，则是指当前会话登陆时所在位置下。

 

SELECT … INTO OUTFILE 使用时有以下特点:

 

• 此语句可被用于本地或远程服务端。由于是服务端本身来写文件，因此生成的结果文件总是被建立在服务端。
• 需要输出文件不能已经存在。
• 语句可用于任何存储引擎。
• 语句要求有FILE权限。
• 输出格式可以通过使用语句项，定义其指定列和行分隔符，引用符和逃逸符来进行控制。

 

使用INTO OUTFILE可以在以下几个方面改变SELECT语句的操作：

 

• 文件被写于服务端，而非通过网路将文件发送至客户端（文件名不能已经存在）。
• 服务端会在其主机上写一个新文件（执行语句需要登录服务端并使用具有FILE权限的账号）
• 被建立的文件具有文件系统访问权限，并为MySQL服务端所有，不过对所有用户开放可读。
• 文件的包含的数据按查询语句返回结果中每条记录一行（默认，列值之间以tab制表符进行分隔，每行则在出现新记录时终止）

 

 

你可以像CSV格式一样建立以逗号分隔值，使用双引号括起值，并对行以回车换行符（Carriage Return：CR）结尾的格式文件。以这种格式来输出结果信息的话，可以使用以下SELECT … INTO OUTFILE语句：

 

 

SELECT * INTO OUTFILE '/tmp/data-out.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r'
FROM t;

 

 

 

11.4.2 使用mysqldump制作文本备份

 

 

mysqldump客户端程序可以将表内容导出至文件中。它有以下特点：

• 此程序可用于导出所有数据库，或特定数据库，或特定表。
• mysqldump可用于对本地或远程服务端进行备份，其dump文件的存放位置取决于你如何调用程序。对于使用tab分隔符导出的数据文件（程序使用--tab命令项），服务端会在其服务器主机端生成文件写入数据。对于包含有CREATE FILE和INSERT语句以进行表重建的SQL格式dump文件，服务端会将表内容发至mysqldump，其会将文件写在客户端所在主机中。
• 此工具可应用于使用任何存储引擎的表。
• 以文本格式生成的输出文件是可移植的，因此这些文件可被用于将数据库内容传输至其它服务器。
• 它可以导出所有结构，导出文件中可以包括DROP和CREATE语句，可以进行一个完整的重建设置
• 它可以仅导出数据
• 它可以仅导出结构
• 它可以以一种标准格式进行导出
• 它可以仅导出MySQL指定的对象以优化导出速度
• 由于导出数据为明文，因此它可以以高比率进行压缩处理

 

有三种通用方式来调用mysqldump：

 

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3 …]
shell> mysqldump [options] --all-databases

 

 

 

如果你在db_name之后不指定任何表名，或如果你使用 --databases 或 --all-databases项，那么对应整个数据库会被导出。

以下例子是在语句中数据库名后紧跟着表名，如City和Country表，那么mysqldump仅会将这些表导出到名为city_country.sql的文件中。

 

 

shell> mysqldump world City Country > city_country.sql

• 使用 --databases（或 -B）项，mysqldump会将任何非命令项参数作为数据库名并导出其下所有表。例如，以下命令将world和test两个数据库导出到一个文件中：

shell> mysqldump --databases world test > world_and_test.sql

• 使用 --all-databases（或 -A）项，mysqldump会导出所有数据库下所有表。例如，此命令会对将所有数据库备份到文件sql中：

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

当你管理了大量数据，那所导出的alldb.sql文件也会很大。因此请确保在执行此命令前你有足够的磁盘空间来存储它。

 

为了导出一个指定的数据库到一个文本文件中，你也可以以一下方式使用mysqldump并指定导出的文件和位置。例如，从一个名为guestdb数据库中导出其下所有表数据：

shell> mysqldump guestdb > guestdb.txt

这样就会将所有相关重建表和guestdb库下的数据写入guestdb.txt文本文件中了。如果仅向导出一张表？假设表名为guestTbl，那么：

shell> mysqldump guestdb guestTbl > guestdb.txt

注意：已经存在的同名文件会被覆盖

 

在txt文件中所建立的数据，我们假设在目标服务端中没有同名的表已被建立。导入文件前，如果新库中表已经存在，那么对于dump文件，从源头导出时可以使用 --add-drop-table项在生成的文件中加上drop table的语句，以保证当文件导入时，对新库中已存表进行清理。同时，例子中也没有提到使用命令时的登陆认证操作。因此完整的举例命令使用应该像这样：

 

 

shell> mysqldump -p --user=username --add-drop-table guestdb guestTbl
Enter password: ********

 

 

其中 -p项通知了MySQL来提示用户提供密码。--user项设置用户名，--add-drop-table增加必要的删表语句。命令输出类似如下：

 

 

 

 

 

** 如果你希望倒入的数据中不设置GTID设置，那么还需要考虑加上 --set-gtid-purged=OFF。

 

 

 

mysqldump在使用中一般标准的连接参数项，有 --host和 --user。在默认连接参数不适合的时候，你就需要提供并设置它们。mysqldump也有一些特定于控制导出操作的参数项。你可以通过mysqldump --help项来查看那些可用项。以下列出了部分比较有用的项可作参考：

• --add-drop-table

指导mysqldup在对每个表导出数据前预先加上一句DROP TABLE 语句用于倒入时删除表。此项保证了在重新导入dump文件时，在表重建前，同名已存在的表已经被移除干净了。

 

• --add-locks

 

导出的文件中，在INSERT语句前后进行锁表处理。

 

• --create-options

 

指导mysqldump对每张导出表所生成的CREATE TABLE语句包含有所有MySQL特定项。默认，mysqldump不会将所有建表相关项加上，这样使得dump文件更适合移植，倒入其它非MySQL RDBMS数据库中。使用 --create-options项，将使得重新倒入MySQL的表和源表具有相同的表设置项。

 

 

• --disable-keys

 

在dump文件中加入ALTER TABLE语句来禁用和启用索引更新，在倒入数据时，这将使得重新导入的速度更快。

 

• --extended-insert或 -e

 

在不加此项的情况下，mysqldump会对导出的每行数据分为一个单独的INSERT语句。此项会生成多行INSERT语句，即一次将多行插入表中。多行插入语句可以使得重新导入变得更有效率，不过它们会比单个独立插入语句欠缺一些可读性，在移植到其它数据库时，其移植性会差些。

 

 

• --flush-logs

 

告知服务端在启动导出操作前，先进行flush日志处理。这样下一个将要同步的binary log（其记录开始时间起到检查点的作用）开始时间为dump文件导出时间。这对于以后数据恢复操作非常有帮助。

 

• --lock-tables

 

在导出表前，mysqldump会对所要导出的表获取一个READ LOCAL锁。对于MyISAM表，READ LOCAL表被导出时候仍允许接受并行插入。对于InnoDB表，READ LOCAL则和READ锁同义。

 

• --no-create-db或 -n

 

正常，当你运行mysqldump时同时带有 --all-databases或 --databases项，程序会在导出文件前卫每个数据库加入一条CREATE DATABASE语句以保证当其不存在时数据库被建立。--no-create-db项可以阻止CREATE DATABASE语句在导出时被写入。不过注意，它们在文件中的出现并不会有什么影响，因为这些语句都会带有IF NOT EXISTS语法，这个项的考虑主要是为了倒入其它不存在此语法的数据库考虑的。

 

• --no-create-info或 -t

 

此命令项会隐藏含有表数据的INSERT语句一般之前的CREATE TABLE语句。用来仅导出你所关心的表数据。此项一般用于应对被倒入数据库已经存在这些表的情况。

 

• --no-date或 -d

 

此项会隐藏将包含表数据的INSERT语句。当你仅需要导出表结构而非数据时可以使用此项。--no-data项提供了一种建立和原表一致表结构的空表方法。

 

• --quick

 

此项告知mysqldump当其从服务端每读取一行，即刻进行导出写入，这对于大表比较有用。默认，mysqldump会在写出前将一张表的所有行读取到内存中；对大表，这会要求更大量的内存，这就可能导致导出失败。

 

• --opt

 

使用此项将开启一组其它项以方面导出和重新导入操作。需要注意的是，它等于同时使用了 --add-drop-tables，--add-locks，--create-options，--quick，--extended-insert，--lock-tables和 --disable-keys项。由于所加入的语句同样降低了对其它非MySQL数据库的可移植性。此项从MySQL 4.1版本开始是作为默认被启用的。因此，如果有移植考虑，可以使用 --skip-opt来进行禁用。如果希望启用 --opt但仅是禁用部分项功能，可以使用这些项对应的 --skip类型项。例如，禁用 --quick，使用 --skip-quick。

 

• --single-transaction

 

将整个导出表的操作作为一个事务来进行。推荐对于InnoDB表的导出作为一个事务进行。它会使用一致性读的特性来保证在dump开始时保持当时InnoDB的状态，不必考虑其它客户端的活动。

 

11.4.3 使用MySQL Workbench生成文本备份

 

 

MySQL Workbench工具也提供了备份和恢复的能力。它会通过调用mysqldump来生成可用于导入的SQL语句，这些语句可用于重建数据库和表并重新导入数据。实际上这些文件就是mysqldump生成的SQL格式备份文件。

 

 

 

 

导航栏中DATA Export和DATA Import/Restore部分即是使用的mysqldump工具。

 

 

 

从执行返回结果中，我们可以看到调用mysqldump的过程。