When automating MySQL backups or database operations, we often need to pass credentials directly in the command line. The standard interactive approach:

mysqldump -u root -p database_name

requires manual password entry, which breaks automation workflows. Let's explore proper solutions.

Both these common attempts fail due to security restrictions:

mysqldump -u root -p{PASSWORD} database_name
mysqldump -u root --password={PASSWORD} database_name

Modern MySQL versions (5.6+) deliberately block this for security reasons, as command line arguments are visible to other users via ps commands.

Option 1: Using Configuration Files

The safest approach is using ~/.my.cnf:

[client]
user=root
password=your_secure_password

Then run simply:

mysqldump database_name

Set strict permissions:

chmod 600 ~/.my.cnf

Option 2: Environment Variables

For temporary sessions:

export MYSQL_PWD="your_password"
mysqldump -u root database_name

Remember this appears in shell history and environment listings.

Option 3: Using Process Substitution

A clever workaround for one-liners:

mysqldump -u root --password=$(cat /path/to/password_file) database_name

For cron jobs or scripts:

• Always use configuration files for production
• Consider using MySQL user with restricted privileges
• Rotate passwords regularly
• Never store passwords in version control

Each method has trade-offs:

Method	Security Level	Automation Friendly
Interactive Prompt	High	No
Config File	High	Yes
Environment Variable	Medium	Yes
Command Line	Low	Yes

The right choice depends on your specific security requirements and automation needs.

---

When automating database backups with mysqldump, many developers encounter the frustrating requirement to manually enter passwords. The standard approach:

mysqldump -u root -p database_name

forces an interactive password prompt, which breaks automation scripts.

MySQL's documentation suggests these should work:

mysqldump -u root -pYourPassword database_name
mysqldump -u root --password=YourPassword database_name

But in practice, many systems (especially newer Ubuntu installations) reject these methods due to security hardening.

Option 1: Using MySQL Configuration Files

Create ~/.my.cnf with:

[client]
user = root
password = YourSecurePassword

Then run simply:

mysqldump database_name

Option 2: Environment Variables

Temporarily set credentials:

export MYSQL_PWD="YourSecurePassword"
mysqldump -u root database_name
unset MYSQL_PWD

Option 3: Process Substitution (Bash)

For one-liners:

mysqldump -u root --password=$(cat /path/to/password_file) database_name

• Never store passwords in scripts or command history
• Set strict permissions (600) on config files
• Consider using mysql_config_editor for encrypted credential storage

If you still encounter authentication problems:

# Check if MySQL is using auth_socket
SELECT plugin FROM mysql.user WHERE User = 'root';

For socket authentication, either:

1. Change authentication method
2. Use sudo mysql without password
3. Create a dedicated backup user