Delete all backup tables automatically using MySQL database version 8.0.12

[landomarossi]

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

[Thallius]

Maybe this answers your question?

Drop Tables with Wildcard

So I got SELECT CONCAT( "DROP TABLE ", GROUP_CONCAT(TABLE_NAME) ) AS stmt FROM information_schema.TABLES WHERE TABLE_SCHEMA = "my_db" AND TABLE_NAME LIKE "__temp_%"; It generates a stmt like DROP TABLE __temp_afsrpsy,__temp_dfsposb,__temp_exyrygx,__temp_gfyunsc,__temp_jhwshaj,__temp_khlmgnv,__temp_mhbhtfy,__temp_ondgdqr,__temp_oqucumc,__temp_prmgzax,__temp_qqhlwgi,__temp_vhrmdpu,__temp_xpqhrew,__temp_xraqrss,__temp_yvnplnu This is OK as it is a two step proce…

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.

[landomarossi]

Thanks for this help.

[ServerStorm]

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