Table of contents
MySQL Configuration Requirements
To take advantage of ZFS snapshots, all MySQL database files (data, log, indexes) belonging to the backup set must be stored in ZFS volumes to ensure consistency.
If any of the files are not on ZFS volumes, a raw backup using mysqlhotcopy, or a logical backup using mysqldump is performed depending on the storage engines of the tables in each of the databases.
Refer to the Solaris ZFS documentation for details on ZFS administration.
Here are some valid scenarios of database storage on ZFS:
- All MySQL data is stored on a single ZFS Volume
- Specific database directories are stored on different volumes
- 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 all on ZFS volumes.
- The InnoDB shared data files are on a separate ZFS volume
- The InnoDB logs are on a separate ZFS volume
MySQL Backup User sudo Privileges
The MySQL backup user (described in System Requirements) must be granted sudo privileges to execute ZFS commands on the MySQL server. Add a line similar to the following example to /usr/local/etc/sudoers on the MySQL server:
mysql MySQLserver.mycompany.com=NOPASSWD:/usr/sbin/df,NOPASSWD:/usr/sbin/zfs
where MySQLserver.mycompany.com is the fully-qualified domain name for the MySQL server. If the MySQL database resides on the the ZRM server, the ZRM server name should be used. Note that if ZFS commands are installed in non-standard locations, the above example would not work without editing it to reflect the different paths.
To test the sudo configuration, run the command as the "mysql" user. The command should execute correctly without prompting for a password. For example:
# su - mysql $ sudo /usr/sbin/df