I am having trouble with a MariaDB install. I have blown away a previous install of Linux Mint and replaced it with a 19.2-64. I installed the cinnamon version, but then added the Mate desktop. MariaDB did install, but it never asked me to set the root password. This is on a i7-8700 cpu. I would really prefer to get Mariadb working, but if I have to I can try to get mysql working. My comments will be in italics bold, my commands will be in bold.

I have used the following website to reset the password as refrence:

https://www.digitalocean.com/communi...-root-password




terry@cloudedleopard:~$ mysql --version
mysql Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
terry@cloudedleopard:~$ sudo systemctl stop mariadb
terry@cloudedleopard:~$ sudo mysqld_safe --skip-grant-tables --skip-networking &
[1] 2028
terry@cloudedleopard:~$ 200513 18:19:23 mysqld_safe Logging to syslog.
200513 18:19:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

At this point the command prompt just halts, so I fired up another one and continued on with the instructions


mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

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

First I flush the privileges

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Then I try to alter the password

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypass';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'root'@'localhost' IDENTIFIED BY 'mypass'' at line 1

According to the web site, that was supposed to work with this version. But just in case I try the command that is supposed to work on older versions.

MariaDB [(none)]> set password for 'root'@'localhost' = password(mypass');
ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings

Finally, I run the third option:

MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD('mypass') where User = 'root';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 Warnings: 0

I have tried, but can not stop the temporary MariaDB, finally I reboot the whole system.

MariaDB [(none)]> exit
Bye
terry@cloudedleopard:~$ sudo kill `/var/run/mariadb/mariadb.pid`
bash: /var/run/mariadb/mariadb.pid: No such file or directory
[sudo] password for terry:

Usage:
kill [options] <pid> [...]

Options:
<pid> [...] send signal to every <pid> listed
-<signal>, -s, --signal <signal>
specify the <signal> to be sent
-l, --list=[<signal>] list all signal names, or convert one to a name
-L, --table list all signal names in a nice table

-h, --help display this help and exit
-V, --version output version information and exit

For more details see kill(1).

After rebooting, I try to log in:

terry@cloudedleopard:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Any suggestions???

I have mariadb version 10.1 running on Mint 19.3. Not that it will solve your problem, but I suggest taking a look at this tutorial: How to Install MySQL on Ubuntu 20.04

In installing mariadb, I had problems, some of these problems were caused by "old" tutorials. One type of problem had to do with changes in login security. The reason for citing the tutorial above is that it is recent. So I suggest looking for recent tutorials. A couple have been posted in the Syndicated News Sub-forum.

My first piece of advice: Do NOT try to change or assign a root password after installing mariadb. What I suggest is that you immediately create a new user. Grant that new user all permissions. Do all your work under that user's credentials until you feel comfortable with how things are set-up. I did eventually change/assign a root password after installing PHPMyAdmin. My experience is limited, but it appeared to me that changing the root password must be done from PHPMyAdmin (even though you should be able to do it under mariadb itself).

A freshly installed MariaDB uses the Unix-Socket plugin for authentication. That means, in short, that it queries the system for the user's UID and uses that to log in rather than a password. It is supposedly as or more secure than a password. In my opinion the jury is still out, but it does make a bit of sense.

Any way, to your problem. In a freshly installed MariaDB you must initially sign in as root, i.e. you must BE root to sign in.You will NOT be asked for a password. If you already are root then you obviously are authorized to be root, etc., etc., so you are permitted entry. So, either su to root or use sudo: and you will be able to get in.

From there you can create other users and do anything you wish. To understand the system and/or change back to the old way see . It can be a little confusing but they know what they are talking about and their instructions work.

That was the information I needed, thank you.

Terry

I went back and played with it a bit. Any user can use the unix_socket authentication. If you have an existing user you want to change to the new system, as root in mariadb Now the user can sign in three ways when logged into the system:The password in the third instance is the password to log into the system, not mariadb. There is no separate password to the database. Note that you can only sign in when you are signed into the system as that user. Being logged in as george and then trying mysql -u fred -p won't work.

I haven't tried logging in remotely yet. I can see that could possibly be a problem.

I hope this helps.

1 members found this post helpful.

Interesting. I immediately stopped experimenting after getting my installation of mariadb to finally work. Didn't want to put up with the anguish of "breaking" it again. Thanks for the feedback.

More information: I tested remote logins. Logging in to the remote machine by ssh and then starting MariaDB with unix_socket works. However, trying it all in one command does not. Otherwise, to log in remotely it appears you have to be set up to use a password. I was unable to login remotely with any form of the unix_socket. The only method seemed to be the old fashioned password. Of course the user had to exist for the % host. A user for the % host cannot exist for both unix_socket and password. Any attempt to create a user for the % host by one method will fail if the user exists for the other method. It also appears that a user with only a % host line (no localhost) can still sign in from localhost but not remotely at all.

In summary, then, it appears the unix_socket works from localhost only, regardless of the form of the user entry record. However, to login remotely the user MUST have a password % entry.

I had been intending to check all this out but never got around to it. Thank you for your question which encouraged me to finally do it.

1 members found this post helpful.

Just to add additional information to this useful thread. You can remotely log into mariadb with Linux Base by using: MariaDB Connector/J 2.6 Series.

The libreoffice-mysql-connector that comes with Mint 19.3 also works to connect Linux Base (even remotely) to mariadb.

1 members found this post helpful.