Zmanda Recovery Manager for MySQL: Difference between revisions
Line 15: | Line 15: | ||
== [[Do I need to make changes to MySQL database configuration?]] == | == [[Do I need to make changes to MySQL database configuration?]] == | ||
== How do I configure MySQL ZRM? | == [[How do I configure MySQL ZRM?]] == | ||
== | |||
== Finally, Can I do MySQL backups? == | == Finally, Can I do MySQL backups? == |
Revision as of 03:39, 19 September 2006
What is Zmanda Recovery Manager for MySQL
Zmanda Recovery Manager for MySQL (MySQL ZRM) is a flexible and robust backup and recovery solution for MySQL server. It also provides users the capability to schedule and get report on the backup of MySQL Databases.
What does MySQL ZRM run on?
I have questions/suggestions/bug fixes. How do I contact other users/developers?
What can MySQL ZRM do?
What will be implemented in future releases?
How do you install MySQL ZRM?
Do I need to make changes to MySQL database configuration?
How do I configure MySQL ZRM?
Finally, Can I do MySQL backups?
Yes!!! All configuration is complete. You can do backups now or schedule backups for a later time.
Backup now
MySQL ZRM scheduler tool can be used to perform backups immediately. The following command does a full backup for backup set dailyrun:
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0
Backup now option of mysql-zrm-scheduler should be used after MySQL ZRM configuration changes. The backup now option also checks the MySQL ZRM configuration before doing the backup.
If mailto parameter is configured for the backup set, following email will be sent to the administrator.
backup-set=dailyrun backup-date=20060828102505 mysql-version=4.1.12 backup-directory=/var/lib/mysql/dailyrun/20060828102505 backup-level=0 raw-databases=cdcol mysql phpmyadmin test backup-time=400 seconds. backup-size=0.29 MB last-backup=/var/lib/mysql/dailyrun/20060828101657 backup-status=Backup succeeded
Incremental backups
If backup level is set to 1 in mysql-zrm-scheduler command line or in mysql-zrm.conf configuration file, incremental backup is performed. Incremental backups are performed with respect to prior full backup or incremental backup for the backup set. Binary logging should be enabled in the MySQL server. Incremental backups uses binary logging and the binary log backups are not restricted to the list of databases or tables specified in the backup set. MySQL binary logs contain all database events executed by the MySQL server.
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 1
Scheduling backup runs
The mysql-zrm-scheduler tool can schedule daily, weekly, monthly backup runs for a backup set. You can also query the scheduled runs as well as delete a scheduled run. If start time is not specified, start time of 2am is assumed for daily runs, 3am for weekly runs and 12:00am is assumed for monthly runs. The start time is based on date and time on the machine where MySQL ZRM is running.
The mysql-zrm-scheduler tool also purges MySQL backups based on backup retention policy every day at 4am.
Examples on how to use mysql-zrm-scheduler:
- To schedule a weekly full backup run for backup set BackupSet1
# mysql-zrm-scheduler --add --interval weekly
- To schedule a daily incremental backup run at 1:35pm:
# mysql-zrm-scheduler --add --interval daily --start 13:35 --backup-level 1
- To display scheduled backup runs:
# mysql-zrm-scheduler --query 0 2 * * 0 /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set BackupSet1 35 13 * * * /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set BackupSet1 --backup-level 1 0 4 * * * /usr/bin/mysql-zrm --action purge --destination /var/lib/mysql-zrm # purging expired backup files at 4am daily
- To delete a weekly backup run from the schedule for the backup set BackupSet1:
# mysql-zrm-scheduler --delete --interval weekly
- To delete a weekly backup run which has specific start time from schedule:
# mysql-zrm-scheduler --delete --interval weekly --start 08:20
- To schedule a backup run for backup set dailyrun now:
# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0
<br\> To change backup run schedule:
- Run mysql-zrm-scheduler --query to list the current scheduled backup runs.
- Run mysql-zrm-scheduler --delete .. to remove the scheduled run.
Checking status of backup run
The mysql-zrm-reporter tool provides information on status on each backup run. The status can be
- Backup succeeded
- Backup done with errors
- Backup failed
MySQL ZRM logs can be used to figure out why the backup failed or why the errors occurred. The email notification also contains information on the status of the backup run.
Following example shows how to mysql-zrm-reporter to find status of backup runs for the backup set "backupSet3":
# mysql-zrm-reporter --fields backup-set,backup-date,backup-level,backup-status --where backup-set=backupSet3 *********************************************************************************************************** backup-set backup-date backup-level backup-status *********************************************************************************************************** backupSet3 20060829140739 0 Backup done but with errors backupSet3 20060829140836 0 Backup succeeded backupSet3 20060829141029 0 Backup succeeded
Email notification
The mysql-zrm tool sends email to MySQL database administrator after every backup run. The email contains information about the status of backup run, backup statistics, location of backup and backup level.
mailto parameter in mysql-zrm.conf configuration file should be configured to get email notification.
The machine where MySQL ZRM is running should be configured for sending emails.
Example: Mail message sent after successful incremental backup run.
Subject: MySQL ZRM Backup Report backup-set=backup backup-date=20060829093000 mysql-version=4.1.21-standard-log backup-directory=/mysql-zrm/backup/20060829093000 backup-level=1 incremental=vmsql4-bin.[0-9]* backup-time=0 seconds. backup-size=3431 Bytes next-binlog=vmsql4-bin.000009 last-backup=/mysql-zrm/backup/20060829092833 backup-status=Backup succeeded
Verification of backup images
MySQL ZRM has the capability of verifying the backup images. It verifies the consistency of backup using backup checksums. It is a recommended practice to verify the newly created backup image after a backup run. Best practice is to verify the backup images for all backup sets on a regular basis.
The following command verifies the backup images of the last backup run for the backup set backup:
# mysql-zrm --action verify-backup --backup-set backup
The following command verifies the specific backup run of backup set backup:
# mysql-zrm --action verify-backup --backup-set backup \ --source-directory /var/lib/mysql-zrm/backup/20060829093000
mysql-zrm returns 0 if the verification is successful.
Backup failed. What do I do?
MySQL ZRM logs are available under /var/log/mysql-zrm directory on the machine where MySQL ZRM is running. All backup and recovery operations, status of these operations are logged. These logs can be used for debugging in case of failures as well as auditing backup/recovery database operations.
mysql-zrm-scheduler and mysql-zrm-reporter also have log files in the same directory.
Example: mysql-zrm log file entries from a restore of incremental backup:
Wed Aug 30 02:39:48 2006: INFO: mysql-zrm started
All the MySQL backup runs starts with "mysql-zrm started" message.
Wed Aug 30 02:39:48 2006: INFO: action being performed is restore Wed Aug 30 02:39:48 2006: INFO: backup set being used is backup
Above lines show the list of command line parameters to mysql-zrm
Wed Aug 30 02:39:48 2006: INFO: Reading options from file /etc/mysql-zrm/backup/mysql-zrm.conf Wed Aug 30 02:39:48 2006: INFO: Mail address: root is ok Wed Aug 30 02:39:48 2006: INFO: Input Parameters Used { Wed Aug 30 02:39:48 2006: INFO: verbose=1 Wed Aug 30 02:39:48 2006: INFO: backup-level=1 Wed Aug 30 02:39:48 2006: INFO: mailto=root Wed Aug 30 02:39:48 2006: INFO: destination=/mysql-zrm Wed Aug 30 02:39:48 2006: INFO: databases=wikidb Wed Aug 30 02:39:48 2006: INFO: source-directory=/mysql-zrm/backup/20060830020843 Wed Aug 30 02:39:48 2006: INFO: host=localhost Wed Aug 30 02:39:48 2006: INFO: database=wikidb Wed Aug 30 02:39:48 2006: INFO: backup-mode=raw Wed Aug 30 02:39:48 2006: INFO: password=****** Wed Aug 30 02:39:48 2006: INFO: user=root Wed Aug 30 02:39:48 2006: INFO: stop-position=5003 Wed Aug 30 02:39:48 2006: INFO: }
The above lines show the list of parameters used for restore operation
Wed Aug 30 02:39:48 2006: INFO: Getting the data directory Wed Aug 30 02:39:48 2006: INFO: mysqladmin --user=root --password=***** --host=localhost variables 2> /tmp/mysql-zrm-20060830023948.out Wed Aug 30 02:39:48 2006: INFO: datadir is /var/lib/mysql/ Wed Aug 30 02:39:48 2006: INFO: mysql_version is 4.1.21-standard-log Wed Aug 30 02:39:48 2006: INFO: Checking if this is a replication slave using command Wed Aug 30 02:39:48 2006: INFO: echo "show slave status"|mysql --user=root --password=***** --host=localhost Wed Aug 30 02:39:48 2006: INFO: This is not a replication slave or we do not have appropriate access rights. replication data if any has not been backed up. Wed Aug 30 02:39:48 2006: INFO: Ignoring the --replication option
The above lines show validation of backup parameters
Wed Aug 30 02:39:48 2006: INFO: Restoring incremental Wed Aug 30 02:39:48 2006: INFO: mysqlbinlog --user=root --password=***** --host=localhost --stop-position=5003 --database=wikidb "/mysql-zrm/backup/20060830020843"/vmsql4-bin.[0-9]* | mysql --user=root --password=***** --host=localhost Wed Aug 30 02:39:48 2006: INFO: Incremental restore done
Incremental restoration is complete
Wed Aug 30 02:39:48 2006: INFO: for database wikidb Wed Aug 30 02:39:48 2006: INFO: Wed Aug 30 02:39:48 2006: INFO: Shutting down MySQL Wed Aug 30 02:39:53 2006: INFO: Restore done in 5 seconds.
MySQL server is shutdown after database restoration.
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.
- 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 allows user to specify the format for each backup parameter. User can specify the size of each parameter value and alignment 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
- 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 (">") aligned
How do I recover data when there is a failure or data loss?
Backups can be recovered from full backups as well as incremental backups. mysql-zrm tool has options to restore full and incremental backups. It is possible to do selective restoration of a backup set from incremental backups.
MySQL ZRM does not support restoring to live databases. All applications using the database that is being used must be stopped.
Before restoring backups, it is necessary to find out the directory location of full/incremental backups for a backup set. The mysql-zrm-reporter tool can be used to find the location of backups and backup levels for a backup set.
Following example shows the backup directory, backup level, backup date stamp for a backup set backupSet1:
# mysql-zrm-reporter --fields backup-set,backup-date,backup-level,backup-directory \ --where backup-set=backupSet1 ****************************************************************************************** backup-set backup-date backup-level backup-directory ******************************************************************************************* backupSet1 20060829140710 0 /var/lib/mysql-zrm/backupSet1/20060829140710 backupSet1 20060829140803 0 /var/lib/mysql-zrm/backupSet1/20060829140803 backupSet1 20060829140933 0 /var/lib/mysql-zrm/backupSet1/20060829140933
After restoring the database or tables in the database, it is important to verify the restored database contents before restarting the MySQL server.
Complete restoration of full/incremental backups
The mysql-zrm can be used to restore full/incremental backups using restore action. Following example shows complete restoration of backup set backupSet1:
# mysql-zrm --action restore --backup-set backupSet1 \ --source-directory /var/lib/mysql-zrm/backupSet1/20060829140710 MySQL server has been shutdown. Please restart after verification.
Selective restoration
Selective restoration of a backup set is possible only from an incremental backup. The prior full backup or incremental backup should have been restored before attempting selective restoration.
It is important to determine the list of database events that should included or excluded from selective restoration. The next section talks about how to browse incremental backups (MySQL binary logs) to determine the database events in the binary logs.
The database events that have to be selectively restored can be specified in terms of database events or in terms of time when the events actually occurred.
Browsing MySQL binary logs
The mysql-zrm provides an option (--action parse-binlogs) to parse binary logs to determine the log positions and timestamp of database events. Information from mysql-zrm tool parse binary logs output can be used as input for mysql-zrm restore action. The binary logs output contains the binary log filename, position in the log, timestamp, type of event and actual database event. Binary logs only contain database events modify data or data attributes.
The backup directory location for the incremental backups can be found using mysql-zrm-reporter command.
The following mysql-zrm command displays binary logs from incremental backup directory /var/lib/mysql:
# mysql-zrm --action parse-binlogs --source-directory=/var/lib/mysql
Sample output from parse binary logs command:
---------------------------------------------------------------------------- Log filename | Log Position | Timestamp | Event Type | Event ---------------------------------------------------------------------------- /var/lib/mysql/my-bin.000015 | 9762 | 06-09-19 06:20:03 | Query | CREATE TABLE `table_InnoDB` ( `name` varchar(20) default NULL, `age` int(3) default NULL, `address` varchar(200) default NULL, `sex` char(1) default NULL, `DOB` date default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /var/lib/mysql/my-bin.000015 | 10058 | 06-09-19 06:20:03 | Query | /var/lib/mysql/my-bin.000015 | 10178 | 06-09-19 06:20:03 | Query | INSERT INTO `table_InnoDB` VALUES ('1kkg',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg1',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg2',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg3',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg4',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg5',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg6',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg7',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg8',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg9',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg10',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg11',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'); /var/lib/mysql/my-bin.000015 | 11013 | 06-09-19 06:20:03 | Xid = 4413 | COMMIT; /var/lib/mysql/my-bin.000015 | 11040 | 06-09-19 06:20:03 | Query | /var/lib/mysql/my-bin.000015 | 11159 | 06-09-19 06:20:03 | Query | DROP TABLE IF EXISTS `table_MyISAM`; /var/lib/mysql/my-bin.000015 | 11263 | 06-09-19 06:20:03 | Query | CREATE TABLE `table_MyISAM` ( `name` varchar(20) default NULL, `age` int(3) default NULL, `address` varchar(200) default NULL, `sex` char(1) default NULL, `DOB` date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /var/lib/mysql/my-bin.000015 | 11559 | 06-09-19 06:20:03 | Query | /var/lib/mysql/my-bin.000015 | 11679 | 06-09-19 06:20:03 | Query | INSERT INTO `table_MyISAM` VALUES ('1kkg',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg1',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg2',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg3',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg4',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg5',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg6',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg7',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg8',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg9',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg10',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'),('1kkg11',38,'asdsad safdasd sqdasd sadsad','m','1998-07-09'); /var/lib/mysql/my-bin.000015 | 12514 | 06-09-19 06:20:03 | Query | /var/lib/mysql/my-bin.000015 | 12633 | 06-09-19 06:20:03 | Stop | /var/lib/mysql/my-bin.000016 | 4 | 06-09-19 06:20:14 | Start: binlog v 4, server v 5.0.21-max-log created 060819 6:20:14 at startup | ROLLBACK; /var/lib/mysql/my-bin.000016 | 98 | 06-09-19 06:25:10 | Stop | /var/lib/mysql/my-bin.000017 | 4 | 06-09-19 19:15:13 | Start: binlog v 4, server v 5.0.21-max-log created 060819 19:15:13 at startup | ROLLBACK; /var/lib/mysql/my-bin.000017 | 98 | 06-09-19 19:35:57 | Query | FLUSH TABLES /*!32323 `kkg123`.`table_ARCHIVE`, `kkg123`.`table_BerkeleyDB`, `kkg123`.`table_InnoDB`, `kkg123`.`table_MyISAM` */; /var/lib/mysql/my-bin.000017 | 285 | 06-09-19 20:08:28 | Rotate to my-bin.000018 pos: 4 |
Restoration based on timestamp - Point in time recovery
MySQL ZRM can do point in time recovery. Selective recovery of an incremental backup can be done till a particular time or starting from a particular time. For example: A full backup of a backup set is done at 1am and followed by an incremental backup at 10am. It is possible to recover the full backup and recover the database to the state at 8am from the incremental backup.
The following example shows restoration of backup set backup to the state at 9pm on Aug 30, 2006.
# mysql-zrm --action restore --backup-set backup \ --source-directory /var/lib/mysql-zrm/backup/20060830020843 \ --stop-datetime "200608302100"
MySQL server has been shutdown. Please restart after verification.
Restoration based on log position
MySQL ZRM can do selective database recovery based on the positions in the binary log. This selective recovery method is useful for recovering from operator errors. For example: Suppose there was errant SQL statement executed to drop a database table between last full backup and last incremental backup. To recover from the error, the last full backup should be restored. The incremental backup is selectively restored starting from the beginning till the errant statement and followed by another restoration from the event after the errant statement.
The mysql-zrm tool option --stop-position can be used to recover the database till the particular log position. All events that have log positions less than the log position are recovered.
Option --start-position can be used to start recovery from a particular log position instead of beginning of the log file.
If there are multiple binary log files in an incremental backup, the --start-position refers to the log position in the first log file and --stop-position refers to the log position in the last log file. --bin-logs should be used to specify the binary log file names when there are more than one binary log file in the incremental backup.
The mysql-zrm also supports --offset parameter to specify an offset that can be skipped to skip N entries from the first log file.
Following example shows restoration from incremental backup stored in /var/lib/mysql-zrm/backup/2006830020843 directory starting from log position 4 to log position 22:
# mysql-zrm --action restore --backup-set backup \ --source-directory /var/lib/mysql-zrm/backup/20060830020843 \ --start-position 4 --stop-position 122 MySQL server has been shutdown. Please restart after verification.
If MySQL server is running, it is stopped by mysql-zrm tool during database restoration.
Following example will selectively restore from log position 100 from /var/lib/mysql-zrm/backupset1/200608 18121532/mysql-bin.00001 from multiple binary log files using single connection to the MySQL server:
# mysql-zrm --action restore --bin-logs \ "/var/lib/mysql-zrm/backupset1/20060818121532/mysql-bin.[0-9]* \ /var/lib/mysql-zrm/backupset1/20060819121532/mysql-bin.[0-9]*" --start-position=100
Verifying restoration
After restoration of the database, MySQL ZRM shuts down the MySQL server. It is important to check the database(s)/table(s) that were restored before restarting the MySQL server. SQL command CHECK TABLE can be used for consistency checking. Use of EXTENDED option is recommended. EXTENDED option does a full key lookup for all rows in the table and will take significant time for a large database.
mysql> CHECK TABLE <table1>, <table2> EXTENDED;
No other application must be using the database during table consistency check.
How did the MySQL ZRM do the job?
Did I use the correct method to backup?
The mysql-zrm-reporter tool provide backup statistics about the MySQL backup run. It is a good idea to review the backup-time, read-locks-time, flush-logs-time and backup-size to determine whether the method used by MySQL ZRM is appropriate for the backup set. The predefined backup-performance-info report is a useful tool to tune backup process or switch to a different backup method.
An example command displaying backup size and backup time for a backup set backupSet2:
# mysql-zrm-reporter --fields backup-date,backup-level,backup-size,backup-time \ --where backup-set=backupSet2 *************************************************************************** backup-set backup-date backup-level backup-size backup-time **************************************************************************** backupSet2 20060829140723 0 58MB 15 seconds. backupSet2 20060829140819 0 59MB 26 seconds. backupSet2 20060829141001 0 78MB 40 seconds.
Using mysql-zrm-reporter tool, you can figure out what backup method was used to do the backup (backup-method-info report). You can override the backup method by setting backup parameters - replication, logical-backup, lvm-snapshot in MySQL ZRM configuration for the backup set.
Can I make backups more efficient?
The time taken to do a backup (backup window) and backup image size depends on various factors:
- Backup method used for each database - logical backups, raw backups, lvm snapshot, MySQL replication
- Backup level for the backup set
- Size of the database
- Database activity - read-only, read/write ratio
- Database transaction rates
- Recovery requirements - how often do you recover data? What is the reason for data recovery?
Use mysql-zrm-reporter tool and mysql-zrm logs to analyze the backups. The backup-performance-info report is a good starting point to analyze the bottlenecks in the backup strategy. It is possible to change all the MySQL backup parameters using the configuration file. Often, it takes multiple backup runs to arrive at a good parameters for a backup set.
Example: Backup performance report
# /usr/bin/mysql-zrm-reporter --where backup-set=backupSet1 --show backup-performance-info backup_set backup_date backup_level backup_size backup_time read_locks_time flush_logs_time ------------------------------------------------------------------------------------------------- backupSet1 20060909100021 0 4.26 MB 00:00:06 00:00:03 00:00:00 backupSet1 20060909100123 0 4.26 MB 00:00:03 00:00:02 00:00:00 backupSet1 20060909100300 0 4.26 MB 00:00:03 00:00:02 00:00:00