When automating MySQL database backups through shell scripts, many developers encounter the frustrating situation where mysqldump keeps prompting for a password despite providing it in the command. Here's why this happens and how to properly implement password handling:

MySQL intentionally obscures password parameters in command lines to prevent exposure through process listing. The -p$MyPass syntax you're using is problematic because:

# This is visible in process listings
ps aux | grep mysql

Here are three secure approaches to solve this:

1. Using Configuration Files

# ~/.my.cnf
[client]
user = backup
password = YourSecurePassword
host = localhost

# Then your script becomes simply:
$MYSQLDUMP $DB > $DEST/$FILE

2. Environment Variables

export MYSQL_PWD="YourSecurePassword"
$MYSQLDUMP -u backup $DB > $DEST/$FILE

3. Prompt-Only When Interactive

if [ -t 0 ]; then
    # Interactive session - prompt for password
    $MYSQLDUMP -u backup -p $DB > $DEST/$FILE
else
    # Non-interactive - use config file
    $MYSQLDUMP --defaults-file=/path/to/config.cnf $DB > $DEST/$FILE
fi

For production environments, consider creating a dedicated options file with restricted permissions:

# Create secure config file
cat > /etc/mysql/backup.cnf << EOF
[client]
user = backup
password = ${DB_PASSWORD}
host = db.example.com
EOF

chmod 600 /etc/mysql/backup.cnf

# Use in script
$MYSQLDUMP --defaults-file=/etc/mysql/backup.cnf $DB | gzip > $DEST/$FILE.gz

• Never store passwords in scripts
• Use chmod 600 for configuration files
• Consider using MySQL's encrypted options files
• Implement proper backup rotation

If you still encounter issues:

# Test connection first
mysql --defaults-file=/path/to/config.cnf -e "SHOW DATABASES;"

# Verify file permissions
ls -la /path/to/config.cnf

# Check for special characters in password

---

Many developers encounter an annoying issue when using mysqldump in shell scripts - the command keeps prompting for a password even when it's provided in the command line. This typically happens with MySQL version 5.0.27 and similar older versions.

The root cause is often related to how the shell interprets the password string. Special characters in the password can cause the shell to misinterpret the command. The syntax -p$MyPass might not work as expected because:

• The password contains special characters that need escaping
• There's no space between -p and the password
• Environment variables aren't properly expanded

Here are several working approaches:

1. Using a Configuration File

The most secure method is to use a MySQL configuration file:

[client]
user = backup
password = your_password

Then call mysqldump with:

$MYSQLDUMP --defaults-file=/path/to/config.cnf $DB > $DEST/$FILE

2. Proper Variable Handling

For direct command line usage, ensure proper variable handling:

MYSQL_PWD="$MyPass" $MYSQLDUMP -u backup $DB > $DEST/$FILE

Or alternatively:

$MYSQLDUMP -u backup -p"$MyPass" $DB > $DEST/$FILE

3. Using Here Document

For complex passwords, a here document works well:

$MYSQLDUMP -u backup -p < $DEST/$FILE

While these solutions work, remember that:

• Command-line passwords may be visible in process listings
• Configuration files should have restricted permissions (600)
• For production systems, consider using MySQL's encrypted option files

Always test with a simple command first:

mysql -u backup -p"$MyPass" -e "SHOW DATABASES;"

If this works, your mysqldump command should work too.