mysql导入及导出：备份与恢复

mysql导入及导出

导出表内容

待导出表的内容如下：

mysql> select * from user_info;
+---------+-----------+----------------+-----------+
| user_id | user_addr | user_email     | user_name |
+---------+-----------+----------------+-----------+
|       1 | xian      | zzy@taomee.com | zzy       |
|       2 | xian      | ft@qq.com      | fanting   |
|       3 | beijing   | obvious@zz.com | obvious   |
+---------+-----------+----------------+-----------+
4 rows in set (0.00 sec)

使用 SELECT … INTO OUTFILE 语句导出数据

导出表内容：

mysql> select * from user_info  into outfile '/tmp/user_info.txt';
Query OK, 4 rows affected (0.00 sec)

查看表内容：

$ vim /tmp/user_info.txt
 1   xian    zzy@taomee.com  zzy     
 2   xian    ft@qq.com   fanting
 3   beijing obvious@zz.com  obvious
 4   beijing obvious@zzy.com obvious2

设置导出数据格式

导出的表内容十分简陋，可以通过选项来设置数据的格式：

mysql> select * from user_info  into outfile '/tmp/user_info.txt'
     -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
     -> LINES TERMINATED BY '\r\n';
Query OK, 4 rows affected (0.00 sec)

"1","xian","zzy@taomee.com","zzy"
"2","xian","ft@qq.com","fanting"
"3","beijing","obvious@zz.com","obvious"
"4","beijing","obvious@zzy.com","obvious2" 

注意事项

- LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。
- SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
- 输出不能是一个已存在的文件。防止文件数据被篡改。
- 你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
- 在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

导入表数据

LOAD DATA INFILE可以将文件读回到数据库

LOAD DATA INFILE 基本语法：

load data  [low_priority] [local] infile 'file_name txt' [replace | ignore] //there
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'\' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name,   )]

load data infile 语法说明：

• character set gbk 加到there可以防止编码带来的导入不完整的问题
• 如果你指定关键词low_priority，那么MySQL将会等到没有其他人读这个表的时候，才把插入数据
• 如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上

• replace和ignore关键词控制对现有的唯一键记录的重复的处理。

  • replace：新行将代替有相同的唯一键值的现有行。
  • ignore：跳过有唯一键的现有行的重复行的输入。
  • 不指定任何一个选项：当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略。

• fields关键字指定了文件记段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：

  • enclosed by字段括起字符
  • terminated by描述字段的分隔符，默认情况下是tab字符（\t）
  • escaped by描述的转义字符。默认的是反斜杠（backslash：\ ）

插入到指定的列

load data infile 可以把文件导入到数据库中指定的列
load data infile "" into table table_name(field_1, field_2);
如果数据顺序不对，调整field1，field2的位置即可。

导出表作为原始数据

mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。
把zzy数据库下user_info表导出到tmp目录下

$ mysqldump -uroot -p --no-create-info --tab=/tmp zzy user_info    //
$ ls /tmp/
user_info.sql  user_info.txt

一共产生了两个文件，分别以sql和txt结尾。
user_info.txt内容如下：

1       xian    zzy@taomee.com  zzy
2       xian    ft@qq.com       fanting
3       beijing obvious@zz.com  obvious
4       beijing obvious@zzy.com obvious2

user_info.sql为空白

mysqldump

导出表数据

$ mysqldump -u root -p zzy user_info > dump.txt

$ vim dump.txt
... //省略
CREATE TABLE `user_info` (
  `user_id` int(11) NOT NULL,
  `user_addr` varchar(20) DEFAULT NULL,
  `user_email` varchar(20) NOT NULL,
  `user_name` varchar(40) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_info`
--

LOCK TABLES `user_info` WRITE;
/*!40000 ALTER TABLE `user_info` DISABLE KEYS */;
INSERT INTO `user_info` VALUES (1,'xian','zzy@taomee.com','zzy'),...//省略

导出数据库数据

$ mysqldump -uroot -p zzy > zzy_dump.txt

备份所有数据库

$ mysqldump -u root -p --all-databases > database_dump.txt

将数据被备份到远程主机

$ mysqldump -u root -p database_name | mysql -h other-host.com database_name

将远程服务器的数据拷贝到本地

如果你需要将远程服务器的数据拷贝到本地，你也可以在 mysqldump 命令中指定远程服务器的IP、端口及数据库名
在源主机上执行以下命令，将数据备份到 dump.txt 文件中
mysqldump -h other-host.com -P port -u root -p database_name > dump.txt

mysqlimport

从文件 dump.txt 中将数据导入到 mytbl 数据表中

$ mysqlimport -u root -p --local database_name dump.txt

mysqlimport命令可以指定选项来设置指定格式,命令语句格式如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****

mysqlimport 语句中使用 –columns 选项来设置列的顺序：

$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

mysqlimport的常用选项介绍

导入整个数据库

新建一个数据库zzy，然后把备份的zzy数据库的内容导入到该数据库

$ mysql -uroot -p zzy < zzy_dump.txt
$ mysql -uroot -p
Enter password:
MariaDB [(none)]> use zzy;
MariaDB [zzy]> select * from user_info;
+---------+-----------+-----------------+-----------+
| user_id | user_addr | user_email      | user_name |
+---------+-----------+-----------------+-----------+
|       1 | xian      | zzy@taomee.com  | zzy       |
|       2 | xian      | ft@qq.com       | fanting   |
|       3 | beijing   | obvious@zz.com  | obvious   |
|       4 | beijing   | obvious@zzy.com | obvious2  |
+---------+-----------+-----------------+-----------+
4 rows in set (0.01 sec)

zzy数据库已经恢复到了备份之前的样子。