2

How do I export from MYSQL to CSV in mariadb on Centos 7?

I am using:

Server version      5.5.37-MariaDB
Protocol version    10
Connection          Localhost via UNIX socket
UNIX socket         /var/lib/mysql/mysql.sock

on CentOS Linux release 7.0.1406 (Core)

I tried the following:

    mysql> SELECT *
        -> INTO OUTFILE 'cd /var/foo.csv'
        -> FIELDS TERMINATED BY ','
        -> ENCLOSED BY '"'
        -> ESCAPED BY '\\'
        -> LINES TERMINATED BY '\r\n'
        -> FROM foo;
    Query OK, 4079 ROWS affected (0.02 sec)

Where foo is the database name? But it gives me a syntax error. Do I not have to select a particular table?

Then I tried this :

mysql -u root -p enterpass nameofdb -B -e “select * from \`person\`;” | sed     ‘s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ > nameofdb.csv

Same again with syntax error.

I am practicing this so I can see if I could manually migrate one kind of database store into another. A zen cart db to an open cart one without having to use some paid product like cart2cart and mainly so I can see the guts of the stores to better understand.

  • which sytax error on first example ? your filename is cd /var/foo.csv or just /var/foo.csv ? (without cd) – Archemar Nov 5 '14 at 10:05
  • thanks, will try without cd and for second one it says "unknown command: `?'". – cea Nov 5 '14 at 12:16
  • made no difference, still saying no database selected – cea Nov 6 '14 at 7:52
  • select syntax will export a table not a database. have you tried with a single table ? (or is foo you table, not your database ? ). – Archemar Nov 6 '14 at 7:57
  • On second tough, migrating from one database to another is usually done by mysqldump > foo.sql old database/engine ; mysql < foo.sql new database/engine. – Archemar Nov 6 '14 at 7:59
2

Seeing no-one else is answering I will but to be fair Archemar worked it out before me. I knew I needed to select a table but was unsure of syntax and didn't consider making sure folder apache owned.

To export mysql database table to CSV file you log in to admin then use following commands.

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products;

It then left three files like this "systemd-private-SjP6B1" , one of which had tmp folder and within that products.csv.

I could open the products.csv in google docs or excel if I had it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.