mysql的导入和导出

mysql的导出

1.使用 SELECT … INTO OUTFILE 语句导出数据

语法：

select * from table into outfile '/路径/文件名'
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'

数据库中存了表aaa：

使用select into outfile 将它导出:

SELECT * FROM aaa INTO OUTFILE '/app/mysql/aaa.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

查看导出文件aaa.txt：aaa.txt

FIELDS TERMINATED BY ‘,’：给数据间加了逗号。
ENCLOSED BY ‘"’：将数据用引号包裹起来。
LINES TERMINATED BY ‘\r\n’：以空格加换行终结一行。

2.使用 mysqldump 导出数据

mysqldump产生两种类型的输出，具体取决于是否指定了该–tab 选项：

没有–tab， mysqldump将SQL语句写入标准输出。
使用–tab， mysqldump为每个转储的表产生两个输出文件。

1）导出表作为原始数据

mysqldump -uroot -p --tab=/app/mysql repl aaa

产生两个文件，表结构和数据（文件名和table名称相同）：

-h, --host=name：服务器IP
-u, --user=name：登录名
-p, --password[=name]:登录密码
-A, --all-databases：导出所有数据库
-B, --databases：导出指定的数据库，多个数据库名使用空格分割
–tables：导出指定表
-d, --no-data：仅导出表结构，不导出数据
-t, --no-create-info：不导出表结构，只导出数据
不知道什么用//-n, --no-create-db：不导出CREATE DATABASE IF EXISTS语句
-e, --extended-insert：将多条记录合并成一条INSERT语句来提高插入效率
–add-drop-table：在创建表之前加入DROP TABLE语句
–hex-blob ：将二进制的数据以16进制导出
-R, --routines：导出存储过程和存储函数
–triggers：导出触发器
–master-data[=#]：导出CHANGE MASTER命令，当设置为1时，CHANGE命令正常导出，当设置为2时，CHANGE命令以注释模式导出master-data开启时，会默认启用–lock-all-tables选项，并自动禁用–lock-tables选项
–dump-slave：在从库上执行时，dump-slave用来导出当前主库上的位置信息
–single-transaction：单实例模式运行
–lock-all-tables，-x：在导出前对所有表加全局只读锁，并自动关闭–single-transaction 和 --lock-tables 选项
–lock-tables ：在导出当前表数据前才对表进行加锁，该选项指使用与MyISAM表。–lock-tables无法保证所有表数据在数据库级别一致。
–default-character-set=charset：设置导出时使用的字符集
–quick，-q ：在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

为了使数据文件可以使用其他格式写入， mysqldump支持以下选项：
–fields-terminated-by=str
用于分隔列值的字符串（默认值：制表符）。

–fields-enclosed-by=char
包含列值的字符（默认值：无字符）。

–fields-optionally-enclosed-by=char
包含非数字列值的字符（默认值：无字符）。

–fields-escaped-by=char
用于转义特殊字符的字符（默认值：不转义）。

–lines-terminated-by=str
行终止字符串（默认值：换行符）。

例如，要转储以逗号分隔的值格式的表，并以回车/换行符对（\r\n）终止，请使用以下命令（在单行中输入）

mysqldump --tab=/app/mysql --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a -uroot -p repl users

2）导出 SQL 格式的数据

mysqldump -u root -p repl aaa > dump.csv

导出整个数据库数据：

mysqldump -u root -p repl > dump.csv

导出所有数据库：

mysqldump -u root -p --all-database > dump.csv

也可以使用以下命令将导出的数据直接导入到远程的服务器上:

mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

(权限不足不知道咋处理）

mysql的导入

mysql命令：

mysql -uroot -p < repl.sql

上面命令将数据库repl导入

source导入：

在数据库终端导入：

mysql> source /app/mysql/aaa.sql

使用load data导入：

LOAD DATA LOCAL INFILE '/app/mysql/aaa.txt' INTO TABLE aaa;

指定列的顺序：

LOAD DATA LOCAL INFILE '/app/mysql/aaa.txt' INTO TABLE aaa(b,a);

若数据中穿插符号：

使用：

LOAD DATA LOCAL INFILE 'aaa.txt' INTO TABLE aaa
  -> FIELDS TERMINATED BY ','
  -> ENCLOSED BY '"' 
  -> LINES TERMINATED BY '\r\n';

这是SELECT … INTO OUTFILE的逆操作。指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

使用 mysqlimport 导入

mysqlimport -u root -p --local aaa aaa.txt

mysqlimport支持下面的选项：

· —help，-？

显示帮助消息并退出。

· --columns=column_list, -c column_list

该选项采用用逗号分隔的列名作为其值。列名的顺序指示如何匹配数据文件列和表列。

· --compress，-C

压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）。

· —debug[=debug_options]，-# [debug_options]

写调试日志。debug_options字符串通常是’d:t:o,file_name’。

· --delete，-D

导入文本文件前清空表。

· --fields-terminated-by=…，–fields-enclosed-by=…，–fields-optionally-enclosed-by=…，–fields-escaped-by=…，–lines-terminated-by=…

这些选项与LOAD DATA INFILE相应子句的含义相同。参见13.2.5节，“LOAD DATA INFILE语法”。

· --force，-f

不管是否遇到错误，mysqlimport将强制继续插入数据。

· --host=host_name，-h host_name

将数据导入给定主机上的MySQL服务器。默认主机是localhost。

· --ignore，-i

mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。

· --ignore-lines=n

忽视数据文件的前n行。

· --local，-L

从本地客户端读入输入文件。

· --lock-tables，-l

处理文本文件前锁定所有表以便写入。这样可以确保所有表在服务器上保持同步。

· --password[=password]，-p[password]

当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和 密码之间不能有空格。如果在命令行中–password或-p选项后面没有 密码值，则提示输入一个密码。

· --port=port_num，-P port_num

用于连接的TCP/IP端口号。

· --protocol={TCP | SOCKET | PIPE | MEMORY}

使用的连接协议。

· --replace，-r

这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。

· --silent，-s

沉默模式。只有出现错误时才输出。

· --socket=path，-S path

当连接localhost时使用的套接字文件(为默认主机)。

· --user=user_name，-u user_name

当连接服务器时MySQL使用的用户名。

· --verbose，-v

冗长模式。打印出程序操作的详细信息。

· --version，-V

显示版本信息并退出。

如果在最初转储表时对mysqldump使用了任何数据格式化选项 ，则必须对mysqlimport使用相同的选项， 或者LOAD DATA：

mysqlimport --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a -uroot -p repl /app/mysql/users.txt

或者：

mysql> LOAD DATA INFILE 'users.txt' INTO TABLE users
       FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'
       LINES TERMINATED BY '\r\n';