Unable to reset mysql root password

soylentgreen · 08-01-2008, 10:50 AM

I have an installation of mysql on a server. Nobody knows the root password, but the server and forum that are operated on it are still up and running. I am trying to reset the root password on the mysql installation, but I am unable to.

This is what I tried:

Code:

# /usr/local/etc/rc.d/mysql-server stop
Stopping mysql.
Waiting for PIDS: 75698.

# mysqld_safe --skip-grant-tables -u mysql &
[1] 75725
Starting mysqld daemon with databases from /var/db/mysql

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

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.22

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

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

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

mysql> quit
Bye
# /usr/local/etc/rc.d/mysql-server stop
Stopping mysql.
Waiting for PIDS: 75746, 75746STOPPING server from pid file /var/db/mysql/mysql.pid
080801 11:45:56  mysqld ended

.
[1]  + Done                          mysqld_safe --skip-grant-tables -u mysql

# /usr/local/etc/rc.d/mysql-server start
Starting mysql.

# mysql -u root -p mysql
Enter password: (I entered the password of 'password')
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I have tried this several times, can someone clue me in as to what I might be doing incorrectly? Why does the "Query OK" line say 0 rows affected?

Thank you for any guidance.

Steve K. · 08-01-2008, 11:13 AM

Chapter 5 in this tutorial discusses MySQL. http://www.beginning-kdevelop-programming.co.uk/ While the tutorial addresses using KDevelop for programming, I found the MySQL discussion very useful.

soylentgreen · 08-01-2008, 01:26 PM

Well I read chapter 5, and tried to digest the parts that were applicable to me.

I tried resetting my password with the mysqladmin lines in there, but that is only if you have no password to begin with, or if you know the current password.

mysqladmin with the 'password' function does not appear to work when running in the
mysqld_safe --skip-grant-tables &
mode.

Quote:

mysqladmin:
You cannot use 'password' command as mysqld runs
with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead

#mysqladmin flush-privileges password '*'
mysqladmin: unable to change password; error: 'Can't find any matching row in the user table'

So I'm still stuck, anyone else have any ideas?

Thanks

Wim Sturkenboom · 08-02-2008, 09:23 AM

I thought that it was an issue with case sensitivity, but I tested it and that does not seem to be the case.

Only difference that I ran it as root, not as user mysql. But doubt very much if that would make a difference.

Steve K. · 08-02-2008, 10:03 PM

Here's another resource that may more closely address your problem: http://dev.mysql.com/doc/refman/5.0/...rmissions.html

Steve K. · 08-02-2008, 10:05 PM

Sorry about the abbreviated URL It reads .../5.0/en/resetting-permissions.html