MySQL Importing and Exporting Data

Table data

mysql> select * from todo;
+---------------------+-------------------+--------+----+
| t                   | content           | status | id |
+---------------------+-------------------+--------+----+
| 2010-02-20 14:11:32 | what is your name | open   |  1 | 
| 2010-02-20 14:10:30 | go to work man    | closed |  2 | 
| 2010-08-15 23:12:35 | lovely            | open   |  6 | 
| 2010-08-15 23:12:59 | lovely            | open   |  7 | 
+---------------------+-------------------+--------+----+
4 rows in set (0.00 sec)

make the /tmp writable

root@sophal-desktop:/home/sophal# chown root:root /tmp
root@sophal-desktop:/home/sophal# chmod 777 /tmp

exporting data to file

Make sure the /tmp directory is writable, if it is not writable you will get an error.

mysql> SELECT * INTO OUTFILE "/tmp/my_todo_list2.sql" FROM todo;
Query OK, 4 rows affected (0.00 sec)

list the /tmp/my_todo_list2.sql file

root@sophal-desktop:/tmp# cat my_todo_list2.sql
2010-02-20 14:11:32	what is your name	open	1
2010-02-20 14:10:30	go to work man	closed	2
2010-08-15 23:12:35	lovely	open	6
2010-08-15 23:12:59	lovely	open	7

load data infile from a remote server

You can use scp command to copy or transfer file from the remote server to your local host. Learning scp commend for file transfer

LOAD DATA INFILE Statement

• first empty the table with truncate
• load data from file

mysql> truncate table todo;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from todo;
Empty set (0.00 sec)

mysql> LOAD DATA LOCAL INFILE '/tmp/my_todo_list2.sql' INTO TABLE todo;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from todo;
+---------------------+-------------------+--------+----+
| t                   | content           | status | id |
+---------------------+-------------------+--------+----+
| 2010-02-20 14:11:32 | what is your name | open   |  1 | 
| 2010-02-20 14:10:30 | go to work man    | closed |  2 | 
| 2010-08-15 23:12:35 | lovely            | open   |  6 | 
| 2010-08-15 23:12:59 | lovely            | open   |  7 | 
+---------------------+-------------------+--------+----+
4 rows in set (0.00 sec)

/tmp/new_todo_list.sql file context

10,fishing and drinking,open,2012-03-02 10:30:30
20,reading and watching tv,open,2012-03-04 10:30:30
30,eating and watching tv,closed,2012-03-18 10:20:30

Load data local file with field and lines terminate

mysql> LOAD DATA LOCAL INFILE '/tmp/new_todo_list.sql'
    -> INTO TABLE todo
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> (id,content,status,t)
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Check result

mysql> SELECT * FROM todo;
+---------------------+-------------------------+--------+----+
| t                   | content                 | status | id |
+---------------------+-------------------------+--------+----+
| 2010-02-20 14:11:32 | what is your name       | open   |  1 | 
| 2010-02-20 14:10:30 | go to work man          | closed |  2 | 
| 2010-08-15 23:12:35 | lovely                  | open   |  6 | 
| 2010-08-15 23:12:59 | lovely                  | open   |  7 | 
| 2012-03-02 10:30:30 | fishing and drinking    | open   | 10 | 
| 2012-03-04 10:30:30 | reading and watching tv | open   | 20 | 
| 2012-03-18 10:20:30 | eating and watching tv  | closed | 30 | 
+---------------------+-------------------------+--------+----+
7 rows in set (0.00 sec)

/tmp/new_todo_list.sql

10,fishing and drinking,open,2012-03-02 10:30:30
20,reading and watching tv,open,2012-03-04 10:30:30
30,eating and watching tv,closed,2012-03-18 10:20:30
40,working and assignment,closed,2012-03-11 10:30:00

load data local infile ignore lines

We are only inserted the last line, and ignore the first 3 lines.

mysql> LOAD DATA LOCAL INFILE '/tmp/new_todo_list.sql'
    -> INTO TABLE todo
    -> fields terminated by ','
    -> lines terminated by '\n'
    -> ignore 3 lines
    -> (id,content,status,t)
    -> ;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Result

mysql> SELECT * FROM todo;
+---------------------+-------------------------+--------+----+
| t                   | content                 | status | id |
+---------------------+-------------------------+--------+----+
| 2010-02-20 14:11:32 | what is your name       | open   |  1 | 
| 2010-02-20 14:10:30 | go to work man          | closed |  2 | 
| 2010-08-15 23:12:35 | lovely                  | open   |  6 | 
| 2010-08-15 23:12:59 | lovely                  | open   |  7 | 
| 2012-03-02 10:30:30 | fishing and drinking    | open   | 10 | 
| 2012-03-04 10:30:30 | reading and watching tv | open   | 20 | 
| 2012-03-18 10:20:30 | eating and watching tv  | closed | 30 | 
| 2012-03-11 10:30:00 | working and assignment  | closed | 40 | 
+---------------------+-------------------------+--------+----+
8 rows in set (0.00 sec)

select into outfile fields terminated

mysql> SELECT * INTO OUTFILE  '/tmp/my_todo_10.sql'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM todo;
Query OK, 8 rows affected (0.01 sec)

result of the /tmp/my_todo_10.sql file content

"2010-02-20 14:11:32","what is your name","open","1"
"2010-02-20 14:10:30","go to work man","closed","2"
"2010-08-15 23:12:35","lovely","open","6"
"2010-08-15 23:12:59","lovely","open","7"
"2012-03-02 10:30:30","fishing and drinking","open","10"
"2012-03-04 10:30:30","reading and watching tv","open","20"
"2012-03-18 10:20:30","eating and watching tv","closed","30"
"2012-03-11 10:30:00","working and assignment","closed","40"

Load data infile fields terminated

LOAD DATA INFILE '/tmp/my_todo_10.sql' INTO TABLE todo
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';