Default mySQL user behavior not matching online guides

Wiley Rush

11/30/23, 8:05 PM

My issue resembles this one, this, and this but is not the same. It includes this problem.

I am installing mySQL on a fresh Ubuntu 22.04 per this guide from DigitalOcean.

It is very straightforward, mostly just apt install everything including mysql-server, and I have followed it exactly except for using apt-get. However some inconsistencies appeared:

After apt install mysql-server, I am unable to run sudo mysql_secure_installation. After entering a password for the mySQL root user, I got this error mentioned earlier which was solved by the solution in that answer, which is to run sudo mysql and ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'supersecretpassword'; after which I can finish sudo mysql_secure_installation.

Now, the guide indicates I should be able to run sudo mysql with no user or password. I can't. I can run sudo mysql -p and only if I provide the mySQL root user password at the prompt.

The guide even adds this comment:

...you didn’t need to provide a password to connect as the [mySQL] root user, even though you have defined one when running the mysql_secure_installation script. That is because the default authentication method for the administrative MySQL user is unix_socket instead of password.

Not for me. In fact if I try to run sudo mysql -u unix_socket or add -p, I cannot log in.

Why is mySQL behaving differently for me? Does this indicate a specific problem? I have replicated this issue on 5 different machines, all fresh installs of 22.04 and apt-get upated.

0 + 6

server

lamp

mysql

Ray

11/30/23, 8:52 PM

I haven't done the above in a long time. But, based on how you asked your question, you're assuming that DigitalOcean is correct -- as if it's a gold standard. It's possible it is wrong. Since it is a fresh install that you've also replicated 5 times, my conclusion from what you've described is just that DigitalOcean is wrong. That is, rather than asking why what you're seeing doesn't match online information, you should just ask how to enable passwordless access of MySQL. As an aside, is having the password in a file an acceptable solution for you?

Ray

11/30/23, 8:53 PM

I meant by having a `.my.cnf` [file](https://serverfault.com/questions/399262/connect-to-mysql-through-command-line-without-using-root-password).

Rinzwind

11/30/23, 8:58 PM

you do not use `sudo mysql` you use `sudo -i` and use a `#` to start `mysql` command line, Then setup mysql by adding your use to use and edit my.cnf to match that.

Wiley Rush

11/30/23, 11:01 PM

Ray, thanks for your comment, I'll live with this conclusion of the blog being wrong, after all they make duplicate articles for different ubuntu versions and software stacks and likely copy-paste old sections. I followed your suggestion regarding .my.cnf as well, and it works, though I suspect I'll need a well-thought-out system of users and access levels now. Rinzwind, thanks also, I don't understand but I suspect that is because your comment is condensed ideas that are new to me.

matigo

12/1/23, 12:31 AM

@WileyRush were you able to complete the installation of MySQL? Or are you still looking for an answer that will allow you to install the database engine and have it properly configured?

Wiley Rush

12/1/23, 3:22 AM

I think I am finished for now. Do I need to update the question with something?

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Default mySQL user behavior not matching online guides

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.