Running MariaDB 10.1.23-MariaDB-9+deb9u1 on Debian 9.1. Fresh OS installation, installed MariaDB with apt-get install mariadb-server mariadb-client

Apparently MariaDB doesn't ask for a root password on install so I'm going to set it after the fact:

# mysql -uroot
> select user from mysql.user;
| user |
| root |

Ok, so root exists. Now to change its password:

> set password for 'root'@'localhost' = PASSWORD('P@ssw0rd');
> flush privileges;
> exit

Did it work?

# mysql -uroot -pblabla
MariaDB [(none)]>

Setting the password went ok but why is MariaDB accepting any random password and even an empty one?

This installation doesn't accept ALTER USER statement.


The answer: https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/:

Apparently the mysql-server installation on 16.04 (or any 5.7 installation?) allows root access not through password, but through the auth_socket plugin. Running sudo mysql -u root (n.b. w/o a password) will give you mysql console whereas running the command as non-root prompts you for a password.

It would seem that changing the password doesn't make much of a difference since the auth backend doesn't even check for a password.

To disable this auth_socket plugin, on the mysql prompt do

update mysql.user set plugin=null where user='root';
flush privileges;

This makes MariaDB also ask a password for [Linux] root.

Thanks jesse-b and derobert for the in-depth discussion and your answers.

  • this is the best answer WORKING i found, without this command, mariadb will not work if you use phpmyadmin ! – Abdelouahab Apr 17 '18 at 20:44

also check your my.cnf if it has "skip-grant-tables" if yes, remove it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.