Before I screw up something, when I login using $ mysql -u root -p, and show databases: +-+ Database +-+ informationschema mysql performanceschema gamedata test +-+ Then I tried to create a new user and notice something is wrong with the PRIVILEGES. So I deleted the new users, and I guess I removed the 'root' and 'Admin' accidentally. Then I try to create 'root' again, but get Access denied error when doing grant all privileges. Mysql CREATE USER 'root'@'localhost' IDENTIFIED BY 'password'; mysql grant all privileges on.
Shell> mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) It means a root password has already been assigned during installation and it has to be supplied.
to 'root'@'localhost' identified by 'password' with grant option; ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) If I login to MySQL again using $ mysql -u root -p, and show databases, +-+ Database +-+ informationschema +-+ All the other databases are gone. How do I fix MySQL now? I cannot find the database 'mysql', cannot create database, create user, anything I try to do will get an error. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES). Should I reinstall MySQL using MacPorts?
If reinstall, I will lose the database gamedata, right? 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('mypassword') where USER='root'; $mysql FLUSH PRIVILEGES; If you face the unknown field Password error above use: update user set authenticationstring=password('mypassword') 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: mypassword 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/iblogfile0 /usr/local/mysql/data/iblogfile0.bak sudo mv /usr/local/mysql/data/iblogfile1 /usr/local/mysql/data/iblogfile1.bak sudo cp -a /usr/local/mysql/data/ibdata1.bak /usr/local/mysql/data/ibdata1 sudo cp -a /usr/local/mysql/data/iblogfile0.bak /usr/local/mysql/data/iblogfile0 sudo cp -a /usr/local/mysql/data/iblogfile1.bak /usr/local/mysql/data/iblogfile1 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='mysqlnativepassword' where User='root'; mysql flush privileges; According to the docs, with plugin set to an empty string, it should have effectively defaulted to mysqlnativepassword, but may be getting confused by an empty password hash. For more nuance, you can read the documentation here.