Historical data, nightly snapshots
I have an application where I need to take nightly snapshots of a large InnoDB database (10s of GB) and then have them available to be queried alongside the current data. My underlying filesystem is (or at least can be) ZFS, so actually creating the snapshots is easy enough. But how do I connect to the files contained in the snapshot so that I can query the tables via MariaDB?
If it's easier through another storage engine, I could consider that, though I do need row locking in the current data. The historical data would be read only (obviously) and wouldn't need to be changed.
Answer Answered by Sergei Golubchik in this comment.
If you have a snapshot of innodb tablespaces, they weren't properly closed, so they look like they were a result of a crash. The first time you start innodb on these files, it will perform a crash recovery, then the tables will be usable.
So, you can do the following:
- start another mariadb on your snapshot, let innodb do its crash recovery.
- export tablespaces using FLUSH TABLES FOR EXPORT.
- copy them into your running mariadb and import them there.
after that you can use these tables in the running mariadb instance
And, of course, you don't really need to copy multi-GB files, you can just move or hard-link them in place.
