MySQLwindows
I'm gonna install MySQL 5.5 in a virtual Windows Server. I'd like to have the data in an separate partition (it may be in a different virtual disk) than the system one. I'm considering two options:
Install MySQL in the default installation path ("C:\Program Files\MySQL\MySQL"), create the data folder in the "D:\mysqldata" and modify the "my.cnf" to point to the new folder
Install MySQL in directly in "D:\mysql"
Are there any difference between then? Is there a recommended approach?
The maintenance feature is a bit hidden in MySQL Workbench. You can reach it by opening your connection as usual and right clicking on your schema to open the schema inspector:
You will get a new tab in the editor area with many pages for inspection/maintenance. Click on the Maintenance button at the bottom to open this page:
From there it should be clear what to do.
In Linux, the MySQL process will keep the files open until it's done with them, even if you delete the files. This can be viewed with lsof -p $(pidof mysqld)
I suspect this is true in Windows as well, as your question indicates. A quick Google indicated something about handler process keeping files open, but don't quote me!
Regardless, the bad news is you can't force MySQL to write it to disk. The data will be gone as soon as you either a) stop/start service or b) issue a FTWRL as is common in many backup strategies
The good news is you can still get the data back if you mysqldump it out.
My recommendation is:
Best Answer
I do not think that the location matters rather it is a matter of personal preference, and the MySQL installer will automatically update the my.cnf file for you.
However I would rather go with D:\mysql as the installation directory because its lowercase, has no special characters (spaces, capital letters) and is not in the Program Files directory which you may need to clean out for a new install or upgrade.
You may also need to add the D:\mysql\bin directory to the PATH environment variable so that you can run the MySQL commands from any command prompt