Export tables as csv from MySQL

MySQL consists of some databases including tables. Today, I will explain how to output specific tables as csv from MySQL database.

First, connect to your server and log in MySQL via SSH. In this case, MySQL username is “root” and the password is required.

(I’m Windows user, so use RLogin software.)

$ mysql -u root -p

After logging in, select the database to type use command. If you want to know about the detail of databases, you should use show databases; command.

mysql> show databases;
mysql> use test_db;

Check tables included in this database, and the details of a specific table you want to export. Deciding the table to output, you type this command.

mysql> show tables;
mysql> desc users;
mysql> select * from users
       into outfile "/var/lib/mysql-files/users.csv"
       fields terminated by ',';

If system shows this error message, you should check secure_file_priv variable because system allows you to export to specific directory.

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> show variables like "secure_file_priv";

You can export to the directory specified in secure_file_priv, but you should not forget that mysqluser owns this directory.

If you change this directory’s owner with chown command, system shows this error message.

ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql-files/users.csv' (Errcode: 13 - Permission denied)

Comments