Notes on how I set password for Mariadb 10.5 FBSD 12.2

Some notes here for me:

1) When i installed mariadb and after running mysql_secure_installation , it would still allow root access to mariadb without a password.
2) Part of the reason for this is I was logged in as root #, the other part is I do not have sudo installed.
3) There is a way to fix this. Run the following commands:
Code:
root# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.5.10-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]>ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("password");
Query OK, 0 rows affected (0.001 sec)

root@localhost [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

root@localhost [(none)]> quit
Bye

root@nexus:~ # mysql -u root -p  
Enter password:  wrong password
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)


root@nexus:~ # mysql -u root -p
Enter password: correct password
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.5.10-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]>


These are notes for me so if I have this problem again. I will search the forums see this. Know I did it and feel silly for having to look up my own notes.
 
Best practice for "no password" is to use a ~/.my.cnf file; Content example:
INI:
[client]
password=your_password
So a password remains, but you don't have to type it into a command line, and it doesn't appear in a history or be hardcoded in a script.

I haven't used "ALTER USER" statements to adjust access permissions, but "GRANT" for user access (root is only needed once to set up your user); Examples:
SQL:
GRANT USAGE ON *.* TO username@localhost IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO username@localhost IDENTIFIED BY 'password';
Note that the "username" has nothing to do with the servers users - databases usually have their own user management. More about "GRANT" can be read here: https://mariadb.com/kb/en/grant/
 
mysql_secure_installation

Enter current password for root (enter for none):
OK, successfully used password, moving on...


Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.


You already have a root password set, so you can safely answer 'n'.


Change the root password? [Y/n] n
... skipping.

On the "Change the root password?" press "Y" and change your password.
 
As far as my tests where going you can always use 127.0.0.1.
I avoid sockets & localhost
You have sometimes to edit, my.cnf and edit
Code:
bind_address=127.0.0.1
There is also a php configuration file for authentication.
 
I don't consider sockets more secure than the use of a non-routable IP-address.
And once you have a configuration for an IP-address/host it is easily modified for another one.
 
Back
Top