Set MySQL root password from init-file

Stop the MySQL server by sending a normal kill to the mysqld process

Create a text file containing the following statements (replace “TheNewPassword” with the password that you want to use):

UPDATE mysql.user SET Password=PASSWORD(‘TheNewPassword’) WHERE User=’root’;
FLUSH PRIVILEGES;

Enter the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

Save the file. For this example, the file will be named /root/newpwd.txt. The file contains the password, so it should not be saved where it can be read by other users. If you are not logged in as mysql (the user the server runs as), make sure that the file has permissions that permit mysql to read it.

Start the MySQL server with the special –init-file option:

shell> mysqld_safe –init-file=/root/newpwd.txt &

The server executes the contents of the file named by the –init-file option at startup, changing each root account password.

After the server has started successfully, remember to delete the file containing the password ( /root/newpwd.txt).

You should now be able to connect to the MySQL server as root using the new password.

Stop the server and restart it normally.

———————–

Source: dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html