CODEWITHSUNDEEP
mysqlshell
Rayne
Rayne

Reputation: 14977

Running mysql command in a while loop

I have a list of mysql databases, and I wish to export a couple of tables from databases whose name ends in "_abc" using a linux script. However, I keep getting the error "Unexpected end of file".

This is my script, where DB_FILE is a text file containing the database names of those that end in "_abc".

while read db_name;
do
    OUT_FILENAME="${db_name}_table.csv"
    mysql -u$MYSQLUSER -p$MYSQLPASS -h $MYSQLHOST $db_name << EOFMYSQL
    SELECT * FROM table_abc INTO OUTFILE '$OUT_FILENAME' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
    EOFMYSQL
done < $DB_FILE

rm -f $DB_FILE

If I remove the 3 MYSQL lines, then the script runs without error.

Another question, if I have 2 SELECT queries, do I have to run each query separately, i.e. each enclosed by the EOFMYSQL statements, or can I have both queries within one EOFMYSQL block?

Upvotes: 3

Views: 2333

Answers (2)

J. Chomel
J. Chomel

Reputation: 8395

I think you must have the EOFMYSQL without spaces before it: do not indent that one only.

while read db_name;
do
    OUT_FILENAME="${db_name}_table.csv"
    mysql -u$MYSQLUSER -p$MYSQLPASS -h $MYSQLHOST $db_name << EOFMYSQL
    SELECT * FROM table_abc INTO OUTFILE '$OUT_FILENAME' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
EOFMYSQL
done < $DB_FILE

rm -f $DB_FILE

In some appropriate text editor, it will show up easily, here notepad++:

right indent:

notepad++ right indent

wrong indent:

notepad++ wrong indent

Upvotes: 2

sjsam
sjsam

Reputation: 21965

$OUT_FILENAME will not expand inside single quotes.

I guess you could put the query inside an array like below:

MYQUERY=( SELECT \* FROM table_abc INTO OUTFILE "$OUT_FILENAME" FIELDS TERMINATED BY \",\" LINES TERMINATED BY \"\\n\"\; )

and then change the mysql command to :

mysql -u"$MYSQLUSER" -p"$MYSQLPASS" -h "$MYSQLHOST" -e "${MYQUERY[@]}" "$db_name"

Sidenotes

  1. I have double quoted all the variables to avoid word splitting.
  2. Note that we wish to recreate actual query here. So you need to escape those characters which have special meaning here like \*,\" and so.

Upvotes: 2

Related Questions