Server Stuff => Linux Server => Topic started by: Marook on 2013-09-25, 23:49

Title: [Ubuntu] Reset MySQL root password
Post by: Marook on 2013-09-25, 23:49
If you lost your mysql root password, you can use the default maintenance user called debian-sys-maint to reset your password. This user is created within the installation of mysql and can be used to do administrative jobs.

First of all, we need the password of this user. Its generated by the system and stored in /etc/mysql/debian.cnf as plaintext.

Code: [Select]
root@root~ # cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
host     = localhost
user     = debian-sys-maint
password = 0lndlei7t7aU0iUM
socket   = /var/run/mysqld/mysqld.sock
host     = localhost
user     = debian-sys-maint
password = 0lndlei7t7aU0iUM
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

In this example, the password is 0lndlei7t7aU0iUM

Next, we need to connect to our mysql server with that user :

Code: [Select]
root@root~ # mysql -u debian-sys-maint -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 129
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Now we can use a simple mysql-query to set a new password :

Code: [Select]
mysql> update mysql.user set Password=password('NewPasswordHere') where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 4  Changed: 1  Warnings: 0

Then we need to flush the privileges with :

Code: [Select]
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

And close the conenction with a simple quit; command.

Now restart the mysql-server and connect to it with root and the new password.