Fix "mysqldump: Access denied... (using password: NO)" in an Automated Backup Script

If you followed our guide on automating MySQL backups to Cloudflare R2 and your cron job or systemd timer suddenly started failing, there's a good chance your log file shows exactly this:

mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect

or its close relative:

mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: YES)" when trying to connect

The confusing part is that mysql -u root -p might work perfectly fine when you type it yourself — the exact same credentials fail only when the command runs unattended, from a script. That difference in behavior is the actual clue, and almost every guide covering this error skips straight to "reset your password" without explaining why an automated script hits it when an interactive login doesn't.

The real cause: auth_socket, not a wrong password

On current Ubuntu and Debian systems, MySQL's root user is set up by default to use the auth_socket authentication plugin (called unix_socket on some MariaDB installs) instead of a traditional password. Check which one you're on:

sudo mysql -e "SELECT user, host, plugin FROM mysql.user WHERE user='root';"

If the plugin column shows auth_socket, that's your answer. This plugin authenticates based on your operating system username matching the MySQL username — it works when you're logged in interactively as the matching OS user (which is why sudo mysql or mysql -u root -p from your own SSH session can succeed with an empty or ignored password), but it has nothing to do with the password you pass to mysqldump in a script. Pass any password — or none — from a script, and it's simply the wrong authentication method entirely, which is exactly why the error insists on rejecting you regardless of what you type.

The fix: a dedicated backup user (recommended)

Rather than changing root's authentication method — which affects every tool and script that connects as root — the safer fix for an automated backup is a dedicated MySQL user that exists only for this, with the minimum privileges a backup actually needs:

sudo mysql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'a-real-generated-password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

This user can read and dump data but can't modify it — so if a backup script or its stored credentials were ever compromised, the damage is capped at "read the data," not "delete the database." Update the backup script from our automated backups guide to use these credentials instead of root:

DB_USER="backup_user"
DB_PASS="a-real-generated-password"

Test it directly before trusting it in cron:

mysqldump --single-transaction --quick --routines --triggers \
  -u backup_user -p'a-real-generated-password' your_database > /tmp/test-dump.sql

If that produces a real, non-empty .sql file with no error, the fix worked — and your automated backup script will now run the same way whether it's you typing the command or cron running it unattended at 2:30 AM.

The alternative fix: switch root's authentication plugin

If you specifically need to keep using the root account itself — less ideal, but sometimes unavoidable on an existing setup — you can switch it to password-based authentication instead:

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'a-real-generated-password';
FLUSH PRIVILEGES;
EXIT;

caching_sha2_password is the current MySQL 8+ default; use mysql_native_password instead if you're on an older MySQL version or need broader client compatibility. Either way, this changes how every connection authenticates as root — including sudo mysql, which will now actually require the password you just set, so don't lose it.

If you're getting "using password: YES" instead

This variant usually means a different, simpler mistake: a stray space between -p and the password.

# Wrong — space after -p is treated as a separate (invalid) argument
mysqldump -u backup_user -p 'yourpassword' database > dump.sql

# Correct — no space
mysqldump -u backup_user -p'yourpassword' database > dump.sql

If the syntax is already correct and you're still seeing this variant, double-check the password itself hasn't been changed or rotated since it was last saved into your backup script — a stale, hardcoded password in a script is easy to forget about until the exact moment it silently starts failing.

Why this specifically bites automated backups

This error is common enough in general MySQL troubleshooting content, but it disproportionately affects backup automation for one structural reason: people set up and test their backup command by typing it manually — as themselves, or via sudo — where auth_socket quietly works. The script then gets saved and scheduled in cron or a systemd timer, which runs as a different, non-interactive context. The command that "worked" during setup and testing was never actually testing the same authentication path the automated job would use — which is exactly why our own backup guide's Step 4, testing a real restore, matters as much as it does: it's the point where a setup that looks correct on the surface gets to prove it actually is.

Commands and defaults in this guide reflect current MySQL 8+ and Ubuntu/Debian behavior as of August 2026.