i/o
一、导出(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,col2
from 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_table
fields 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后,如果出来的是目录,把本地文件放到此目录下即可或者指定为空
