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
OR
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