MySQL Query “SELECT…” Save as CSV

MySQL select and save as csv file

We want to get some posts from table, and export the result into csv file [/tmp/posts.csv]. Here’s the query

SELECT
  a.* 
FROM 
  sc_posts a 
  LEFT JOIN sc_term_relationships b on a.ID = b.object_id 
WHERE 
  b.term_taxonomy_id = 66 
  INTO OUTFILE '/tmp/posts.csv' 
  FIELDS TERMINATED BY ',' 
  ENCLOSED BY '"' 
  LINES TERMINATED BY '\n';

An error occurs:

Access denied for user 'mysqluser'@'localhost' (using password: YES)

It seems that We’re having issue with the database privileges and writing permission to the folder mentioned. So We might execute this command first. Just remind, that these actions will require mysql root access.

GRANT ALL PRIVILEGES 
  ON YOUR_DATABASE.* 
  TO 'mysqluser'@'localhost' 
  IDENTIFIED BY 'mysqlpassword';

Then

FLUSH PRIVILEGES;

Also, make sure we have granted FILE to user ‘mysqluser’@’localhost’.

GRANT FILE ON *.* TO 'mysqluser'@'localhost';

What’s the default password of mariadb on CentOS?

In order to log into MariaDB to secure it, we’ll need the current password for the root user. If you’ve just installed MariaDB, and you haven’t set the root password yet, the password will be blank, so you should just press enter here.

sudo mysql -u root

–secure-file-priv

If an error

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

occurs, that’s because MySQL doesn’t have permission to write /tmp directory. So¬†We may use

SHOW VARIABLES LIKE "secure_file_priv";

to see the directory where MySQL can write.

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.05 sec)

So simply change the query to

SELECT
  a.* 
FROM 
  sc_posts a 
  LEFT JOIN sc_term_relationships b on a.ID = b.object_id 
WHERE 
  b.term_taxonomy_id = 66 
  INTO OUTFILE '/var/lib/mysql-files/posts.csv' 
  FIELDS TERMINATED BY ',' 
  ENCLOSED BY '"' 
  LINES TERMINATED BY '\n';

Done, posts.csv is stored in /var/lib/mysql-files directory.

Leave a Reply