MySQL fails on: mysql ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded
I got a solution!
When resetting the 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!
Full code solution
1. First, run these bash commands
sudo /etc/init.d/mysql stop # stop mysql service
sudo mysqld_safe --skip-grant-tables & # start mysql without password
# enter -> go
mysql -uroot # connect to mysql
2. Then run mysql commands => copy paste this to CLI manually
use mysql; # use mysql table
update user set authentication_string=PASSWORD("") where User='root'; # update password to nothing
update user set plugin="mysql_native_password" where User='root'; # set password resolving to default mechanism for root user
flush privileges;
quit;
3. Run more bash commands
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start # reset mysql
# try login to database, just press enter at password prompt because your password is now blank
mysql -u root -p
4. Socket issue (from your comments)
When you see a socket error, a community came with 2 possible solutions:
sudo mkdir -p /var/run/mysqld; sudo chown mysql /var/run/mysqld
sudo mysqld_safe --skip-grant-tables &
(thanks to @Cerin)
Or
mkdir -p /var/run/mysqld && chown mysql:mysql /var/run/mysqld
(thanks to @Peter Dvukhrechensky)
Blind paths and possible edge errors
Use 127.0.0.1 instead of localhost
mysql -uroot # "-hlocalhost" is default
Can lead to "missing file" or slt error.
mysql -uroot -h127.0.0.1
Works better.
Skip the socket issue
I've found many ways to create mysqld.sock
file, change access rights, or symlink it. It was not the issue after all.
Skip the my.cnf
file
The issue also was not there. If you are not sure, this might help you.
MySQL Won't let User Login: Error 1524
It appears your user table is corrupted. Likely the reboot you mentioned triggered an upgrade to MySQL and the mysql_upgrade
script was not run. This should resolve the situation:
mysql_upgrade -u root -ppassword --skip-grant-tables
mysql -u root -ppassword -e "UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'mangos'; FLUSH PRIVILEGES"
Source: http://kb.odin.com/en/126676
Providing the --force
option to mysql_upgrade
will re-apply the upgrade scripts even if an upgrade has already been done. This may be needed in case of partial restoration from backup.
Also worth mentioning, the command to change a user password has changed in MySQL 5.7.6 / MariaDB 10.2.0 and forward:
ALTER USER mangos IDENTIFIED BY 'mangos';
This is now the preferred method for setting the password, although the older SET PASSWORD
syntax is not officially deprecated.
Related Topics
How to Access the "Previous Row" Value in a Select Statement
SQL Server 2008 Management Studio Not Checking the Syntax of My Query
MySQL Error:: 'Access Denied For User 'Root'@'Localhost'
Datetime2 VS Datetime in SQL Server
Difference Between Scope_Identity(), Identity(), @@Identity, and Ident_Current()
Tsql Pivot Without Aggregate Function
How to Schedule a Job to Run a SQL Query Daily
Auto Increment After Delete in MySQL
How to Generate a Range of Numbers Between Two Numbers
Any Downsides of Using Data Type "Text" For Storing Strings
SQL Statement Is Ignoring Where Parameter
Postgresql Distinct on With Different Order By
Insert Statement Conflicted With the Foreign Key Constraint - SQL Server
Error 1452: Cannot Add or Update a Child Row: a Foreign Key Constraint Fails