Today I was trying to log in to mysql database administration on one of my hosting machines and as I was getting ‘permission denied for user [email protected]’ I tried to fix the root account and reset its password 다운로드. But I wasn’t successful and later on I realized, that the root account was deleted from the mysql database by one of my friends accidentally. I started to search the internet what to do, but the only thing I have found regarding this issue was question on MySQL forum with exactly the same problem but no answer 윈도우 8.1 업데이트 수동 다운로드. So as usual, I tried to solve it by myself and here is it is.
The pre requisition is a shell access on your MySQL machine 다운로드. Then you have to stop the standard mysql daemon and start the database in safe mode.
/etc/init.d/mysql stop mysqld_safe --skip-grant-tables
Then log in as a root and switch to mysql system database 다운로드.
mysql -u root mysql> use mysql;
Try to check that the root user is not present in user table:
mysql> select * from user where User='root';
If the database return empty record, lets manually insert the root user with empty password and then set all the permissions which he normally needs:
mysql> insert into user (Host, User, Password) values ('localhost','root',''); Query OK, 1 rows affected (0.04 sec) mysql> update user set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',Create_user_priv='Y' where user='root'; Query OK, 1 rows affected (0.03 sec)
Then quit the database console, kill the mysqld_safe daemon and start the standard mysql daemon again:
mysql> quit killall mysqld_safe /etc/init.d/mysql start
Try to log in into mysql console again with an empty password and for double check, try to run ‘grant’ command to see that the account is fully working:
mysql -u root mysql> grant all privileges on *.* to 'root'@'localhost' with grant option; Query OK, 1 rows affected (0.03 sec)</pre>
And thats it, your basic root acount is working again centos 폴더 다운로드. Don’t forget to change the password or add the hostname to it, but that depends on your needs.
Thank you for writing everything plain and clear.
Cheers!
thanks dude amazing post
Thanks!!!!!!!!
It works, thank you very much!!
I am getting
“The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement”
Exception
what to do?
If try enabling ‘grant-table’ login not permitting.
Please Reply
It’s simply awesome…
Works perfect, you saved my life
Thanks =)
I had to run:
FLUSH PRIVILEGES;
after: update user…
thanks a lot.. you rock!
This post is pretty good to read. Great!!
Thank you very much for sharing, can I post it on my blog to share with my friends?
this doesn’t work on mysql 5.7.17
very frustrating time with this and many others to skip grant tables.