What information can be obtained from a backup report?

MySQL ZRM stores backup policy, backup performance, impact on application and database/tables information in a database. All the information is organized in terms of backup run. The mysql-zrm-reporter tool can be used to extract the information stored about the backup runs.

All backup parameter field names can be passed as parameters to fields argument to display select set of fields. If select set of field names are not provided as parameter, predefined backup report backup-status-info are displayed. The reporter tool can also search for a specific field value.

The list of information about a backup run that are available:

Backup parameters

backup-set: Name of the Backup Set

comment: Database administrator comments about the backup set or the backup run

backup-date: Date and time stamp of when the backup was done.

mysql-version: Version of the MySQL server used to backup the backup set. When MySQL server is being upgraded to a newer version, this field can be used to check the MySQL version of the backup images.

backup-directory: The location of backup directory on the machine where MySQL ZRM is running.

backup-level: Backup level (full or incremental). Full backup is 0. Incremental backup is level 1.

retention-policy: Retention time for the backup image. The backup images will be removed after retention time since the backup-date value. The unit can be D (days), W (weeks), M (months), Y (years). 30 days in a month and 365 days in a year are assumed.

compress: Compression program used for backup data compression.

encrypt: Encryption program used for backup data encryption.

Tables/Databases that were backed up

host: Host name of the MySQL server.

raw-databases: List of databases that have raw backups - backups done using the mysqlhotcopy command. If the raw-tables field is present, all the tables listed in raw-tables belong to the database in this parameter.

raw-tables: List of tables backed up using the mysqlhotcopy command. All the tables listed in this parameter belong to the database in the raw-databases field. This parameter will have valid values only if --tables parameter was specified for the backup run in the mysql-zrm.conf configuration file.

raw-databases-snapshot: List of databases backed up using LVM snapshots.

raw-tables-snapshot: List of tables backed up using LVM snapshots. All the tables listed in this parameter belong to the database in the raw-tables-snapshot field.

logical-databases: List of databases backed up using mysqldump(1)

logical-tables: List of tables belonging to "logical-databases" backed up using mysqldump(1)

replication: Names of replication files that were backed up - namely master.info and relay-log.info

slave-load-files: Names of SQL_LOAD* files that were backed up

incremental: Names of Binary log files that are part of incremental backup.

innodb_data: List of InnoDB data files that are part of backup image.

innodb_logs: List of InnoDB log files that are part of backup image.

Status and performance of backup run

backup-time: Time taken by the backup run. The time includes the time for compression and encryption if these features were used during backup run. Format is HH:MM:SS

compress-encrypt-time: Time taken to do compression and/or encryption of backup image. Format is HH:MM:SS. This time is included in backup-time.

backup-size: Size of backup image in MB.

backup-size-compressed: Gives the size of backup data after compression and encryption

read-locks-time: During backups, the mysql-zrm tool holds the read lock on the database(s) or the table(s) that being backed up. The time for which the read locks were held is available.

flush-logs-time: The time taken to flush database pages from memory to disk. All modified database pages written from memory to the disk during backup for some backup methods. Format is HH:MM:SS.

backup-status: Status of the backup run. The values can be Backup Failed, Backup done with errors, and Backup succeeded.

- Backup Failed means there was a fatal error and backup was not completed.
- Backup succeeded means the backup was successful.
- Backup done with errors means there were errors during backup, not all tables/databases in the backup set were not backed up. To find out which databases or tables were backed up successfully, see mysql-zrm log file.

Predefined backup reports

Predefined backup reports can be obtained using --show option to mysql-zrm-reporter tool. The list of predefined backup reports available are shown in the table below:

Backup report name	Description	Information available
backup-status-info	Status of backup runs	backup-set, backup-date, backup-level, backup-status, backup-comment
backup-method-info	Backup methods used	backup-set, raw-databases, raw-databases, logical-databases
backup-retention-info	How long are the backups retained?	backup-set, backup-date, backup-level, backup-size, retention-policy
backup-performance-info	Backup performance	backup-set, backup-date, backup-level, backup-size, backup-size-compressed, backup-time,compress-encrypt-time
backup-app-performance-info	Impact of backups on application	backup-set, backup-date, backup-level, backup-size, backup-time, read-locks-time, flush-logs-time
restore-info	Information for doing full/incremental recovery of databases	backup-set, backup-date, backup-level, backup-directory, backup-status, backup-comment
selective-restore-info	Information for doing selective recovery of databases	backup-set, backup-date, incremental
replication-info	Replication files backed up	backup-set, backup-date, replication, slave-load-files

Specific backup runs can be selected from the predefined backup reports using --where option.

Example: A backup status report

# mysql-zrm-reporter --where backup-set=backupSet1 --show backup-status-info
backup_set       backup_date      backup_level  backup_status    comment
-------------------------------------------------------------------------------------------
backupSet1       20060909100021   0             Backup           Before application upgrade
                                               succeeded
backupSet1       20060909100123   0             Backup           After application upgrade
                                               succeeded
backupSet1       20060909100300   0             Backup           Nightly
                                               succeeded

Formatting backup reports

mysql-zrm-reporter report format can be controlled by specifying format in /etc/mysql-zrm/mysql-zrm-reporter.conf.

The configuration file - mysql-zrm-reporter.conf allows user to specify the format for each backup parameter. User can specify the size of each parameter value, alignment and date format in the backup reports. The configuration file is used for predefined reports as well as custom reports generated by the user.

Each line in the file is for a specific backup run parameter. The syntax for each line is

fieldname=width,alignment[,format]

width: the number of characters for the value. If the value exceeds the number of characters specified, the value will wrap around into multiple lines

alignment: values can be left ("<") or right (">") aligned. Value ("|") means the value is aligned in the center. Center alignment only works on HTML reports.

format: The format parameter is available only for backup-date parameter. The format syntax is same as FORMAT syntax for Unix date(1) command. For example: "%F %T" format would display "2006-09-19 15:47:37"

For example: Backup status report with backup-date format "%c"

 # /usr/bin/mysql-zrm-reporter -where backup-set=backupSet1
 backup_set  backup_date              backup_level  backup_status        backup_directory
 ----------------------------------------------------------------------------------------
 backupSet1  Tue 29 Aug 2006                     0  Backup succeeded      /var/lib/mysql-zrm/backupSet1/
             02:08:03 PM PDT                                              20060829140803
 backupSet1  Tue 29 Aug 2006                     0  Backup succeeded      /var/lib/mysql-zrm/backupSet1/
             02:09:33 PM PDT                                              20060829140933

HTML output

mysql-zrm-reporter can provide backup run reports in HTML. The html output can be put in a directory under the webserver document root location (use --output option of mysql-zrm-reporter). This would allow backup reports to be accesible via web. Appropriate backup report access controls should be implemented for the backup html directory location to prevent unauthorised access using a webserver.

Example: Creating backup status html report for backup set BackupSet2

# /usr/bin/mysql-zrm-reporter --show backup-status-info --where backup-set=backupSet2 \
  --type html --output /var/www/html/backup-reports/backup-status-backupSet2

Example: Backup status HTML output

backup_set	backup_date	backup_status	comment
backupSet2	2006-08-29 14:07:23	Backup done but with errors	----
backupSet2	2006-08-29 14:08:19	Backup succeeded	----
backupSet2	2006-08-29 14:10:01	Backup succeeded	----
backupSet2	2006-08-29 17:45:14	Backup succeeded	----
backupSet2	2006-08-29 17:46:16	Backup succeeded	----
backupSet2	2006-08-29 17:48:04	Backup succeeded	----

</body> </html>

Automatic generation of backup reports

MySQL ZRM can generate HTML and Text backup reports about last backup run automatically after the backup run is completed. The backup reports are generated even if the backup run fails.

The backup reports are stored under the directory html-report-directory specified in mysql-zrm.conf. The list of backup predefined backup reports automatically generated can be controlled using html-reports in mysql-zrm.conf configuration file. All HTML reports are stored under sub-directory html and text reports are stored under sub-directory text

Example: Backup report automatic generation configuration in mysql-zrm.conf

 html-report-directory=/var/www/html/ZRMForMySQL/Reports/
 html-reports=backup-status-info, backup-performance-info

If HTML reports are stored under the webserver document root, the backup reports are accessible from any web browser. Access to the html-report-directory should be restricted to prevent unauthorized access.

Backup report available as RSS feeds

MySQL ZRM can provide backup reports on the scheduled backup runs through RSS feeds. The RSS feed are available automatically. MySQL administrators can use any RSS reader to look at the backup reports.

Configuring backup report RSS feed

Configure MySQL ZRM for automatic generation of backup reports.

Modify RSS header file template available in /etc/mysql-zrm/RSS.header for the webserver configruation.

RSS header template file contents:

Title            = "Zmanda Recovery Manager for MySQL backup reports"
Link             = "http://yourbackupserver.company.com/"
Creator          = "yourbackupserver.company.com"
Publisher        = "yourbackupserver.company.com"
Rights           = "Copyright 2006, company.com"
Subject          = "ZRM for MySQL backup"
Description      = "Reports generated by Zmanda Recovery Manager (ZRM) for MySQL"
Language         = "en-us"
ManagingEditor   = "webmaster\@yourbackupserver.company.com"
webMaster        = "webmaster\@yourbackupserver.company.com"
UpDatePeriod     = Daily
UpDateFrequency  = 1

yourbackupserver.company.com must be replaced the fully qualified domain name of the server running MySQL ZRM. company.com should be replaced by the name of the company.

Assumption: Webserver is running on the server that runs MySQL ZRM.

Configure RSS feed parameters in the mysql-zrm.conf. These parameters can be specified in the global configuration file or the backup set specific configuration file.
- webserver-url : URL of the webserver location that is used to access the RSS feed. Example: http://backupserver.company.com/Reports
- rss-header-location : The name of the RSS header file created in the last step. It is usually /etc/mysql-zrm/RSS.header
- html-reports : List of backup reports about the backup runs that are available as RSS feed. Multiple predefined backup reports can be specified as values. The values are separated by , (comma) character.
- html-report-directory : Directory where HTML and Text backup reports are created. This directory must be under webserver document root so that the directory contents are accessible using the web server. The access to this directory should be restricted to prevent unauthorized access to backup reports.

Example: RSS feed configuration in mysql.zrm.conf

 html-reports=backup-status-info 
 webserver-url=http://backupserver.company.com/reports/html
 rss-header-location=/etc/mysql-zrm/RSS.header

Example: rss.xml file created for a backup run.

<?xml version="1.0" encoding="UTF-8"?>
 <rss version="2.0" >
 <channel>
      <title>Zmanda Recovery Manager Backup Reports for MySQL</title>
      <link>http://backupserver.company.com/</link>
      <description>Reports Created by Zmanda Recovery Manager (ZRM) for MySQL</description>
      <language>en-us</language>
      <copyright>Copyright 2005, company.com</copyright>
      <pubDate>Tue, 10 Oct 2006 13:52:50 -0700</pubDate>
      <lastBuildDate>Tue, 10 Oct 2006 13:52:50 -0700</lastBuildDate>
      <managingEditor>[email protected]</managingEditor>
      <webMaster>[email protected]</webMaster>
      <item>
           <title>Backup Report for MySQL ZRM at 04:00:20 PM on 2006-10-10</title>
           <link>http://backupserver.company.com/reports/html/backup-status-04002010102006.html</link>
           <description>Zmanda Recovery Manager (ZRM) for MySQL is flexible and robust backup 
                        and recovery solution for MySQL server.
          </description>
      </item>
       
      <item>
           <title>Backup Report for MySQL ZRM at 01:52:50 PM on 2006-10-10</title>
           <link>http://backupserver.company.com/reports/html/backup-status-01525010102006.html</link>
           <description>Zmanda Recovery Manager (ZRM) for MySQL is flexible and robust backup 
                        and recovery solution for MySQL server.
           </description>
      </item>
  </channel>
</rss>

What information can be obtained from a backup report?

Contents

Backup parameters

Tables/Databases that were backed up

Status and performance of backup run

Predefined backup reports

Formatting backup reports

HTML output

Automatic generation of backup reports

Backup report available as RSS feeds

Configuring backup report RSS feed

Navigation menu

What information can be obtained from a backup report?

Backup parameters

Tables/Databases that were backed up

Status and performance of backup run

Predefined backup reports

Formatting backup reports

HTML output

Automatic generation of backup reports

Backup report available as RSS feeds

Configuring backup report RSS feed

Navigation menu

Search