The blog post serves as a follow-up to Deploying MySQL over NFS using the Oracle ZFS Storage Appliance and Deploying MySQL over Fibre Channel / iSCSI using the Oracle ZFS Storage Appliance.
The snapshot and clone features of the Oracle ZFS Storage Appliance provides a convenient mechanism to backup, restore, and fork a MySQL database. A ZFS snapshot is a read-only copy of a file-system that is created instantaneously while initially occupying zero additional space. As the data of a file-system changes, the differences are tracked inside the snapshot. A ZFS clone is a writeable snapshot that can be used to branch off an existing filesystem without modifying the original contents.
Creating a MySQL Snapshot
1. From the MySQL console, disable autocommit:
mysql> set autocommit=0;
2. From the MySQL console, close and lock all database tables with a read lock. This will help to create a consistent snapshot.
mysql> flush tables with read lock;
3. From the ZFS console, create a project-level snapshot of the MySQL project:
zfs:> shares select mysql snapshots snapshot test_snapshot
4. From the MySQL console, unlock all tables to restore the databases to normal operation:
mysql> unlock tables;
Restoring a MySQL Snapshot
1. From the MySQL console, close and lock all database tables with a read lock. This will help to create a consistent snapshot.
mysql> flush tables with read lock;
2. From the ZFS console, rollback the previously created snapshot for each InnoDB share:
zfs:> confirm shares select mysql select innodb-data snapshots select test_snapshot rollback zfs:> confirm shares select mysql select innodb-log snapshots select test_snapshot rollback
3. From the MySQL console, unlock all tables to restore the databases to normal operation:
mysql> unlock tables;
Cloning a MySQL Snapshot
1. From the ZFS console, create a clone of both InnoDB snapshots:
zfs:> shares select mysql select innodb-data snapshots select test_snapshot clone innodb-data-clone zfs:shares mysql/innodb-data-clone (uncommitted clone)> commit zfs:> shares select mysql select innodb-log snapshots select test_snapshot clone innodb-log-clone zfs:shares mysql/innodb-log-clone (uncommitted clone)> commit
2. Mount the newly created shares depending on which protocol you intend to deploy. Refer to the two previous blog posts on this subject.
3. Update /etc/my.cnf with the new InnoDB locations:
innodb_data_home_dir = /path/to/innodb-data-clone innodb_log_group_home_dir = /path/to/innodb-log-clone
4. Start a new instance of MySQL with the updated my.cnf entries. Any further updates will have no effect on the original database.
Sample REST script for creating a MySQL snapshot
The following python script has been developed to show how to automate this process. It leverages the REST API of the Oracle ZFS Storage Appliance.#!/usr/bin/python import MySQLdb import datetime import json import urllib2 def zfs_snapshot(): user = "zfssa_username" password = "zfssa_password" url = "zfssa_ip_address" path = "/api/storage/v1/pools/poolname/projects/mysql/snapshots" url = "https:// " + zfs + ":215" + path properties = {"name":"MySQL-snapshot"} post_data = json.dumps(properties) request = urllib2.Request(url, post_data) request.add_header("Content-type", "application/json") request.add_header("X-Auth-User", user) request.add_header("X-Auth-Key", password) response = urllib2.urlopen(request) def main(): mysql_server = "localhost" mysql_user = "root" mysql_pass = "" try: connection = MySQLdb.connect(host=mysql_server, user=mysql_user,passwd=mysql_pass) except MySQLdb.OperationalError: print "Could not connect to the MySQL server" sys.exit(-5) print "Connected to the MySQL server" start_time = datetime.datetime.now().replace(hour=0) backup = connection.cursor() backup.execute("set autocommit=0;") backup.execute("flush tables with read lock;") print "Creating project snapshot \"MySQL-snapshot\"" zfs_snapshot() backup.execute("unlock tables;") finish_time = datetime.datetime.now().replace(hour=0) total_time = finish_time - start_time print "Completed in ", total_time if __name__ == "__main__": main()