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>