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?

not related to the question, but you can also save a lot of typing by setting up your host, user and password in a .my.cnf file — Ken
– Ken, Commented May 27, 2011 at 10:09

Prince John Wesley · Accepted Answer · 2011-05-27 10:08:27Z

58

Try like this:

echo "select 1" | mysql

answered May 27, 2011 at 10:08

Prince John Wesley

63.9k12 gold badges90 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dogbane · Accepted Answer · 2011-05-27 10:06:20Z

30

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

answered May 27, 2011 at 10:06

dogbane

276k77 gold badges407 silver badges415 bronze badges

1 Comment

adlawson Over a year ago

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

Ken · Accepted Answer · 2011-05-27 10:06:58Z

26

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

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

answered May 27, 2011 at 10:06

Ken

79.2k32 gold badges88 silver badges101 bronze badges

1 Comment

Ken Over a year ago

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

Bennett Brown · Accepted Answer · 2016-05-11 14:04:17Z

12

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.

edited May 11, 2016 at 14:04

Bennett Brown

5,3901 gold badge29 silver badges35 bronze badges

answered Aug 7, 2015 at 10:41

tim

1211 silver badge3 bronze badges

1 Comment

Bennett Brown Over a year ago

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

Miki · Accepted Answer · 2011-05-27 10:04:38Z

8

Have you tried mysql -e query?

answered May 27, 2011 at 10:04

Miki

7,2382 gold badges34 silver badges42 bronze badges

Comments

willgriffin · Accepted Answer · 2019-10-15 23:35:37Z

1

cat <<EOD | mysql [-u user] [-ppassword] [database]
  select 1;
  select 2;
  select 3;
EOD

in your case

cat <<EOD | mysql -u root -p
    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";
    FLUSH PRIVILEGES;
EOD

edited Oct 15, 2019 at 23:35

answered Oct 15, 2019 at 23:23

willgriffin

114 bronze badges

Comments

Daniel Pérez Rada · Accepted Answer · 2017-10-19 12:31:09Z

0

For big queries in a bash script, you can try:

read -d '' SQL_QUERY_1 << EOF

SELECT prod.id as id, prod.title as title, comp.name as company, pho.id as photo_id, pho.image as photo_name
FROM products as prod
JOIN accounts as comp
ON comp.id = prod.account_id
JOIN photos as pho
ON pho.id = prod.featured_photo_id;

EOF

echo ${SQL_QUERY_1} | mysql

answered Oct 19, 2017 at 12:31

Daniel Pérez Rada

1,5721 gold badge13 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to feed mysql queries from bash

7 Answers 7

Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related