Running mysql command in a while loop

Question

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 1

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:

wrong indent: