mysql how to fix Access denied for user 'root'@'localhost'
Follow the steps below.
Start the MySQL server instance or daemon with the
--skip-grant-tables
option (security setting).$ mysqld --skip-grant-tables
Execute these statements.
$ mysql -u root mysql $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root'; $mysql> FLUSH PRIVILEGES;
If you face the unknown field Password error above use:
update user set authentication_string=password('my_password') where user='root';
Finally, restart the instance/daemon without the
--skip-grant-tables
option.$ /etc/init.d/mysql restart
You should now be able to connect with your new password.
$ mysql -u root -p
Enter password: my_password
Fix for MySQL “Unable to lock ibdata1” error
sudo mv /usr/local/mysql/data/ibdata1 /usr/local/mysql/data/ibdata1.bak
sudo mv /usr/local/mysql/data/ib_logfile0 /usr/local/mysql/data/ib_logfile0.bak
sudo mv /usr/local/mysql/data/ib_logfile1 /usr/local/mysql/data/ib_logfile1.bak
sudo cp -a /usr/local/mysql/data/ibdata1.bak /usr/local/mysql/data/ibdata1
sudo cp -a /usr/local/mysql/data/ib_logfile0.bak /usr/local/mysql/data/ib_logfile0
sudo cp -a /usr/local/mysql/data/ib_logfile1.bak /usr/local/mysql/data/ib_logfile1
sudo /etc/init.d/mysql restart
None of the above were helpful for me. I found I needed to clear the plugin method. In 5.6, I could do:
sudo mysql -u root
use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
In 5.7, I found I needed to:
sudo mysql -u root
use mysql;
[mysql] update user set plugin='mysql_native_password' where User='root';
[mysql] flush privileges;
According to the docs, with plugin set to an empty string, it should have effectively defaulted to mysql_native_password, but may be getting confused by an empty password hash. For more nuance, you can read the documentation here: https://dev.mysql.com/doc/refman/5.7/en/native-authentication-plugin.html
Also make sure needed record in table user
has empty plugin
field (there can be, for example, "unix_socket"
).
Since version 5.5.7 mysql has various auth plugins support https://dev.mysql.com/doc/refman/5.6/en/authentication-plugins.html
So if you have non-empty plugin
field then password would be ignored and there would be warning at mysql error log (for me it's /var/log/mysql/error.log
):
[Warning] 'user' entry 'root@localhost' has both a password and an authentication plugin specified. The password will be ignored.