Computer FAQs & Tips

One day you forgot your root password for your MySQL database server and blaming yourself for not written it somewhere for easy access.

Ok, do it no more! here’s my short version (of official instruction) for advance user who does not need too much of instruction details

On Windows

1. As admin, Stop the MySQL server, make sure it’s completely stop
2. Create a text file eg. C:mysql-init.txt and past the following statement in

   UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';
   FLUSH PRIVILEGES;

3. Open up your Windows Command Prompt
4. Run this command

   C:mysqlbinmysqld-nt --init-file=C:mysql-init.txt

   Change path if your sever install on somewhere else.
   For wizard installation of mysql, you might have to do this

   "C:Program FilesMySQLMySQL Server 5.0binmysqld-nt.exe"
   --defaults-file="C:Program FilesMySQLMySQL Server 5.0my.ini"
   --init-file=C:mysql-init.tx

5. Now delete C:mysql-init.txt then restart MySQL server again

On Linux or Unix

1. Stop MySQL server as follow
   Find .pid file in /var/lib/mysql/, /var/run/mysqld/ and /usr/local/mysql/data/
2. Now run

   $ kill `cat /mysql-data-directory/host_name.pid`

3. Create a text file eg. /home/yourid/mysql-init and past the following statement in

   UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';
   FLUSH PRIVILEGES;

4. Start mysql server with the following options

   mysqld_safe --init-file=/home/me/mysql-init &

5. Delete mysql-init file then finished

Alternative but less secure

1. Stop mysqld and restart it with the --skip-grant-tables option.
2. Connect to the mysqld server with this command

   $ mysql

3. Now run the following statement

   UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root';
   FLUSH PRIVILEGES;

4. Finished