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

本文地址:https://www.askmaclean.com/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客户端程序可以将表内容导出至文件中。它有以下特点:

  • 此程序可用于导出所有数据库,或特定数据库,或特定表。【dbdao.com 数据岛】
  • 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文件时,在表重建前,同名已存在的表已经被移除干净了。【dbdao.com 数据岛】

  • --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的状态,不必考虑其它客户端的活动。【dbdao.com 数据岛】

 

11.4.3 使用MySQL Workbench生成文本备份

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

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

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

by 汪伟华,dbdao.com 数据岛

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号