How to feed mysql queries from bash

Question

I'm trying to make a bash script that creates a mysql user and database but I can't find a way to feed the sql into mysql, I'm trying with this format:

mysql < echo "query"

But that is not working, see the example below:

mysql --host=localhost --user=user --password=password < echo "CREATE USER 'testuser'@'localhost' IDENTIFIED BY  'jakdJxct8W';
CREATE DATABASE IF NOT EXISTS 'testuser_dev' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON  'testuser_dev' . * TO  'testuser'@'localhost';
CREATE DATABASE IF NOT EXISTS 'testuser_qa' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON  'testuser_qa' . * TO  'testuser'@'localhost';"

How to feed mysql with the queries?

Solution 1

Try like this:

echo "select 1" | mysql

Solution 2

Try using a here document like this:

mysql --host=localhost --user=user --password=password << END

CREATE USER 'testuser'@'localhost' IDENTIFIED BY  'jakdJxct8W';
CREATE DATABASE IF NOT EXISTS 'testuser_dev' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON  'testuser_dev' . * TO  'testuser'@'localhost';
CREATE DATABASE IF NOT EXISTS 'testuser_qa' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON  'testuser_qa' . * TO  'testuser'@'localhost';

END

Alternatively place all you commands in text file and run it:

mysql --host=localhost --user=user --password=password < commands.sql

Solution 3

mysql --batch --silent -e 'SHOW TABLES';

Batch and silent are handy if you are planning to pipe the output

Solution 4

The reason your attempt did not work was because the < expects a file name and you fed it a string. You would have to do something like

echo "YOURQUERYSTRINGHERE">tmpfile
mysql --host=localhost --user=user --password=password dbname <tmpfile

ken's suggestion of

 mysql  --host=localhost --user=user --password=password -e "QUERY" dbname

can work, but if you try to use bash variables in your query you can fall foul of parameter expansion. eg

QUERY="select * from $MYTABLE WHERE name=\"[email protected]\";"
mysql --host=localhost --user=user --password=password -e "$QUERY" mydbname

may not do what you expect. One option is use

echo "$QUERY"|mysql --host=localhost --user=user --password=password mydbname

which works if the query string contains appropriate quoting. Another option is the "here" document as suggested by dogbane.

Solution 5

Have you tried mysql -e query?

View more solutions

Comments

[adlawson]

+1 I'd also like to mention that if the query uses backticks, you need to quote the opening label: mysql <<'END'

[Bennett Brown]

Good explanation of OP's error. Thanks for gathering others' responses and explaining caveats!

[Ken]

@cerd --batch without --silent shows column headers