John Bernard Abella [blog]

‘Like locking your keys in the car, to forget your password after
you’ve spent an hour installing and tweaking a new MySQL server can be
an embarassment to say the least.’

Fortunately, if you have root access to that computer, all in NOT lost.

Here are the steps when you found yourself in this situation.

1. Kill the server process.
% kill pid
(where pid is the process id. Do not use kill -9 unless absolutely
necessary, as this may damage your table files)

2. Run safe-mysqld with the –skip-grant-tables command line option.
(This instructs the MySQL server to allow unrestricted access to
anyone) NOTE: restrict all remote access before issuing this command
% safe-mysqld --skip-grant-table

3. Change root password
mysql > use mysql;
mysql > update user set password = password('newpassword') where user
= 'root';

4. Disconnect and instruct the mysql server to reload the grants
tables to begin requiring passwords.
% mysqladmin flush-privileges

“That does it — and nobody ever has to know what you did. As for
locking your keys in your car, you’re on your own there.”

Must have for php-mysql beginners developers.