Reset the root password on MySQL

Have you ever forgotten the root password on one of your MySQL servers?
This is a quick h00tow (how to) reset your MySQL root password. It does require root access on your server.

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.
mysqld_safe --skip-grant-tables
You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.
mysql --user=root mysql

update user set Password=PASSWORD('YourNewRootPassword');
flush privileges;
exit;

If you are using Ubuntu 8.04 Hardy Heron, when you try to restart the mysql service using
sudo /etc/init.d/mysql restart
you’ll get an error like

error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

The debian_sys_maint user is the one used by mysql user system under debian sytems.In order to fix this (seeming to be a grant error) do like this:

Save the password of debian-sys-maint user which is localized in /etc/mysql/debian.cnf file (readable only by root):


sudo more /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = lP3Ufasdas3AVm7EdC
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user = debian-sys-maint
password = lP3Ufasdas3AVm7EdC
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
root@scrat:/etc/mysql#

Save somewhere this password, then connect on mysql under root:
mysql -u root -p

and execute following command:

GRANT ALL PRIVILEGES ON *.* TO ‘debian-sys-maint’@'localhost’
IDENTIFIED BY ‘your_password’ WITH GRANT OPTION;

Everything should be OK now.

There is another method to reset the root password on MySQL in Ubuntu:
sudo /etc/init.d/mysql reset-password

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close