MySQL Bugs: #33364: Online Backup: Purge statement missing

Bug #33364	Online Backup: Purge statement missing
Submitted:	19 Dec 2007 11:06	Modified:	14 Nov 2008 19:20
Reporter:	Lars Thalmann	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0 source	OS:	Any
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
For binary log handling we have "PURGE {BINARY|MASTER} LOGS".
Wouldn't it make sense for backups to have "PURGE BACKUPS"?

Something like:

PURGE BACKUPS TO <backup_id>;  # This would delete files and
                               # references to (not including) this id.

PURGE BACKUPS BEFORE <date(time)>; /* This would delete backups and
                                      references to all backups taken
                                      prior to this point-in-time
                                   */

The reason for suggesting this is so that we have a centralized
way of removing old backups from disk without having stale
links to the path in the IS table.
In 3.23/4.0 people had problems when removing binlogs physically
from the file system since the binlog.index file still thought
they were there, I could see the same happening here if we don't
have a command to run that syncs the IS table with the physical
backup files.

The reason for picking the TO and BEFORE as keywords for the
PURGE command makes it behave and look the same as PURGE BINARY LOGS,
since this has those two options too. Consistent and easy to
remember.

(Thanks to Tobias for the above description!)

How to repeat:
The command is obviously missing

Suggested fix:
Add command

Friendly ammendment...

The SQL could be made a bit more logical if we used

PURGE BACKUP LOGS...

and

PURGE RESTORE LOGS...

If there is no heartburn over this I will proceed.

Partially fixed by WL#4296 (in review). This bug (feature request) cannot be completed until WL#4296 has been pushed.

Please check the backup_log_output test and uncomment a section of the test to allow purging of backup logs.

There are two places in the backup_log_output test that use a grep command to check the contents of the log files. This command is not portable and must be replaced.

The suggested implementation would be to complete the purge command as stated in this worklog then purge the logs prior to backup and then use another command to dump the output or even diff against a known output file. This should ensure the test is possible across platforms.

WL#4296 implements merely the FLUSH comamnd that is needed by the purge implementation. WL#4296 does not implement any purge capability.

REQUIREMENTS
------------
The solution for this worklog should be designed to allow users to purge the backup logs and to purge any backup images. 

1. To purge the backup logs, the syntax (or similar) is:

PURGE BACKUP LOGS

This command purges the backup logs of all data.

PURGE BACKUP LOGS TO <backupid>

This command purges the backup logs of all data with a backupid less than the one specified in the command.

PURGE BACKUP LOGS BEFORE <date> 

This command purges the backup logs of all data prior to the datetime specified in the command. Note that this should be based on the start time in the backup history log.

2. To purge (delete) backup images, the syntax is:

PURGE BACKUP IMAGES

This command deletes all of the backup images specified in the backup history log. This is accomplished by reading the backupfile column.

PURGE BACKUP IMAGES TO <backupid>

This command deletes backup images with a backupid less than the one specified in the command.

PURGE BACKUP IMAGES BEFORE <date> 

This command deletes backuip images created prior to the datetime specified in the command. Note that this should be based on the start time in the backup history log.

Design Plan
-----------
1) Add new SQLCOM enums for each of the three commands above.

  (in sql_lex.h)

  SQLCOM_PURGE_BACKUP, SQLCOM_PURGE_BACKUP_DATE, SQLCOM_PURGE_BACKUP_ID,

2) Add new cases in 'big switch' for commands.

  (in sql_parse.cc)

  case SQLCOM_PURGE_BACKUP:
  case SQLCOM_PURGE_BACKUP_DATE:
  case SQLCOM_PURGE_BACKUP_ID:

3) Add new sections to PURGE in sql_yacc.yy to process the commands.

4) Add methods to MYSQL_BACKUP_LOG class for processing commands.
  
   (in log.h/log.cc)
   int MYSQL_BACKUP_LOG::purge_backup_logs();
   int MYSQL_BACKUP_LOG::purge_backup_before_date(time_t purge_time);
   int MYSQL_BACKUP_LOG::purge_backup_before_id(ulonglong id);
   
5) Write tests.

Note: The methods included in the patch may differ slightly from those listed above pending code implementation and testing results.

Note: The design plan for purging backup images shall follow the same design plan for the purge backup logs commands as described above.

It has been determined the command:

PURGE BACKUP IMAGES;

is too powerful. This command would delete all backup images in the backupdir.

To make it a bit more safe, the command syntax is now:

PURGE BACKUP IMAGES <wildcard>;

This forces the user to decide what files they want to delete. Of course, one can still form a wildcard to include all files. This is much safer in that it gives the user extra incentive to consider the consequences.

PURGE BACKUP Command syntax:

// Delete all entries in the backup logs.
// Applies to table and/or file output destinations.
PURGE BACKUP LOGS;

// Delete all log entries with backup_id < id specified.
// Applies to table output destinations only.
PURGE BACKUP LOGS TO <id>;

// Delete all log entries with start_time < datetime specified.
// Applies to table output destinations only.
PURGE BACKUP LOGS BEFORE <datetime>;

// Delete all backup images that match the wildcard string
// that reside in the backupdir path.
// Applies to table and/or file output destinations.
PURGE BACKUP IMAGES <wildcard>;

// Delete all backup images referenced in the backup_history
// log that have a backup_id < id specified.
// Applies to table output destinations only.
PURGE BACKUP LOGS TO <id>;

// Delete all backup images referenced in the backup_history
// log that have a start_time < datetime specified.
// Applies to table output destinations only.
PURGE BACKUP IMAGES BEFORE <datetime>;

User notes:
* Users should first purge backup images then purge backup logs. 
  This will allow for complete removal of previous backup or
  restore historical and progress data along with associated
  backup image files.
* Users should take care in forming the wildcard for the purge
  backup images command. It is advisable to use the most 
  restrictive wildcard possible when deleting backup images
  using this method.

If the PURGE BACKUP IMAGES command is issued with a wildcard of '%' and backupdir = datadir, an error is issued. This is to protect the files in the datadir from accidental deletion. Good thing, eh?

The PURGE BACKUP IMAGES <wildcard> command shall delete only backup images. It cannot delete normal files.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56532

2712 Chuck Bell	2008-10-18
      BUG#33364 Online Backup: Purge statement missing 
      
      This patch adds the following commands:
      
      PURGE BACKUP LOGS;
      PURGE BACKUP LOGS TO <id>;
      PURGE BACKUP LOGS BEFORE <datetime>;
      PURGE BACKUP IMAGES <wildcard>;
      PURGE BACKUP IMAGES TO <id>;
      PURGE BACKUP IMAGES BEFORE <datetime>;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56533

2712 Chuck Bell	2008-10-18
      BUG#33364 Online Backup: Purge statement missing 
      
      This patch adds the following commands:
      
      PURGE BACKUP LOGS;
      PURGE BACKUP LOGS TO <id>;
      PURGE BACKUP LOGS BEFORE <datetime>;
      PURGE BACKUP IMAGES <wildcard>;
      PURGE BACKUP IMAGES TO <id>;
      PURGE BACKUP IMAGES BEFORE <datetime>;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56534

2712 Chuck Bell	2008-10-18
      BUG#33364 Online Backup: Purge statement missing 
      
      This patch adds the following commands:
      
      PURGE BACKUP LOGS;
      PURGE BACKUP LOGS TO <id>;
      PURGE BACKUP LOGS BEFORE <datetime>;
      PURGE BACKUP IMAGES <wildcard>;
      PURGE BACKUP IMAGES TO <id>;
      PURGE BACKUP IMAGES BEFORE <datetime>;

The privileges needed for the purge backup logs commands are SUPER.
The privileges needed for the purge backup images commands are SUPER plus OS privileges for deleting files. For example, the user would need SUPER plus READ, DELETE for the backupdir when deleting backup images with the command PURGE BACKUP IMAGES '%.bak'.

Note: <id> is a ulonglong of the same type and range as mysql.backup_history (backup_id).
Note: <datetime> is a valid datetime such as '2008-10-10 13:45:11'.

The delete of backup images only is defined as reading the magic numbers defined at the front of the file (in the header). If the magic numbers are valid, and the stream class validates the file as a backup image, the file is deleted. If there are any errors in reading the header of the file, opening the file, or any other error, the file is not deleted.

It has been decided that the PURGE BACKUP IMAGES command shall not be included in this work and that a new worklog has been created to allow for proper design and architecture reviews. Please direct all comments concerning PURGE BACKUP IMAGES and deleting of backup image files in general to WL#4608.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57049

2717 Chuck Bell	2008-10-24
      BUG#33364 Online Backup: Purge statement missing 
      
      This patch adds the following commands:
      
      PURGE BACKUP LOGS;
      PURGE BACKUP LOGS TO <id>;
      PURGE BACKUP LOGS BEFORE <datetime>;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57050

2717 Chuck Bell	2008-10-24
      BUG#33364 Online Backup: Purge statement missing 
      
      This patch adds the following commands:
      
      PURGE BACKUP LOGS;
      PURGE BACKUP LOGS TO <id>;
      PURGE BACKUP LOGS BEFORE <datetime>;

Good to push after one minor fix indicated in the review.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57156

2718 Chuck Bell	2008-10-27
      BUG#33364 Online Backup: Purge statement missing 
      
      This patch adds the following commands:
      
      PURGE BACKUP LOGS;
      PURGE BACKUP LOGS TO <id>;
      PURGE BACKUP LOGS BEFORE <datetime>;

Approved pending removal of printf statement.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57206

2718 Chuck Bell	2008-10-28
      BUG#33364 Online Backup: Purge statement missing 
      
      This patch adds the following commands:
      
      PURGE BACKUP LOGS;
      PURGE BACKUP LOGS TO <id>;
      PURGE BACKUP LOGS BEFORE <datetime>;

Pushed into 6.0.9-alpha  (revid:cbell@mysql.com-20081028141705-k7pjftthi2cpf15i) (version source revid:jorgen.loland@sun.com-20081114134411-xypyf8wyjc2nm3ly) (pib:5)

Noted in 6.0.9 changelog.

A new statement, PURGE BACKUP LOGS, enables the contents of the MySQL
Backup logs to be culled.

Also created new section documenting the statement.