MySQL – Create a database and user with a random password in bash (with PostgreSQL Alternative)

Managing databases from the command line is one of the fastest ways to provision new applications on a Linux server. Instead of logging into the MySQL shell and executing multiple SQL statements manually, you can automate the entire process with a simple Bash script.

This tutorial demonstrates how to create a new MySQL database, create a dedicated user, generate a secure random password, and grant the required privileges. At the end of the article, you’ll also find the equivalent process for PostgreSQL.

Creating a MySQL Database and User with Bash

The following script performs the following tasks automatically:

  • Creates a new database (if it doesn’t already exist)
  • Generates a secure random password
  • Creates a new MySQL user
  • Grants the user full privileges on the new database
  • Reloads MySQL privileges
#!/bin/bash

EXPECTED_ARGS=2
E_BADARGS=65

MYSQL=$(which mysql)

PASS=$(tr -cd '[:alnum:]' < /dev/urandom | fold -w16 | head -n1)

if [ $# -ne $EXPECTED_ARGS ]; then
    echo "Usage: $0 <database_name> <database_user>"
    exit $E_BADARGS
fi

DBNAME=$1
DBUSER=$2

SQL="
CREATE DATABASE IF NOT EXISTS \`${DBNAME}\`;
CREATE USER IF NOT EXISTS '${DBUSER}'@'localhost' IDENTIFIED BY '${PASS}';
GRANT ALL PRIVILEGES ON \`${DBNAME}\`.* TO '${DBUSER}'@'localhost';
FLUSH PRIVILEGES;
"

$MYSQL -uroot -p -e "$SQL"

echo "Database: $DBNAME"
echo "User: $DBUSER"
echo "Password: $PASS"

How the Script Works

1. Checks the Required Arguments

The script expects two parameters:

  • Database name
  • Database username

If they are missing, it displays the correct usage and exits.

./create-db.sh mydatabase myuser

2. Generates a Secure Password

Instead of using a hardcoded password, the script generates a random 16-character alphanumeric password.

PASS=$(tr -cd '[:alnum:]' < /dev/urandom | fold -w16 | head -n1)

This provides a strong password suitable for production use.

3. Creates the Database

CREATE DATABASE IF NOT EXISTS mydatabase;

The IF NOT EXISTS clause prevents errors if the database already exists.

4. Creates the Database User

CREATE USER IF NOT EXISTS 'myuser'@'localhost'
IDENTIFIED BY 'generated-password';

The user is restricted to local connections (localhost). If your application connects remotely, replace localhost with the appropriate hostname or % (only if required and secured).

5. Grants Database Permissions

GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';

This gives the user full control over the specified database while keeping access isolated from other databases on the server.

6. Reloads Privileges

FLUSH PRIVILEGES;

This ensures MySQL immediately recognizes the newly created user and permissions.

Running the Script

Make the script executable:

chmod +x create-db.sh

Run it as follows:

./create-db.sh wordpress wpuser

Example output:

Database: wordpress
User: wpuser
Password: a8F2dK9LmQ4PzX7N

Store the generated password securely, as you’ll need it for your application’s database configuration.

Creating a PostgreSQL Database and User

If you’re using PostgreSQL instead of MySQL, the process is similar but uses PostgreSQL roles and ownership.

Step 1: Switch to the PostgreSQL User

sudo -u postgres psql

Step 2: Create a Database User

Replace myuser and strongpassword with your preferred values.

CREATE USER myuser WITH PASSWORD 'strongpassword';

Step 3: Create the Database

CREATE DATABASE mydatabase;

Step 4: Assign Ownership

Make the new user the owner of the database.

ALTER DATABASE mydatabase OWNER TO myuser;

Step 5: Grant Database Privileges

Although ownership already provides broad control, you can explicitly grant privileges if required.

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Exit PostgreSQL when finished:

\q

Automating PostgreSQL with Bash

The same process can be automated with a Bash script.

#!/bin/bash

if [ $# -ne 2 ]; then
    echo "Usage: $0 <database_name> <database_user>"
    exit 1
fi

DBNAME=$1
DBUSER=$2

PASS=$(tr -cd '[:alnum:]' < /dev/urandom | fold -w16 | head -n1)

sudo -u postgres psql <<EOF
CREATE USER ${DBUSER} WITH PASSWORD '${PASS}';
CREATE DATABASE ${DBNAME} OWNER ${DBUSER};
GRANT ALL PRIVILEGES ON DATABASE ${DBNAME} TO ${DBUSER};
EOF

echo "Database: ${DBNAME}"
echo "User: ${DBUSER}"
echo "Password: ${PASS}"

Run it as follows:

chmod +x create-pg-db.sh
./create-pg-db.sh mydatabase myuser

Security Considerations

When automating database creation, keep these best practices in mind:

  • Use strong, randomly generated passwords.
  • Avoid granting global database privileges unless absolutely necessary.
  • Restrict users to the databases they need.
  • Store generated passwords securely in a password manager or secrets management system.
  • Consider using environment variables or configuration management tools instead of embedding credentials in scripts.

Conclusion

Automating database provisioning saves time and reduces the chance of configuration errors. Whether you’re deploying a WordPress site, a web application, or a development environment, a simple Bash script can create databases, users, and permissions in seconds.

For MySQL, the script above provides a quick and repeatable way to provision new databases with secure credentials. PostgreSQL offers a similarly straightforward workflow using roles and database ownership, making it easy to automate deployments across different database platforms.

Published on: 28 June 2013
Posted by: Sami K.