0

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

1 Answer 1

2

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.

2
  • this is what i actually did, but is a heavyweight solution. I wanted to provide an independent solution for the customer to do (with just ssh access to their production and a dev server), without letting them setup and tear down additional VMs/Containers. This gets more complex (and I dont want to run SolusVM which would allow such, with IP pools for customer profiles, etc - using proxmox here). I suppose they could run a chroot, which I've actually setup for them, but is a bit complex still -- seems that is the most minimal way to get an actual mysqldump however.
    – math
    Oct 19, 2023 at 3:42
  • So basically you're trying to duplicate the Amazon RDS level of ease of use? That's a lot of work. Oct 19, 2023 at 3:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.