When automating database operations through Bash scripts, developers often face security concerns when handling MySQL root credentials. Here's why your current approach raises issues:

# Security risks in current implementation
MYSQL_USER=root
MYSQL_PASS=mypass_goes_here  # Password exposed in script

MySQL provides several secure alternatives to password-based authentication:

# Method 1: Using mysql_config_editor for secure connections
mysql_config_editor set --login-path=local --host=localhost --user=root --password

# Method 2: Leveraging .my.cnf configuration file
[client]
user = root
password = your_secure_password
host = localhost

Here's how to modify your script using best practices:

#!/bin/bash

DB_NAME="new_database"
TEMP_SCRIPT="/tmp/script_$$.sql"  # Using PID for unique filename

# Secure creation method using login-path
cat << EOF > $TEMP_SCRIPT
CREATE DATABASE IF NOT EXISTS $DB_NAME;
EOF

mysql --login-path=local < $TEMP_SCRIPT
rm -f $TEMP_SCRIPT

For PostgreSQL, similar secure methods exist:

# Using .pgpass file
localhost:5432:*:postgres:your_password

# In your script
psql -h localhost -U postgres -c "CREATE DATABASE $DB_NAME;"

• Use limited-privilege users instead of root
• Implement MySQL's auth_socket plugin for local connections
• Consider temporary credentials with AWS RDS or similar services
• Always clean up temporary files securely

# Example using dedicated admin user
CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'complex_password';
GRANT ALL PRIVILEGES ON *.* TO 'db_admin'@'localhost';

When automating database operations in bash scripts, hardcoding root credentials creates significant security vulnerabilities. The common error message "Access denied for user 'root'@'localhost'" typically indicates one of three scenarios:

# Common failure pattern
mysql --user=root --password="wrong_pass" -e "SHOW DATABASES;"
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Option 1: MySQL Configuration File

Create ~/.my.cnf with restricted permissions:

[client]
user=root
password=your_actual_password
host=localhost

# Set strict permissions
chmod 600 ~/.my.cnf

Then your script becomes:

DB_NAME="new_database"
echo "CREATE DATABASE $DB_NAME;" | mysql

Option 2: MySQL Native Password Plugin

For MySQL 8.0+ where caching_sha2_password is default:

ALTER USER 'root'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'new_password';

For PostgreSQL automation, use pgpass file:

# ~/.pgpass format
hostname:port:database:username:password

# Example:
localhost:5432:*:postgres:dbpassword

# Script usage:
psql -h localhost -U postgres -c "CREATE DATABASE new_db;"

For production environments, consider SSL certificate authentication:

# MySQL SSL config
GRANT ALL PRIVILEGES ON *.* TO 'script_user'@'%'
REQUIRE SSL
WITH GRANT OPTION;

Then connect using:

mysql --ssl-ca=/path/to/ca.pem \
      --ssl-cert=/path/to/client-cert.pem \
      --ssl-key=/path/to/client-key.pem

Always implement proper error handling in your scripts:

if ! mysql -e "CREATE DATABASE $DB_NAME"; then
    echo "Database creation failed" >&2
    exit 1
fi