Skip to content

MySQL root password

October 29, 2011
By Administrator in All, Linux administration, MySQL server

Resetting a lost MySQL root password is a fairly simple procedure and one that every system adminsitrator or software developer should be familiar with. It can be broken down into several easy steps. The environment used in this tutorial consists of an Ubuntu Server 11.10 and MySQL 5.1.58, but the instructions should be applicable to various versions of the MySQL server software and most Linux/Unix based operating systems. The steps are as follows:

1. Stop the instance of MySQL running at the moment:

[email protected]:~# /etc/init.d/mysql stop

2. Start the MySQL daemon manually adding an option that will cause it to ignore its authentication information and load all databases with default permissions:

[email protected]:~# mysqld_safe --skip-grant-tables &
[1] 1445
111028 20:52:31 mysqld_safe Logging to syslog.
111028 20:52:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

3. Connect to the above MySQL daemon instance using the root user with no password:

[email protected]:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.58-1ubuntu1 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql>

The system logs you in since the MySQL daemon was instructed to ignore the authentication information located in the user table.

4. While connected execute the following command that will update the user table and set a new password for the root MySQL user:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

The MySQL root password has now been reset.

5. Stop the current instance of the MySQL dameon and start a new one without any additional options:

[email protected]:~# ps aux | grep mysql
root 1445 0.0 0.1 2040 576 pts/0 S 20:52 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 1562 0.1 3.3 137160 17228 pts/0 Sl 20:52 0:03 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --skip-grant-tables --pid-file=/var/lib/mysql/server.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
root 1590 0.0 0.1 4184 800 pts/0 S+ 21:27 0:00 grep --color=auto mysql
[email protected]:~# kill -9 1445 1562

We used the ps command to locate the process IDs of the current MySQL daemon instance and then the kill command to terminate their execution. Then, we start the MySQL daemon again in the conventional way:

[email protected]:~# /etc/init.d/mysql start

6. We test the results:

[email protected]:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.1.58-1ubuntu1 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql>

Typing the “newpassword” when prompted, logs you in to the MySQL server as root without any issues.

Tags: , , , , ,

Comment Feed

No Responses (yet)



Some HTML is OK

or, reply to this post via trackback.