7

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';
    
6

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

  • Thank you for reply. Yes, I'm aware of such restrictions. But these are the steps from MySQL's manual. And I tried to follow your instructions, please see my edited question. – retif Oct 29 '15 at 6:05
  • There is no password column anymore in mysql.user, so this method will never work again. However, currently also the other methods (via --skip-grant-tables and --init-file) are broken, so you cannot reset passwords in 5.7.9 atm. – Mike Lischke Nov 17 '15 at 15:25
  • @MikeLischke Thank you. I need to delete this answer – RolandoMySQLDBA Nov 17 '15 at 15:57
  • @MikeLischke I just realized the password column was renamed authentication_string. – RolandoMySQLDBA Nov 17 '15 at 21:25
  • Well, actually modifying the user table directly is a thing of the past and will go away, but even using standard commands (like alter user) don't work because currently they are refused as you are considered being an anonymous user which is not allowed to issue those commands (if you started the server with --skip-grant-tables or --init-file). The --initialize option however is the right way to reset the root pw. – Mike Lischke Nov 18 '15 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.