Idempotent user and database creation script with MySQL I have written a shell script which creates a database and user on a MySQL server. The user and database are to be used by a web application and generally this task only be performed once per website installation, but sometimes, (e.g. testing the script) the script needs to be run many times.
Currently the script assumes the user always exists and just pre-emptively deletes, i.e.

DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';


Ive also tried drop user ${web_app_sql_user}@localhost; but then that fails if the user doesn't exist if you can believe that! lol

Script

#!/bin/bash
echo -e "Creating a SQL user and database for the webapp"

read -s -p "enter SQL root user password: " password

cat &lt;&lt;- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;

    -- Deal with possibility user already exists!
    DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';
    -- drop user ${web_app_sql_user}@localhost;
    FLUSH PRIVILEGES;

    CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    -- CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    GRANT ALL ON ${web_app_database}.* TO '${web_app_sql_user}'@'localhost';

    GRANT USAGE ON * . * TO  '${web_app_sql_user}'@'localhost' IDENTIFIED BY  '${web_app_sql_user_password}' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';

    FLUSH PRIVILEGES;
EOF


Result when running the script

--------------
DROP DATABASE IF EXISTS `web_app_database`
--------------

--------------
CREATE DATABASE `web_app_database` CHARACTER SET utf8 COLLATE utf8_general_ci
--------------

--------------
DELETE FROM mysql.user WHERE User = 'web_app_user'
--------------

--------------
FLUSH PRIVILEGES
--------------

--------------
CREATE USER 'web_app_user'@localhost IDENTIFIED BY ''
--------------

ERROR 1396 (HY000) at line 17: Operation CREATE USER failed for 'web_app_user'@'localhost'


Is it possible for MySQL to be able to handle these situations?


MySQL Server version: 5.5.46-0ubuntu0.14.04.2 (Ubuntu)




Updated script based on michael-sqlbot's answer

read -s -p "enter SQL root user password: " password
cat &lt;&lt;- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';
    FLUSH PRIVILEGES;
EOF
The task is too complex to safely (and simply) do in a shell script.  Get into some programming language (Perl, etc) and write enough code to check for various end cases, syntax errors, punctuation in user names, etc, etc.  You can also check for the existence of the user and/or database and exit or say "Dup name" or proceed with the delete, or whatever.

And totally disallow 'root' and 'mysql' and 'information_schema' and 'performance_schema'!

The CREATE USER + 3 GRANTs can be simplified to just

GRANT ALL PRIVILEGES ON db.* TO user@localhost IDENTIFIED BY '...' WITH ...