Terrible, just setup a new server, then i forgot root password for mysql database. So i need to reset the root password.

This is the way i do:
1. Login to our server machine as root (system administrator). Yes! we must have root access to our machine.

2. Shutdown mysql server (if already running). Make sure no other mysql daemon running.

$ /etc/init.d/mysql stop

3. Create a text file and place the following statements in it. Replace the password with the password that you want to use.

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

The UPDATE and FLUSH statements each must be written on a single line. And make sure we have statement “where user=’root’“, to make sure, only our root password is updated.

4. Save the text file into our home directory, or somewhere else. Example, i saved this as: “/home/vandai/mysql-reset

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

mysqld_safe –init-file=/home/vandai/mysql-reset &

This will execute the contents of the file named by the –init-file option at startup, changing each root account password.

6. Don’t forget to delete mysql-reset file immediately!!

Now we will be able to login to MySql database as root with our new password.

Never forget the password again!!