mysql dump to cvs file

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

mysql dump to cvs file

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

Re: mysql dump to cvs file

Postby lightman47 » 2017/09/16 20:59:51

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

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

Re: mysql dump to cvs file

Postby Blisk » 2017/09/16 21:19:35


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

Re: mysql dump to cvs file

Postby lightman47 » 2017/09/16 21:29:51

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

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

Re: mysql dump to cvs file

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

Re: mysql dump to cvs file

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

Re: mysql dump to cvs file

Postby 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: 20646
Joined: 2009/09/24 10:40:56
Location: Brighton, UK

Re: mysql dump to cvs file

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

Re: mysql dump to cvs file

Postby 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 4405 times

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

Re: mysql dump to cvs file

Postby 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