Safer MySQL Database Provisioning from a Shell Script

A shell script can make local or test database provisioning repeatable, but the obvious implementation has two common problems: it exposes an administrative password on the command line and interpolates untrusted values directly into SQL.

For production fleets, use the organization’s secrets system and configuration-management tooling. The example below is intentionally narrow: one database and one application user on a MySQL server where the operator already has an authenticated client profile.

Store authentication outside the script

MySQL supports login paths created with mysql_config_editor. This keeps the script from containing a root password or passing it as -pPASSWORD, which can expose it to process inspection or shell history.

Create a dedicated administrative login path interactively:

mysql_config_editor set \
  --login-path=app-provisioner \
  --host=localhost \
  --user=provisioner \
  --password

The provisioner account should have only the privileges required to create this application’s database, account, and grants. Do not assume it needs the full privileges of root.

Validate inputs before building SQL

MySQL parameter binding does not apply to database and account identifiers in the same way it applies to data values. This script therefore accepts only a conservative identifier pattern and supplies the password through an environment variable for the one command invocation.

#!/usr/bin/env bash
set -euo pipefail

: "${DB_NAME:?Set DB_NAME}"
: "${DB_USER:?Set DB_USER}"
: "${DB_PASSWORD:?Set DB_PASSWORD}"

identifier='^[A-Za-z_][A-Za-z0-9_]{0,63}$'
[[ "$DB_NAME" =~ $identifier ]] || { echo "Invalid DB_NAME" >&2; exit 1; }
[[ "$DB_USER" =~ $identifier ]] || { echo "Invalid DB_USER" >&2; exit 1; }

escaped_password=${DB_PASSWORD//\\/\\\\}
escaped_password=${escaped_password//\'/\'\'}

mysql --login-path=app-provisioner --protocol=socket <<SQL
CREATE DATABASE IF NOT EXISTS \`$DB_NAME\`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS '$DB_USER'@'localhost'
  IDENTIFIED BY '$escaped_password';
ALTER USER '$DB_USER'@'localhost'
  IDENTIFIED BY '$escaped_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP
  ON \`$DB_NAME\`.* TO '$DB_USER'@'localhost';
SQL

Run it without saving the password in the script:

read -rsp 'Application database password: ' DB_PASSWORD
echo
export DB_NAME=my_app DB_USER=my_app DB_PASSWORD
./provision-database.sh
unset DB_PASSWORD

The privilege list is an example for an application that manages its own schema. A read-only reporting process should receive only SELECT; a migration account can be separate from the runtime account. Restrict the account host to the actual connection source rather than using % by default.

Know what this does not solve

This script provisions schema access. It does not create backups, verify restores, encrypt connections, rotate credentials, configure replication, or recreate data after a failure. Those are separate operational responsibilities.

Test the script against the same MySQL major version used by the target environment. If repeatable provisioning extends beyond a few controlled instances, move the declarations into infrastructure or configuration-management code with auditable secret handling and automated tests.

Safer MySQL Database Provisioning from a Shell Script

Store authentication outside the script

Validate inputs before building SQL

Know what this does not solve

Sources