mysql导入及导出
- mysql导入及导出
- 导出表内容
- 使用 SELECT INTO OUTFILE 语句导出数据
- 设置导出数据格式
- 注意事项
- 导入表数据
- LOAD DATA INFILE可以将文件读回到数据库
- LOAD DATA INFILE 基本语法
- load data infile 语法说明
- 插入到指定的列
- 导出表作为原始数据
- mysqldump
- 导出表数据
- 导出数据库数据
- 备份所有数据库
- 将数据被备份到远程主机
- 将远程服务器的数据拷贝到本地
- mysqlimport
- 从文件 dumptxt 中将数据导入到 mytbl 数据表中
- mysqlimport的常用选项介绍
- 导入整个数据库
导出表内容
待导出表的内容如下:
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数据库已经恢复到了备份之前的样子。