MySql service on Windows won't start after changing datadir

Question

Tried to change the data directory for a default Windows installation of MySQL. Here's the steps I've taken.

• Stopped the MySql service.
• Located the data directory of MySql and moved it to another drive, ensuring the path name had no spaces.
• Made a copy of the my-default.ini file in C:\Program Files\MySQL\MySQL Server 5.7 then renamed it to my.ini and edited it to look like this:

[mysqld] datadir = F:\ProgramData\MySQL\MySQLServer\Data

• Copied the contents of the original data folder into this new data folder.
• The MySql Service is running under the NETWORK SERVICE user, so I have given this user full control permission on the new folder.
• Deleted the original data folder.
• MySql Service fails to start, with no useful error messages appearing in the windows event logs.

As far as I know, the MySql error logs are located in the datadir, so it's not much help looking there?

How can I find and fix what I've done wrong?

Answer 1

If You open Windows Services and check the service properties, You will find something like this:

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57

not enough to change datadir in my.ini file, need also point windows to run service with proper my.ini file

Answer 2

In my case it also had to do with the file encoding of Windows Notepad. When editing the my.ini file, the server could not read it because it is default saved in UTF-8. By choosing "Save As" and selecting the ANSI encoding, it worked fine.

Answer 3

Server was not starting for me after changing data folder.

I tried to copy all security permisions by using xcopy /S or xcopy /O/X/E/H/K, but it was not copying the special permissions. I also manually added network permissions, but was still not able to start mysql server.

The only thing which worked for me was go to the mysql service properties and change "start session" to Local System. And now it's working for my needs.

Answer 4

The only thing that worked for me was to edit the my.ini file using Notepad++ ... do NOT use Windows Notepad as any change will stop mysql from starting.

I would guess this is because mysql fails to parse the file, so uses internal defaults which are no good as its looking for mysql master db in 'program files'.