mysql 导入导出总结
2017-03-18 本文已影响500人
codefine
mysql 导入导出方式总结，介绍 mysqldump , source命令，load data infile 3种mysql 导入导出的命令。
使用mysqldump命令
mysqldump 是命令行工具，主要用于mysql备份和还原数据。这个命令使用起来方便，直接在终端执行。
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump 的主要参数：
-h, --host=ip 主机地址
-u, --user=name 登录用户名
-P, --port=# Mysql连接端口
-p, --password[=name] Mysql密码。如果不给定值，直接回车，会提示输入密码
-d, --no-data 不包含行信息，只导出表结构
这些命令都不用刻意去记，执行 mysqldump --help ， 就可以查询使用帮助。
导出整个表(包含数据)
mysqldump -u 用户名 -p [密码] 数据库名 > 导出的文件名
导出表
# 包含数据
mysqldump -u 用户名 -p 数据库名 表名 > 导出的文件名
# 只导出表结构
mysqldump -u 用户名 -d -p 数据库名 表名 > 导出的文件名
导入到远程服务器
使用管道命令，还可以将导出的数据直接导入到远程的服务器上，前提是服务器可以相互访问。
mysqldump -u 用户名 -p 数据库名 | mysql -h 远程服务器HOST 远程数据库名
SQL文件压缩备份&还原
mysqldump -u 用户名 -d -p 数据库名 | gzip > 文件名.sql.gz
gunzip < 文件名.sql.gz | mysql -u 用户名 -p 数据库
示例SQL
create database test;
use test;
create table tb1(
f1 int auto_increment,
f2 varchar(50),
primary key(f1)
)ENGINE= INNODB default charset = utf8;
insert into tb1 values(null, 'a');
insert into tb1 values(null, 'b');
insert into tb1 values(null, 'c');
insert into tb1 values(null, 'd');
create table tb2(
t1 int auto_increment,
t2 varchar(50),
primary key(t1)
)ENGINE= INNODB default charset = utf8;
insert into tb2 values(null, 'a');
insert into tb2 values(null, 'b');
insert into tb2 values(null, 'c');
insert into tb2 values(null, 'd');
select * from tb1;
select * from tb2;
mysqldump -uhomestead -p testdb > test_ddl.sql
mysqldump -uhomestead -p test tb1 > ~/Code/tmp/db1_ddl.sql
mysqldump -uhomestead -p test -d tb1 > ~/Code/tmp/db1_ddl.sql
mysqldump -uhomestead -d -p test | gzip > backup-file.sql.gz
gunzip < backup-file.sql.gz | mysql -uhomestead -p test
使用source 命令
可以在mysql控制台里，加载执行SQL文件。
# 登入Mysql
mysql -u root -p
mysql> set names utf8; (防止乱码，先设置字符集)
mysql> use test;
mysql> source ~/Code/tmp/db1_ddl.sql
使用into outfile 和 load data infile命令
有时候，我们需要根据查询条件导出SQL，可以使用mysql语句：into outfile 和 load data infile。
同source命令， 都需要先登入mysql。
导出
select * from tb1 into outfile '/var/lib/mysql-files/outfile1' fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;
导入
load data infile "/var/lib/mysql-files/outfile1" replace into table tb1 fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;
用这两条命令还是有注意事项：
分隔符参数
fields关键字指定了文件记段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：
- terminated by 分隔符：意思是以什么字符作为分隔符
- enclosed by 字段括起字符
- escaped by 转义字符
- terminated by 描述字段的分隔符，默认情况下是tab字符（ ）
- enclosed by 描述的是字段的括起字符。
- escaped by 描述的转义字符。默认的是反斜杠（backslash： ）
字符集设置
load data infile '/var/lib/mysql-files/outfile1' replace into table tb1 character set utf8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;
目录限制
mysqld 的secure_file_priv配置 ，用来限制LOAD_FILE（）和LOAD DATA和SELECT … ，INTO OUTFILE报表到指定的目录。
导入导出一定是在设定的目录文件，否则会报错：ERROR 1290 (HY000):The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
查看配置
SELECT @@secure_file_priv;
