On MySQL database version 8.0.12 I have this data table set as table master.
For each month on days from 1th to 15th, I have programmed the daily backup of the master table.
These are the backup tables
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
Any suggestion for delete all backup tables automatically?
Thanks in advance for help, really appreciated.
Maybe this answers your question?
So I got
"DROP TABLE ",
) AS stmt
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.
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.