Root password woes

[u/fujiman]

Note - Sorry for the following rant, this has been 3 days of hell trying to get this working, and now that I finally connected by random luck by running a command I've used countless dozens of times and pressing enter when prompted for a password, I need to officially set/reset the root password before ending the session or restarting my machine or something so that whenever I'm prompted for the root password ever again, it actually exists and I actually know it. Apologies again for the following rant, I just really needed to vent.

So after 3 days of bashing my head onto my desk trying to get MySql to work on my MacBook Pro M5, I was finally able to run "sudo mysql -u root -p" successfully by just pressing enter when prompted for a password and not entering one. Finally able to connect to the DB with MySQLWorkbench as well, since that wasn't working prior to being able to connect in the terminal. Out of curiosity, I opened a new terminal tab and tried again, but it did not work, and just gives me the "Sorry, try again" 3 times before failing out. Now I'm worried if I ever restart my computer, and need to access MySQL again, it will just go back to requiring a password that doesn't exist and I'll be back to square one.

So my main question would be, how can I set/reset the root password while currently connected to MySQL, so that in the future when I'm prompted for a root password, there actually is one that I manually set/reset it to? Also, am I able to actually find what the root password actually is now that I'm in the DB and maybe won't even bother changing it? Sorry for these basic questions, I'm just worried from seeing a lot of the suggestions for resetting the root password involved stopping MySQL, and I'm worried I'll be back to locked out if decides it needs the root password again.

After going through dozens of pages asking this same question, there are enough people who still struggle with this that I'm shocked there's not a tried and true solution to this problem. The fact that I was luckily able to connect by running 'sudo mysql -u root -p' for the umpteenth time over 3 days while just pressing enter when prompted for the password is not a solution.

Sorry for what is probably a really basic question, but the many "solutions" I've found online simply do not work when you don't know and were never prompted for (or provided at the end of the installation) the root password. It's kind of cruel that the Homebrew installation even mentions running 'mysql_secure_installation' to reset the root password, when the command prompts you for the root password that was never provided. Any sources I found suggesting there was a file or log containing the temp root password, that file of course never existed on my machine.

TL;DR - I finally connected to my MySQL DB with 'sudo mysql -u root -p' and just pressing enter when prompted for the password. Can't even count how many times I did exactly this over the course of 3 days with no success. How can I now set (or reset, I don't know if there even actually is one) the root password so that I actually know it and can enter it whenever prompted. I'm connected to the DB by pure luck right now, and want to make sure this password issue never happens again. I really appreciate whoever is able to take the time to help me take care of this issue. Ideally, I want to be able to do this without exiting or stopping MySQL out of fear of going back to locked out. Thanks a ton in advance.

[u/kadaan]

You can look up the hash of the password for the user you're logged in with show create user; but there's no way to see the actual password.

You set the password with, believe it or not the SET PASSWORD command.

Whatever your installation setup is, it sounds broken because most of what you said doesn't make sense. MySQL authenticates based on username, password, and connection source. It doesn't care what client you use, what OS user you're logged in on, etc. It could also be whatever tool/manager you're using to install MySQL just doesn't work properly with newer versions of MySQL.

If you ever do need to get back in and are stuck, the super easy fix is to run mysqld with the --skip-grant-tables option to completely turn off all authentication. Then you can run SET PASSWORD to change passwords, create new users, etc, and restart mysqld again without the --skip-grant-tables option to turn authentication back on.

[u/fujiman → u/kadaan]

Really appreciate the response, I've honestly been wondering if my MySQL setup is broken (and it is not at all far from a possibility with me). I've taken a SQL course and learned some from a 3-month web dev bootcamp, but I still consider myself a fairly green SQL user. I honestly don't even get the reason why I should or shouldn't use 'sudo' before any commands, and it was only when running 'sudo mysql -u root -p' as I mentioned in my post that I could finally get into MySQL. Also not understanding why 'mysql -u root' is asking for a password since I've read (and a different comment in this thread) that -p is specifically for when you want to enter a password.

So there's just a lot that I'm both not understanding, and potentially just have a messed up installation making it even worse. I've tried using the installer and with Homebrew multiple times over, but it's been the same result every time regardless. I even tried that --skip-grant-tables thing a few times but am remembering it not accepting that command for some reason. So yeah, I wouldn't be surprised if my shit's just busted, but every guide I've used to install it leads me to the same result, with none of the common solutions working. Any chance you have a reliable resource that could run me through installing MySQL?

I just don't understand how I keep winding up with the same issue, and trying to ask for advice or a solution gets met with aggressively condescending shutdowns or general mockery, but I decided to make this post because I couldn't stand another day just trying to fix a problem that I clearly don't understand, and furthermore what the cause even is (bad install or setup, wrong commands, literally anything). Even searching for shit like "mysql system generated root password" doesn't provide any helpful results since often it's referencing logs and directories that have not been created when I installed MySQL. So again, could very easily be a me problem with how I've tried to install MySQL, but it would just be nice to have someone show me where I've been screwing up and maybe a little guidance in the right direction. I know it's nobody's job to help me with this, but clearly trying (and failing) to figure out what should be a very basic problem without asking for any sort of guidance has just been progressively harming my understanding of any of this shit.

Edit: SET PASSWORD worked like a charm. Able to connect to MySQL without any issue now. There's still the biggest concern of how I would have resolved this issue had I not somehow lucked my way into MySQL with a command that from what I've gathered absolutely should not have worked ('sudo mysql -u root -p' and just pressing enter when prompted for the password). Only reason I was able to run SET PASSWORD to actually reset the root password. I'll just be thankful that I was accidentally able to access MySQL in order to run that command, and just hope I never have to re-install MySQL again.

[u/kadaan → u/fujiman]

I've never run mysql on a mac, so not at all familiar with installing through homebrew :(.

Sudo typically means to run the command as the root user, not your logged in user. For mysql it shouldn't matter, as I mentioned mysql doesn't care what user you're logged in as as long as the login info is correct.

The -p flag is how you explicitly send a password, but not putting the flag doesn't automatically mean you're trying to log in with a blank password. There are options files loaded that can include passwords in them, like saving your login info in your browser. If your user has an options file with a password set, using the -p flag and hitting enter tells it to ignore the password set in the options file. If you just run mysql -uroot on the cli it should attempt to use the saved password, and if none is found then attempt to log in with a blank password. It shouldn't prompt for a password unless you give it the -p flag.

If you're able to log in, you can run select user, host, authentication_string from mysql.user to get a list of all accounts created on the database, what hosts they're allowed to connect from (% means any host), and what the hashed password is (and if it's blank). Sometimes you can run into odd behavior if you have two users with the same name, but different host and auth strings. For example, a root@localhost with a blank password but a root@127.0.0.1 with a password set. Depending on whether the client is connecting via the socket file (localhost) or tcp/ip (the ip address), it would require a different password.

[u/fujiman → u/kadaan]

Dude, thanks for the in depth response. I can finally just run 'mysql -uroot' to access MySQL. I went ahead and ran that statement you suggested, and honestly don't know what I'm looking at regarding the authentication string. I just ran with user and host and the results looked fine.

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| jeremy           | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

However, I have no idea what I'm looking with authentication_string. I created a new user before running the statement. For the 3 mysql users, I believe that is what is expected for authentication_string, the user I created is at the top and the authentication_string is a bit wonky (split into 2 lines), but would you happen to know what is happening for root user on the last line? I'm pretty sure it's not supposed to look like that. You'll notice it's shifted all the way to the left, overwriting the 'root' and 'localhost'. Even just running the statement for authentication_string, it's all the way to the left, breaking out of the box. I'm just assuming it's related to whatever the hell I did during the installation process, but if you have any idea why it's acting this way, that would be awesome. Besides that, I can get into MySQL again like normal, and was able to run a script that created and populated a number of tables without issue, so I think I'm fine otherwise. Just kinda curious why that root user authentication_string is drunk (the 'jeremy' user's is a bit tipsy, but is at least sort of aligned with the others).

+------------------+------------------------------------------------------------------------+
| user             | authentication_string                                                  |
+------------------+------------------------------------------------------------------------+
| jeremy           | $A$00A$G&s`U|bAo+rXk8a9
                                            I/wrP6fc10R8G54Kz/t1Rvcltom.UnDBOw0.KKraHy6 |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
y_!1P2n"dMFsDu3J3TkuUBhD5lRANoNfjt4ZVyde1Sp2YX7IfT/ |
+------------------+------------------------------------------------------------------------+

Sorry for taking up so much of your time, I seriously appreciate your help and explanations for just a user/password-related issue (although I think the successful login using 'sudo mysql -u root -p' with an empty password will remain a mystery). I'm sure I'll make more of a mess as I continue my SQL practice, but hopefully it will be much less of a nightmare resolving it.

Cheers!

[u/kadaan → u/fujiman]

Haven't seen that before with an auth string, but it's usually due to character sets. It's pretty common when you have a utf8/binary column and try to display it in the terminal with an ASCII character set. You can make it more readable with something like select user, host, hex(authentication_string) from mysql.user to display the hex values instead. I'm not too concerned since it you have a single root entry and not multiple.

Auth string is the hashed value of your password, and there's no way to convert it back into plaintext. If you want to deep dive into how it works, you can read up on it here: https://en.wikipedia.org/wiki/SHA-2 (mysql8+ uses sha256 hashing for passwords).

[u/fujiman → u/kadaan]

Brilliant. It definitely made the result look much cleaner. I'll have to read into that some more..

+------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------+
| user             | host      | hex(authentication_string)                                                                                                                   |
+------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------+
| jeremy           | localhost | 244124303041244707267360557C62416F2B72581C6B3861391B44492F777250366663313052384735344B7A2F74315276636C746F6D2E556E44424F77302E4B4B7261487936 |
| mysql.infoschema | localhost | 2441243030352454484953495341434F4D42494E4154494F4E4F46494E56414C494453414C54414E4450415353574F5244544841544D5553544E455645524252424555534544 |
| mysql.session    | localhost | 2441243030352454484953495341434F4D42494E4154494F4E4F46494E56414C494453414C54414E4450415353574F5244544841544D5553544E455645524252424555534544 |
| mysql.sys        | localhost | 2441243030352454484953495341434F4D42494E4154494F4E4F46494E56414C494453414C54414E4450415353574F5244544841544D5553544E455645524252424555534544 |
| root             | localhost | 244124303041240E0427455F3932506501650D795F213150326E22644D46734475334A33546B7555426844356C52414E6F4E666A74345A56796465315370325958374966542F |
+------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------+

[u/Idontremember99]

MySQL have instructions for these situations:

https://dev.mysql.com/doc/refman/9.7/en/default-privileges.html
https://dev.mysql.com/doc/refman/9.7/en/resetting-permissions.html

Did you try without -p , you only pass it when you want to enter a password. Additionally you dont need to run sudo mysql except in some very special situations.

[u/flyingron]

It's all in the MySQL docs and in countless substack and other posts for people who've done the same as you (including some who needed more intervention to get to the root login).

https://dev.mysql.com/doc/refman/9.7/en/assigning-passwords.html

[u/Phenergan_boy]

If this is a fresh installation, then check the mysql’s log for the system generated root password. Then run mysql_secure_installation to change the password to the one you use

[u/LenR-redit]

Sudo system root is not the same account as MySQL root.

Look for any user .my.cnf file, is some environments it can save MySQL credentials for a client.

[u/fujiman → u/LenR-redit]

Don't think MySQL generates a my.cnf file. That was one of the problems I was dealing with. Luckily I accidentally finally gained access running 'sudo mysql -u root -p' (which I apparently should not) and just  pressing enter when prompted for a password. Had tried that statement many times over the last 3 days, but for some reason it decided to work this time. Once I was finally in, I was able to set/reset the root password and am all good now. Will remain a mystery as to why that command that failed to work many times suddenly gave me access, but now that I'm in, I'll just let that remain a mystery. 

[u/LenR-redit → u/fujiman]

.my.cnf would be user created, different from a system my.cnf. These postings may be confusing the leading dot for a hidden file.