Automatically drop all backup tables using MySQL database version 8.0.12 – Databases – SitePoint Forums

Hi everyone,

On MySQL database version 8.0.12, this data table is defined as table master.

t_table_southwest

For each month the days from 1 to 15, I scheduled 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 the backup tables and keep only the main table.

I can generate a contiguous date range in MySQL with a query like this DB Violin

Any suggestion to delete all backup tables automatically?

Thanks in advance for the help, really appreciated.

ML


Maybe that answers your question?

BTW, duplicating a table with data in the same database isn’t really a backup, if the database is corrupted you’re still lost. I would prefer to do a mysqldump to a file instead. In this case, you can also very easily delete the backups by deleting the files.



2 likes

Hi,

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

In this case, you can dump to file at any rate you want and once you can have a CRON job to delete dump files that meet certain types of criteria such as file age.

Cheers,
Steve