MySQL root pass reset

If you ever find yourself in position that you've forgot or don't know the root password for a MySQL DB and you've got root privileges on that machine, you can reset it like this:

1. Create a file with the following sql to update the password on all root users records (you probably have at least 2 entries for root@localhost and root@127.0.0.1):

   UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User LIKE 'root@%';

   FLUSH PRIVILEGES;

2. Stop the mysqld, e.g. run:

   /etc/init.d/mysqld stop

3. Tell mysqld_safe to run your sql on start:

   mysqld_safe --init-file=/your/file &

4. Kill mysqld_safe, start regular mysqld and login using the new password. Voilà!

When passing a path for --init-file make sure that mysqldsafe can find the file (best to use a full path) and that it has permissions to read it. If mysqldsafe can't read the init file it will just silently exit, and you'll need to look in error logs for the exact problem.