MySQL ZFS snapshot restoration

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….

This entry was posted in FreeBSD Administration, MySQL, ZFS and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *