94

I installed a MySQL server with installer and it started. After reboot I tried to start it again and get the error:

D:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld -u root -p
mysqld: Can't change dir to 'D:\Program Files\MySQL\MySQL Server 5.7\data\'     (Errcode: 2 - No such file or directory)
2015-11-17T08:30:18.822962Z 0 [Warning] TIMESTAMP with implicit DEFAULT     value is deprecated. Please use --explicit_defaults_for_timestamp server option     (see documentation for more details).
2015-11-17T08:30:18.822962Z 0 [Warning] Insecure configuration for --secure-    file    -priv: Current value does not restrict location of generated files.     Consider setting it to a valid, non-empty path.
2015-11-17T08:30:18.822962Z 0 [Note] mysqld (mysqld 5.7.9) starting as     process 1108 ...
2015-11-17T08:30:18.838586Z 0 [Warning] Can't create test file D:\Program     Files\MySQL\MySQL Server 5.7\data\DESKTOP-RNBR3E8.lower-test
2015-11-17T08:30:18.838586Z 0 [Warning] Can't create test file D:\Program     Files\MySQL\MySQL Server 5.7\data\DESKTOP-RNBR3E8.lower-test
2015-11-17T08:30:18.838586Z 0 [ERROR] failed to set datadir to D:\Program     Files\MySQL\MySQL Server 5.7\data\
2015-11-17T08:30:18.838586Z 0 [ERROR] Aborting


2015-11-17T08:30:18.838586Z 0 [Note] Binlog end
2015-11-17T08:30:18.838586Z 0 [Note] mysqld: Shutdown complete

I tried to reinstall MySQL.
UPDATE:
When I run mysqld -u root -p as administrator, nothing happens.

Boris
  • 4,944
  • 7
  • 36
  • 69
  • 3
    Make sure the folder exists and mysql user has permissions – georgecj11 Jun 01 '16 at 16:36
  • Same just happened to me. I believe the problem is 1. nonstandard install directory (not C:\ ..) and \ or 2. nonstandard Data directory and \ or 3. MySQL not installed as service. I solved it using nulliusinverba's answer, though it seem the instance (process) still behaves strange. – Riva Aug 01 '16 at 23:54
  • If you are installing on Windows, I highly recommend using the `.msi` installer. Much much easier than manual installation – Kellen Stuart Sep 13 '17 at 18:01
  • 1
    @KolobCanyon Unfortunately you can't use the msi installer to upgrade between major versions – Ian Oct 04 '18 at 03:04

17 Answers17

102

I have met same problem. In my case I had no ..\data dir in my C:\mysql\ so I just executed mysqld --initialize command from c:\mysql\bin\ directory and I got the data directory in c:\mysql\data. Afterwards I could use mysqld.exe --console command to test the server startup.

1stthomas
  • 731
  • 2
  • 15
  • 22
keerthiprasath
  • 1,062
  • 1
  • 7
  • 7
  • 2
    For the record, the complete procedure for installing and configuring the "noinstall" version of the MySQL Server is detailed [in the official docs](https://dev.mysql.com/doc/refman/8.0/en/windows-install-archive.html) – mcont Mar 07 '19 at 19:28
  • 2
    Also, don't forget to start the `cmd` as `Administrator` (i.e. right click on `cmd` --> Run as Administrator – JejeBelfort Mar 18 '19 at 14:37
  • 1
    this helped me to bypass problem ( [ERROR] failed to set datadir to C:\Program Files\MySQL\MySQL Server 5.7\data\ ) when trying to reset root pw on windows10 – Drahoš Maďar Apr 24 '19 at 08:54
  • I create the data folder manually and run this command `mysqld --initialize` and the problem solved. – Mustafa Poya Aug 09 '22 at 13:14
  • 1
    I did the same thing as @Mustafa Poya by creating a "data" folder, and then ran the command in cmd as administrator and it worked - HOWEVER - I went into services.msc and saw that MySQL80 wasn't running, so I had to "Start" MySQL80 service, and then when I opened up MySQL Workbench, everything worked – SergeNYC Jun 30 '23 at 14:11
99

Since you used the Windows installer, everything is set up for you to run MySQL 5.7 as a Windows service, which is a great option in most cases.

Instead of running mysqld.exe from the command line,

  1. Win + R
  2. Run services.msc
  3. Right-click on MySQL57
  4. Start the service.
AaronDanielson
  • 2,230
  • 28
  • 29
  • 16
    Except when you specify during installation (as I did) that you don't want to install MySQL server as service. Than the service is not created at all and you will be searching for it in Services in vain. – Riva Aug 01 '16 at 23:57
  • 2
    If you'll be working with MySQL all the time, you can ask it to be loaded automatically with Windows. Follow @AaronDanielson instructions, right-click the MySQL57 service, open "Properties" and change the "Type of initialization" from "manual" to "automatic". – Renato Byrro Jun 22 '17 at 14:56
  • If you work in Windows as a restricted user then you click the Windows Start Symbol, type in services.msc and then right click it and start as admin.. – Robi Wan Kenobi Nov 17 '17 at 17:54
  • 2
    First run "mysqld -u root --initialize-insecure". It will create data folder with root as user without password and necessary file. Then run "mysqld.exe --console". – Nizar Sep 03 '18 at 07:43
  • This way it doesn't process the init file and the password is not reset. It starts but I still can't get in. – RichMeister Jan 11 '19 at 19:37
30

In my case, I had installed the data directory to a different location. So the data directory really wasn't in the default location. Therefore, when I ran the mysqld command from the command prompt, I had to specify the data directory manually:

mysqld --datadir=D:/MySQLData/Data

Here's the documentation for mysqld command-line arguments.

Hanshan
  • 3,656
  • 5
  • 29
  • 36
  • Thanks for this answer. It solved my problem. Mysql installed the data directory in the ProgramData directory whereas mysqld is looking for it in the Program Files. With your answer, I just specified the --datadir and bam, it worked! – Michael Iyke Oct 01 '19 at 12:34
  • This worked when I had MariaDB portable moved to another location. Thanks for the answer! – Ben Mar 30 '20 at 09:15
19

First run 

mysqld -u root --initialize-insecure

It will create data folder with root as user without password. Then run 

mysqld.exe -u root --console
Nizar
  • 500
  • 3
  • 12
  • I ran your first instruction and it created my folder "data" and after like a documentation:https://dev.mysql.com/doc/mysql-windows-excerpt/5.7/en/resetting-permissions-windows.html : I ran this instruction:C:\> mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 5.7\\my.ini" --init-file=C:\\mysql-init.txt – vljc2004 Oct 05 '18 at 14:45
11

What I did (Windows 10) for a new installation:

  1. Start cmd in admin mode (run as administrator by hitting windows key, typing cmd, right clicking on it and selecting "Run as Administrator"

  2. Change into "MySQL Server X.Y" directory (for me the full path is C:\Program Files\MySQL\MySQL Server 5.7")

  3. using notepad create a my.ini with a mysqld section that points at your data directory

    [mysqld]
datadir="X:\Your Directory Path and Name"

  4. created the directory identified in my.ini above.

  5. change into bin Directory under server directory and execute: mysqld --initialize

  6. Once complete, started the service and it came up fine.

Floern
  • 33,559
  • 24
  • 104
  • 119
Keith Ballantyne
  • 111
  • 1
  • 2
  • Works for me, but when I try to login as root for the first time it says Access Denied. I thought the default root password was no password. – Ozymandias May 16 '17 at 21:38
7
  1. Check that the director exists.
  2. If it exists make sure mysql has access rights on it (read/write). Can be a good idea to run MySQL as a system process, but not mandatory.

If this is stil not working try having the following notation with double slashes: C:\WebSerer\MySQL\data or C:/WebServer/MySQL/data

Stephane Paquet
  • 2,315
  • 27
  • 31
3

If you are having this issue and can't find the MySQL service either, just launch "MySQL Installer" from the Start menu (or download it), then choose "Reconfigure" on MySQL Server. Follow the steps to set a root user password and then the service will be created and started automatically (and set to auto-start on boot).

Stuart Welch
  • 409
  • 5
  • 3
2

In mysql 8.0.13 zip package initializing.

  1. Verify that data folder is empty.

  2. Under the mysql bin path run

    mysqld.exe --initialize-insecure

  3. Add to my.ini native mysql

    [mysqld]

    default_authentication_plugin=mysql_native_password

Community
  • 1
  • 1
mariofertc
  • 383
  • 2
  • 7
2

When I encountered this same error, I noticed MySQL Configuration file in "C:\Program Files\MySQL\MySQL Server X.Y\" has changed to my-default.ini

I solved it by

  1. Copy my.ini from "C:\ProgramData\MySQL\MySQL Server X.Y\my.ini"
  2. Paste it in "C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
  3. Restart MySQL Server from services.msc

In the .ini file, their is part that reads:

# On Windows you should keep this file in the installation directory 
# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To
# make sure the server reads the config file use the startup option 
# "--defaults-file".
Suleiman Umar
  • 31
  • 3
1

Check your real my.ini file location and set --defaults-file="location" with this command

mysql --defaults-file="C:\MYSQL\my.ini" -u root -p

This solution is permanently for your cmd Screen.

Ferhat KOÇER
  • 3,890
  • 1
  • 26
  • 26
1

remove all files in "{path-to-mysql}\data" directory and run:

mysqld --initialize-insecure --basedir={path-to-mysql}\mysql --datadir={path-to-mysql}\data --console
1

I don't know if it's your problem but try this...

For me, the version of my.ini which is created under "ProgramData\MySQL\MySQL Server [version]" wasn't read. I had to move it to "Program Files\MySQL\MySQL Server [version]".

RTS.

Rex the Strange
  • 133
  • 1
  • 6
0

If you installed MySQL Server using the Windows installer and as a Window's service, then you can start MySQL Server using PowerShell and experience the convenience of not leaving the command line.

Open PowerShell and run the following command:

Get-Service *sql*

A list of MySQL services will be retrieved and displayed. Choose the one that you want and run the following command while replacing service-name with the actual service name:

Start-Service -Name service-name

Done. You can check that the service is running by running the command Get-Service *sql* again and checking the status of the service.

Edmond Weiss
  • 471
  • 5
  • 11
0

mariofertc completely solved this for me here are his steps:

  1. Verify mysql's data directory is empty (before you delete it though, save the err file for your records).

  2. Under the mysql bin path run: mysqld.exe --initialize-insecure

  3. add to my.ini (mysql's configuration file) the following: [mysqld] default_authentication_plugin=mysql_native_password

Then check services (via task manager) to make sure MySql is running, if not - right click MySql and start it.

I'll also note, if you don't have your mysql configuration file in the mysql bin and can't find it via the windows search, you will want to look for it in C:\Program Data\Mysql\ Note that it might be a different name other than my.ini, like a template, as Heesu mentions here: Can't find my.ini (mysql 5.7) Just find the template that matches the version of your mysql via the command mysql --version

DRoberts
  • 29
  • 4
0

This solution uses the windows mysql installer.

I have tried every other way mentioned here and other related posts, but it did not solve my problem, the service just wont start, but the below approach with the mysql-installer did.

If you still have your installer or atleast remember the version then follow below steps:

  1. Start your windows mysql installer. For me it was "mysql-installer-community-8.0.20.0"
  2. Then remove/uninstall the SQL Server and remove all configurations
  3. Manually delete the SQL Server folder from "C:\Program Files\MySQL\MySQL Server 8.0."
  4. Start your mysql installer again and install the SQL Server again

You can check from the window's services that the MySqL Server has started.

Hope it helps someone.

Lazycoder-007
  • 1,055
  • 9
  • 19
0

Check for missing folders that are required by the server, in my case it was UPLOADS folder in programData which was deleted by empty folder cleaner utility that I used earlier.

How did I find out:

run the server config file my.ini(in my case it was in programData) as the defaults-file param for mysqld (don't forget to use --console option to view error log on screen) 'mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --console'

Error:

mysqld: Can't read dir of 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\' (OS errno 2 - No such file or directory)

Solution:

Once I manually created the Uploads folder the server started successfully.

mahee96
  • 705
  • 6
  • 15
0

In my case, I'm MySQL5.6, install by workbrench install tools in Win10 pro. there are two configuration file need to modify, only edit the line: datadir=E:\MySQL

  1. stop MySQL56 service using services.msc
  2. edit my.ini under C:\ProgramData\MySQL\MySQL Server 5.6
  3. edit my-default.ini under C:\Program Files\MySQL\MySQL Server 5.6
  4. copy C:\ProgramData\MySQL\MySQL Server 5.6*.* to E:\MySQL
  5. start MySQL56 service using services.msc

test: create database 'testdb'; It's successed if found testdb folder exist in E:\MySQL.

allen yang
  • 95
  • 1
  • 3