I’m having an issue with MySQL on my Ubuntu system, which utilizes the ZFS file system. My default MySQL data directory is located at /var/lib/mysql, and this directory is based on a ZFS dataset.
Here’s the approach I’ve taken:
I take a snapshot of the ZFS dataset.
I then mount that snapshot to /var/lib/mysql1.
I change the owner to mysql:mysql using chown.
Next, I adjust the MySQL configuration to set the data directory to /var/lib/mysql1.
Finally, I attempt to restart the MySQL service.
However, MySQL fails to start with this new data directory setup, and it only starts when I revert it back to /var/lib/mysql. I’m using the InnoDB storage engine.
Am I overlooking something important? Are there specific permissions or configurations that need attention?
Ultimately, I’m aiming to configure hourly snapshots that can be transferred to another server over SSH for redundancy.
This happens because MySQL’s consistency checks fail when you point it to a snapshot mount. MySQL validates the data directory structure and file integrity on startup. When you mount a ZFS snapshot to a different path, MySQL spots inconsistencies in the binary logs or metadata that still reference the original path. I’ve fixed this by updating the MySQL config file - don’t just set the datadir, also explicitly set log-bin, log-error, and socket paths to match your new directory structure. Make sure you stop MySQL cleanly before taking the snapshot to avoid transaction log corruption. InnoDB is especially picky about path changes since its redo log files contain absolute path references.
zfs snapshots act weird with mysql. use zfs clone instead of mounting the snapshot directly - clones are writable and work like normal filesystems. check your mysql error log at /var/log/mysql/error.log for the actual error messages that’ll tell you what’s breaking.
Check if AppArmor’s blocking MySQL from accessing the new path. I hit this exact issue when I moved my data directory to a ZFS snapshot mount. AppArmor has MySQL profiles that only allow access to predefined paths like /var/lib/mysql/. Switch to /var/lib/mysql1/ and AppArmor blocks it even with correct ownership and permissions. Check /var/log/syslog for AppArmor DENIED messages to confirm. Quick fix: edit /etc/apparmor.d/usr.sbin.mysqld, add your new path, then reload with sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld. Or temporarily disable AppArmor for MySQL to test. Also make sure the socket path in your MySQL config matches the new directory - people forget this all the time.
I’ve encountered a similar issue with ZFS snapshots and MySQL. Typically, the problem arises because ZFS snapshots mount as read-only by default, preventing MySQL from writing to its data directory. Even after changing the ownership with chown, MySQL may still fail to operate correctly. Ensure you mount the snapshot using -o rw to set the correct read-write permissions. If you’re using SELinux, verify that the context is properly configured for the mounted snapshot, as this could also impede MySQL’s access. Additionally, confirm that all subdirectories under /var/lib/mysql1 possess the appropriate permissions, as the mysql user must have write access to certain directories for optimal functionality. For backups, consider utilizing MySQL’s built-in backup utilities alongside ZFS snapshots for a more reliable backup solution.