Changing Data Directory on MySQL 8

Question

Need: To store / accesses dbTable data from a separate drive not the default

I was using MySQL 5.7 so far & as specified in the solution - Link1 I added the my.ini file with the info as stated & it worked fine.

I now uninstalled MySQL 5.7, installed MySQL 8, created a my.ini file & specified the info as stated in this solution Link2. But when I check it still points to the default C:\ drive & hence unable to access data from the existing database stored in E:\ drive

SHOW GLOBAL VARIABLES LIKE 'datadir';

shows

C:\ProgramData\MySQL\MySQL Server 8.0\Data\

The new my.ini file which I have created as below in

C:\Program Files\MySQL\MySQL Server 8.0 

[mysqld]
datadir = E:/MySqlData
secure-file-priv="E:/MySqlData/Uploads"

Hope the issue faced is clear, await inputs.

Edit:

I found this input - Link Could anyone let me know if this could be a reason ? Also any known hex editors for Windows 10 ?

Update: Even after removing the hex characters, the server still does not start if I point the parameter datadir in my.ini file to my database folder in another drive

Answer 1

Make sure the directory where you want to write the data files is accessable for MySQL. I put it the security settings on Everyone.

You might need to copy the data also from old data directory to the new one.

Answer 2

Assuming you specified my.ini location when you created the service, then you can open services, right click MySQL service, and choose properties. open my.ini file and check datadir location.

installed MySQL without specify my.ini location, navigate to mysql\bin directory using PowerShell or CMD. then execute the following command

.\mysqld --help --verbose > \output\location\out.txt
 notepad \output\location\out.txt

then search for Default options are read from the following files in the given order: this should specify my.ini location. and locate it from their.

and change datadir based inside the used my.ini

Answer 3

Try to save as your my.ini to ANSI. MySQL's default encoding of my.ini now is UTF-8. It should be ANSI, then restart the mysql server.

Answer 4

It may be tricky if some detail was ignored. The initialize step is really crucial when the Server has been initialized before. There are 4 steps.

1.Check the MySql80 service configuration.

Almost the time we will have a config, like this

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

2.Change the datadir property in the my.ini file.

Maybe something like this datadir=E:\mysql\Data

3.Remove all files in the $datadir.

This is important and cannot be omitted. Or the initialize step will fail.

4.Reinitialize the MySQL80 service with the actual same options.

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --initialize --console

As you see, just remove the service name and append two options to the service starting command.

After initialization, the password for root will be printed on the console. Check this official document for more information;