can you purge data older than 4 months old
hi all, i was just wondering i know about the "mysqlcheck -a and -o" ie to optimize and to analyse ie to cut it down in size but NOT to purge any data but is there like a purge option to delete all data older than 4 months old in the database thanks, rob -- Regards, Robert K Wild.
Hi Rob, mysqlcheck -a analyses and reports, -o optimizes (which in innodb means writing all data out to a temporary table, then truncating and replacing the original. This can be slow and write-blocking whilst it’s underway). Neither is destructive – they don’t delete or purge data. There is no mariadb command to act upon a random database and purge old data, probably because mariadb itself does not record the age of rows (afaik) The normal process for expiring aged data is to have a column in your table with a date (which can be automatically added at the time of insertion if specified in that table’s schema) Then you would run something like; “DELETE FROM TableName WHERE DateRowName < DATE_SUB(NOW(),INTERVAL 1 YEAR);” Which would delete all rows older than a year based on the date in DateRowName. Note: That may not release the disk space until an mysqlcheck -o is run, or the equivalent sql command “OPTIMIZE TABLE TableName;”. (See wider topics on innodb fragmentation) From: robert k Wild via discuss <discuss@lists.mariadb.org> Sent: Wednesday, June 28, 2023 7:52 PM To: discuss@lists.mariadb.org Subject: [MariaDB discuss] can you purge data older than 4 months old hi all, i was just wondering i know about the "mysqlcheck -a and -o" ie to optimize and to analyse ie to cut it down in size but NOT to purge any data but is there like a purge option to delete all data older than 4 months old in the database thanks, rob -- Regards, Robert K Wild.
Thanks Simon On Thu, 29 Jun 2023, 08:55 Simon Avery, <Simon.Avery@atass-sports.co.uk> wrote:
Hi Rob,
mysqlcheck -a analyses and reports, -o optimizes (which in innodb means writing all data out to a temporary table, then truncating and replacing the original. This can be slow and write-blocking whilst it’s underway).
Neither is destructive – they don’t delete or purge data.
There is no mariadb command to act upon a random database and purge old data, probably because mariadb itself does not record the age of rows (afaik)
The normal process for expiring aged data is to have a column in your table with a date (which can be automatically added at the time of insertion if specified in that table’s schema)
Then you would run something like;
“DELETE FROM TableName WHERE DateRowName < DATE_SUB(NOW(),INTERVAL 1 YEAR);”
Which would delete all rows older than a year based on the date in DateRowName.
Note: That *may not *release the disk space until an mysqlcheck -o is run, or the equivalent sql command “OPTIMIZE TABLE TableName;”. (See wider topics on innodb fragmentation)
*From:* robert k Wild via discuss <discuss@lists.mariadb.org> *Sent:* Wednesday, June 28, 2023 7:52 PM *To:* discuss@lists.mariadb.org *Subject:* [MariaDB discuss] can you purge data older than 4 months old
hi all,
i was just wondering i know about the "mysqlcheck -a and -o" ie to optimize and to analyse ie to cut it down in size but NOT to purge any data
but is there like a purge option to delete all data older than 4 months old in the database
thanks,
rob
--
Regards,
Robert K Wild.
participants (2)
-
robert k Wild -
Simon Avery