12.1.2 逻辑备份和恢复

1.逻辑备份

逻辑备份也可称为文件级备份，是将数据库中的数据备份为一个文本文件，而备份的大小取决于文件大小。并且该文本文件是可以移植到其他机器上的，甚至是不同硬件结构的机器。

⑴使用MYSQLDUMP命令生成INSERT语句备份。

此方法类似于Oracle的EXPDP/EXP工具，语法如下。使用帮助如下。

mysqldump [arguments] > file_name.sql

[root@gc～]# mysqldump Usage: mysqldump [OPTIONS] database [tables] OR mysqldump[OPTIONS]--databases[OPTIONS]DB1[DB2 DB3...] OR mysqldump[OPTIONS]--all-databases[OPTIONS] For more options, use mysqldump --help

【范例12-1】

备份所有数据库，语句如下。

mysqldump -uroot -proot --all-database > /tmp/dumpback/alldb.sql

备份某些数据库，语句如下。

mysqldump -uroot -proot --database sqoop hive > /tmp/dumpback/sqoop_hive.sql

备份某数据库中的表，语句如下。

mysqldump -uroot -proot sqoop tb1 > /tmp/dumpback/sqoop_tb1.sql

查看备份内容，语句如下。

[root@gc dumpback]#more sqoop_tb1.sql --MySQL dump 10.13 Distrib 5.5.24,for Linux(x86_64) -- --Host:localhost Database:sqoop -------------------------------------------------------- --Server version 5.5.24 /!40101 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT/; ...... -- --Table structure for table'tb1' -- DROP TABLE IF EXISTS'tb1'; /!40101 SET@saved_cs_client =@@character_set_client/; /!40101 SET character_set_client=utf8/; CREATE TABLE'tb1'( 'table_schema'varchar(64)CHARACTER SET utf8 NOT NULL DEFAULT'', 'table_name'varchar(64)CHARACTER SET utf8 NOT NULL DEFAULT'', 'table_type'varchar(64)CHARACTER SET utf8 NOT NULL DEFAULT'' )ENGINE=InnoDB DEFAULT CHARSET=latin1; /!40101 SET character_set_client=@saved_cs_client/; -- --Dumping data for table'tb1' -- LOCK TABLES'tb1'WRITE; /!40000 ALTER TABLE'tb1'DISABLE KEYS/; INSERT INTO'tb1'VALUES('information_schema','CHARACTER_SETS','SYSTEM VIEW') ...... /!40000 ALTER TABLE'tb1'ENABLE KEYS/; UNLOCK TABLES; /!40103 SET TIME_ZONE=@OLD_TIME_ZONE/; ..... --Dump completed on 2013-03-25 18:26:53

这里需要思考的是：如何保证数据备份的一致性？要想保证数据的一致性可以通过以下两种方法做到。

①同一时刻取出所有数据。

对于事务支持的存储引擎，如Innodb 或者BDB 等，可以通过控制将整个备份过程在同一个事务中，使用“--single-transaction”选项。示例语句如下。

mysqldump --single-transaction test > test_backup.sql

②数据库中的数据处于静止状态。

通过锁表参数未完成。

●LOCK-TABLES 每次锁定一个数据库的表，此参数默认为true(见上面备份内容实例)。

●LOCK-ALL-TABLES 一次锁定所有的表，适用于dump的表分别处于各个不同的数据库中的情况。

⑵生成特定格式的纯文本文件备份。

①通过SELECT ...TO OUTFILE FROM ...命令。

通过Query将特定数据以指定方式输出到文本文件中，类似于Oracle中的SPOOL功能。

参数说明如下。

●FIELDS ESCAPED BY ['name'] ：在SQL 语句中需要转义的字符；

●FIELDS TERMINATED BY：设定每两个字段之间的分隔符；

●FIELDS [OPTIONALLY] ENCLOSED BY 'name'：包装，有OPTIONALLY数字类型不被包装，否则全包装；

●LINES TERMINATED BY 'name'：行分隔符，即每记录结束时添加的字符。

【范例12-2】

mysql> select * into outfile '/tmp/tb1.txt' -> fields terminated by ',' -> optionally enclosed by '"' -> lines terminated by '\n' --默认 -> from tb1 limit 50; Query OK, 50 rows affected (0.00 sec) [root@gc tmp]# more tb1.txt "information_schema","CHARACTER_SETS","SYSTEM VIEW" "information_schema","COLLATIONS","SYSTEM VIEW" ......

②通过MYSQLDUMP工具命令导出文本。

用此方法可以生成一个文本数据和一个对应的数据库结构创建脚本，主要重要参数如下。

-T,--tab=name Create tab-separated textfile for each table to given path.(Create .sql and .txt files.) NOTE: This only works if mysqldump is run on the same machine as the mysqld server.

技巧 如果没有指定具体备份的数据表，则MySQL默认会导出该数据库的所有表。

【范例12-3】

导出Sqoop库的tb1表。

mysqldump -uroot -proot -T /tmp sqoop tb1 --fields-enclosed-by=\" --fields-terminated-by=,

[root@gc tmp]# ls
tb1.sql tb1.txt


2.逻辑备份的恢复

⑴INSERT 语句文件的恢复。

①使用MySQL命令直接恢复。

把sqoop库的tb1表恢复到test库，语句如下。

mysql -uroot -proot -D test < /tmp/dumpback/sqoop_tb1.sql

②连接上MySQL在命令行中执行恢复。

【范例12-4】

上面的例子同样可以使用下面的方法。

[root@gc～]# mysql -uroot -proot -D test mysql> select database(); +------------+ | database() | +------------+ |test | +------------+ 1 row in set (0.00 sec) mysql> source /tmp/dumpback/sqoop_tb1.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ......

或是如下语句。

mysql> ./tmp/dumpback/sqoop_tb1.sql

⑵纯文本文件的恢复。

①使用LOAD DATA INFILE命令。

此命令是SELECT ...TO OUTFILE FROM反操作，类似于Oracle的Sqlldr工具，其语法如下。]]

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [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,...]]

【范例12-5】

mysql> use sqoop; Database changed mysql> load data infile '/tmp/tb1.txt' into table tb1 -> fields terminated by ',' -> optionally enclosed by '”' -> lines terminated by '\n'; Query OK, 50 rows affected (0.01 sec) Records:50 Deleted:0 Skipped:0 Warnings:0

②使用mysqlimport工具恢复。

此工具可用于恢复上面MYSQLDUMP生成TXT和SQL两文件，所以要保证TXT文件对应的数据库中的表存在。

【范例12-6】

首先恢复表结构，语句如下。

[root@gc～]# mysql -uroot -proot -D test < /tmp/tb1.sql

恢复数据，语句如下。

[root@gc～]# mysqlimport -uroot -proot test --fields-enclosed-by=\" --fields-terminated-by=, /tmp/tb1.txt test.tb1:Records:93 Deleted:0 Skipped:0 Warnings:0