Backup of MySQL Databases on Solaris ZFS

From wiki.zmanda.com
Jump to navigation Jump to search

Solaris ZFS filesystem is an advanced filesystem that is available on Solaris 10 and Open Solaris. The filesystem is easy to use and has an integrated volume manager.

ZRM for MySQL has a mechanism to help backup such installations using snapshot plugin. It can create temporary snapshots of the ZFS filesystem and use the snapshot to do backups. The advantage of using snapshots is that you need to lock the database tables only for the time taken to create a snapshot. The snapshots are removed when the backups are completed. Snapshots help to create a consistent copy of the MySQL database as the consistency is ensured before the snapshot is taken.

MySQL Configuration

To be able to take advantage of ZFS snapshots, the mysql data, logs and index files should be reside on ZFS filesystems. The following are some of the possible configurations

  • All MySQL data is on a single ZFS filesystem.
  • Specific database directories are on different ZFS filesystems
  • For databases containing InnoDB based tables, the snapshot can only be used if the database directory, the InnoDB data files and the InnoDB logs are on ZFS filesystems.
    • The InnoDB shared data files are on a separate filesystem.
    • The InnoDB logs are on a separate filesystem
  • If the ZRM user is the default user 'mysql' or non-root user, sudo(8) configuration file must be configured. Please see Pre-installation steps chapter for more information.

Pre-conditions for Using ZFS Snapshots

  • mysql user should have privileges to execute zfs commands as superuser on the MySQL server.
  • All MySQL database files (data, log, indexes) must be stored in ZFS filesystems to ensure consistency. If any of the files are not on ZFS, backup using snapshots will not be performed. Instead raw backup via mysqlhotcopy or a logical backup using mysqldump will be performed based on the storage engines of the tables in each of the databases
  • Sufficient reserve space must be available on the zpool to maintain copy-on-write filesystem snapshots.
  • Backup image directory (/var/lib/mysql-zrm) and the database being backed up must not be on the same ZFS filesystem.

Configuration Parameters

The snapshot-plugin parameter must be specified in the mysql-zrm.conf. It should point to ZFS snapshot plugin location. Backup type can be regular or quick. 

snapshot-plugin="/usr/share/mysql-zrm/plugins/zfs-snapshot.pl"

The parameter can be configured in:

  • The global configuration file,
  • Specific file relating to a backup set, or
  • Passed as an argument to backup action on the Command line.

Note

  • The snapshot option is ignored if the backup-mode is logical.
Retrieved from ‘http://wiki.zmanda.com/index.php?title=Backup_of_MySQL_Databases_on_Solaris_ZFS&oldid=6512’