Rayne Rayne - 5 months ago 37
Bash Question

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?

Answer

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