Delete all backup tables automatically using MySQL database version 8.0.12

Databases
#1

Hi all,

On MySQL database version 8.0.12 I have this data table set as table master.

t_table_southwest

For each month on days from 1th to 15th, I have programmed the daily backup of the master table.

These are the backup tables

t_table_southwest_20220401
t_table_southwest_20220402
t_table_southwest_20220403
t_table_southwest_20220404
t_table_southwest_20220405
t_table_southwest_20220406
t_table_southwest_20220407
t_table_southwest_20220408
t_table_southwest_20220409
t_table_southwest_20220410
t_table_southwest_20220411
t_table_southwest_20220412
t_table_southwest_20220413
t_table_southwest_20220414
t_table_southwest_20220415

When the current date is greater than the date of day 15 (last day of the table backup) I need to automatically delete all backup tables and keep only the master table.

I can generate a range of contiguous dates in MySQL with a query like this DB Fiddle

Any suggestion for delete all backup tables automatically?

Thanks in advance for help, really appreciated.

LM

#2

Maybe this answers your question?

BTW, duplicating a table with data in the same database is not really a backup, if the database gets corrupted you are still lost. I would prefer to do an mysqldump to a file instead. In that case you can also very easy delete the backups by deleting the files.

2 Likes
#3

Thanks for this help.

#4

Hi,

I too would do a MySQL dump rather than a db backup.

In this case you could dump to file in your desired cadence and once you could have a CRON job to delete dump files that meet some type of criteria like the age of files.

Regards,
Steve

1 Like