Reset of root password not working?

Question

Our previous DBA left and didn't note the root password down, and I've been pressganged into doing part of his job until we can hire someone.

So I create a file :

/root/mysql.password

Inside that file, I have:

UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';

So I do a clean shutdown of the database, and then:

mysqld_safe --init-file=/root/mysql.password

I get 'starting mysqld' ... and then it exits with code 1.

I don't get it. What's wrong with that file?

Using ..

mysqld_safe --skip-grant-tables &

.. isn't an option, unfortunately, as it's a public-facing production box.

Thanks.

Answer 1

Different methods to reset 'root' password

1)

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

2)

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('cleartext password');

3)

mysqladmin -u root password 'NEWPASSWORD'

Answer 2

The very first thing you should do is take a backup of your database using mysqldump or similar, because if you mangle something, your root password will be useless anyway.

Answer 3

The problem may have been permissions

You had to run

chown mysql:mysql /root/mysql.password

or

chmod 777 /root/mysql.password

before starting mysqld