Mysql – Install MySQL for Windows from .zip and reset root password

MySQLmysql-5.7password-recoverywindows

I wanted to have on my PC (with Windows 8.1 x64) the MySQL server only, without Workbench or something. So I downloaded .zip archive from dev.mysql.com/downloads. It's a download for Win64 on x86_64 version 5.7.9 (MySQL Community Server (GPL)).

I installed it as a Windows service, but there were no mysql database, only information_schema. So I executed this:

mysql_upgrade.exe --upgrade-system-tables

And mysql databases were created. But along with it something happened with root user, because I couldn't access mysql anymore.

So I decided to reset this suddenly appeared password (because I didn't have it before that). I founded the following solution in the official manual, I started the server like this:

mysqld.exe --skip-grant-tables --console

Then I opened mysql without password: 

mysql.exe –u root

And then tried to reset the root password:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD(‘passhere');

But I got this error:

ERROR 1131 (42000): You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords".

What is this? How am I anonymous? All commands were executed in cmd.exe under the Administrator.

What should I do to reset the root password in this situation?

Update 1: I tried to check the current user:

SELECT USER(), CURRENT_USER();

That gives:

+--------+----------------+
| USER() | CURRENT_USER() |
+--------+----------------+
| root@  | @              |
+--------+----------------+

That's weird, because I started it like mysql.exe -u root.

Then I checked users table:

SELECT user FROM mysql.user;

That gives:

+-----------+
| user      |
+-----------+
| mysql.sys |
+-----------+

That's even more weird. Also there is no password field:

SELECT user, password FROM mysql.user;

ERROR 1054 (42S22): Unknown column 'password' in 'field list'

So I cannot change its password.

And I cannot create a new user:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'passhere';

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Now I'm lost.

Update 2: I guess, I did everything wrong from the beginning. Apparently, I missed a mysqld.exe --initialize command somehow.

So, this is how I installed MySQL from .zip-archive, thanks to @RolandoMySQLDBA:

  1. Unpack archive, prepare my.ini.
  2. Execute mysqld.exe --initialize, get temporary password from the .err file.

  3. Install MySQL as a service and run it

    mysqld --install MySQL --defaults-file="d:/path/to/mysql/my.ini"

  4. Connect to it mysql -u root -p with temporary password.

  5. Change the temporary password:

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';

Best Answer

Since you started mysqld with --skip-grant-tables, you cannot execute any standard GRANT, REVOKE, or SET PASSWORD commands. Notwithstanding, you can change the password of root@localhost as follows:

UPDATE mysql.user SET password=PASSWORD(‘passhere') WHERE user='root' and host='localhost';

Then, restart mysqld and you are back in business.

As to you being anonymous, if you login right now and run

SELECT USER() HowYouAttemptedToLogin,CURRENT_USER() HowYouWereAllowedToLogin;

You will note that the HowYouWereAllowedToLogin will have an blank username and some host.

GIVE IT A TRY !!!

UPDATE 2015-11-17 16:24 EST

The column known as password in mysql.user no longer exists in MySQL 5.7.

It was renamed authentication_string.

Proper Approach

What you should have done is run

mysqld --initialize

That would create the data folder for you.

The root@localhost was assigned a temporary password, which is visible in the error file starting with the following datetime and string

2015-11-17T20:56:02.175980Z 1 [Note] A temporary password is generated for root@localhost:

You could log in with it and then run ALTER USER

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

See the MySQL 5.7 Documentation on SET PASSWORD

Related Question