If you are like me, you wouldn’t want anyone with console access to your server to be able to have root credentials to the database engine if they own your server. The use of “mysql -u root” should not by default grant you root on the database server.
Well apparently in MariaDB 10.1.xx I found out this was the case, because of something they call “plugin” feature of “unix_socket” which is the default.
If you are seeing this on your system and want to confirm that’s why, use the following query in the mysql shell:
MariaDB [(none)]> select Host,User, Password,plugin from mysql.user;
+-----------+------+-------------------------------------------+-------------+
| Host | User | Password | plugin |
+-----------+------+-------------------------------------------+-------------+
| localhost | root | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 | unix_socket |
+-----------+------+-------------------------------------------+-------------+
So to fix this, reset or update your root password first then execute this inside the mysql shell:
UPDATE mysql.user SET plugin = '' WHERE user = 'root' AND host = 'localhost';