Friday 31 January 2014

Recover MySQL root password

Stop the MySQL server process
/etc/init.d/mysql stop

Start again with no grant tables
mysqld_safe --skip-grant-tables &

Login to MySQL as root
mysql -u root

Set new password
mysql> use mysql;
mysql> update user set password=PASSWORD("password") where User='username';
mysql> flush privileges;

Exit MySQL and restart MySQL server

mysql> quit
/etc/init.d/mysql stop
/etc/init.d/mysql start

Set root password if there is no root password
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql>SET PASSWORD = PASSWORD('new_password');

Allow the user to connect to the server from localhost using the password

mysql>grant usage on *.* to 'username'@'%' identified by 'password';

No comments:

Post a Comment