Access Denied for root@localhost After the MariaDB 10.4 Upgrade

An upgrade goes through cleanly, the application keeps serving traffic, and then you go to take a backup and cannot get into your own database:

$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The password is right. You have used it for two years. You paste it, type it, check the keyboard layout, and it keeps failing. Then somebody on the internet tells you to reset the root password with --skip-grant-tables, and now you are stopping a production database in single user mode to solve a problem you do not have.

What actually happened is that MariaDB 10.4 changed how the root account authenticates. It now uses the unix_socket plugin, which ignores passwords entirely and checks which operating system user is behind the connection. Your password is not wrong. It is not being consulted.

sudo mysql

CREATE USER 'app'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON app_db.* TO 'app'@'localhost';
FLUSH PRIVILEGES;

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

Why sudo mysql works and mysql -u root -p does not

Two authentication plugins, two completely different questions.

mysql_native_password asks "can you prove you know the password for this account". It works over any connection, including TCP from another machine.

unix_socket asks "which operating system user opened this socket connection". The kernel answers that question, not the client, so it cannot be faked and there is no password to steal. If the OS user matches the database user name, you are in. Running sudo mysql makes you the OS user root, so you match root. Running mysql -u root -p as yourself does not, and no password can rescue it.

Confirm what your accounts are actually using:

SELECT user, host, plugin FROM mysql.user;

+-------------+-----------+-----------------------+
| user        | host      | plugin                |
+-------------+-----------+-----------------------+
| root        | localhost | unix_socket           |
| mysql       | localhost | mysql_native_password |
| app         | localhost | mysql_native_password |
+-------------+-----------+-----------------------+

Option 1: leave root alone and create a proper user

This is the right answer in most cases, and the upgrade has done you a favour by exposing the problem. If your application connects to the database as root, that is the actual defect. A bug in one report should not be able to drop another company's database on the same server.

CREATE USER 'app'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'localhost';
FLUSH PRIVILEGES;

Note the grant names the one database, and does not include DROP or ALTER unless your application performs migrations at runtime. Software that runs schema changes on deploy needs those; software that does not, should not have them.

Option 2: keep the socket and add a password

Some tooling insists on a password for root. Backup scripts, phpMyAdmin, and the Frappe bench commands that create and restore sites all ask for the MariaDB root password and fail with error 1698 when the account will not take one. MariaDB 10.4 lets an account carry more than one authentication method, so you do not have to give up the socket:

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

Now sudo mysql still works without a password, and mysql -u root -p works with one. If instead you want the old behaviour exactly, drop the socket half:

ALTER USER 'root'@'localhost'
  IDENTIFIED VIA mysql_native_password USING PASSWORD('secret');
FLUSH PRIVILEGES;

I would not. Keeping the socket method means there is always a way in from the console even when the password is lost, which is a much nicer position than the one that starts with stopping the database.

Where the privileges actually live now

10.4 moved account privileges into a table called mysql.global_priv, storing them as JSON, and turned mysql.user into a view over it for compatibility. This is why the ten year old advice on the internet no longer works:

UPDATE mysql.user SET password = PASSWORD('secret') WHERE user = 'root';

That statement is from a MariaDB that no longer exists. Use ALTER USER or SET PASSWORD. If you want to see what is really stored:

SELECT user, host, JSON_DETAILED(priv) FROM mysql.global_priv WHERE user = 'root'\G

The one case where the drastic method is correct

There is a real situation behind the advice I dismissed at the top: you have genuinely lost the root password, and there is no account with socket access you can reach. Then you do need to start the server without authentication, and it is worth doing properly rather than from memory.

Take the machine off the network first, or bind the server to the loopback address, because for the next two minutes anybody who can connect is root:

sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost'
  IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD('new-secret');

The FLUSH PRIVILEGES before the ALTER is not optional. With --skip-grant-tables the privilege system is not loaded, and account management statements fail until you load it. Then shut that instance down and start the service normally:

sudo mysqladmin shutdown
sudo systemctl start mariadb

Two minutes, offline, and only when there is no other way in. Compare that with typing sudo mysql, and you can see why the internet's default answer to error 1698 annoys me.

Verification

sudo mysql -e "SELECT CURRENT_USER()"          # root@localhost
mysql -u root -p -e "SELECT CURRENT_USER()"    # root@localhost, if you added a password
mysql -u app -p app_db -e "SELECT 1"           # the application account works

Then test the thing that failed in the first place, which for me is always the backup:

sudo mysqldump --single-transaction --routines --triggers app_db > /tmp/test.sql
head -n 3 /tmp/test.sql
ls -lh /tmp/test.sql

A backup script that has been failing quietly since the upgrade is the real danger here, not the login prompt. Check that its last successful output is dated after the upgrade, and if your backups run as a cron job as root, note that they will keep working through the socket while your interactive login does not, which is exactly the sort of asymmetry that hides a broken job for months.

What people get wrong

Starting the server with --skip-grant-tables. This is the top result for the error message and it is wildly disproportionate. It disables authentication for every account while the server is running, on a machine that may be reachable from the network, to solve a problem that sudo mysql solves in one command. Reserve it for a genuinely lost root password with no working sudo.

Updating mysql.user directly. It is a view now. The statement either fails or does not do what you think, and then you are debugging privileges rather than fixing them.

Giving root a password and pointing the application at it again. You have restored the old convenience and the old blast radius. Create the application user; it takes one minute.

Assuming the same rules on MySQL. MySQL made a related change, defaulting root to the auth_socket plugin, and MySQL 8 also changed the default password plugin to caching_sha2_password, which older clients cannot speak at all. Different products, different details, same lesson: check the plugin column before you touch a password.

When it is still broken

• sudo mysql also fails. The server is not running, or it is listening on a socket path your client does not expect. Check systemctl status mariadb and compare the socket setting in the server and client sections of the configuration.
• Access denied from another machine. Unrelated to any of this. An account is identified by user and host together, and 'app'@'localhost' does not authorise a connection from another IP. You need a separate grant, and you need to be sure you want the port open at all.
• An application connects but sees no tables. It authenticated as a different account from the one you granted. SELECT CURRENT_USER() from inside that connection tells you which, and it is frequently the anonymous account.
• Everything authenticates and queries behave oddly instead. That is a different class of problem, usually the server's SQL mode changing across versions, which I went through in the note on ONLY_FULL_GROUP_BY.