Interactive CLI wizard to create a MySQL/MariaDB user with a password and full privileges on a specified database. No SQL knowledge required — just run and follow the prompts. Auto-creates the database if it doesn't exist. Works on Linux and macOS.

create_mysql_user.sh

#!/bin/bash

# Check if mysql command exists
if ! command -v mysql &> /dev/null; then
    echo "MySQL command not found in PATH."
    read -p "Please provide the full path to mysql: " MYSQL_PATH
    if [ ! -f "$MYSQL_PATH" ]; then
        echo "Error: MySQL executable not found at $MYSQL_PATH"
        exit 1
    fi
    MYSQL_CMD="$MYSQL_PATH"
else
    MYSQL_CMD="mysql"
fi

# Ask for MySQL username (default: root)
read -p "Enter MySQL username [root]: " MYSQL_USER
MYSQL_USER=${MYSQL_USER:-root}

# Ask for MySQL password (default: empty)
echo ""
read -sp "Enter MySQL password (press Enter for no password): " MYSQL_PASS
echo ""

# Set password argument - only include -p if password is not empty
if [ -z "$MYSQL_PASS" ]; then
    MYSQL_PASS_ARG=""
else
    MYSQL_PASS_ARG="-p$MYSQL_PASS"
fi

# Connect to MySQL and check if connection is successful
echo ""
echo "Connecting to MySQL..."
$MYSQL_CMD -u"$MYSQL_USER" $MYSQL_PASS_ARG -e "SELECT 1;" &> /dev/null
if [ $? -ne 0 ]; then
    echo "Error: Failed to connect to MySQL. Please check your username and password."
    exit 1
fi

echo "Connected successfully!"
echo ""
echo "Available databases:"
$MYSQL_CMD -u"$MYSQL_USER" $MYSQL_PASS_ARG -e "SHOW DATABASES;"

# Ask for new user details
echo ""
read -p "Enter new MySQL username to create: " NEW_USER
if [ -z "$NEW_USER" ]; then
    echo "Error: Username cannot be empty."
    exit 1
fi

read -sp "Enter password for new user: " NEW_PASS
echo ""
if [ -z "$NEW_PASS" ]; then
    echo "Error: Password cannot be empty."
    exit 1
fi

read -p "Enter database name to grant privileges on: " NEW_DB
if [ -z "$NEW_DB" ]; then
    echo "Error: Database name cannot be empty."
    exit 1
fi

# Create database if it doesn't exist
echo ""
echo "Creating database if it doesn't exist..."

$MYSQL_CMD -u"$MYSQL_USER" $MYSQL_PASS_ARG -e "CREATE DATABASE IF NOT EXISTS $NEW_DB;" 2> /dev/null
if [ $? -ne 0 ]; then
    echo "Error: Failed to create database."
    exit 1
fi

# Create user and grant privileges
echo "Creating user '$NEW_USER' with access to database '$NEW_DB'..."

$MYSQL_CMD -u"$MYSQL_USER" $MYSQL_PASS_ARG -e "CREATE USER '$NEW_USER'@'localhost' IDENTIFIED BY '$NEW_PASS';" 2> /dev/null
if [ $? -ne 0 ]; then
    echo "Error: Failed to create user."
    exit 1
fi

$MYSQL_CMD -u"$MYSQL_USER" $MYSQL_PASS_ARG -e "GRANT ALL PRIVILEGES ON $NEW_DB.* TO '$NEW_USER'@'localhost';" 2> /dev/null
if [ $? -ne 0 ]; then
    echo "Error: Failed to grant privileges."
    exit 1
fi

$MYSQL_CMD -u"$MYSQL_USER" $MYSQL_PASS_ARG -e "FLUSH PRIVILEGES;" 2> /dev/null
if [ $? -ne 0 ]; then
    echo "Error: Failed to flush privileges."
    exit 1
fi

# Verify user creation
$MYSQL_CMD -u"$MYSQL_USER" $MYSQL_PASS_ARG -e "SELECT User, Host FROM mysql.user WHERE User='$NEW_USER';" &> /dev/null
if [ $? -ne 0 ]; then
    echo "Error: Failed to verify user creation."
    exit 1
fi

echo ""
echo "========================================"
echo "SUCCESS!"
echo "========================================"
echo "User '$NEW_USER' has been created with:"
echo "   - Password: $NEW_PASS"
echo "   - Full privileges on database: $NEW_DB"
echo "   - Host: localhost"
echo "========================================"
exit 0

More details and explanation at the blog post