FFS MySQL

FFS MySQL

I recently lost 2 hours of my life trying to change a MySQL root password.

F.F.S !!

If you search the interwebs, you’ll find the pundits recommending something like the following:

For some reason, the mysqld_safe script wouldn’t run in the background even though the ampersand was definitely present. Control never returned to the foreground and I then tried connecting to the MySQL server in another shell and perform the password reset yet it didn’t work – the result being that the password was reset to a blank password. Not good.

[Ed – Not a blank password – the auth::plugin was working as expected. See below]

Really Ed? Because I assigned a root password when I ran the mysql_secure_installation script. The reason I was changing the password was that I had stupidly used special characters and the password could not be entered into a bash script. (Yes, I’m aware it’s insecure – thx..)

All the online chatter suggests the auth::socket is only used when a blank password is entered

The fix is to understand the authentication plugin and how it fits into the mySQL schema. Over to you, carp.

MySQL fails on: mysql “ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded”

I got a solution! When resetting root password at step 2), also change the auth plugin to mysql_native_password: use mysql; update user set authentication_string=PASSWORD(“”) where User=’root’; update user set plugin=”mysql_native_password” where User=’root’; # THIS LINE flush privileges; quit; This allowed me to log in successfully!

Which means when changing the root password on MySQL 5.7 you need to execute the following commands after connecting to the SQL Server:

Then using an SQL client check you have the correct plugin for user ‘root’:

What exactly does the mysqld_safe script do?

The best explanation I found was here:

mysqld_safe

The mysqld_safe startup script is in MariaDB distributions on Linux and Unix. It is a wrapper that starts mysqld with some extra safety features. For example, if mysqld_safe notices that mysqld has crashed, then mysqld_safe will automatically restart mysqld. mysqld_safe is the recommended way to start mysqld on Linux and Unix distributions that do not support .

You can wade through the script if you wish (I took a quick peek – it’s long!).

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz