LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 09-21-2010, 02:41 PM   #1
Hi_This_is_Dev
Member
 
Registered: May 2009
Location: India
Distribution: On my PC I use RHEL, at office AIX, Solaris, HP-UX, RHEL.
Posts: 254

Rep: Reputation: 18
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?
 
Old 09-21-2010, 04:00 PM   #2
bathory
Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 10,881

Rep: Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322
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;
 
1 members found this post helpful.
Old 09-21-2010, 05:01 PM   #3
Hi_This_is_Dev
Member
 
Registered: May 2009
Location: India
Distribution: On my PC I use RHEL, at office AIX, Solaris, HP-UX, RHEL.
Posts: 254

Original Poster
Rep: Reputation: 18
Quote:
Originally Posted by bathory View Post
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.
 
Old 09-21-2010, 05:18 PM   #4
bathory
Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 10,881

Rep: Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322
What distro and mysql version are you running and how did you install myslq?
 
Old 09-21-2010, 05:39 PM   #5
Hi_This_is_Dev
Member
 
Registered: May 2009
Location: India
Distribution: On my PC I use RHEL, at office AIX, Solaris, HP-UX, RHEL.
Posts: 254

Original Poster
Rep: Reputation: 18
Linux and MySQL Versions

Quote:
Originally Posted by bathory View Post
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)

Last edited by Hi_This_is_Dev; 09-21-2010 at 05:45 PM.
 
Old 09-21-2010, 05:54 PM   #6
bathory
Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 10,881

Rep: Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322
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 &

Last edited by bathory; 09-21-2010 at 11:55 PM. Reason: typos
 
Old 09-21-2010, 06:15 PM   #7
Hi_This_is_Dev
Member
 
Registered: May 2009
Location: India
Distribution: On my PC I use RHEL, at office AIX, Solaris, HP-UX, RHEL.
Posts: 254

Original Poster
Rep: Reputation: 18
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!
 
Old 09-21-2010, 06:54 PM   #8
Hi_This_is_Dev
Member
 
Registered: May 2009
Location: India
Distribution: On my PC I use RHEL, at office AIX, Solaris, HP-UX, RHEL.
Posts: 254

Original Poster
Rep: Reputation: 18
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>
 
Old 09-22-2010, 12:23 AM   #9
bathory
Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 10,881

Rep: Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322
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
 
Old 09-22-2010, 10:44 AM   #10
Hi_This_is_Dev
Member
 
Registered: May 2009
Location: India
Distribution: On my PC I use RHEL, at office AIX, Solaris, HP-UX, RHEL.
Posts: 254

Original Poster
Rep: Reputation: 18
Quote:
Originally Posted by bathory View Post
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#
 
  


Reply

Tags
mysql, mysqld, password, reset


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql: still can't login after root password reset andredude Linux - Software 3 12-27-2012 09:48 PM
Unable to reset the root password for mysql on fedora cll975dll Linux - Newbie 1 08-08-2009 09:47 PM
Unable to reset mysql root password soylentgreen Linux - Server 5 08-02-2008 10:05 PM
MySQL Root Password Reset carlosinfl Linux - Server 6 07-18-2008 03:58 AM
mysql root password reset. gtum5 LinuxQuestions.org Member Intro 0 03-08-2007 08:57 PM


All times are GMT -5. The time now is 02:37 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration