忽然之间的博客

Thoughts, stories and ideas.

mysql 导入导出总结

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 outfileload 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;