3

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?

Improve this question
4
  • you changed basedir varibale whereas data files are present in datadir variable, if you want to change datadir location, stop mysql service, change datadir location in my.ini, copy the folder to new location and start mysql service from there..hope it helps
    – Nawaz Sohail
    Mar 17, 2015 at 4:15
  • No, that's not what I said... datadir location is EMPTY on both new and old datadir.
    – Max Hunter
    Mar 17, 2015 at 4:29
  • Are you sure the datadir is completely empty? No ibdata* file there?
    – ypercubeᵀᴹ
    Mar 17, 2015 at 10:40
  • Yes. I know it's empty because "data" folder isn't even present. Is there any way to dump the cache into disk files?
    – Max Hunter
    Mar 17, 2015 at 15:19

1 Answer 1

Reset to default
1

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.
Improve this answer
1
  • Like I mentioned, I restarted the server (even the service) and the data was still present.
    – Max Hunter
    Mar 18, 2015 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.