mysql dump to cvs file

[Blisk]

I just can't figure out how to copy mysql database to a cvs file.
I like to do that with cronjob every 10 minutes. I just need database results not tables.
Can somebody help me with it?

[lightman47]

So, what have you tried that isn't working for you?

[Blisk]

this, but my database require user and password

http://www.tech-recipes.com/rx/1475/sav ... -csv-file/

https://stackoverflow.com/questions/657 ... -using-php

[lightman47]

... and it won't accept your userID & password?

[Blisk]

I tried this
mysqldump -u root -pmypassword FORUM > /home/dump.txt
but I get also tables I need only data.
DROP TABLE IF EXISTS `archive`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `archive` (
`dateTime` int(11) NOT NULL,
`usUnits` int(11) NOT NULL,
`interval` int(11) NOT NULL,

I need only raw data separated with space or commas

[hunter86_bg]

It should be like:

Code: Select all

mysql -u root -ppass -e "SELECT order_id,product_name,qty FROM orders WHERE foo = 'bar' INTO OUTFILE '/var/lib/mysql-files/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';' "

Ofcourse replace everything to match yours. Don't forget that 'google' is your best friend.
P.S.:You can also change the delimiter.

[Blisk]

I googled for that but I don't know what to put for
order_id,
product_name,
FROM orders WHERE
foo = 'bar'

mysql -u root -ppass -e "SELECT order_id,product_name,qty FROM orders WHERE foo = 'bar' INTO OUTFILE '/var/lib/mysql-files/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';' "

[TrevorH]

Amend the query to fit your table column names and table name. You can use * for all columns. If you want the entire table then you don't need a WHERE clause at all.

[Blisk]

Still don't know how to do it.
For example I like to have all of this in one file or many files it doesn't matter, important is only that I get in txt format.

2017-09-18 11_54_42phpMyAdmin 4.4.15.10.png (171.01 KiB) Viewed 5577 times

[TrevorH]

The important bit about the SELECT statement given is

Code: Select all

INTO OUTFILE '/var/lib/mysql-files/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';' "

That tells it to a) write to the file mentioned and b) write it in CSV format. The rest of the select is up to you and uses the same syntax as any other select.