• Reset MySQL root password (MySQL 5.6)

    A couple of times recently I've had a need to help a web client regain access to their MySQL database root password. 
    These notes worked for MySQL 5.6, whether or not this method to reset the root password works with later versions of MySQL server instances, I don't yet know.

    These instructions were tested on a Windows machine, but with minor tweaking should be similar on Linux.

    Create MySQL init file

    Create a file somewhere easily remembered called mysql-init.txt, I put it at the root of C: to make typing the path easier later.
    In your mysql-init.txt file you can specify the new MySQL root password:

        SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newrootpassword');

    Of course you will want to change 'newrootpassword' to your choice of password.

    Start MySQL 5.6 with the init file

    If you are running MySQL server as a service you will need to stop that service now.
    From a command line shell (run... cmd, or use PowerShell) you need to run this command to to start MySQL 5.6 with the mysql-init.txt file you created earlier (don't forget to change paths as appropriate to your system):

    .\mysqld.exe --init-file=c:\\mysql-init.txt --console

    Depending on how your machine is set up, you may need to navigate to the directory containing mysqld.exe program before executing this command.

    Re-enable MySQL 5.6 Server service

    You should now see output from MySQL server startup. Look for any errors.

    Assuming you didn't see any errors in the output, your root password should now be whatever you provided in your mysql-init.txt file

    You should now stop the server that is running in the command shell (ctrl-c). Restart the normal MySQL system service.

    Login as root using your new password.