Copy single mysql database from filesystem snapshot without booting snapshot or mysqldump

Question

The question on how to copy without mysqldump has been answered - however, that entails duplicating the entire mysql tree.

My scenario is I have (zfs) snapshots of the filesystem at various times, and I wish to 'rollback' a single database to recover deleted records from some tables. (The entire tree is large, but each single database is small - not copying the whole tree is of great value.)

Without having to boot the whole vm or a mysql server from the snapshot dirtree, I want to copy just the relevant files into a mysql install.

Blindly doing so seems to result in some missing tables (perhaps dependent on the engine type). I am guessing some metadata regarding that specific database is missing or otherwise.

I realize a binary copy of a running database from a snapshot is not perfect, and a mysql repair may be required, but it is better than data loss.

(A flush tables before the snapshot is performed, but the flush+snap is not fully atomic. Nor can we afford the time to lock the databases for long enough to perform a mysqldump of the entire tree).

Copying just the single database's directory into an existing mysql server tree does not result in a coherent database - there are missing tables:

mysql> drop database S21031201;
mysql> create database S21031201;
mysql> exit;
# service mysql stop;
# cp -rp $sourcebinarytree/var/lib/mysql/S21031201 /var/lib/mysql
# service mysql start;
# mysqlrepair -r S21031201;
S21031201.attestation
Error    : Table 'S21031201.attestation' doesn't exist in engine
status   : Operation failed

Answer 1

There's no way to restore part of a filesystem snapshot.

It's inadvisable to try to "restore" MySQL tables by copying files around on the filesystem. That's a good way to corrupt your whole instance (even the other schemas).

What you'll have to do is this multi-step procedure:

1. Restore a snapshot on a temporary server (not your live instance)
2. Use mysqldump to export the single schema that you need
3. Import that dump to your live instance
4. Discard the temporary server

An alternative strategy is to run each schema on its own separate MySQL instance, to make it easier to restore each schema independently using snapshots.