This is how I create a MySQL authorized, all privileged DB user and a corresponding DB instance with the same name, directly from Bash:

echo "CREATE USER "${domain}"@"localhost" IDENTIFIED BY \"${dbup}\";" | mysql -u root -p"${dbrp}"
echo "CREATE DATABASE ${domain};" | mysql -u root -p"${dbrp}"
echo "GRANT ALL PRIVILEGES ON ${domain}.* TO ${domain}@localhost;" | mysql -u root -p"${dbrp}"

Could the first two commands merged into one command somehow?


Use a "Here Document" aka heredoc. e.g.:

cat <<EOF | mysql -u root -p"${dbrp}"
CREATE USER '${domain}'@localhost IDENTIFIED BY '${dbup}';
CREATE DATABASE '${domain}';
GRANT ALL PRIVILEGES ON ${domain}.* TO '${domain}'@localhost;

Unfortunately, mysql still doesn't support rollback of CREATE USER or CREATE DATABASE or GRANT etc (it does an implicit commit for DDL operations), otherwise I'd say you should wrap all those commands inside a transaction (so that if one fails, they all fail and everything reverts back to the way it was).

  • Thanks. I already tried heredocument but had problems expanding the variables inside the MySQL shell. One more thing, what I did in the end was to unite the first two sql commands into on line: CREATE USER "${domain}"@"localhost" IDENTIFIED BY ${dbup}; CREATE DATABASE ${domain};. – Arcticooling Jan 16 '18 at 17:25
  • this does not expand anything inside mysql's shell. the sh or bash or whatever shell expands it all and then pipes it into mysql. – cas Jan 16 '18 at 17:33
  • Yes I seem to have done this with heredocument in a different way in which the variables weren't expanded. – Arcticooling Jan 16 '18 at 17:37
  • if you put single-quotes around the heredoc marker (e.g. <<'EOF' instead of <<EOF) then shell expansion does not happen, just as if the entire heredoc was in single-quotes. – cas Jan 16 '18 at 17:39
  • Yes I think I didn't put these and another problem caused that (probably single quotes around the variables or I'm misrecall something. – Arcticooling Jan 16 '18 at 17:41

Why not even more simple? Oneliner is enough.

[root@dev2.net][~]# mysql -e'show databases' | grep test_se          # not exists yet
[root@dev2.net][~]# domain=test_se
[root@dev2.net][~]# mysql -v -e"create database ${domain};grant all on ${domain}.* to ${domain}@localhost identified by 'mysuperpassw00rd'"
create database test_se

grant all on test_se.* to test_se@localhost identified by 'mysuperpassw00rd'

[root@dev2.net][~]# mysql -e'show databases' | grep test_se           # here we are!

I almost never use create user, it is just useless from my standpoint unless you need to add the same user to many DBs. Also, concatenation with ; as you can see works pretty well.

Remove -v to turn off verbose output.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.