MySQL running fine while data files (disk) are not present. Data folder nowhere to be found I'm running MySQL server 5.6 on Windows 2012 R2, and since the installation didn't go as planned, I had to re-install it to change the data directory path.
The installer for some reasons never gave the option back to change the directory upon installation, despite multiple restarts.

I have re-installed MySQL anyway, stopped the service, deleted the "data" folder, and tried changing basedir through my.ini. It never worked. When I type "SHOW VARIABLES WHERE Variable_Name LIKE "%dir"" through SQL it returns that the data folder (datadir) is still "C:\ProgramData\MySQL\MySQL Server 5.6\Data\".

I know I shouldn't have deleted the data folder, but I figured they would create a fresh new DB when I delete it.

What's weird is that my database is working probably through some sort of cache.
I backed up my database through phpmyadmin, and restarted the server machine to see if the database is gone because the data folder is missing.
Nope, I was wrong. The database is still there with no data loss! And the data folder is still missing!

How is this even possible?
Where is the database being stored right now then?. How could I force MySQL server to write what's in the cache into the disk?
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:


mysqldump data
verify dump is good
restore into a different server to verify it worked
swap out datadir depending on your maintenance window requirements; eg: copying files will be faster than importing a mysqldump file on large datasets.