Quick Start Example - Open Solaris/ZFS snapshots

This section shows an example of how to install, configure, backup and restore MySQL databases using Zmanda Recovery Manager (ZRM) for MySQL on Open Solaris. This example takes advantage of ZFS snapshots to do full backups.

This example assumes that the ZRM server and MySQL server are the same machine. We are backing up MySQL database "myisamnetflix" to the same machine running Solaris Express Community Edition snv_77 on AMD platform.

ZRM for MySQL installation

Installation has to be done as super user.

ZRM for MySQL works with Perl 5.8.4 from SUNWperl584core package.

Install perl-DBD and perl-XML-parser modules. SUNWperl-xml-parser is part for Solaris Express Community Edition.

Download ZRM for MySQL Solaris packages from Zmanda downloads page.

Install ZRM for MySQL (ZRM server package is sufficient because MySQL server and ZRM server are the same machine).

# gunzip MySQLZrm-2.0-SunOS5.10-noarch.pkg.gz
# pkgadd -d MySQLZrm-2.0-SunOS5.10-noarch.pkg

The following packages are available:
  1  MySQLZrm     Zmanda Recovery Manager for MySQL backup
                  (all) 2.0

Select package(s) you wish to process (or 'all' to process
all packages). (default: all) [?,??,q]:

Processing package instance <MySQLZrm> from </tmp/MySQLZrm-2.0-SunOS5.10-noarch.pkg>

Zmanda Recovery Manager for MySQL backup(all) 2.0
Zmanda Inc. (www.zmanda.com)
Using </> as the package base directory.
## Processing package information.
## Processing system information.
   12 package pathnames are already properly installed.
## Verifying package dependencies.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
## Checking for setuid/setgid programs.

This package contains scripts which will be executed with super-user
permission during the process of installing this package.

Do you want to continue with the installation of <MySQLZrm> [y,n,?] y

Installing Zmanda Recovery Manager for MySQL backup as <MySQLZrm>

## Installing part 1 of 1.
/etc/logrotate.d/mysql-zrm
/etc/mysql-zrm/RSS.header
/etc/mysql-zrm/mysql-zrm-release
/etc/mysql-zrm/mysql-zrm-reporter.conf
/etc/mysql-zrm/mysql-zrm.conf
/usr/bin/mysql-zrm
/usr/bin/mysql-zrm-abort-backup
/usr/bin/mysql-zrm-backup
/usr/bin/mysql-zrm-check
/usr/bin/mysql-zrm-extract-backup
/usr/bin/mysql-zrm-getconf
/usr/bin/mysql-zrm-list
/usr/bin/mysql-zrm-migrate-file-ownership
/usr/bin/mysql-zrm-parse-binlogs
/usr/bin/mysql-zrm-perl <symbolic link>
/usr/bin/mysql-zrm-purge
/usr/bin/mysql-zrm-reporter
/usr/bin/mysql-zrm-restore
/usr/bin/mysql-zrm-scheduler
/usr/bin/mysql-zrm-verify-backup
/usr/bin/zrm-pre-scheduler
/usr/lib/mysql-zrm/Data/Report.pm
/usr/lib/mysql-zrm/Data/Report/Base.pm
/usr/lib/mysql-zrm/Data/Report/Plugin/Csv.pm
/usr/lib/mysql-zrm/Data/Report/Plugin/Html.pm
/usr/lib/mysql-zrm/Data/Report/Plugin/Text.pm
/usr/lib/mysql-zrm/XML/RSS.pm
/usr/lib/mysql-zrm/ZRM/Common.pm
/usr/lib/mysql-zrm/ZRM/MySQL.pm
/usr/lib/mysql-zrm/ZRM/Replication.pm
/usr/lib/mysql-zrm/ZRM/SnapshotCommon.pm
/usr/share/doc/MySQL-zrm-2.0/AUTHORS
/usr/share/doc/MySQL-zrm-2.0/INSTALL
/usr/share/doc/MySQL-zrm-2.0/README
/usr/share/doc/MySQL-zrm-2.0/README-plugin-encrypt
/usr/share/doc/MySQL-zrm-2.0/README-plugin-parse-binlogs
/usr/share/doc/MySQL-zrm-2.0/README-plugin-post-backup
/usr/share/doc/MySQL-zrm-2.0/README-plugin-pre-backup
/usr/share/doc/MySQL-zrm-2.0/README-plugin-pre-scheduler
/usr/share/doc/MySQL-zrm-2.0/README-plugin-snapshot-plugin
/usr/share/doc/MySQL-zrm-2.0/README-plugin-socket-copy
/usr/share/doc/MySQL-zrm-2.0/README-plugin-ssh-copy
/usr/share/man/man1/mysql-zrm-abort-backup.1
/usr/share/man/man1/mysql-zrm-backup.1
/usr/share/man/man1/mysql-zrm-check.1
/usr/share/man/man1/mysql-zrm-extract-backup.1
/usr/share/man/man1/mysql-zrm-list.1
/usr/share/man/man1/mysql-zrm-parse-binlogs.1
/usr/share/man/man1/mysql-zrm-purge.1
/usr/share/man/man1/mysql-zrm-reporter.1
/usr/share/man/man1/mysql-zrm-restore.1
/usr/share/man/man1/mysql-zrm-scheduler.1
/usr/share/man/man1/mysql-zrm-verify-backup.1
/usr/share/man/man1/mysql-zrm.1
/usr/share/man/man5/mysql-zrm-reporter.conf.5
/usr/share/man/man5/mysql-zrm.conf.5
/usr/share/mysql-zrm/plugins/encrypt.pl
/usr/share/mysql-zrm/plugins/lvm-snapshot.pl
/usr/share/mysql-zrm/plugins/parse-binlogs.pl
/usr/share/mysql-zrm/plugins/post-backup.pl
/usr/share/mysql-zrm/plugins/pre-backup.pl
/usr/share/mysql-zrm/plugins/socket-copy.pl
/usr/share/mysql-zrm/plugins/ssh-copy.pl
/usr/share/mysql-zrm/plugins/zfs-snapshot.pl
/var/log/mysql-zrm/mysql-zrm.log
[ verifying class <none> ]
## Executing postinstall script.

Installation of <MySQLZrm> was successful.

MySQL server configuration

Check to see if MySQL server is running. If MySQL server is not installed, please install SUNWmysqlr, SUNWmysqlt, SUNWmysqlu packages from the distribution. Update the "root" MySQL server with a password using mysqladmin command (/usr/sfw/bin/mysqladmin --user root password boot12). We are using "boot12" as the root password. This user will be used for doing MySQL backups and restores. It is better to user a specific user with minimal privileges to do MySQL backups instead of using "root" MySQL user.

The MySQL server has to run as "mysql" user and "mysql" OS user should belong to "mysql" group. The default installation of ZRM for MySQL requires MySQL server to run as "mysql" user.

Enable binary logging on the MySQL server. Binary logging must be enabled to do incremental backups of the MySQL server.
Edit /etc/my.cnf configuration file. Add "log-bin" in mysqld section

[mysqld]
log-bin

Create a ZFS filesystem as shown below. We will be storing MySQL data in /testpool/testfs directory.

# zpool list
NAME       SIZE   USED  AVAIL    CAP  HEALTH  ALTROOT
testpool  29.8G   628M  29.1G     2%  ONLINE  -
# zfs list
NAME                   USED  AVAIL  REFER  MOUNTPOINT
testpool               521M  23.8G  33.2K  /testpool
testpool/testfs        521M  23.8G   520M  /testpool/testfs

Change /etc/my.cnf configuration file so that datadir points to the ZFS filesystem.

[mysqld]
datadir = /testpool/testfs/mysql/data/

Restart MySQL server. The MySQL server is listening to default port 3036.

   mysql   616   592   0   Mar 03 console    1:2:13 /usr/sfw/sbin/mysqld --basedir=/usr/sfw --datadir=/testpool/testfs/mysql/data/

We have mysql database "myisamnetflix" that contains two tables. We will be backing this database. This database uses MyISAM storage engine

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myisamnetflix      |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)
mysql> use myisamnetflix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------------+
| Tables_in_myisamnetflix |
+-------------------------+
| MovieID                 |
| MovieRatings            |
+-------------------------+
2 rows in set (0.00 sec)

mysql> select count(*) from MovieID;
+----------+
| count(*) |
+----------+
|    17770 |
+----------+

MySQL client commands are installed in /usr/bin/ directory. If they are not, accordingly configure the client command location and binary log location in mysql-zrm.conf. See MySQL server parameters section for details.

ZRM configuration

This should be done as mysql user

$ id
uid=100(mysql) gid=100(mysql)

Create the backup set directory. The backup set is called "zfstest".

$ mkdir /etc/mysql-zrm/zfstest

Create mysql-zrm.conf configuration file. The "myisamnetflix" database is being backed up. The location of MySQL client commands have specified in "mysql-binpath" parameter. ZFS snapshot plugin has been specified as the backup method.

mysql-binlog-path="/var/log/mysql"
$ cat /etc/mysql-zrm/zfstest/mysql-zrm.conf
mysql-binpath="/usr/sfw/bin"
host="localhost"
databases="myisamnetflix"
password="boot12"
user="root"
snapshot-plugin="/usr/share/mysql-zrm/plugins/zfs-snapshot.pl"

Perform ZRM backups

This should be done as mysql user
Perform full backup of the database immediately using mysql-zrm-scheduler.

$ mysql-zrm-scheduler --now --backup-set zfstest --backup-level 0
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
zfstest:backup:INFO: START OF BACKUP
zfstest:backup:INFO: PHASE START: Initialization
zfstest:backup:INFO: backup-set=zfstest
zfstest:backup:INFO: backup-date=20080326053921
zfstest:backup:INFO: mysql-server-os=Linux/Unix
zfstest:backup:INFO: host=localhost
zfstest:backup:INFO: backup-date-epoch=1206535161
zfstest:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
zfstest:backup:INFO: mysql-version=4.0.24-log
zfstest:backup:INFO: backup-directory=/var/lib/mysql-zrm/zfstest/20080326053921
zfstest:backup:INFO: backup-level=0
zfstest:backup:INFO: backup-mode=raw
zfstest:backup:INFO: PHASE END: Initialization
zfstest:backup:INFO: PHASE START: Running pre backup plugin
zfstest:backup:INFO: PHASE END: Running pre backup plugin
zfstest:backup:INFO: PHASE START: Flushing logs
zfstest:backup:INFO: PHASE END: Flushing logs
zfstest:backup:INFO: PHASE START: Creating snapshot based backup
zfstest:backup:INFO: File       Position        Binlog_do_db    Binlog_ignore_db
mysql-bin.045   4
zfstest:backup:INFO: innodb-data=/testpool/testfs/mysql/data/ibdata1;
zfstest:backup:INFO: innodb-logs=/testpool/testfs/mysql/data/./ib_logfile*
zfstest:backup:INFO: raw-databases-snapshot=myisamnetflix
zfstest:backup:INFO: PHASE END: Creating snapshot based backup
zfstest:backup:INFO: PHASE START: Find table type
zfstest:backup:INFO: PHASE END: Find table type
zfstest:backup:INFO: PHASE START: Calculating backup size & checksums
zfstest:backup:INFO: next-binlog=mysql-bin.045
zfstest:backup:INFO: backup-size=261.87 MB
zfstest:backup:INFO: PHASE END: Calculating backup size & checksums
zfstest:backup:INFO: read-locks-time=00:00:01
zfstest:backup:INFO: flush-logs-time=00:00:00
zfstest:backup:INFO: backup-time=00:02:19
zfstest:backup:INFO: backup-status=Backup succeeded
zfstest:backup:INFO: Backup succeeded
zfstest:backup:INFO: PHASE START: Running post backup plugin
zfstest:backup:INFO: PHASE END: Running post backup plugin
zfstest:backup:INFO: PHASE START: Mailing backup report
zfstest:backup:INFO: PHASE END: Mailing backup report
zfstest:backup:INFO: PHASE START: Cleanup
zfstest:backup:INFO: PHASE END: Cleanup
zfstest:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully

Delete some entries from the "myisamnetflix" database (so that we can do incremental backup of the database). We are deleting all movies that start with "Sherlock"

mysql> use myisamnetflix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delete from MovieID where MovieTitle regexp 'Sherlock*';

Query OK, 31 rows affected (0.13 sec)

Perform incremental backup of the backup set.

$ mysql-zrm-scheduler --now --backup-set zfstest --backup-level 1
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
zfstest:backup:INFO: START OF BACKUP
zfstest:backup:INFO: PHASE START: Initialization
zfstest:backup:INFO: backup-set=zfstest
zfstest:backup:INFO: backup-date=20080326071026
zfstest:backup:INFO: mysql-server-os=Linux/Unix
zfstest:backup:INFO: host=localhost
zfstest:backup:INFO: backup-date-epoch=1206540626
zfstest:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
zfstest:backup:INFO: mysql-version=4.0.24-log
zfstest:backup:INFO: backup-directory=/var/lib/mysql-zrm/zfstest/20080326071026
zfstest:backup:INFO: backup-level=1
zfstest:backup:INFO: PHASE END: Initialization
zfstest:backup:INFO: PHASE START: Running pre backup plugin
zfstest:backup:INFO: PHASE END: Running pre backup plugin
zfstest:backup:INFO: PHASE START: Flushing logs
zfstest:backup:INFO: PHASE END: Flushing logs
zfstest:backup:INFO: PHASE START: Creating incremental backup
zfstest:backup:INFO: incremental=mysql-bin.[0-9]*
zfstest:backup:INFO: PHASE END: Creating incremental backup
zfstest:backup:INFO: PHASE START: Calculating backup size & checksums
zfstest:backup:INFO: next-binlog=mysql-bin.046
zfstest:backup:INFO: last-backup=/var/lib/mysql-zrm/zfstest/20080326053921
zfstest:backup:INFO: backup-size=0.00 MB
zfstest:backup:INFO: PHASE END: Calculating backup size & checksums
zfstest:backup:INFO: read-locks-time=00:00:00
zfstest:backup:INFO: flush-logs-time=00:00:00
zfstest:backup:INFO: backup-time=00:00:01
zfstest:backup:INFO: backup-status=Backup succeeded
zfstest:backup:INFO: Backup succeeded
zfstest:backup:INFO: PHASE START: Running post backup plugin
zfstest:backup:INFO: PHASE END: Running post backup plugin
zfstest:backup:INFO: PHASE START: Mailing backup report
zfstest:backup:INFO: PHASE END: Mailing backup report
zfstest:backup:INFO: PHASE START: Cleanup
zfstest:backup:INFO: PHASE END: Cleanup
zfstest:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully

ZRM backup reports

Use mysql-zrm-reporter to look at the status of backups available

$ /usr/bin/mysql-zrm-reporter --where backup-set=zfstest --show backup-status-info

REPORT TYPE : backup-status-info

          backup_set  backup_date                  backup_level  backup_status         comment
-----------------------------------------------------------------------------------------------------------
             zfstest  Wed Mar 26 07:10:26 2008                1  Backup succeeded      ----
             zfstest  Wed Mar 26 05:39:21 2008                0  Backup succeeded      ----

ZRM reports can also provide information on impact on MySQL application.

$ /usr/bin/mysql-zrm-reporter --where backup-set=zfstest --show  backup-app-performance-info

REPORT TYPE : backup-app-performance-info

          backup_set  backup_date                  backup_level     backup_size  backup_time   read_locks_time     flush_logs_time
-------------------------------------------------------------------------------------------------------------------------------------
             zfstest  Wed Mar 26 07:10:26 2008                1         0.00 MB  00:00:01      00:00:00            00:00:00
             zfstest  Wed Mar 26 05:39:21 2008                0       261.87 MB  00:02:19      00:00:01            00:00:00

Database recovery

Use ZRM reporting tool to identify the location of MySQL backup images.

$ /usr/bin/mysql-zrm-reporter --where backup-set=zfstest --show restore-info

REPORT TYPE : restore-info

          backup_set  backup_date                  backup_level  backup_directory                          backup_status         comment
-----------------------------------------------------------------------------------------------------------------------------------------------------
             zfstest  Wed Mar 26 07:10:26 2008                1  /var/lib/mysql-zrm/zfstest/20080326071026  Backup succeeded      ----
             zfstest  Wed Mar 26 05:39:21 2008                0  /var/lib/mysql-zrm/zfstest/20080326053921  Backup succeeded      ----

You can parse incremental backups to identify database events of interest. In our example, we will look for the "DELETE" event.

$ /usr/bin/mysql-zrm-parse-binlogs --source-directory /var/lib/mysql-zrm/zfstest/20080326071026  --mysql-binpath /usr/sfw/bin
parse-binlogs:INFO: ZRM for MySQL Community Edition - version 2.0
------------------------------------------------------------
Log filename | Log Position | Timestamp | Event Type | Event
------------------------------------------------------------
/var/lib/mysql-zrm/zfstest/20080326071026/mysql-bin.045 | 4 | 08-03-26 07:08:59 | Query | use myisamnetflix; delete from MovieID where MovieTitle regexp 'Sherlock Holmes*';
/var/lib/mysql-zrm/zfstest/20080326071026/mysql-bin.045 | 110 | 08-03-26 07:09:27 | Query | delete from MovieID where MovieTitle regexp 'Sherlock*';
/var/lib/mysql-zrm/zfstest/20080326071026/mysql-bin.045 | 209 | 08-03-26 07:10:26 | Rotate to mysql-bin.046  pos: 4 |
------------------------------------------------------------

Restore the database from the full backup done at 05:39:21

$ /usr/bin/mysql-zrm-restore --mysql-binpath /usr/sfw/bin --user=root --password=boot12 --source-directory=/var/lib/mysql-zrm/zfstest/20080326053921

restore:INFO: ZRM for MySQL Community Edition - version 2.0

BackupSet1:restore:INFO: Restored database from raw backup: myisamnetflix
BackupSet1:restore:INFO: Restore done in 336 seconds.
MySQL server has been shutdown. Please restart after verification.

Restart the MySQL server. ZRM for MySQL shuts down the database after recoveryso that the contents can be verified.

Verify database recovery

mysql> use myisamnetflix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from MovieID where MovieTitle regexp 'Sherlock*';
+---------+------+------------------------------------------+
| MovieID | Year | MovieTitle                               |
+---------+------+------------------------------------------+
|     742 | 2002 | Sherlock: Case of Evil                   |
|     757 | 1984 | Sherlock Hound                           |
|     777 | 1944 | Sherlock Holmes and the Spider Woman     |
|     804 | 1944 | Sherlock Holmes: The Scarlet Claw        |
|    1332 | 1954 | Sherlock Holmes                          |
|    2688 | 1994 | The Memoirs of Sherlock Holmes           |
|    4013 | 1993 | Sherlock Holmes: The Eligible Bachelor   |
|    4902 | 1985 | Young Sherlock Holmes                    |
|    5569 | 1939 | The Adventures of Sherlock Holmes        |
|    6468 | 2000 | The Sherlock Holmes Collection           |
|    7159 | 1942 | Sherlock Holmes and the Secret Weapon    |
|    7741 | 1943 | Sherlock Holmes in Washington            |
|    8654 | 1984 | Sherlock Holmes: The Hound of the Basker |
|    8860 | 1984 | The Adventures of Sherlock Holmes        |
|   10289 | 1993 | Sherlock Holmes: The Last Vampyre        |
|   10450 | 1943 | Sherlock Holmes Faces Death              |
|   11142 | 1991 | The Casebook of Sherlock Holmes          |
|   12176 | 1999 | The Fall and Rise of Sherlock Holmes     |
|   12416 | 1942 | Sherlock Holmes and the Voice of Terror  |
|   12871 | 1926 | Our Hospitality / Sherlock Jr.           |
|   12929 | 1944 | Sherlock Holmes: The Pearl of Death      |
|   13253 | 1946 | Sherlock Holmes: Dressed to Kill         |
|   14577 | 1970 | The Private Life of Sherlock Holmes      |
|   14636 | 1945 | Sherlock Holmes: In Pursuit to Algiers   |
|   14859 | 1986 | The Return of Sherlock Holmes            |
|   15353 | 1945 | Sherlock Holmes: The House of Fear       |
|   15676 | 1946 | Sherlock Holmes: Terror by Night         |
|   15987 | 1987 | Sherlock Holmes: The Sign of Four        |
|   16089 | 2004 | Sherlock Holmes and the Case of the Silk |
|   16729 | 1945 | Sherlock Holmes: The Woman in Green      |
|   17684 | 1992 | Sherlock Holmes: The Master Blackmailer  |
+---------+------+------------------------------------------+
31 rows in set (0.15 sec)

Quick Start Example - Open Solaris/ZFS snapshots

Contents

ZRM for MySQL installation

MySQL server configuration

ZRM configuration

Perform ZRM backups

ZRM backup reports

Database recovery

Navigation menu

Quick Start Example - Open Solaris/ZFS snapshots

ZRM for MySQL installation

MySQL server configuration

ZRM configuration

Perform ZRM backups

ZRM backup reports

Database recovery

Navigation menu

Search