逻辑备份

  • mysqldump命令及相关参数
  • SELECT...INTO OUTFILE命令
  • 逻辑备份的恢复
  • LOAD DATA INFIL命令
  • mysqlimport命令


mysqldump命令及相关参数

mysqldump备份工具最初由Igor Romanenko编写完成，通常用来完成 转存（dump）数据库的备份 及 不同数据库之间的移植

如从MySQL低版本数据库升级到MySQL高版本数据库，又或者从MySQL数据库移植到Oracle、Microsoft SQL Server数据库等。

mysqldump的语法如下：

mysqldump[arguments] > fle_name

如果想要备份所有的数据库，可以使用--all-databases选项：

mysqldump --all-databases > dump.sql

如果想要备份指定的数据库，可以使用--databases选项:

mysqldump --databases db1 db2 db3 > dump.sql

备份出的文件内容就是表结构和数据，所有这些都是用SQL语句方式表示
文件开始和结束的注释部分是用来设置MySQL数据库的各项参数，一般用来使还原工作更有效和准确地进行。之后的部分先是CREATE TABLE语句，接着就是INSERT的SQL语句了

mysqldump的参数选项很多，可以通过使用mysqldump--help命令来查看所有的参数，有些参数有缩写形式，如--lock-tables的缩写形式-l，以下列举较重要的参数：

  • --single-transaction：在备份开始前，先执行START TRANSACTION命令，以此来获得备份的一致性，该参数只对InnoDB存储引擎有效。当启用该参数并进行备份时，确保没有其他任何的DDL语句执行，因为一致性读并不能隔离DDL操作
  • --lock-tables（-l）：在备份中，依次锁住 每个架构下 的 所有表。一般用于MyISAM存储引擎，当备份时只能对数据库进行读取操作，不过备份依然可以保证一致性。对于InnoDB存储引擎，不需要使用该参数，用--single-transaction即可。并且--lock-tables和--single-transaction是互斥（exclusive）的，不能同时使用。如果用户的MySQL数据库中，既有MyISAM存储引擎的表，又有InnoDB存储引擎的表，那么这时用户的选择只有--lock-tables了。此外，--lock-tables选项是依次对每个架构中的表上锁的，因此 只能保证 每个架构下 表备份 的一致性，而不能保证 所有架构下 表的一致性
  • --lock-all-tables（-x）：在备份过程中，对 所有架构中 的 所有表 上锁。这个可以避免之前说的--lock-tables参数不能同时锁住所有表的问题
  • --add-drop-database：在CREATE DATABASE前先运行DROP DATABASE。这个参数需要和--all-databases或者--databases选项一起使用。在默认情况下，导出的文本文件中并不会有CREATE DATABASE
  • --master-data[=value]：通过该参数产生的备份转存文件 主要用来 建立一个replication。当value的值为1时，转存文件中记录CHANGE MASTER语句。当value的值为2时，CHANGE MASTER语句被写出SQL注释。在默认情况下，value的值为空。--master-data会自动忽略--lock-tables选项。如果没有使用--single-transaction选项，则会自动使用--lock-all-tables选项
  • --events（-E）：备份事件调度器
  • --routines（-R）：备份存储过程和函数
  • --triggers：备份触发器
  • --hex-blob：将BINARY、VARBINARY、BLOG和BIT列类型备份为十六进制的格式。mysqldump导出的文件一般是文本文件，但是如果导出的数据中有上述这些类型，在文本文件模式下可能有些字符不可见
  • --tab=path（-T path）：产生TAB分割的数据文件。对于每张表，mysqldump创建一个包含CREATE TABLE语句的table_name.sql文件，和包含数据的tbl_name.txt文件。可以使用--fields-terminated-by=...，--fields-enclosed-by=...，--fields-optionally-enclosed-by=...，--fields-escaped-by=...，--lines-terminated-by=...来改变默认的分割符、换行符等
  • --where='where_condition'（-w’where_condition’）：导出给定条件的数据

SELECT…INTO OUTFILE命令

SELECT…INTO语句也是一种逻辑备份的方法，更准确地说是导出一张表中的数据
SELECT…INTO的语法如下：

SELECT[column 1],[column 2]...
INTO
OUTFILE'file_name'
[{FIELDS|COLUMNS}
[TERMINATED BY'string']
[[OPTIONALLY]ENCLOSED BY'char']
[ESCAPED BY'char']
]
[LINES
[STARTING BY'string']
[TERMINATED BY'string']
]
FROM TABLE WHERE......
  • FIELDS[TERMINATED BY'string']表示每个列的分隔符
  • [[OPTIONALLY]ENCLOSED BY'char']表示对于字符串的包含符
  • [ESCAPED BY'char']表示转义符
  • [STARTING BY'string']表示每行的开始符号
  • TERMINATED BY'string'表示每行的结束符号。如果没有指定任何的FIELDS和LINES的选项，默认使用以下的设置：
FIELDS TERMINATED BY'\t'ENCLOSED BY''ESCAPED BY'\\'
LINES TERMINATED BY'\n'STARTING BY''
  • file_name表示导出的文件，但文件所在的路径的权限必须是mysql:mysql的，否则MySQL会报没有权限导出

默认导出的文件是以TAB进行列分割的，如果想要使用其他分割符，如“，”，则可以使用FIELDS TERMINATED BY'string'

逻辑备份的恢复

mysqldump的恢复操作比较简单，因为备份的文件就是导出的SQL语句，一般只需要执行这个文件就可以了，可以通过以下的方法：

[root@xen-server～]#mysql -u root -p < test_backup.sql
Enter password:

如果在导出时包含了创建和删除数据库的SQL语句，那必须确保删除架构时，架构目录下没有其他与数据库相关的文件，否则可能会得到以下的错误：

mysql>drop database test;
ERROR 1010(HY000):Error dropping database(can't rmdir'./test',errno:39)

因为逻辑备份的文件是由SQL语句组成的，也可以通过SOURCE命令来执行导出的逻辑备份文件，如下：

mysql>source/home/mysql/test_backup.sql;

通过mysqldump可以恢复数据库，但是经常发生的一个问题是，mysqldump可以导出存储过程、导出触发器、导出事件、导出数据，但是却不能导出视图
因此，如果用户的数据库中还使用了视图，那么在用mysqldump备份完数据库后还需要导出视图的定义，或者备份视图定义的frm文件，并在恢复时进行导入，这样才能保证mysqldump数据库的完全恢复

LOAD DATA INFIL命令

若通过mysqldump-tab，或者通过SELECT INTO OUTFILE导出的数据需要恢复，这时可以通过命令LOAD DATA INFILE来进行导入
LOAD DATA INFILE的语法如下：

LOAD DATA INTO TABLE a IGNORE 1 LINES INFILE'/home/mysql/a.txt'
[REPLACE|IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS|COLUMNS}
[TERMINATED BY'string']
[[OPTIONALLY]ENCLOSED BY'char']
[ESCAPED BY'char']
]
[LINES
[STARTING BY'string']
[TERMINATED BY'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name=expr,...]

要对服务器文件使用LOAD DATA INFILE，必须拥有FILE权
其中对于导入格式的选项和之前介绍的SELECT INTO OUTFILE命令完全一样
IGNORE number LINES选项可以忽略导入的前几行

mysqlimport命令

mysqlimport是MySQL数据库提供的一个命令行程序，从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同。其语法格式如下：

shell>mysqlimport[options]db_name textfile1[textfile2...]

和LOAD DATA INFILE不同的是，mysqlimport命令可以用来导入多张表，并且通过--user-thread参数并发地导入不同的文件
这里的并发 是指 并发导入多个文件，而不是指 mysqlimport 可以 并发地 导入 一个文件，这是有明显区别的

此外，通常来说并发地对同一张表进行导入，其效果一般都不会比串行的方式好