MySQL not starting after changing root password

Temujin_12 · 05-19-2006, 10:27 AM

I installed MySQL (server) and then went in and changed the root user's password (in MySQL) with the following query:

Code:

update user set Password=password("newpassword") where User="root";

I then ran "FLUSH PRIVILEGES" and then exited. Now, when I run

Code:

/etc/init.d/mysql restart

it stops properly, but when it tries to restart it says the following:

Code:

Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
.
FIXME: This is still too noisy but will be changed, soon!
This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, stored procedures, or
more secure passwords in 4.1

Got a failure from command:
cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=root --host=localhost --database=mysql
Please check the above output and try again.

Running the script with the --verbose option may give you some information of what went wrong.

If you get an 'Access denied' error, you should run this script again and give the MySQL root user password as an argument with the --password= option
Checking for crashed MySQL tables in the background.

It just hangs there until I kill the process. Strangely, MySQL starts because I can login and query different databases.

It appears that it is assuming an empty password for the root user becuase when I run (note the -p):

Code:

cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=root --host=localhost --database=mysql -p

and enter the password, it works just fine.

It actually dumps a bunch of errors similar to

Code:

ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv'

But from my research, these errors can be ignored.

My main problem with this whole thing is that the above startup errors are causing my other startup scripts and commands to not execute.

Is there a way I can get the MySQL startup script to use the root password? I would really prefer not to set the root MySQL user's password to be empty.

Thanks.

EDIT:

I noticed the line

Code:

If you get an 'Access denied' error, you should run this script again and give the MySQL root user password as an argument with the --password= option

. Does anyone know where I can modify the script refered to here so I can set the --password="newpassword" flag?

Temujin_12 · 05-19-2006, 11:20 AM

Ok, I found the file that needed to be changed to include the root password. It was in '/usr/bin/mysql_fix_privilege_tables'.

I don't like the idea that this file's permissions are -rwxr-xr-x. So, anyone can open this file to read the root MySQL user's password!!?? Any thoughts on this?

Anyways, now I don't get the "Got a failure from command" error message. But now it displays:

Code:

Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
FIXME: This is still too noisy but will be changed, soon!
root@rico:/usr# This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, stored procedures, or
more secure passwords in 4.1

done
Checking for crashed MySQL tables in the background.

And it hangs there. Any ideas?

Thanks in advance for your help.

Temujin_12 · 05-19-2006, 11:32 AM

Geez, that was a dumb one. Notice in the above post the "root@rico:/usr#" prompt? It wasn't hanging but just had some timing issues with output to the prompt. Sure enough, when I hit enter it gives me another prompt output. At least I figured out that mysql_fix_privilege_tables error.

Also, I changed the permissions to '/usr/bin/mysql_fix_privilege_tables' to be -rwxr-x--- and I am able to run '/etc/init.d/mysql restart' w/o any problems. I'd reccomend that people do this so their root password for MySQL isn't in the clear.

I'm going to test how things work on a restart and I'll post the results later today.

Temujin_12 · 05-19-2006, 11:40 AM

Hmm, on restart it didn't load some of my other startup scripts (I'll check them to see if they are the problem). I'm still wondering if it should be outputting

Code:

Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
FIXME: This is still too noisy but will be changed, soon!
root@rico:/usr# This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, stored procedures, or
more secure passwords in 4.1

done
Checking for crashed MySQL tables in the background.

and if that is causing any problems. Most other machines that I've run a MySQL restart command on do not output anything other than the "stopping" and "starting" commands.

Any ideas?