How to Reset the Root Password of MySQL

Hi_This_is_Dev · 09-21-2010, 02:41 PM

I followed the steps described on this web page

http://dev.mysql.com/doc/refman/5.0/...rmissions.html

for resetting the root user's password. Actually, I am using www.linuxzoo.net for online practice of Linux and have MySQL installed on it. But I forgot the password I set. There was no password by default set for the root user. So, I just trying setting a password last week. Now I am not getting what I did.

Anyways, I did these things:

Code:

-bash-2.05b# cat /root/mysql-init
UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
FLUSH PRIVILEGES;
-bash-2.05b#

Code:

mysqld (pid 3119 3118 3117 3116) is running...
-bash-2.05b# kill `cat /var/run/mysqld/mysqld.pid`
-bash-2.05b# service mysqld status
mysqld dead but subsys locked

Code:

-bash-2.05b# mysqld_safe --init-file=/root/mysql-init
-bash: mysqld_safe: command not found

Code:

-bash-2.05b#
-bash-2.05b# service mysqld --skip-grant-tables restart
Usage: /etc/init.d/mysqld {start|stop|status|condrestart|restart}
-bash-2.05b# service "mysqld --skip-grant-tables" restart
mysqld --skip-grant-tables: unrecognized service

So, mysqld_safe is not working. I am not able to restart mysqld service with the option "--skip-grant-tables" as described in the web page given above.

Any help guys?

bathory · 09-21-2010, 04:00 PM

You can do the following to start the service with the --skip-grant-tables option:

Code:

service mysqld stop
/usr/sbin/mysqld --skip-grant-tables

After that follow the generic instructions from the page you mentioned:

Quote:

Connect to the mysqld server with this command:
shell> mysql

Issue the following statements in the mysql client. Replace the password with the password that you want to use.

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
mysql> FLUSH PRIVILEGES;

Hi_This_is_Dev · 09-21-2010, 05:01 PM

Quote:

Originally Posted by bathory

You can do the following to start the service with the --skip-grant-tables option:

Code:

service mysqld stop
/usr/sbin/mysqld --skip-grant-tables

After that follow the generic instructions from the page you mentioned:

I tried these steps:

Code:

mysqld (pid 1359 1358 1357 1356) is running...
-bash-2.05b# kill `cat /var/run/mysqld/mysqld.pid`
-bash-2.05b# /usr/sbin/mysqld --skip-grant-tables
-bash: /usr/sbin/mysqld: No such file or directory

The above commands didn't work. So I did this:

Code:

-bash-2.05b# which mysqld
/usr/bin/which: no mysqld in (/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sb           in:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin:/usr/ke           rberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbi           n:/usr/bin:/usr/X11R6/bin:/root/man)

No clue!

So I looked for mysqld...

Code:

-bash-2.05b# find / -name mysqld
/etc/logrotate.d/mysqld
/etc/rc.d/init.d/mysqld
/usr/libexec/mysqld
/var/lock/subsys/mysqld
/var/run/mysqld

Okay, I tried these mysqld executables...

Code:

-bash-2.05b# /etc/rc.d/init.d/mysqld --skip-grant-tables
Usage: /etc/rc.d/init.d/mysqld {start|stop|status|condrestart|restart}
-bash-2.05b# service mysqld start
Starting MySQL:                                            [  OK  ]
-bash-2.05b# service mysqld stop
Stopping MySQL:                                            [  OK  ]
-bash-2.05b# /etc/rc.d/init.d/mysqld
Usage: /etc/rc.d/init.d/mysqld {start|stop|status|condrestart|restart}

Code:

-bash-2.05b# /usr/libexec/mysqld --skip-grant-tables
Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
100921 23:07:12  Aborting

100921 23:07:12  /usr/libexec/mysqld: Shutdown Complete

-bash-2.05b#

So, still no result.

bathory · 09-21-2010, 05:18 PM

What distro and mysql version are you running and how did you install myslq?

Hi_This_is_Dev · 09-21-2010, 05:39 PM

Quote:

Originally Posted by bathory

What distro and mysql version are you running and how did you install myslq?

Code:

-bash-2.05b# uname -a
Linux host-6-7.linuxzoo.net 2.6.18.1 #5 Thu Nov 30 12:51:52 GMT 2006 i686 i686 i386 GNU/Linux

-bash-2.05b# mysqladmin --version
mysqladmin  Ver 8.23 Distrib 3.23.58, for redhat-linux-gnu on i386

Code:

-bash-2.05b# cat /etc/issue
Fedora Core release 2 (Tettnang)
Kernel \r on an \m

I didn't install it manually. There is a UML (User Mode Linux) available on www.linuxzoo.net which we can use for our practice purpose. MySQL is installed on it by default. I think I had mistyped the password otherwise I generally remember my passwords and I use which I can remember when doing some testing. But I am curious to troubleshoot it.

Code:

-bash-2.05b# mysql -u root -p
Enter password:
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

bathory · 09-21-2010, 05:54 PM

Quite old mysql version 3.23.58!!!

Quote:

-bash-2.05b# mysql -u root -p
Enter password:
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Are you sure that root has a password?

Anyway to reset the password, you can try:

Code:

/usr/libexec/mysqld --skip-grant-tables -u mysql &

Hi_This_is_Dev · 09-21-2010, 06:15 PM

Code:

-bash-2.05b# service mysqld stop
Stopping MySQL:                                            [  OK  ]

-bash-2.05b# /usr/libexec/mysqld --skip-grant-tables -u mysql &
[1] 1775

-bash-2.05b# Cannot initialize InnoDB as 'innodb_data_file_path' is not set.
If you do not want to use transactional InnoDB tables, add a line
skip-innodb
to the [mysqld] section of init parameters in your my.cnf
or my.ini. If you want to use InnoDB tables, add to the [mysqld]
section, for example,
innodb_data_file_path = ibdata1:10M:autoextend
But to get good performance you should adjust for your hardware
the InnoDB startup options listed in section 2 at
http://www.innodb.com/ibman.html
/usr/libexec/mysqld: ready for connections

-bash-2.05b# mysql -u mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases
    -> ;
+----------+
| Database |
+----------+
| DevDB    |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

mysql> quit
Bye
-bash-2.05b# mysql -u mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
mysql> quit
Bye
-bash-2.05b# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Thanks a lot!

Hi_This_is_Dev · 09-21-2010, 06:54 PM

How can we view all the user names?

I tried this...

Code:

-bash-2.05b# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user from mysql.user;
+------+
| user |
+------+
|      |
| root |
|      |
| root |
+------+
4 rows in set (0.00 sec)

mysql>

mysql> quit
Bye

Apparently there is only root user appearing twice but the above output reports "4 rows".

Why?

I am also able to log in using the user name: mysql....

Code:

-bash-2.05b# mysql -u mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

So, I am not able to assign a password to this user: mysql...

mysql> update mysql.user set password=PASSWORD('test') where user='mysql';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql>

bathory · 09-22-2010, 12:23 AM

Quote:

Apparently there is only root user appearing twice but the above output reports "4 rows".

The 2 blank rows mean that there are also 2 "anonymous" users.
The fact that you can connect using mysql (it's an anonymous account) is because you're running the server with "--skip-grant-tables". You can connect to the database using whatever name you want in this situation.

Regards

Hi_This_is_Dev · 09-22-2010, 10:44 AM

Quote:

Originally Posted by bathory

The 2 blank rows mean that there are also 2 "anonymous" users.
The fact that you can connect using mysql (it's an anonymous account) is because you're running the server with "--skip-grant-tables". You can connect to the database using whatever name you want in this situation.

Regards

Thanks for the explanation!

Here is my experiment:

Code:

mysql> select user from mysql.user;
+------+
| user |
+------+
|      |
| root |
|      |
| root |
+------+
4 rows in set (0.00 sec)

mysql> delete from mysql.user where user <> 'root';
Query OK, 2 rows affected (0.00 sec)

mysql> select user from mysql.user;
+------+
| user |
+------+
| root |
| root |
+------+
2 rows in set (0.00 sec)

mysql>

mysql> quit
Bye

Code:

-bash-2.05b# mysql -u mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye

Code:

-bash-2.05b# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> quit
Bye

Code:

-bash-2.05b# mysql -u mysql
ERROR 1045: Access denied for user: 'mysql@localhost' (Using password: NO)
-bash-2.05b#

Code:

-bash-2.05b# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> insert into mysql.user (user) values ('');
Query OK, 1 row affected (0.00 sec)

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

mysql> quit
Bye

Code:

-bash-2.05b# mysql -u mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye
-bash-2.05b#