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)