mysql数据的导入导出

2017-12-15
 
 
 

一. mysqldump工具基本用法,不适用于大数据备份

 
1. 备份所有数据库: mysqldump -u root -p --all-databases > all_database_sql
2. 备份mysql数据库:mysqldump -u root -p --databases mysql > mysql_database_sql
3. 备份指定的多个数据库:mysqldump -u root -p --databases db1 db2 db3 > bak.sql
4. 备份mysql数据库下的user表:mysqldump -u root -p mysql user > user_table
 
如果不用--databases选项,在后期进行数据还原操作时,如果该数据库不存在,必须先创建该数据库;而在例子3指定多个数据库时,必须要加--databases参数,否则db2会被认为是db1库的表。
 
把备份的所有数据文件还原:
mysql -u root -p < all_database_sql,这里不需要指定库,因为是全部数据库
mysql -u root -p mysql < mysql_database_sql #这里就需要指定是mysql库了
 
备份时不影响业务运行的命令
mysqldump -ubackup -pxxx -P13306 -h10.5.86.10 --lock-tables=false --single-transaction=TRUE --all-databases > ${sql_dir}/full-$(date +%F).sql
 
从windows本地导入sql文件,这里的extend.sql导出的时候使用了参数databases 
mysql -uroot -pxxxx -h192.168.2.4 < C:\Users\Naruto\Desktop\extend.sql
 

二. 导出纯文本数据

在某些情况下,为了一些特定的目的,经常需要将表里的数据导出为某些符号分割的纯数据文本,而不是 SQL 语句,因为LOAD DATA 的加载速度比普通的 SQL 加载要快 20 倍以上

 

方法 1:使用 SELECT ...INTO OUTFILE ...命令来导出数据,具体语法如下。

mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option];

其中 option 参数可以是以下选项:

命令参数 说明
fields terminated by '字符' 字段分隔符,默认字符为制表符'\t'
fields [optionally] enclosed by '单字符' 字段引用符,加上optionally后在数字类型上不会有引用符号
fields escaped by '单字符' 转义字符,默认为'\'
lines starting by '字符' 每行前都加此支付,默认为空
lines terminated by '字符' 行结束符,默认为'\n'

 

 

例子1,将 emp 表中数据导出为数据文本,其中,字段分隔符为“,”,每个字段用双引号引用起来,记录结束符为回车符(默认如此,可以不写)

mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," enclosed by '"';

输出结果如下

mysql> system more /tmp/emp.txt
"1","z1","aa"
"2","z1","aa"
"3","z1","aa"

 

 

例子2,发现例1中第一列是数值型,如果不希望字段两边用引号,则语句改为如下

mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by ","  optionally enclosed by '"' ;

 结果输出如下
mysql> system more /tmp/emp.txt
1,"z1","aa"
2,"z1","aa"
3,"z1","aa"

 

 

下面测试一下转义字符,大概包括三类,转义字符本身,字段分隔符(导出的文本中用什么符号分隔),记录分隔符(每条记录之间用什么分隔,默认是回车)

例子3,更改上面例子中id=1的name为\"##!aa

update employee  set name ='\\"##!aa' where id=1;   #更新操作中转义字符本身也需要用转义字符来转义,所以这里有2个\\
# 然后做导出操作
select * from employee into outfile '/tmp/employee' fields terminated by "," optionally enclosed by ‘”’;

导出结果如下
mysql> system more /tmp/emp.txt
1,"\\\"##!aa","aa"
2,"z1","aa"
3,"z1","aa"

说明:name 中含有转义字符本身“\”,域引用符“”“,因此,在输出的数据中我们发现这两种字符前面都加上了转义字符“\”

 

例子4,将id=1的name更新为含有字段分隔符”,“的字符串

update employee set name='\\"#,#,!aa' where id=1;

然后导出
mysql> system rm /tmp/emp.txt  #需要删掉重名文件

mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"' ;

输出结果如下
mysql> system more /tmp/emp.txt
1,"\\\"#,#,!aa","aa"
2,"z1","aa"
3,"z1","aa"

说明:发现数据中的字符","没有被转义,原因是它和后面真正的字段分隔符之间没有冲突,因为name字段包含在双引号之间。

 

例子5:继续做测试,将输出文件的字段引用符去掉,这个时候,我们的预期是数据中的“,”将成为转义字符而需要加上“\”

mysql> system rm /tmp/emp.txt
mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," ;

输出结果如下

mysql> system more /tmp/emp.txt
1,\\"#\,#\,!aa,aa
2,z1,aa
3,z1,aa

说明:果然,现在的“,”前面加上了转义字符“\”。而刚才的引用符“””却没有被转义,因为它已经没有什么歧义,不需要被转义

 

注意:SELECT…INTO OUTFILE...产生的输出文件如果在目标目录下有重名文件,将不会创建成功,源文件不能被自动覆盖

 

 

方法 2:用 mysqldump 导出数据为文本。

mysqldump –u username –T target_dir dbname tablename [option]

其中 option 参数可以是以下选项:

1) --fields-terminated-by=name(字段分隔符);
2) --fields-enclosed-by=name  字段引用符,比如每个字段用双引号括起来;
3) --fields-optionally-enclosed-by=name(字段引用符,只用在 char、varchar 和 text 等字符型字段上
4) --fields-escaped-by=name(转义字符);
5) --lines-terminated-by=name(记录结束符)。

 

常用参数
 --compact :使得输出结果简洁,不包括默认选项中的各种注释,例如例中对 test 数据库中的表 emp 进行简洁导出 mysqldump -uroot -p  --compact test emp >a

 -F --flush-logs(备份前刷新日志):加上此选项后,备份前将关闭旧日志,生成新日志。使得进行恢复的时候直接从新日志开始进行重做,大大方便了恢复过程

 -l --lock-tables(给所有表加读锁):可以在备份期间使用,使得数据无法被更新,从而使备份的数据保持一致性,可以配合-F选项一起使用。

 

注意:

MyISAM 存储引擎在备份的时候需要加上-l 参数,表示将所有表加上读锁,在备份期间,所有表将只能读而不能进行数据更新。

但是对于事务存储引擎(InnoDB 和 BDB)来说,可以采用更好的选项--single-transaction,此选项将使得 InnoDB 存储引擎得到一个快照(Snapshot),使得备份的数据能够保证一致性

 

例子,采用 mysqldump 生成指定分隔符分隔的文本:

mysqldump -uroot -T /tmp test emp --fields-terminated-by ',' --fields-optionally-enclosed-by '"'

输出结果如下

more /tmp/emp.txt

1,"\\\"#,#,!aa","aa"
2,"z1","aa"
3,"z1","aa"

 

注意:

1)ubuntu测试中会出错,解决方法如下。将输出目录改为/var/lib/mysql-files/. 例子中test是库名,emp是表名,如果不写则备份全部表

mysql> mysql> select * from person into outfile '/tmp/x.txt' fields terminated by ",";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show global variables like "%secure%";
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+

 

2)如果非要用/tmp,则需要做如下操作 (测试了下又不行了...)

在/etc/apparmor.d/usr.sbin.mysqld文件中加入一行代码如下
/data/export/** rw, 保存退出后
/etc/init.d/apparmor reload  #重新载入配置 
再次导出即可。

 

 

 

 

三. 导入纯文本数据

这里只讨论用 SELECT… INTO OUTFILE 或者 mysqldump 导出的纯数据文本的导入方法。和导出类似,导入也有两种不同的方法,分别是 LOAD DATA INFILE…和mysqlimport,它们的本质是一样的,区别只是在于一个在 MySQL 内部执行,另一个在 MySQL 外部执行。

 

需要注意的是在导入纯文本数据之前,需要事先创建好表结构

1. 一个简单的完整例子

首先准备表结构和文本数据,新建test库:create database test,然后在test下新建表person

create table person(  
id int not null auto_increment,  
name varchar(40) not null,  
city varchar(20),  
salary int,  
primary key(id)  
)
engine=innodb default charset=utf8; 

 

张三  北京  3000 
李四  杭州  4000 
王五  /N  4500 
小明  天津  /N

每一项之间用Tab键进行分隔,如果该字段为NULL,则用/N表示。

 

进入mysql并且切换到对应的库后,执行下面命令导入数据:

load data local infile  "/var/lib/mysql/test/data.txt"  into table person(name,city,salary); 

 

注意

1. data.txt存放的位置,其他地方会报错

2. 在mysql配置文件中修改字符编码

mysql 5.6
sudo vim /etc/mysql/my.cnf,重启mysql来完成字符集的设置。添加以下两行 (如果是上传安装包,vim /etc/my.cnf)
[mysqld]
character_set_server=utf8
binlog_format=row

mysql 5.7 可能需要在etc/mysql/mysql.conf.d/mysqld.cnf中额外增加如下代码,有时候也不需要
[client]
default-character-set=utf8

 

 

2. 使用“LOAD DATA INFILE…”命令导入数据详解

mysql > LOAD DATA [LOCAL] INFILE ‘filename’ INTO TABLE tablename [option]

option 可以是以下选项:
1. FIELDS TERMINATED BY 'string'(字段分隔符,默认为制表符'\t')
2. FIELDS [OPTIONALLY] ENCLOSED BY 'char'   字段引用符,如果加 OPTIONALLY 选项则只会做用在char, varchar和text等字符型字段上,其他类型字段默认不使用引用符
3. FIELDS ESCAPED BY 'char'(转义字符,默认为'\');
4. LINES STARTING BY 'string'(每行前都加此字符串,默认'');
5. LINES TERMINATED BY 'string'(行结束符,默认为'\n');
6. IGNORE number LINES(忽略输入文件中的前 n 行数据);
7. (col_name_or_user_var,...) (按照列出的字段顺序和字段数量加载数据);
8. SET col_name = expr,... 将列做一定的数值转换后再加载。

 

注意:
1. 其中 char 表示此符号只能是单个字符,string 表示可以是字符串。
2. FILELD 和 LINES 和前面 SELECT …INTO OUTFILE…的含义完全相同,不同的是多了几个不同的选项

 

例子1:将文件“/tmp/emp.txt”中的数据加载到表 emp 中

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by'"' ;

 

例子2:如果不希望加载文件中的前2行,加上ignore字段

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ','  enclosed by '"'  ignore 2 lines;

 

例子3:如果发现文件中的列顺序和表中的列顺序不符,或者只想加载部分列,可以在命令行中加上列的顺序

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id,content,name);

如果只想加载第一列,字段的列表里面可以只加第一列的名称:

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"'  ignore 2 lines (id);

 

例子4:如果希望将 id 列的内容+10 后再加载到表中,可以如下操作:

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ','  enclosed by '"'  set id=id+10;

 

 

3. 用 mysqlimport 来实现导入数据详解,具体命令如下


shell>mysqlimport –u root –p*** [--LOCAL] dbname order_tab.txt [option]
其中 option 参数可以是以下选项:
1.  --fields-terminated-by=name(字段分隔符);
2.  --fields-enclosed-by=name(字段引用符);
3.  --fields-optionally-enclosed-by=name(字段引用符,只用在 char、varchar 和 text 等字符型字段上
4.  --fields-escaped-by=name(转义字符);
5.  --lines-terminated-by=name(记录结束符);
6.  -- ignore-lines=number(或略前几行)。


这与 mysqldump 的选项几乎完全相同,这里不再详细介绍,简单来看一个例子:

mysqlimport -uroot test /tmp/emp.txt --fields-terminated-by=',' --fields-enclosed-by='"'

 

 

 

posted @ 2017-12-15 10:34  坚强的小蚂蚁  阅读(42905)  评论(0编辑  收藏  举报