For some unknown reason when I installed mariadb (currently ver 10.5 on my Manjaro linux). it wouldn't let me login without sudo. So i investigated (and i used that that authentication plugin unix_socket and mysql_native_password etc thing; to no avail unfortunately, because user table is no longer a table but a view); later I find out using MariaDB authentication mariadb documentation; that the password for root and mysql are invalid in the mysql.grobal_priv table.
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');
and also changed mysql password
SET PASSWORD FOR 'mysql'@'localhost' = PASSWORD('pass');
Now i can login without password, but my question is
- why did it happened. I did set the password on mysql_secure_installation command.
- Is setting this way (which sets authentication_string and Password same) is all I needed to do.
FYI: I did used special symbols !@ in my password. Maybe bash console had some part in this invalid password initialization. Just guessing
For a brute-force approach on MariaDB < 10.4 (for later versions see MariaDB-10.4+ user control for better methods - eg. "SET PASSWORD" or "ALTER USER" etc.!):
In answer to question 1: The default access for a new MariaDB install is root IDENTIFIED VIA unix_socket (hence sudo required). So even though you did set a password during the install, it isn't used (i.e. invalid). It is done this way to be secure (no remote access), and also so that other processes running as root can access the server without a password (upgrade checks etc.).
In answer to question 2: Yes, it is that simple, see below. But be aware that you may need to update the password for the system maintenance task - check your logs (systemctl status mariadb.service).
To change for 'root' anyway:
or
Then restart mariadb:
And voila you will have access from your personal account via "mysql -u root -p"
To change back:
I generally find that it is better to leave the 'root' user as is (socket), and simply create a new "root" user ("super", "administrator", "superadmin" etc.) with GRANT ALL PRIVILEGES ON ., which uses a password to authenticate if required..