头像如何上传到mysql_MySQL->导出/导入资料[20180521]

MySQL 导出

INTO OUTFILE将资料导出至文件中

mysqldump工具导出资料和数据结构，并且可以针对数据库、数据表、索引的结构。

INTO OUTFILE测试

select * from seq_test into outfile '/tmp/backup_v0.txt';

生成一个文件，各值用逗号隔开

select *  into outfile '/tmp/backup_v1.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n' from seq_test;

select * from seq_test into outfile '/tmp/backup_v2.txt' fields terminated by ',' enclosed by '"' lines terminated by '\r\n' ;

 

mysqldump工具导出原始资料

mysqldump -u root -p runoob seq_test|gzip > backup_data.zip

也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

$ mysqldump -u root -p database_name \

| mysql -h other-host.com database_name

资料导入

针对使用INTO OUTFILE方式导出的资料，可以使用LOAD DATA LOCAL INFILE方式导入资料。

LOAD DATA LOCAL INFILE '/tmp/backup_v0.txt' INTO TABLE seq_test;

mysql> select count(*) fromseq_test;+----------+

| count(*) |

+----------+

| 111 |

+----------+

1 row in set (0.00sec)

mysql> truncate tableseq_test;

Query OK,0 rows affected (0.00sec)

mysql> LOAD DATA LOCAL INFILE '/tmp/backup_v0.txt' INTO TABLEseq_test;

Query OK,111 rows affected (0.00sec)

Records:111 Deleted: 0 Skipped: 0 Warnings: 0mysql> select count(*) fromseq_test;+----------+

| count(*) |

+----------+

| 111 |

+----------+

1 row in set (0.00sec)

还可以使用mysqlimport工具导入

[root@t-xi-mysql01 tmp]# cp backup_v0.txt seq_test.txt

[root@t-xi-mysql01 tmp]#  mysqlimport -u root -p --local runoob seq_test.txt

Enter password:

runoob.seq_test: Records: 111  Deleted: 0  Skipped: 111  Warnings: 0

将mysqldump导出的资料进行导入

gunzip  -c backup_data.zip>backup_data.sql

mysql -u root -p

mysql> source backup_data.sql

[root@t-xi-mysql01 tmp]# mysqldump -u root -p runoob seq_test|gzip >backup_data.zip

Enter password:[root@t-xi-mysql01 tmp]# gunzip -c backup_data.zip>backup_data.sql[root@t-xi-mysql01 tmp]#[root@t-xi-mysql01 tmp]# mysql -u root -p

Enter password:

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 18Server version:5.1.71Source distribution

Copyright (c)2000, 2013, Oracle and/or its affiliates. Allrights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql> userunoob;

Readingtable information for completion of table and columnnames

You can turnoff this feature to get a quicker startup with -ADatabasechanged

mysql>source backup_data.sql

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.01sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,111 rows affected (0.00sec)

Records:111 Duplicates: 0 Warnings: 0Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,0 rows affected (0.00sec)

mysql> select count(*) fromseq_test;+----------+

| count(*) |

+----------+

| 111 |

+----------+

1 row in set (0.00 sec)