Bash Script To Create Database and User

I find that during development, there are three database commands I typically execute for any project that uses a MySQL database.

I typically need to do three things:

  • create a database
  • create a user
  • grant database access to the user

I created a simple bash script to run these commands, along with setting a few defaults.

#!/bin/bash

#get the database name
read -p "Database name: " name

#get the database password with default
read -p "Password [dbpass]: " pass
pass=${pass:-dbpass}

#get the database creator with default
read -p "Database creator [root]: " user
user=${user:-root}

#execute sql commands with the user
mysql -u $user -p -e "CREATE DATABASE $name;CREATE USER $name@localhost identified by '$pass';GRANT ALL ON $name.* to $name@localhost WITH GRANT OPTION;"

#confirmation message
echo "Created database and user: $name"

You can then execute this from the terminal: sh createdb.sh

Edit: November 23, 2019
I needed to update my script because the validate password plugin policy is set to high by default. It is also possible to uninstall the plugin, however, that would be inadvisable in a production environment.

#execute sql commands with the user
mysql -u $user -p -e "SET GLOBAL validate_password.policy=LOW;CREATE DATABASE $name;CREATE USER $name@localhost identified with mysql_native_password by '$pass';GRANT ALL ON $name.* to $name@localhost WITH GRANT OPTION;"