Importing and exporting csv files in MySQL

To import/output csv you need some permissions. As root on mysql run the following code:


> grant all privileges
on YOUR_DATABASE.*
to 'YOUR_USER'@'localhost'
identified by 'your_password';
> GRANT FILE ON *.* TO 'YOUR_USER'@'localhost';
> flush privileges;

Exporting CSV from MySQL

For security reasons mysql can only export files to a limited number of directories, this is especially true with Ubuntu which now uses AppArmor. You can change permissions to get around the security restrictions, but it is a major headache. It is much easier to export the file to /tmp and then move it.

To export into the /tmp directory use the following MySQL command. Make sure to replace “database.table_name” with your database and table name.

select * into outfile '/tmp/outfile.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' from database.table_name;

Now move the file from /tmp to your favorite directory:


cp /tmp/outfile.csv /home/kami/data/

Unfortunately there is no way to remove /tmp/outfile.csv without using sudo.

Importing a csv file into MySQL

For security reasons the ability to load files from the local directory is disabled by default. To temporarily override this restriction use the “–local-infile=1” parameter when starting MySQL.


mysql --local-infile=1 -u USERNAME -p DATABASE

Lets say my csv file out.csv looked like the following:

a,b,c
1,2,3
2,2,3
2,3,4

The MySQL table I want to load the file into is called my_table and has columns a,b,c,d. I would use the following MySQL command.


LOAD DATA local INFILE '/home/kami/outfile.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(a,b,c);

This command will load the csv file ‘/home/kami/outfile.csv’ into the columns a, b and c in table my_table. It will also ignore the first row.

Further Reading

  • http://stackoverflow.com/questions/2783313/how-can-i-get-around-mysql-errcode-13-with-select-into-outfile
  • http://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold

MySQL generate mysql create command

MySQL can automatically output the command necessary to create a duplicate table. Very useful for copying tables between servers or for doing small modifications between two tables.


SHOW CREATE table table_name;