How to extract data line by line from an MySQL database with BASH

Question

I don't quite understand how to extract all the datum from the MySQL my_database database by using BASH. Ideally I would like to extract the data line by line, and print it out into mydata.csv text file line by line [with an IFS].

My difficulty is how to read the lines individually, and have each of the columns separated with an infield separator "," .

I became even more perplexed after do some reading:

Any help is much appreciated!

I've thus far implemented

#!/bin/bash

myvariable=$(echo "SELECT * FROM cs_microrage_crm.stock_trans" | mysql -uUSER -pPASSWORD)

echo $myvariable

This results to ERROR 1045 (28000):

You didn't make clear where your problem lies and show us your effort. Do you have problem interacting with the MySQL server? Do you have trouble forming your queries? — 4ae1e1, Apr 15 '15 at 01:52
@4ae1e1, Currently I have a ERROR 1045 (28000), so I am trying to resolve that... :( — 3kstc, Apr 15 '15 at 01:57
@3kstc Please expand on what you did and how you got that error, and put it into your question. — 4ae1e1, Apr 15 '15 at 01:57
@4ae1e1 I've further added what I tried, there has been different things I've tried, all resulting to an `error 1045` — 3kstc, Apr 15 '15 at 02:18

score 0 · Answer 1 · edited Apr 15 '15 at 02:12

0

You can try this:

#!/bin/bash
dbName=foo;
sourseSet=$(mysql -uroot -pmysql -e "use $dbName; show tables;")
tableSet=$(echo $sourceSet | sed "s/Tables_in_$dbName //g")

for tb in $tableSet
do
    mysql -uroot -pmysql -e "use $dbName;\
    select * into outfile '/tmp/${tb}.csv'"\
    fields terminated by ','\
    lines ternimated by '\n'\
    from $tb"\
done

edited Apr 15 '15 at 02:12

Pang

9,564
146
81
122

answered Apr 15 '15 at 02:09

lucifer-v

1
1

How to extract data line by line from an MySQL database with BASH

1 Answers1