04 July 2013

How To: Recover a 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
mysql> use mysql;

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

Exit MySQL and restart MySQL server
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Other useful commands:




Set a root password if there is on root password.
# mysqladmin -u root password newpassword

Update a root password.
# mysqladmin -u root -p oldpassword newpassword


How to create a new user via terminal


Login as root. Switch to the MySQL db. Make the user. Update privs.
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));

mysql> flush privileges;



No comments: