Running MySQL in virtualbox with data dir on shared folder sometimes results in data corruption

Question

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.

Answer 1

Maybe you can stabilize your system by putting the database files on a dedicated virtual hard disk. Steps: set up a good size VHD, give it a mount point, format it, add its mount point to /etc/fstab, copy all database files to the new VHD. Then, edit the (database server's) configuration files so that they point to the files on the new virtual hard drive.

Yes, we can set permissions etc in order to protect the files on a shared folder, but a virtual hard drive could make a better "container" for the database, maybe more suitable for your requirements. M Hashimoto has run some tests, comparing the performance of shared folders/virtual environments/NFS (see the report: http://mitchellh.com/comparing-filesystem-performance-in-virtual-machines). Not that you were complaining about the performance as such, but shared folders with VirtualBox seem to have some "difficulties" (or: seem to have had them in the past).

If you really have to "suspend" the VM, there are some hints here (https://askubuntu.com/questions/63524/whats-the-best-way-to-pause-my-work-in-virtualbox-with-ubuntu-as-a-guest). I can imagine that this works smoother when all files (associated with the db server) are located on virtual hard drives that are attached to the VM.

You can also detach (and keep) the virtual disk that contains the database files, create a new VM, and attach the disk to a new VM if need be.

Answer 2

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.