OS - on a Pi Description: Raspbian GNU/Linux 10 (buster) Release: 10
mysql (mariaDB) version: mysql Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2
Via other online answers to this question, I've attempted the following steps:
sudo systemctl stop mysql.service
sudo ps aux | grep mysql
sudo mysqld_safe --skip-grant-tables --skip-networking &
sudo ps aux | grep mysql
mysql -u root
MariaDB [defaultDB]> use mysql;
MariaDB [mysql]> UPDATE user SET authentication_string = password("NEWPASSWORD") where User='root';
and in response to this critical change, I get the following results:
Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0
then I go on to the subsequent steps:
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> quit
sudo ps aux | grep mysql
mysqladmin shutdown
sudo ps aux | grep mysql
sudo systemctl start mysql.service
sudo ps aux | grep mysql
(the "sudo ps aux | grep mysql" commands are of course there to confirm the daemons have been shut down and the correct ones/states have been started)
and then I attempt to log onto mysql using the password I've just set:
mysql -u root -p
Enter password:
I enter the password just set above and get:
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'defaultDB'
I've seen some comments referring to TCP vs socket but I'm not sure how to address that, if that bears on the issue. What am I doing wrong?
First of all, don't modify the mysql.user table directly, instead of use CREATE, ALTER or DROP USER commands. In recent versions of MariaDB mysql.user is a view which can't be modified.
On Debian the default authentication method for socket connections (localhost) is handled via unix_socket authentication plugin: The plugin allows it to retrieve the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid. Once it has the user name, it will authenticate the connecting user as the MariaDB account that has the same user name.
so a simple sudo mysql -uroot should work to connect as root user.