What information can be obtained from a backup report?

MySQL ZRM stores policy, performance, 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, backup-set, backup-date, backup-directory, backup-level and backup-status 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.

Tables/Databases that were backed up

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.

Status and performance of backup run

backup-time: Time taken by the backup run. Format is HH:MM:SS

backup-size: Size of backup image in MB.

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 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 and impact on application	backup-set, backup-date, backup-level, backup-size, backup-time, read-locks-time, flush-logs-time
restore-full-info	Information for doing full/incremental restoration	backup-set, backup-date, backup-level,backup-directory
restore-incr-info	Information for selective restoration	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. 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 <html> <head> <title>Z R M B A C K U P R E P O R T</title> <link rel="stylesheet" href="css/default.css"> </head> <body> <table class="main" border="2" bordercolor="blue" > <tr class="head"> <th align="right" class="h_backup_set">backup_set</th> <th align="left" class="h_backup_date">backup_date</th> <th align="center" class="h_backup_level">backup_level</th> <th align="left" class="h_backup_status">backup_status</th> <th align="left" class="h_comment">comment</th> </tr> <tr class="r_normal"> <td align="right" class="c_backup_set">backupSet2</td> <td align="left" class="c_backup_date">2006-08-29 14:07:23</td> <td align="center" class="c_backup_level">0</td> <td align="left" class="c_backup_status">Backup done but with errors</td> <td align="left" class="c_comment">----</td> </tr> <tr class="r_normal"> <td align="right" class="c_backup_set">backupSet2</td> <td align="left" class="c_backup_date">2006-08-29 14:08:19</td> <td align="center" class="c_backup_level">0</td> <td align="left" class="c_backup_status">Backup succeeded</td> <td align="left" class="c_comment">----</td> </tr> <tr class="r_normal"> <td align="right" class="c_backup_set">backupSet2</td> <td align="left" class="c_backup_date">2006-08-29 14:10:01</td> <td align="center" class="c_backup_level">0</td> <td align="left" class="c_backup_status">Backup succeeded</td> <td align="left" class="c_comment">----</td> </tr> <tr class="r_normal"> <td align="right" class="c_backup_set">backupSet2</td> <td align="left" class="c_backup_date">2006-08-29 17:45:14</td> <td align="center" class="c_backup_level">0</td> <td align="left" class="c_backup_status">Backup succeeded</td> <td align="left" class="c_comment">----</td> </tr> <tr class="r_normal"> <td align="right" class="c_backup_set">backupSet2</td> <td align="left" class="c_backup_date">2006-08-29 17:46:16</td> <td align="center" class="c_backup_level">0</td> <td align="left" class="c_backup_status">Backup succeeded</td> <td align="left" class="c_comment">----</td> </tr> <tr class="r_normal"> <td align="right" class="c_backup_set">backupSet2</td> <td align="left" class="c_backup_date">2006-08-29 17:48:04</td> <td align="center" class="c_backup_level">0</td> <td align="left" class="c_backup_status">Backup succeeded</td> <td align="left" class="c_comment">----</td> </tr> </table> </body> </html>

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

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

Navigation menu

Search