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 databases were created. But along with it something happened with root user, because I couldn't access
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();
+--------+----------------+ | 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;
+-----------+ | 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-tablesoption 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:
- Unpack archive, prepare
mysqld.exe --initialize, get temporary password from the
Install MySQL as a service and run it
mysqld --install MySQL --defaults-file="d:/path/to/mysql/my.ini"
Connect to it
mysql -u root -pwith temporary password.
Change the temporary password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';