0

MySQL: I Forgot My MySQL Root Password

Don’t worry, it’s not the end of the world as we have all done it before! It’s actually quite simple to fix.

First, we shut down our MySQL server:

# /etc/init.d/mysql stop
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql stop

Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the stop(8) utility, e.g. stop mysql
mysql stop/waiting

* This error tells me service mysql stop works also.

Now, we start MySQL and skip the password file with the mysqld_safe –skip-grant-tables & command:

# mysqld_safe --skip-grant-tables &
[1] 1342
121006 17:25:03 mysqld_safe Logging to syslog.
121006 17:25:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

If your output is messed up, hit Enter on your keyboard.

Next we login as root without any password with mysql -u root

# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
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
owners.

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

mysql>

We issue the following commands:
use mysql;
update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;
flush privileges;
quit;

# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
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
owners.

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

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=PASSWORD("Ouwurs1QuiWr") where User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

Now we stop and restart MySQL:

/etc/init.d/mysql stop
/etc/init.d/mysql start

If you run Ubuntu: service mysql stop and service mysql start.

Now, we try and login with our new MySQL root password:

# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
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
owners.

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

mysql>

If you see this, type exit and you have reset your password!

If not, check your output buffer and see if you misspelled your password.

Chris_C