How to extract data from mysql to a csv file?

Question

I need to extract some data from a mysql database. I need to extract it to a csv file. It is only certain data I need from the db. Can I use mysqldump?

I need to do it from a customized backend. A button is created and when clicking that button it should extract certain data from the DB in a csv file.

Answer 1

You can use mysql dump or query to extract data to csv file

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

Please note that mysql is the user writing /`tmp/products.csv'. When you use this file, you'll need to copy it into your own working directory, so the file acquires your privileges.

Depending on your version of MySQL, you might not be able to run this command again without first removing the file. The reason for this is you might get an error saying the file already exists. Therefore, you might have to become root to remove the file from '/tmp', before running the command a second time.

Answer 2

You can use:

* mysqldump –tab=/path/for/export

Or try using:

* mysql -h remote_host -e “SELECT * FROM my_schema.my_table” –batch –silent > my_file.csv

Hope this helps!

Source: http://blogs.devart.com/dbforge/export-mysql-table-to-csv-file-methods-overview.html

Answer 3

alternatively use phpMyadmin to export data as CSV

Answer 4

This solution is for non-root users. To extract data from a table on a remote database server:

$ mysql -u user_name -p -e "use database_name; SELECT * FROM table_name" -B | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > file_name.csv

To retrieve the file from the server:

$ scp user_name@server_address:file_name.csv file_name.csv