Running MySQL in virtualbox with data dir on shared folder sometimes results in data corruption I'm trying to move our development environment onto a vagrant box (virtual machine using Oracle virtualbox). Currently I'm stuck on one problem:

The environment needs a MySQL server and some databases. I've got all the scripts set up that install MySQL, create and populate the databases, etc. That works.

Since the developers are likely to set up further data of their own, my idea was to put the MySQL data folder in a shared virtualbox folder. This way when the vagrant machine is destroyed/recreated, the data folder remains and can be reused without loss of data.

However sometimes (but not always, I don't know what triggers it) when I simply suspend and then resume the virtual machine, MySQL starts complaining that a lot of its tables (but not all) are corrupted. Fixing the tables results in all data in them being lost. Obviously this is unacceptable.

Does anyone have any idea why this happens and how to prevent it?

A few possible clues or red herrings - since I read that virtualbox's shared folders are slow, I've increased the InnoDB buffer pool size to 1GB, so that it has a lot of cache space. We do use InnoDB and MyISAM in proportions of about 50/50. I haven't yet checked if the failed tables all belong to the same engine - I'll pay attention to it when it next crashes.

Update

Ha, I managed to catch it red handed! It's actually... suspense... BOTH InnoDB and MyISAM.

Selecting from a MyISAM table gives the error


  Table 'xxx' is marked as crashed and should be repaired


Trying to run mysqlcheck -A -a (analyze all tables) actually CRASHES MySQL. The log files tell the story (edited for clarity):

[Warning] InnoDB: Retry attempts for writing partial data failed.
[ERROR] InnoDB: Write to file ./ib_logfile0failed at offset 29885952, 1024 bytes should have been written, only 0 were written. Operating system error number 71. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
[ERROR] InnoDB: Error number 71 means 'Protocol error'
[ERROR] mysqld got signal 6 
Query (0x7fbc12501b50): ANALYZE TABLE `yyy`


And then, in a moment, systemctl restarts the service and all runs perfectly again.

My educated guess is that suspending down the virtual machine releases the locks that virtualbox was holding on opened files, and when virtualbox starts up again, it doesn't re-acquire them. So from MySQL's point of view, it's keeping a file opened, but virtualbox has lost that grip, so when MySQL tries to access the file again, it gets an error.
When the production box crashes, and you find that the MyISAM tables are corrupted there, and you find how how many hours (or days) it takes to REPAIR them, you may rethink whether moving to InnoDB is justified.

At some point your 'suspend' + corruption may make the MyISAM tables so messed up that dev is impacted.

OK, lecture over; back to your questions.

How big is the VM?  If you are 50/50 InnoDB/MyISAM on that box, then use these settings if the VM is at least 4GB:

key_buffer_size = 10% of the VM size
innodb_buffer_pool_size = 35%


If less than 4GB, lower those percentages some.  The goal is to avoid ever swapping.