MySQL initial installation

Question

I know there are many, many threads on this, but hear me out before marking it as a duplicate.

On an Raspberry Pi running stretch

installed mysql

sudo apt-get install mysql-server

No errors

pi@raspberrypi:~ $ mysql -u root

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Then I tried

pi@raspberrypi:~ $ mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB 
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current password 
for the root user. If you've just installed MariaDB, and you haven't set the 
root password yet, the password will be blank, so you should just press 
enter here.

Enter current password for root (enter for none):

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Enter current password for root (enter for none):

Now I had pressed enter, no password which should be the case since it is a brand new install.

Now, If I run the same install script with sudo

pi@raspberrypi:~ $ sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB 
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current password 
for the root user. If you've just installed MariaDB, and you haven't set the 
root password yet, the password will be blank, so you should just press 
enter here.

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.

Set root password? [Y/n]

again, I used not password, just pressed enter

And I can continue Down through setting up new password, disabling remote login, etc.

However when it is all done, and I try and log in....

Reload privilege tables now? [Y/n] Y ... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB 
installation should now be secure.

Thanks for using MariaDB! pi@raspberrypi:~ $ mysql -u root -p Enter 
password: ERROR 1698 (28000): Access denied for user 'root'@'localhost'

pi@raspberrypi:~ $

I am sure I used the correct password (and have actually gone through this process a couple times).

I can login to mysql with just sudo any no password, but this does not solve the problem of needing non sudo access.

pi@raspberrypi:~ $ sudo mysql Welcome to the MariaDB monitor. Commands end 
with ; or \g. Your MariaDB connection id is 26 Server version: 10.1.23-
MariaDB-9+deb9u1 Raspbian 9.0

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

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

MariaDB [(none)]>

I have gone through other examples of how to reset the password (--skip-grant-tables and such) with no luck. But even still I don't really want to RESET any passwords, I am starting with a fresh installation.

I had seen somewhere that the default login for root wasn't any password, but was authenticated via a socket connection, but I have not been able to find out more about that.

Answer 1

mariadb by defaults uses UNIX_SOCKET plugin to authenticate user root. https://mariadb.com/kb/en/mariadb/unix_socket-authentication-plugin/. It works by retrieving uid of the process that has connected to the socket and allowing to connect to the MariaDB account with the corresponding user name. In another word, if you are running your RPi as pi user, when you run

mysql -u root

the uid is not root. So you need to be root to run the command:

sudo mysql -u root

The default password for root is blank.

By the way, once you log in, you can verify that root indeed uses unix_socket by running this mysql command:

MariaDB [(none)]> SELECT user, host, plugin FROM mysql.user;
+---------+-----------+-------------+
| user    | host      | plugin      |
+---------+-----------+-------------+
| root    | localhost | unix_socket |
+---------+-----------+-------------+
2 rows in set (0.00 sec) 

You should create a user for your current user, and grant all the privileges to the user, this will allows you to access the database server without sudo.

Remember to restart the service at shell:

sudo service mysql restart

And finally, you should run mysql_secure_installation to disable root access.