mysql dump to cvs file

Issues related to applications and software problems
Blisk
Posts: 236
Joined: 2011/07/04 14:49:51
Contact:

mysql dump to cvs file

Post by Blisk » 2017/09/16 19:34:13

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
Posts: 662
Joined: 2014/05/21 20:16:00
Location: Central New York, USA

Re: mysql dump to cvs file

Post by lightman47 » 2017/09/16 20:59:51

So, what have you tried that isn't working for you?
"Please solve your problems in advance so we can help you more"
- unknown


lightman47
Posts: 662
Joined: 2014/05/21 20:16:00
Location: Central New York, USA

Re: mysql dump to cvs file

Post by lightman47 » 2017/09/16 21:29:51

... and it won't accept your userID & password?
"Please solve your problems in advance so we can help you more"
- unknown

Blisk
Posts: 236
Joined: 2011/07/04 14:49:51
Contact:

Re: mysql dump to cvs file

Post by Blisk » 2017/09/16 22:07:05

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
Posts: 1163
Joined: 2015/02/17 15:14:33
Location: Bulgaria
Contact:

Re: mysql dump to cvs file

Post by hunter86_bg » 2017/09/17 05:27:53

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
Posts: 236
Joined: 2011/07/04 14:49:51
Contact:

Re: mysql dump to cvs file

Post by Blisk » 2017/09/18 09:15:43

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';' "

User avatar
TrevorH
Forum Moderator
Posts: 22988
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: mysql dump to cvs file

Post by TrevorH » 2017/09/18 09:33:50

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.
CentOS 5 died in March 2017 - migrate NOW!
Full time Geek, part time moderator. Use the FAQ Luke

Blisk
Posts: 236
Joined: 2011/07/04 14:49:51
Contact:

Re: mysql dump to cvs file

Post by Blisk » 2017/09/18 09:57:10

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
2017-09-18 11_54_42phpMyAdmin 4.4.15.10.png (171.01 KiB) Viewed 4677 times

User avatar
TrevorH
Forum Moderator
Posts: 22988
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: mysql dump to cvs file

Post by TrevorH » 2017/09/18 10:03:30

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.
CentOS 5 died in March 2017 - migrate NOW!
Full time Geek, part time moderator. Use the FAQ Luke

Post Reply