MariaDB on Debian: Restore root user

root users on mariadb-server 10.4+ no longer have a password because they authenticate via a unix socket instead of a TCP network connection.

On our new server, someone accidentially dropped that user and when running mysql as root on the shell, we had to enter the username and password. This is not nice when migrating databases.

The Debian README for mariadb-server says:

You may never ever delete the mysql user "root".

The solution is to restore that user:

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED VIA unix_socket WITH GRANT OPTION;
FLUSH PRIVILEGES;

Written by Christian Weiske.

Comments? Please send an e-mail.