Use the Custom Application Agent feature of Tivoli Storage FlashCopy Manager for Unix to protect a MySQL database

Learn how to protect a business critical custom application with Tivoli Storage FlashCopy Manager using FlashCopy/Snapshot technology

This article demonstrates how to protect custom applications with IBM® Tivoli® Storage FlashCopy® Manager and its Custom Application Agent (CAA) feature. Tivoli Storage FlashCopy Manager provides application-aware backups and restores by using the advanced snapshot technologies of storage systems. Using a MySQL database as an example, I show you how to customize the CAA feature and protect your application with Tivoli Storage FlashCopy Manager.

Alexander Neef (alexneef@de.ibm.com), Senior Software Engineer, IBM

Photo of Alexander NeefAlexander Neef is a senior software engineer at IBM with 5 years of experience in software design and software engineering. Alexander is a member of the IBM Tivoli Storage FlashCopy Manager development team. He was the technical lead for the V3.1 VMware feature and for the V2.2 additional platform support.



24 July 2013

Introduction

IBM Tivoli Storage FlashCopy Manager software provides fast application-aware backups and restores by using advanced snapshot technologies in IBM storage systems. Tivoli Storage FlashCopy Manager can deliver the highest levels of protection for mission critical Oracle, DB2®, SAP, and custom applications. This protection is achieved through the use of advanced hardware snapshot technology. This method creates a high performance and low impact application data protection solution.

IBM Tivoli Storage FlashCopy Manager uses the copy service capabilities of intelligent disk subsystems to create an application aware point-in-time copy (FlashCopy or Snapshot) of production data. This copy is then retained on disk as a backup, which allows for a fast restore operation.

In addition to restoring from a point-in-time copy, you can mount this copy on an auxiliary or backup server as a logical copy. This copy can then be accessed for further processing, such as creating a subsequent backup to tape libraries or performing backup verification functions.

This article describes how you can use the Custom Application Agent (CAA) feature of Tivoli Storage FlashCopy Manager to protect a MySQL database that is on an IBM XIV® Storage System.


Custom Application Feature of Tivoli Storage FlashCopy Manager and Application Storage Layout Requirements

Tivoli Storage FlashCopy Manager provides a Custom Application Agent (CAA), which you can use to back up file systems or custom applications.

You can use Tivoli Storage FlashCopy Manager to create a consistent snapshot image of a file system or custom application on a production system. Custom applications are file systems or any database applications other than DB2, Oracle, and SAP with Oracle. The snapshots are managed as backup versions by using the version management policies of Tivoli Storage FlashCopy Manager. The snapshots can be used as a source for instant restore operations.

You can also mount snapshot backups on a secondary system. Then in a Tivoli Storage Manager environment, the Tivoli Storage Manager backup-archive client can be used to initiate a subsequent backup to the Tivoli Storage Manager server.

Using the CAA feature, Tivoli Storage FlashCopy Manager performs snapshot backups and restores of file systems at a volume group level. Therefore, you must ensure that the files to be protected are stored in dedicated file systems and volume groups. It is best to have the data files of a database within at least one dedicated volume group and the log files within another dedicated volume group. This separation of files, allows you to restore the data files with Tivoli Storage FlashCopy Manager without overwriting existing log files in a point-in-time recovery scenario.

In addition to storing this data in dedicated volume groups, the volume groups must be on a file system type that is supported by Tivoli Storage FlashCopy Manager. Refer to the hardware and software requirements for UNIX components of Tivoli Storage FlashCopy Manager for details (see Resources). Any other data that is stored on the volume groups, is also processed by Tivoli Storage FlashCopy Manager, and is included in the backup images. When you restore this data, the whole backup image is restored. Therefore, any data on the file systems that is updated or created after the creation of the backup is deleted. To ensure that nothing is overwritten, store data files only on the data volume group and store log files only on the log volume group.

To achieve a transaction-consistent backup of your database, configure Tivoli Storage FlashCopy Manager to run a pre-flash and post-flash command immediately before and after the creation of the snapshot. These commands suspend database transactions before the snapshot backup and resume transactions after the backup so that the backup is transaction-consistent.


Installation and Configuration of Tivoli Storage FlashCopy Manager with CAA

Tivoli Storage FlashCopy Manager must be installed on the production server as described in this procedure to successfully back up and restore custom applications.

To install Tivoli Storage FlashCopy Manager, you must use the root user ID. Select one of the following methods.

  • To install in GUI mode run the following command:
    ./3.2.0.0-TIV-FCM-OS-platform.bin
  • To install in console mode run the following command:
    ./3.2.0.0-TIV-FCM-OS-platform.bin -i console

When prompted to choose the install set to be installed by the installer, select IBM Tivoli Storage FlashCopy (R) Manager (Custom Applications).

The interactive installer guides you through the installation.

When the installation is finished, run the setup_gen.sh script as the root user to configure Tivoli Storage FlashCopy Manager. The setup_gen.sh script is in the /opt/tivoli/tsfcm/acs_3.2.0.0 directory.

This script copies Tivoli Storage FlashCopy Manager into the home directory of the application backup user. The default installation directory is $HOME/acs where $HOME is the home directory of the application backup user. This setup script also creates a configuration profile that is required by Tivoli Storage FlashCopy Manager.

Make sure to specify pre-flash and post-flash scripts in the profile as shown in Listing 1.

Listing 1. Profile settings to start pre-flash and post-flash scripts
PRE_FLASH_CMD /home/dbuser/acs/preflashscript.sh
POST_FLASH_CMD /home/dbuser/acs/postflashscript.sh

MySQL demonstration environment

The examples in this article use a MySQL database with InnoDB tables. You can separate the data files and log files into a separate file system and volume group by entering the following parameters in the mysql /etc/my.conf configuration file:

innodb_data_home_dir = /mysql/db1/data/
innodb_log_group_home_dir = /mysql/db1/log/

Binary logging was enabled for this database to allow for a point-in-time recovery. Therefore, the following line was also included in the /etc/my.conf file:

log-bin=/mysql/db1/log/mysql-bin

There are many useful settings to help configure logging and modify the behavior of InnoDB tables. Review the MySQL documentation for further details (see Resources).


Customization of Tivoli Storage FlashCopy Manager for MySQL demonstration environment

For transaction-consistent backups, use pre-flash and post-flash scripts.

Pre-flash

To achieve a transaction-consistent backup of the database, use a pre-flash script to flush and lock the tables and log files. In Listing 1, the PRE_FLASH_CMD parameter in the profile is set to /home/dbuser/acs/preflashscript.sh. Listing 2 shows sample contents of the preflashscript.sh file to flush and lock the tables and log files for the db1 database alias using the root user ID and password (root and passw0rd).

Listing 2. Pre-flash script to flush tables and logs
#!/bin/bash
mysql -u root -ppassw0rd -e "USE db1; FLUSH TABLES WITH READ LOCK; FLUSH LOGS;"

Post-flash

The post-flash script runs immediately after the snapshot backup is finished to unlock the tables so that they are ready for further usage. In Listing 1, the POST_FLASH_CMD parameter in the profile is set to /home/dbuser/acs/postflashscript.sh. Listing 3 shows sample contents of the postflashscript.sh file.

Listing 3. Post-flash script to unlock tables
#!/bin/bash
mysql -u root -ppassw0rd -e "USE db1; UNLOCK TABLES;"

Protection of the MySQL application with Tivoli Storage FlashCopy Manager

It is good practice in enterprise environments to continuously protect the log files of a database. For example, move the log files to a Tivoli Storage Manager server in addition to doing snapshot backups of the data files with Tivoli Storage FlashCopy Manager. You can recover the database up to the last recorded transaction after the data files of the snapshot backup are restored by using this combination.

Optionally, the log files can also be protected by Tivoli Storage FlashCopy Manager if they are on applicable volume groups.

Backup scenario

To protect the db1 sample database with Tivoli Storage FlashCopy Manager, a backup input file must first be created. This input file must contain a list of files and directories that are to be backed up. Listing 4 shows the contents of this input file.

Listing 4. Backup input file that contains data files only
/mysql/db1/data

Using the input file, an online snapshot backup of the database can be created by issuing the following command as the application backup user:

/home/dbuser/acs/fcmcli -f backup -I /home/dbuser/acs/input_file

After this command successfully completes, the MySQL database is protected by Tivoli Storage FlashCopy Manager.

Restore scenario

In a typical restore scenario, you usually first try to figure out which backups are available for restoration. With Tivoli Storage FlashCopy Manager, issue the following command to obtain this information:

/home/dbuser/acs/fcmcli -f inquire_detail

Listing 5 shows output from the inquire_detail command.

Listing 5. Sample output of inquire_detail command
Type    Backup-ID        Bkp-Timestamp  TSM Backup-ID    State       DevClass  

#BACKUP A0H8YM7UER       20121031164335 -                SUCCESSFUL  STANDARD
        UsabilityStates: REMOTELY_MOUNTABLE,REPETITIVELY_RESTORABLE

#BACKUP A0H86XSN0D       20121012085008 -                SUCCESSFUL  STANDARD
        UsabilityStates: REMOTELY_MOUNTABLE,REPETITIVELY_RESTORABLE

#BACKUP A0H7X049DS       20121005095728 -                SUCCESSFUL  STANDARD
        UsabilityStates: REMOTELY_MOUNTABLE,REPETITIVELY_RESTORABLE

To restore a specific backup, specify its backup ID on the restore command. For example, to restore backup A0H86XSN0D, issue:

/home/dbuser/acs/fcmcli -f restore -B A0H86XSN0D

Important: The database must be offline before the restore command is issued.

When an input file is not specified on the restore command, the complete content of the backup is restored. In the example above, an input file is not specified because only data files are contained in the backup. If there are other files in the backup, use an input file to specify which files to restore. For example,

/home/dbuser/acs/fcmcli -f restore -I /home/dbuser/acs/restore_input_file

Tivoli Storage FlashCopy Manager restores at a volume group level because a snapshot operation is based on the storage system disk level. When the backup ID is omitted from the restore command, the newest backup is restored automatically.

After sucessfully restoring the data files of a database, you must apply the log files to recover the entire database including the latest transactions. These log files were created from transactions that occurred after the snapshot backup completed. Use the mysqlbinlog utility to apply the log files. For example:

mysqlbinlog mysql-bin.000025 | mysql -u root -ppassw0rd

Using this approach allows near instant restore of the data files while still maintaining the capability to recover the database up to the latest transaction.

See Resources for a link to more details on recovering a MySQL database.

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Tivoli (service management) on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Tivoli (service management), Tivoli
ArticleID=938572
ArticleTitle=Use the Custom Application Agent feature of Tivoli Storage FlashCopy Manager for Unix to protect a MySQL database
publish-date=07242013