MySQL 8.0 'C:\Program Files\MySQL\MySQL Server 8.0\data\' (OS errno 2 - No such file or directory) - can't find data for db from cmd

Question

So, I'm looking to start a MYSQL server using CMD, and do some small simple tasks on it (hence why I'm using it that way and not a GUI)

I was told the best method for this, would be to run this command:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqld --console --standalone

Which I did, however, I am getting this error:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqld --console --standalone
mysqld: Can't change dir to 'C:\Program Files\MySQL\MySQL Server 8.0\data\' 
(OS errno 2 - No such file or directory)
2018-10-30T17:43:27.385678Z 0 [System] [MY-010116] [Server] C:\Program 
Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.12) starting as 
process 4676
2018-10-30T17:43:27.420966Z 0 [Warning] [MY-010091] [Server] Can't create 
test file C:\Program Files\MySQL\MySQL Server 8.0\data\DESKTOP- 
HITD28G.lower-test
2018-10-30T17:43:27.421409Z 0 [Warning] [MY-010091] [Server] Can't create 
test file C:\Program Files\MySQL\MySQL Server 8.0\data\DESKTOP- 
HITD28G.lower-test
2018-10-30T17:43:27.423273Z 0 [ERROR] [MY-010172] [Server] failed to set 
datadir to C:\Program Files\MySQL\MySQL Server 8.0\data\
2018-10-30T17:43:27.434694Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-10-30T17:43:27.442047Z 0 [System] [MY-010910] [Server] C:\Program 
Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 
8.0.12)  MySQL Community Server - GPL.

I have MySQL 8.0 installed, with the server, workbench etc, but apparently the data isn't there.

Have I installed it wrongly then?

Answer 1

While installing MySQL 8.0 via Windows Installer, in the configuration step, the installer puts the configuration file my.ini at location C:\ProgramData\MySQL\MySQL Server 8.0 and sets the variable defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini for mysqld command.

defaults-file just points to the file from where the server reads the default configuration settings when it actually gets started. Read more here.

Now, there are actually two ways to start the server without the GUI requirement.

---

Method 1: Running the server using installed Windows service

Either start, stop and restart the MySQL80 service through Services app or use net commands in cmd to do the same.
(As pointed out by @nonNumericalFloat)

Method 2:

(This actually worked for me)

The MySQL80 service starts the mysqld.exe executable which actually points to the configuration file, my.ini and to the defaults-file variable as evident by the Path to executable field under the Properties tab of the service.

So using the mysqld command alone is not sufficient as it neither provides the path to the default option file needed by the server during startup which the MySQL80 service provides (as described before) nor it provides the right location of data folder (which is also specified within my.ini by datadir) . Thus, the right command to run the server is as follows:

mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --console

Also, because when mysqld is used alone, it tries to find the data folder in C:\Program Files\MySQL\MySQL Server 8.0\data which actually is not present, instead of setting defaults-file, datadir can be set to point to the right data folder location using the following command:

mysqld --datadir="C:\ProgramData\MySQL\MySQL Server 8.0\Data" --console

(Similar workaround mentioned by keerthiprasath)

console parameter is used to show any error and/or status messages while the command is being executed.

Another point to note that within the my.ini file, it is specified that under "Installation Instructions",

# On Windows you should keep this file in the installation directory 
# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y).

So after copying my.ini file from C:\ProgramData\MySQL\MySQL Server 8.0 to C:\Program Files\MySQL\MySQL Server 8.0, mysqld --console alone works fine.

Note:
Commands in method 2 only works when cmd is granted with administrative privileges.
Also, PATH needs to be set for easier invocations of the MySQL programs.

Answer 2

I was facing the same problem; Apparently, after shutting down my server I was not able to start it anymore.

The Server itself was installed as a service - named "mysql" by default. I did not remember renaming it to something else, so I could not start the right service.

Try installing it as a service

1. Start cmd
2. Go to the "C:\Program Files\MySQL\MySQL Server 5.6\bin"
3. type mysqld --install

then start the service:

METHOD #1: Access the Installed Service

Open up the Services app in the Control Panel & Scroll alphabetically to the MySQL service:

Right click the service Click Start Service

METHOD #2: Command Line Execution

Open DOS Window From the C: Prompt and run this:

net start mysql or net start mysql57. The right name may vary on your system

For further information on installing or removing the service check the MySQL docs.