Running MySQL on ESFS

Root permissions

Please note that /etc/init.d/mysql (and perhaps mysql when it starts) attempts to access the data directory as root, which is not allowed by FUSE by default. A possible workaround is to specify -o allow_root when mounting the filesystem.

Performance

The MyISAM engine of MySQL uses a large number of short writes when rows in tables are updated, relying on the underlying filesystem to appropriately cache these to speed up I/O. Because of this, using a loop device can increase the performance of MySQL databases in ESFS - please see Improving Performance.

When taking a snapshot of a MySQL database, it is important to ensure that it is quiescent, which can be achieved using FLUSH TABLES WITH READ LOCK. It is easy to time taking the snapshot using the SYSTEM command. For example:

FLUSH TABLES WITH READ LOCK;
SYSTEM mkdir [ESFS_MOUNT_POINT]/snapshots/[SNAPSHOT_NAME];
UNLOCK TABLES

If you use a loop device for performance reasons, the call to fsfreeze can be integrated as well:

FLUSH TABLES WITH READ LOCK;
SYSTEM fsfreeze -f [LOOP_MOUNT_POINT];
SYSTEM mkdir [ESFS_MOUNT_POINT]/snapshots/[SNAPSHOT_NAME];
SYSTEM fsfreeze -u [LOOP_MOUNT_POINT];
UNLOCK TABLES

If the database is a slave, you may also want to save data needed to restart replication while the lock is in place.