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?

  • 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 '15 at 4:15
  • No, that's not what I said... datadir location is EMPTY on both new and old datadir. – Max Hunter Mar 17 '15 at 4:29
  • Are you sure the datadir is completely empty? No ibdata* file there? – ypercubeᵀᴹ Mar 17 '15 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 '15 at 15:19
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.
  • Like I mentioned, I restarted the server (even the service) and the data was still present. – Max Hunter Mar 18 '15 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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