LinuxQuestions.org - How to Reset the Root Password of MySQL

- Linux - Server (http://www.linuxquestions.org/questions/linux-server-73/)

- - How to Reset the Root Password of MySQL (http://www.linuxquestions.org/questions/linux-server-73/how-to-reset-the-root-password-of-mysql-833619/)

Hi_This_is_Dev

09-21-2010 02:41 PM

How to Reset the Root Password of MySQL

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? :newbie:

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 (Post 4104909)

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.:doh:

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

Linux and MySQL Versions

Quote:

Originally Posted by bathory (Post 4104962)

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 (Post 4105326)

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#

All times are GMT -5. The time now is 09:28 PM.