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

           

 

 

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

　　　　$ 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(*) from seq_test;
+----------+
| count(*) |
+----------+
|      111 |
+----------+
1 row in set (0.00 sec)
 
mysql> truncate table seq_test;
Query OK, 0 rows affected (0.00 sec)
 
mysql>  LOAD DATA LOCAL INFILE '/tmp/backup_v0.txt' INTO TABLE seq_test;
Query OK, 111 rows affected (0.00 sec)
Records: 111  Deleted: 0  Skipped: 0  Warnings: 0
 
mysql> select count(*) from seq_test;
+----------+
| count(*) |
+----------+
|      111 |
+----------+
1 row in set (0.00 sec)
 

 

 

 　　

 

       

    还可以使用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:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.71 Source distribution
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use runoob;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> source backup_data.sql
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 111 rows affected (0.00 sec)
Records: 111  Duplicates: 0  Warnings: 0
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from seq_test;
+----------+
| count(*) |
+----------+
|      111 |
+----------+
1 row in set (0.00 sec)