Fixing the MySQL error "Access denied for user"

"Access denied for user" is the most searched error message in the MySQL world, and in most cases the password is not to blame at all. On Debian and Ubuntu, the login usually fails because of socket authentication, because localhost gets mixed up with 127.0.0.1, or because a user row was created for the wrong host. This article works through the causes in the order in which they actually occur, shows the password reset via skip-grant-tables including the way back, and describes how you can tell that the login is really fixed.

All details refer to Debian 13 (MariaDB 11.8), Debian 12 (MariaDB 10.11), Ubuntu 24.04 (MariaDB 10.11 or MySQL 8.0) and Ubuntu 22.04 (MariaDB 10.6 or MySQL 8.0). One important point up front: Debian ships no mysql-server package at all. If you have installed "MySQL" on a Debian system, what runs there is MariaDB, and that already explains part of the confusion.

Read the error message carefully

The exact wording decides which cause is possible. These five variants are the ones you meet in practice:

Two details are regularly overlooked. First, the host name in the message is the host the server saw you as, not the one you typed. If it says 'app'@'localhost' although you connected with -h 127.0.0.1, then the server performed a reverse lookup. Second, 1045 does not distinguish between "wrong password" and "account does not exist". Both produce the same output, deliberately so, to keep an attacker from probing for valid user names.

The most common case: unix_socket on MariaDB

Since MariaDB 10.4, the Debian and Ubuntu packages create the account root@localhost so that it authenticates through the Unix socket. The account row looks roughly like this:

CREATE USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid'
  OR unix_socket;

That means: whoever is logged in as the system user root gets in without a password. Whoever sends a password is checked against the hash of the string "invalid", and nobody can match that. The mysql_native_password part is only there because SET PASSWORD would otherwise abort with an error. The practical consequence: mysql -u root -p as a normal user fails no matter which password you type. The correct call is:

sudo mariadb

On MySQL 8.0 on Ubuntu the plugin is called auth_socket instead of unix_socket, and the behavior is identical. There the command is sudo mysql.

Check which plugin an account really uses, and do it with SHOW CREATE USER:

mariadb -e "SHOW CREATE USER 'root'@'localhost';"

The output shows the complete chain of both methods:

CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket

There is a trap here that hardly any guide mentions: since MariaDB 10.4, mysql.user is only a view, and the real data sits as JSON in mysql.global_priv. That view knows a single authentication method per account and therefore reports mysql_native_password for root@localhost, although unix_socket is what actually applies. Anyone who takes the widespread query SELECT User, Host, plugin FROM mysql.user as proof believes the server is password authenticated and looks for the fault in the wrong place. JSON_VALUE(priv,"$.plugin") has exactly the same blind spot, because it too reads only the first element. The second method sits in the field auth_or and has to be read out separately:

mariadb -e 'SELECT CONCAT(user,"@",host) AS account, JSON_VALUE(priv,"$.plugin") AS plugin, JSON_QUERY(priv,"$.auth_or") AS auth_or FROM mysql.global_priv;'

For root@localhost the column plugin then still shows mysql_native_password, while auth_or contains [{},{"plugin":"unix_socket"}]. Only this second column reveals that the socket login is active.

Is the plugin loaded at all? After a broken upgrade it can be missing, and then the server reports ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded:

mariadb -e "SELECT plugin_name, plugin_status FROM information_schema.plugins WHERE plugin_name LIKE '%socket%';"

If you deliberately want to switch root over to a password, for example because a backup script runs under a different system user, then keep the socket variant in addition. Otherwise the maintenance scripts of the distribution and sudo mariadb stop working:

ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket
  OR mysql_native_password USING PASSWORD('YourNewPassword');

The better approach is a dedicated administration account anyway, instead of a password for root. For applications that goes double: one database, one user, only the privileges that are needed.

localhost is not 127.0.0.1

This distinction causes more "Access denied" messages than any wrong password. The MySQL and MariaDB clients treat the host name localhost as a special case and connect through the Unix socket. Only 127.0.0.1 forces a TCP connection. From the point of view of privilege management the two are not the same host names, because on a socket connection the server records the host as localhost, while on TCP over the loopback address it records either localhost (after a reverse lookup) or 127.0.0.1, depending on the setting of skip_name_resolve.

An account that exists only as 'app'@'127.0.0.1' cannot be reached through the socket, and the other way round as well. That is exactly what happens when a PHP application has host=localhost in its configuration: PHP connects through the socket, but the privileges were granted for the IP address. Two counter-checks:

mariadb -u app -p'password' -e "SELECT USER(), CURRENT_USER();"
mariadb -h 127.0.0.1 -u app -p'password' -e "SELECT USER(), CURRENT_USER();"

If only one of the two fails, you have found the cause. Watch the name resolution while doing this: as long as skip_name_resolve is switched off, and it is off in the package installations of Debian and Ubuntu, the server resolves the loopback address backwards to localhost. If an account 'app'@'localhost' already exists, the login with -h 127.0.0.1 therefore succeeds too, and CURRENT_USER() then reports app@localhost instead of app@127.0.0.1. An additionally created account 'app'@'127.0.0.1' stays without effect in that case, it only takes over once the localhost account is gone. The clean solution is to create the account for the path the application actually takes, and not to set up both variants "just in case".

Check as well whether name resolution is switched off. If skip_name_resolve is active, account rows with host names such as 'app'@'web01.intern' stop working entirely, and only IP addresses count from then on:

mariadb -e "SHOW VARIABLES LIKE 'skip_name_resolve';"

Another stumbling block is the order in which the server picks matching rows. It sorts from specific to general and takes the first match, not the best one. If an anonymous account ''@'localhost' exists next to 'app'@'%', the anonymous account wins on a local connection, and your login fails with a message that still names your user name. Current packages no longer create anonymous accounts, but on systems migrated over many years you still find them:

mariadb -e "SELECT user, host FROM mysql.global_priv WHERE user = '';"

Password, privileges and capitalization

That leaves the causes that really do have something to do with the credentials.

The shell eats special characters

There must be no space between -p and the password, otherwise the client reads the password as a database name. Passwords containing $, !, & or spaces belong in single quotes, because the shell would otherwise substitute or truncate them. The really clean way is not to pass the password on the command line at all, because there it ends up in the process list and in the history file. Create a file ~/.my.cnf with permissions 0600 instead:

[client]
user=app
password=YourPassword

The other way round, a forgotten ~/.my.cnf is a possible cause of the error as well. It silently overrides what you specify on the command line, and then you get "Access denied" for a user you never typed.

Capitalization: user name yes, host no

User names have to be matched character for character in MySQL and MariaDB, App and app are two different accounts. Host names are compared without regard to upper and lower case, but stored exactly as they were written in the CREATE USER statement. If you have accidentally created 'app'@'LOCALHOST', you see two seemingly different accounts in SHOW GRANTS output and in scripts, although both match the same connection. Duplicates like that make troubleshooting unnecessarily tough, because you grant privileges on one row and the server picks the other. You can track them down like this:

mariadb -e "SELECT user, host FROM mysql.global_priv WHERE host <> LOWER(host);"

Privileges are missing, not the login

If ERROR 1044 shows up instead of 1045, the login succeeded. What is missing then are privileges on a particular database. Look at what the account is really allowed to do:

mariadb -e "SHOW GRANTS FOR 'app'@'localhost';"

You only need FLUSH PRIVILEGES if you have changed the privilege tables directly with INSERT or UPDATE. After GRANT, CREATE USER or ALTER USER it is superfluous, and it occasionally hides the fact that the actual statement did not take effect at all.

The client does not understand the plugin

MySQL 8.0 uses caching_sha2_password by default. Older clients and libraries answer that with ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded. This is not a privilege problem but an incompatibility. In MySQL 8.0, mysql_native_password is still available as a fallback, in MySQL 8.4 it has been removed. When in doubt, update the client rather than turning the encryption back.

Resetting the root password

When nothing helps any more, the server has to start once without privilege checking. Two routes lead to the goal. The route via --init-file is the safer one, because the server keeps running with privilege checking active throughout.

Variant 1: init-file (recommended)

The SQL file has to sit in a place the service is allowed to read. Under /tmp or /root this regularly fails on Ubuntu because of AppArmor and because of PrivateTmp in the systemd service. So put it into the data directory:

sudo systemctl stop mariadb
sudo tee /var/lib/mysql/kh-reset.sql >/dev/null <<'SQL'
ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket
  OR mysql_native_password USING PASSWORD('NewRootPassword');
SQL
sudo chown mysql:mysql /var/lib/mysql/kh-reset.sql
sudo systemctl set-environment MYSQLD_OPTS="--init-file=/var/lib/mysql/kh-reset.sql"
sudo systemctl start mariadb

Be sure to clean up afterwards, otherwise the server runs with the file again on every start:

sudo systemctl unset-environment MYSQLD_OPTS
sudo rm /var/lib/mysql/kh-reset.sql
sudo systemctl restart mariadb

For MySQL 8.0 on Ubuntu the service is called mysql and the SQL line reads:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'NewRootPassword';

Variant 2: skip-grant-tables

This variant switches privilege checking off completely. Without --skip-networking, everyone who can reach the port would have full access to all data during that time. The option is therefore not optional but mandatory.

sudo systemctl stop mariadb
sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"
sudo systemctl start mariadb
sudo mariadb

In the session, load the privilege tables first, otherwise the server rejects ALTER USER with an error message:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket
  OR mysql_native_password USING PASSWORD('NewRootPassword');
EXIT;

And then back to normal operation:

sudo systemctl unset-environment MYSQLD_OPTS
sudo systemctl restart mariadb

If your distribution does not evaluate the variable MYSQLD_OPTS, the drop-in file at /etc/systemd/system/mariadb.service.d/reset.conf always works, with the content [Service] and Environment="MYSQLD_OPTS=--skip-grant-tables --skip-networking", followed by sudo systemctl daemon-reload. Delete the file afterwards and reload systemd again.

When the reset goes wrong

This is exactly where most guides stop. The four most common follow-up problems:

The service no longer starts. Usually a typo in the SQL file. The server then aborts during startup. Debian logs MariaDB to the journal by default, Ubuntu with MySQL additionally to a file:

sudo journalctl -u mariadb -n 50 --no-pager
sudo tail -n 50 /var/log/mysql/error.log

Where the server writes to can be looked up with SHOW VARIABLES LIKE 'log_error';. Do not expect a file path there, though: on the package installations of Debian and Ubuntu the value is empty, because the service runs with --skip-log-error. Everything then goes to standard error and ends up in the journal or in /var/log/syslog, retrievable with journalctl -u mariadb. To fix it, remove the --init-file environment variable and restart.

The server keeps running without privilege checking. That happens when the unset-environment was forgotten, and it is the most dangerous variant, because from the outside everything looks normal. Two checks:

systemctl show-environment
ps -o args= -C mariadbd

If skip-grant-tables turns up in either of the two outputs, privilege checking is still off. systemctl show-environment assumes that systemd runs as PID 1. On an ordinary server that is the case, in a container or on a system with SysV init the command does not exist. There you read the environment directly from the running process:

cat /proc/$(pgrep -n mariadbd)/environ | tr '\0' '\n'

You should not rely on the argument list alone either: started through systemd, ps often shows only /usr/sbin/mariadbd without a single option, while through a SysV init script the full list appears. Another indication: under skip-grant-tables, SHOW GRANTS answers with an error message instead of privileges.

AppArmor blocks the init file. The symptom is a server that does not come up for no apparent reason. A look at the kernel log clears that up:

sudo dmesg | grep -i denied

You lock yourself out completely. That happens when you switch root over to a plain password with ALTER USER ... IDENTIFIED BY, lose socket authentication in the process and then no longer remember the new password. The way out is the same reset once more, this time with the double variant unix_socket OR mysql_native_password shown above. Make a copy of the privilege tables before every such change, it takes seconds and saves hours when it matters:

sudo mariadb-dump mysql > /root/mysql-grants.sql

You can save yourself the otherwise usual option --single-transaction here. It does run without an error, but it has no effect, because the tables of the mysql database sit on Aria or MyISAM and are therefore not transactional.

On managed web hosting you have no system access and therefore none of these options. There, database users and passwords are reset through the hosting control panel. On a KVM root server or dedicated server from KernelHost you have full root access, and if the server can no longer be reached over the network, you get to the system through the console in the customer panel.

Checking that it is really fixed

The fact that a command runs without an error does not yet mean that the login works permanently. These four checks uncover the typical remaining faults.

First, the difference between USER() and CURRENT_USER(). The first function shows who you claimed to be, the second shows which account row the server actually uses:

mariadb -u app -p'password' -e "SELECT USER(), CURRENT_USER();"

If those are two different values, for example app@localhost and app@%, then your privileges take effect through a different row than you think. That explains 1044 errors occurring later on, before they show up in production.

Second, a real access to the target database instead of just a login, plus the evaluation of the return code:

mariadb -u app -p'password' my_db -e "SELECT 1;" ; echo "Exit code: $?"

Third, a restart of the service and then the same login again. That makes sure the change is really stored in the tables and not only in memory, and that no environment variable is left over from the reset:

sudo systemctl restart mariadb
sudo systemctl is-active mariadb

Fourth, the application itself. A successful test on the command line says little about a PHP application that connects as the web server's user and through the socket. So test under the same system user:

sudo -u www-data mariadb -u app -p'password' my_db -e "SELECT CURRENT_USER();"

Differences between the distributions

A guide that claims the same thing for all four systems is wrong in at least one place. This table sums up the relevant deviations:

Further differences that cost time in practice: the configuration files sit under /etc/mysql/mariadb.conf.d/50-server.cnf for MariaDB and under /etc/mysql/mysql.conf.d/mysqld.cnf for MySQL. The service names are mariadb and mysql respectively, with MariaDB additionally shipping an alias mysql. Since version 10.4, MariaDB no longer has a user debian-sys-maint, and the file /etc/mysql/debian.cnf points to root through the socket there. With MySQL on Ubuntu the maintenance user still exists, and it is the best emergency access when the root password is lost and you do not want to restart the server:

sudo mysql --defaults-file=/etc/mysql/debian.cnf

If you keep to this order, so first read the message carefully, then check the plugin and the host row, then the connection path, and only right at the end reset the password, then the vast majority of cases resolve within a few minutes and without downtime. The reset via skip-grant-tables is the last resort, not the first step.