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
0 Comments.