本文地址: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客户端程序可以将表内容导出至文件中。它有以下特点:
此程序可用于导出所有数据库,或特定数据库,或特定表。
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的过程。