How to Change MySQL root password on Windows

Warning: Abandoned
This page/script has been classified as abandoned and will no longer be updated. I will keep this page here indefinitely as a reference, but it will no longer be updated and I no longer offer support of any kind regarding content found on this page.

Submitted by ricocheting on

Description

Use these instructions if you need change the root password for MySQL on Windows or if you need to add additional new users with general or specific limitations.

Setting a root password for MySQL

  1. Start your command line by going to the Start Menu > Run and typing cmd (or type command if you are using an older version of windows)
  2. Change directory to where you installed mysql to:
    C:\> cd C:\mysql\bin
  3. Switch to mysql command line:
    C:\mysql\bin> mysql -u root mysql
  4. Then set a default password:
    mysql> SET PASSWORD FOR root@localhost=PASSWORD('newpass');
    where "newpass" is the password you want to use

Adding more users

  1. Start your command line by going to the Start Menu > Run and typing cmd (or type command if you are using an older version of windows)
  2. Change directory to where you installed mysql to:
    C:\> cd C:\mysql\bin
  3. Switch to mysql command line (if you have not set a root password remove the -p switch when you type it in):
    C:\mysql\bin> mysql -u root -p mysql
  4. Then then add your new user:
    mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
    where "jeffrey" is the username and "mypass" is the password you want to use. You can also limit users to specific database, allow only certain remote hosts to connect all using the GRANT statement. However, that is outside the scope of this tutorial so search for more info on using GRANT if you are interested in those features.
Abandoned
WAMP

Comments

January 14, 2011 - 2:08am — Anonymous

You don't need to go to c:\mysql\bin, as you will find a command shortcut on the start-> program files in Windows.
However, it is good to have this explanation.

May 20, 2011 - 10:08am — Anonymous

> mysql -u root mysq
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
If you don't remember the original password, you'll get something like the above message.

The solution for this is:
  • stop mysql
  • save the query SET PASSWORD FOR root@localhost=PASSWORD('newpass'); in a text file
  • start mysqld with ---init-file=C:\\mysql-init.txt (or whatever you named the above file)
    (note: sometimes you need --defaults-file to specify my.inf)
source: Resetting the Root Password: Windows Systems

December 15, 2015 - 5:53pm — ricocheting

Just dealt with this on a friend's Mac OSX 10.10.5 (Yosemite) when he was setting up Kodi with MySQL v5.7.10 and kept getting ERROR 1045 Access denied for user 'root'@'localhost' (using password: NO) where the install root password he was given did not work.

What we had to do to fix the problem

  • Start MySQL manually (not with the GUI) using --skip-grant-tables parameter (that skips authentication): sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables
  • Then start a mysql prompt: sudo /usr/local/mysql/bin/mysql
  • Then in mysql> update the root password:
    USE mysql;
    UPDATE `user` SET `authentication_string`=PASSWORD('newpass') WHERE `user`='root';
  • At which point you need to stop MySQL and start it normally. You can then continue on as normal with your root new password sudo /usr/local/mysql/bin/mysql -u root -p