Reset the MySQL and MariaDB root password

The database root password is one of those passwords you set once during installation and never need again, because every application works with its own account. Until the day you actually do need it. And then you get this:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

The standard answer on the internet goes: stop the service, start it with --skip-grant-tables, set the password, done. That is true, but only half the story. This guide also explains why the command everyone copies has no effect on Ubuntu with MySQL 8, why in many cases you do not need a password at all, and what to do when the service refuses to start afterwards.

Check first: you probably do not need a password at all

On Debian and Ubuntu, the database root account has not been protected by a password for years. It is protected by the identity of the system user instead. MariaDB calls the mechanism unix_socket, MySQL calls it auth_socket. Both mean the same thing: whoever connects over the local socket and is already root at the operating system level gets in without a password. The reasoning behind it is simple. A system user root can reach all data files and the memory of the process anyway, so an extra password is not a real barrier.

Your first attempt should therefore always be this one:

sudo mariadb
sudo mysql

Note that sudo mysql -u root -p does not work, because the -p switches over to password authentication. That is exactly where most people get stuck. Without -p and with sudo, you normally land straight in the prompt. From there you set a new password in two seconds without touching the database at all.

The second escape route is the maintenance account of the distribution. On Ubuntu with the mysql-server package, the file /etc/mysql/debian.cnf still exists. It holds the credentials for an account with full privileges and is readable by root only:

sudo ls -l /etc/mysql/debian.cnf
sudo mysql --defaults-file=/etc/mysql/debian.cnf -e "SELECT current_user();"

If a row comes back, you are in and can carry on right away. Do not expect root@localhost here: the query answers with debian-sys-maint@localhost. That is correct, because this maintenance account holds ALL PRIVILEGES and is perfectly sufficient for the reset, but you are not root with it. This route is also specific to Debian and Ubuntu. On AlmaLinux, Rocky Linux and Oracle Linux, neither the directory /etc/mysql/ nor the account exists, and the socket login as root from the previous section applies there instead. From MariaDB 10.4 onwards this account is no longer created (the file usually just points to root over the socket), but on existing installations it is often still present. Checking costs nothing and, when it works, saves you the entire rest of this guide.

Which database is actually running here?

This is not a formality, it decides every command that follows. Debian has not shipped a mysql-server package in the official archive for years, so what runs there is practically always MariaDB, even though the command mysql exists. That command is only a symlink to the MariaDB client. Current distributions ship this:

Ask the server itself, not the client:

systemctl list-units --type=service --all | grep -Ei 'mysql|mariadb'
mysqladmin --version
mariadbd --version

The --all belongs there, because otherwise list-units only shows loaded, active units. In exactly the situation where you need the command, namely a service that is not running, the output would stay empty. mysqladmin --version exists on both sides and it names the engine in plain text, MariaDB systems answer with ... Distrib 11.8.6-MariaDB .... With mariadbd --version, a command not found on a pure MySQL system is the expected result and not an error, and the same applies in reverse for mysqld-specific commands on a pure MariaDB system.

If the unit is called mariadb.service, you are working with MariaDB. If it is called mysql.service, with MySQL. On systems with MariaDB there is an additional alias mysql.service that points to mariadb.service, which is why the output of systemctl list-units tells you more than the mere existence of a name.

The unit names themselves differ between the distribution families, and every systemctl call in this guide is written for Debian and Ubuntu. On the Red Hat family there is no unit called mysql.service at all, and systemctl status mysql answers with Unit mysql.service could not be found. In that case use the names from the right-hand column throughout, including in the path of the drop-in directory:

Lock it down first: why this step is not optional

With --skip-grant-tables the server does not read the privilege tables. That does not mean "root may get in without a password", it means "anyone may do anything, without a password, as any user". In this state there is no authentication and no privilege check, not for your customer databases either.

MySQL 8 automatically enables skip_networking in this case, so it no longer accepts TCP connections. Do not rely on that anyway, always add the option yourself. With MariaDB it is the documented recommendation regardless, and anyone who looks after both systems would rather not have to remember which of the two thinks along.

--skip-grant-tables --skip-networking

Two points that are easily overlooked. First: skip_networking only closes the network port. The Unix socket at /run/mysqld/mysqld.sock stays open, and on many systems it is reachable for every local user. A compromised PHP process running as www-data can read every database on the server during that window. So keep the window as short as possible and do not carry out the operation while a web server with unknown code is running. Second: stop everything that connects automatically beforehand, meaning web servers and application services. Their connection attempts do not just get in the way, in this state they run with full privileges.

If the server is reachable from outside, close the firewall as well. How to set that up properly and permanently is covered in setting up the UFW firewall.

sudo apt-get install -y ufw
sudo ufw deny 3306/tcp
sudo ss -ltnp | grep 3306

The first line is not redundant. On a minimal Debian installation ufw is not preinstalled, and the command would otherwise end with sudo: ufw: command not found. On AlmaLinux, Rocky Linux and RHEL there is no ufw at all, and packet filtering runs through firewalld instead:

sudo firewall-cmd --permanent --remove-service=mysql
sudo firewall-cmd --reload

On every Debian and Ubuntu installation we checked, bind-address is set to 127.0.0.1 anyway, confirmed with ss -ltnp. In that case the server does not accept connections from outside to begin with, and the firewall rule is a second safeguard, not the actual protection.

MariaDB: resetting the password

The MariaDB unit starts the server with ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS. That variable is intended for exactly this kind of situation, so you do not need to edit any file. Check it quickly, then you know that the route below works on your system:

systemctl cat mariadb | grep ExecStart

Then, in this order:

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

In the prompt, FLUSH PRIVILEGES comes first. Without this step the server does not have the privilege tables in memory at all and rejects any account management. After that you set the password:

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

This somewhat clunky syntax is deliberate, and it is the most important MariaDB-specific point in the whole guide. Since 10.4, MariaDB can hold several authentication methods per account, and that is exactly how root is set up after a package installation: socket first, password as a fallback. If you use the plain ALTER USER 'root'@'localhost' IDENTIFIED BY '...' instead, you replace the entire chain with pure password authentication. That works, but afterwards sudo mariadb no longer succeeds without a password, and internal maintenance scripts of the distribution that rely on the socket run into a wall. You have then only postponed the problem by a year.

Finally, clear the special state again:

sudo systemctl stop mariadb
sudo systemctl unset-environment MYSQLD_OPTS
sudo systemctl start mariadb

Do not forget unset-environment. The variable is attached to the systemd manager, not to the service, and it survives every restart of the service. If a package update restarts MariaDB during the night, your database keeps running without any privilege check from that point on, and nobody notices. Only a reboot of the server clears the variable by itself.

MySQL 8: the command from most guides does nothing here

For MySQL, the same recipe with systemctl set-environment MYSQLD_OPTS=... is doing the rounds. It comes from Oracle's documentation and matches their own packages. The unit from the Ubuntu archive looks different though, it simply says ExecStart=/usr/sbin/mysqld, without a variable. The command runs through, reports no error, and the server still starts perfectly normally with the privilege check active. You then sit in front of an Access denied and wonder why. Check it yourself:

systemctl cat mysql | grep ExecStart

If no $MYSQLD_OPTS shows up there, you need a drop-in. And if you are creating one anyway, take the better route straight away: --init-file. With it the server starts perfectly normally with the privilege check and executes an SQL file with full privileges while it boots up. There is no open window in which anyone could get in without a password. Oracle explicitly recommends this variant over --skip-grant-tables.

sudo systemctl stop mysql
printf "ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'YourNewPassword';\n" | sudo tee /var/lib/mysql-files/kh-reset.sql
sudo chown mysql:mysql /var/lib/mysql-files/kh-reset.sql
sudo chmod 600 /var/lib/mysql-files/kh-reset.sql
sudo mkdir -p /etc/systemd/system/mysql.service.d

The IDENTIFIED WITH caching_sha2_password is the decisive part and the reason why countless guides fail at this point without showing an error. On Debian and Ubuntu, root@localhost uses the auth_socket plugin with MySQL as well. A plain IDENTIFIED BY 'password' does set the password hash, but it does not switch the plugin. Afterwards mysql.user still says auth_socket, the service starts cleanly, not a single error message appears, and yet every password login ends with ERROR 1698 (28000): Access denied for user 'root'@'localhost'. Particularly treacherous: whoever runs the counter check as the system user root is waved through by auth_socket without a password and believes the reset worked. So test from a different account or over TCP. Only for very old clients that cannot handle caching_sha2_password should you use mysql_native_password instead, with the limitations described in the paragraph further below.

The directory /var/lib/mysql-files was chosen deliberately: it belongs to the database user and is allowed in the AppArmor profile of mysqld. If you put the file in /root or /tmp, the start may fail because of AppArmor, and the message in the log is not much help. Now the drop-in:

[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld --init-file=/var/lib/mysql-files/kh-reset.sql

The empty first ExecStart line is mandatory, otherwise systemd appends your command to the existing one and refuses the service with a configuration error. Save it as /etc/systemd/system/mysql.service.d/override.conf, then:

sudo systemctl daemon-reload
sudo systemctl start mysql
sudo mysql -u root -p

If the login works, remove both again, the SQL file and the drop-in:

sudo rm -f /var/lib/mysql-files/kh-reset.sql
sudo rm -f /etc/systemd/system/mysql.service.d/override.conf
sudo systemctl daemon-reload
sudo systemctl restart mysql

If you want the classic route anyway, replace the line in the drop-in with ExecStart=/usr/sbin/mysqld --skip-grant-tables --skip-networking, connect with sudo mysql and there run FLUSH PRIVILEGES; first, then ALTER USER. A note on the encryption: MySQL 8 uses caching_sha2_password by default. If a very old application then reports "The server requested authentication method unknown to the client", IDENTIFIED WITH mysql_native_password BY '...' helps. That is a dead end though, because this method has been deprecated since 8.0.34 and is no longer included in MySQL 8.4. Updating the client is the better answer.

Error messages verbatim

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement. You forgot FLUSH PRIVILEGES;. Run it, then ALTER USER works.

ERROR 1288 (HY000): The target table user of the UPDATE is not updatable. You are following an old guide that suggests UPDATE mysql.user SET password=.... From MariaDB 10.4 on, the privileges live in mysql.global_priv, and mysql.user is only a view on top of it. Use ALTER USER or SET PASSWORD. As an aside: writing directly into the privilege tables was always a fine way to wreck the account for good.

ERROR 1698 (28000): Access denied for user 'root'@'localhost'. Not a wrong password, the opposite: the account expects socket authentication, and either you are not running as the system user root or you passed -p. Try again with sudo and without -p.

ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded. The account points to a plugin that the running server does not know, which is typical after a switch from MariaDB to MySQL or after copying a data directory. Move the account to a suitable method using the route above.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2). The server is not running. Look at systemctl status and the log first, do not keep experimenting on the client.

mysqld: Can't create directory '/run/mysqld/' (Errcode: 13 - Permission denied) or a start that aborts immediately. This happens when the server was started by hand instead of through systemd, because the runtime directory is then missing. The fix:

sudo mkdir -p /run/mysqld
sudo chown mysql:mysql /run/mysqld

Job for mysql.service failed because the control process exited with error code. On its own this message says nothing. The actual cause is in the journal and in the error log:

sudo journalctl -u 'mysql*' -u 'mariadb*' -n 60 --no-pager

The pattern with both unit names is deliberate, because the obvious single query leads into a silent trap. On Debian with MariaDB, mysql.service is only an alias for mariadb.service. systemctl resolves the alias, but the journal indexes the entries under the real unit name, so journalctl -u mysql answers with -- No entries -- and exit code 0 even though the journal is full. The other way around, journalctl -u mariadb on an Ubuntu system with MySQL returns -- No entries -- just the same.

It is much the same with the error file. The /var/log/mysql/error.log quoted everywhere only exists on Ubuntu with MySQL. On Debian with MariaDB the directory /var/log/mysql/ does not exist at all, because log_error is commented out in 50-server.cnf and MariaDB writes to the journal. The matching line for each system:

If you would rather not guess the path, ask the server itself: sudo mariadb -e "SHOW VARIABLES LIKE 'log_error';" or the same with mysql.

The three most common causes at this point: a typo in the drop-in (systemd then reports the line), a full disk (see disk full on Linux) or a second server process that is still running and keeps the data files locked. You check the latter with pgrep -a mariadbd or pgrep -a mysqld before you start again.

If you get in after the reset but your applications are still being rejected, the problem lies elsewhere: applications such as WordPress or Nextcloud use their own database users, not root. Details on that in fixing MySQL Access denied for user.

How to tell that it really worked

A successful login on its own is not proof, because in the emergency state it works without any password at all. So check four things once the service is running normally again.

First: no special configuration is in place any more. The first output must be empty, the second must no longer show any additional options.

systemctl show-environment | grep MYSQLD_OPTS
systemctl cat mariadb | grep ExecStart

Second: the privilege check is active again. A login with a deliberately wrong password must fail. If it goes through, the server is still running wide open.

Third: the new password and the expected method are stored in the account. In MariaDB you query it like this, in MySQL without the JSON column:

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

Fourth: the network port behaves the way it did before. A database server that is only used locally should listen exclusively on 127.0.0.1 again after the cleanup:

sudo ss -ltnp | grep 3306
grep -rs bind-address /etc/mysql/ /etc/my.cnf /etc/my.cnf.d/

The -s and the three paths are deliberate. grep -r bind-address /etc/mysql/ on its own aborts on the Red Hat family with No such file or directory, because there is no /etc/mysql/ there. With -s, grep silently skips the paths that do not exist, and the line fits both worlds.

Clean up so it does not happen a second time

The password may now be sitting in places you are not thinking about. A command with -e "ALTER USER ... IDENTIFIED BY '...'" ends up in ~/.bash_history, a command typed at the prompt ends up in the history file of the database client. Both need cleaning up:

history -c
rm -f ~/.mysql_history ~/.mariadb_history

Both file names are needed, because the client changed the name. Up to MariaDB 10.11, which includes Debian 12, it writes to ~/.mysql_history. From MariaDB 11 on, meaning from Debian 13, it writes to ~/.mariadb_history. Anyone who deletes only the old file there leaves the password they typed in plain text sitting on the disk without noticing. Better still is to not let the history come into existence in the first place: export MYSQL_HISTFILE=/dev/null or export MARIADB_HISTFILE=/dev/null before the session, or go straight for the --init-file route from the MySQL section, where the password never passes through an interactive session at all.

More sensible than a password you will have forgotten again in a year is a setup that does without one in daily operation. On Debian and Ubuntu that means: root stays on unix_socket or auth_socket, and for everything else you create normal users with exactly the privileges the respective application needs. If you need access from another machine, tunnel it over SSH instead of opening port 3306, see connecting to your server over SSH.

If you are working on the database anyway, take care of the rest at the same time: remove anonymous users, drop the test database, switch off remote access for root. mariadb-secure-installation or mysql_secure_installation handles that in a few minutes, described in detail in securing MariaDB and MySQL. And because the password is rarely the only thing in a bad state on a server you have just taken over, a run through the checklist for new root servers is worth the time.

One last thought on the order of things: before you put a running production server into the privilege-free state, make a backup of the data directory or take a snapshot. The reset itself is harmless, but a service that no longer starts after a typo in the unit is anything but harmless at three in the morning.