When executing mysqldump manually as root, the command succeeds because MySQL client tools automatically use your system's credential cache. However, cron jobs operate in a minimal environment that doesn't inherit these authentication contexts. This explains why you encounter error 1045 ("Access denied") despite running as root.

The most secure approach is using MySQL's configuration file to store credentials without exposing them in scripts:

[client]
user=root
password=your_secure_password

Save this as /root/.my.cnf with strict permissions:

chmod 600 /root/.my.cnf

Your cron script (/etc/cron.daily/mysqlbackup) becomes simple and secure:

#!/bin/bash
BACKUP_DIR="/backups/mysql"
TIMESTAMP=$(date +"%F_%H-%M-%S")

mysqldump --all-databases | gzip > "$BACKUP_DIR/full_backup_$TIMESTAMP.sql.gz"

For environments where configuration files aren't preferred:

#!/bin/bash
MYSQL_PWD=$(cat /root/mysql_password.txt) mysqldump -u root --all-databases

Or using MySQL 8.0+ authentication plugins:

CREATE USER 'backup_admin'@'localhost' IDENTIFIED WITH auth_socket;
GRANT ALL PRIVILEGES ON *.* TO 'backup_admin'@'localhost';

• Always set file permissions to 600 for credential files
• Regularly rotate backup files and monitor disk space
• Consider encrypting backups for sensitive data
• Test restore procedures periodically

---

Many sysadmins encounter this frustrating scenario: while mysqldump --all-databases works perfectly when executed manually as root, the same command fails in cron with:

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO)

This happens because cron jobs don't inherit the same environment variables or authentication contexts as interactive shell sessions.

When you run mysqldump interactively, MySQL might be using one of these authentication methods:

• MySQL configuration file (~/.my.cnf)
• Socket peer authentication (on some Linux distributions)
• Environment variables

Cron jobs typically don't have access to these authentication methods because:

1. They run with a minimal environment
2. They don't have access to your user's home directory
3. They might run before network services are fully initialized

Option 1: Use a Dedicated .my.cnf File

Create a secure configuration file for cron:

sudo mkdir -p /etc/mysql/cron.d/
sudo nano /etc/mysql/cron.d/backup.cnf

Add these contents (with proper permissions):

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

Set strict permissions:

sudo chown root:root /etc/mysql/cron.d/backup.cnf
sudo chmod 600 /etc/mysql/cron.d/backup.cnf

Then modify your cron script:

#!/bin/bash
MYSQL_CNF=/etc/mysql/cron.d/backup.cnf
mysqldump --defaults-extra-file=$MYSQL_CNF --all-databases > /backup/dump.sql

Option 2: MySQL Config Snippet (Debian/Ubuntu)

On Debian-based systems, you can add this to /etc/mysql/conf.d/backup.cnf:

[mysqldump]
user=root
password=your_password

Option 3: MySQL Enterprise Edition Alternatives

For enterprise environments:

# Using mysql_config_editor
mysql_config_editor set --login-path=cron_backup --host=localhost --user=root --password

Then in your script:

mysqldump --login-path=cron_backup --all-databases > backup.sql

Whichever method you choose:

• Always set strict file permissions (600 for config files)
• Consider using a dedicated backup user instead of root
• Regularly rotate passwords and update config files
• Monitor backup logs for authentication failures

To troubleshoot cron-specific problems:

# Capture full environment
env > /tmp/cron.env

# Test with full environment
env - $(cat /tmp/cron.env) /path/to/your/script.sh

Or test with a temporary cron job:

* * * * * /path/to/script.sh > /tmp/cron_test.log 2>&1