Mysql 文件导入与导出

一、导出(mysqldump)

<一>、导出sql文件

1、整库

mysqldump -u <username> -p <password> <database_name> > backup.sql

2、整表

mysqldump -u <username> -p <password> <database_name> <table_name> > backup.sql

• 上述代码导出的是sql语句,如insert into

<二>、导出csv文件

1、整表

mysqldump -u <username> -p
--tab = /var/lib/mysql-files/  --导出目录
--fields-terminated-by=,       --字段分隔符
--fields-enclosedby='"'        --字段标识
--lines-terminated-by='\n'     --行分隔符
<database_name> <table_name>

2、自定义sql
mysqldump不支持传入自定义sql导出csv文件,可用mysql -e命令

mysql -u <username> -p -e
"select col1,col2from table into outfile '/path/file_name'fields terminated by ','enclosed by = '"'line terminated by '\n'
"

二、导入(load)

注:是在mysql命令行而不是linux命令行下执行

• load语句
• 注:是在mysql命令行而不是linux命令行下执行

load data infile 'path/file.txt'
into table your_table
fields terminated by ','  
optionally enclosed by '"'
escaped by '\\'
lines terminated by '/n'
ignore 1 rows

• fields terminated by:接字段分隔符
• optionally enclosed by:接字段标识,一般为",表示用""括起的为字段
• escaped by:接转义符
• lines terminated by:接行分隔符
• ignore 1 row:表示忽略前几行,一般为忽略表头

转化为linux命令行

mysql -u <username> -p -e
"load data infile 'path/file.txt'into table your_tablefields terminated by ','    optionally enclosed by '"' escaped by '\\'             lines terminated by '/n'    ignore 1 rows             
"

三、常见报错

The Mysql server is running with the --secure-file-priv option so it cannot execute this statement

show variables like '%secure_file_priv%'

解决方法:执行上述sql后,如果出来的是目录,把本地文件放到此目录下即可或者指定为空