When you install “MySQL on Windows”, the database Data Files are by default placed/located in “C:/ProgramData/MySQL/MySQL Server 5.1/Data/” folder as shown below:
As a best practice, a DBA would always like to have the DB data files to be placed on a separate drive/volume. To change the folder location, you need to modify it in “MySQL Workbench” > Under Instance, Options File > General Tab > Directories > datadir and set the path to the folder on a another drive/volume (as shown below). Then click Apply.
Click Apply on the “Apply Changes to MYSQL Configuration File” (“C:/Program Files/MySQL/MySQL Server 5.1/my.ini”)
Then copy over the Data Files from initial location to the new location. That is from “C:/ProgramData/MySQL/MySQL Server 5.1/Data/” to “D:/myDB” in above example.
Now, “MySQL Workbench” > Under Instance, Startup / Shutdown MySQL Server” > click “Stop Server”
Rename the existing old Data Files location. That is, “C:/ProgramData/MySQL/MySQL Server 5.1/Data/” to “C:/ProgramData/MySQL/MySQL Server 5.1/Data-OLD/”
p>Now Re-launch the “MySQL Workbench” then goto Under Instance, Startup / Shutdown MySQL Server” > click “Start Server”. Provide the password for the two prompts you receive. Then now your MySQL should be running fine with Data Files being relocated or moved from it’s default “C:/ProgramData/MySQL/MySQL Server 5.1/Data/” to “D:/myDB”.
