X

Paulie's world in a blog

  • March 2, 2015

Backing up MySQL using ZFS Snapshots and Clones

Guest Author

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

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.