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()