Selectively deleting entires in mv_backup_history table

{question}

How to selectively delete entries in mv_backup_history table?

{question}

{answer}

In this article, we are going to discuss how to delete individual records from the information_schema.MV_BACKUP_HISTORY table.

Note: information_schema.MV_BACKUP_HISTORY is populated only when the BACKUP DATABASE command has completed a database backup. Click here to learn more about the table.

Clearing Backup History

You can remove the records in the mv_backup_history table in the information_schema database by running CLEAR BACKUP_HISTORY;but it cleans up the entire table.

In a situation where It's required to remove just one or more records and not the whole table the below command with the backup_id option is needed:

CLEAR BACKUP_HISTORY backup_id = 1;

Here is an example:

mysql> SELECT backup_id, incr_backup_id, database_name, start_timestamp FROM information_schema.mv_backup_history ORDER BY backup_id;
+-----------+----------------+---------------+---------------------+
| backup_id | incr_backup_id | database_name | start_timestamp |
+-----------+----------------+---------------+---------------------+
| 1 | NULL | memsql_demo | 2021-11-02 11:05:57 |
| 2 | NULL | memsql_demo | 2021-11-02 11:07:07 |
+-----------+----------------+---------------+---------------------+
2 rows in set (0.00 sec)

mysql> CLEAR BACKUP_HISTORY backup_id = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT backup_id, incr_backup_id, database_name, start_timestamp FROM information_schema.mv_backup_history ORDER BY backup_id;
+-----------+----------------+---------------+---------------------+
| backup_id | incr_backup_id | database_name | start_timestamp |
+-----------+----------------+---------------+---------------------+
| 2 | NULL | memsql_demo | 2021-11-02 11:07:07 |
+-----------+----------------+---------------+---------------------+
1 row in set (0.00 sec)

Also, we can delete entries based on the Backup Status like success or failure:

Command: CLEAR BACKUP_HISTORY success; or CLEAR BACKUP_HISTORY failure;

mysql> CLEAR BACKUP_HISTORY success;
Query OK, 0 rows affected (0.00 sec)

mysql> CLEAR BACKUP_HISTORY failure;
Query OK, 0 rows affected (0.01 sec)

Click here to learn about database backup and restore in SingleStore.

{answer}