Since yesterdays test on ZFS snapshots, we now have 25 snapshots in the snapshot archive which can be seen below.
# cd /DATA/mysql/.zfs/snapshot/ # ls -lah total 36 dr-xr-xr-x 21 root wheel 21B Oct 16 21:00 . dr-xr-xr-x 4 root wheel 4B Sep 24 20:23 .. drwxr-xr-x 5 mysql mysql 16B Oct 15 21:50 2013101522 drwxr-xr-x 5 mysql mysql 16B Oct 15 21:50 2013101523 == snip == drwxr-xr-x 5 mysql mysql 16B Oct 15 21:50 2013101620 drwxr-xr-x 5 mysql mysql 16B Oct 15 21:50 2013101621
These were generated by the ‘cronned’ script which is making the hourly snapshot and naming it accordingly. The next test is to perform a realistic restoration of a specific record. Its unlikely that we would just want to roll back to a previous time, but more likely a bit of data has been lost and we need it back to re-insert into the current data. The solution proposed here is to copy and rename the archived database and then do the data transfer to the live database.
In this test I chose the snapshot from 11:00 today and am restoring the ‘extranet’ database to a database called ‘restored’.
# cd 2013101611 # cp -R extranet/ /DATA/mysql/restored # chown -R mysql /DATA/mysql/restored/
Now we should be able to open the database up and see the results:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | extranet | | mysql | | restored | | test | +--------------------+ mysql> use restored; Database changed mysql> show tables; +-----------------------+ | Tables_in_restored | +-----------------------+ | table1 | | table2 | == snip == +-----------------------+ nn rows in set (0.00 sec)
All looks fine, but the search goes on….