Easy Steps to Backup and Delete Old Records in MySQL Table

Описание: Learn how to efficiently backup old records in a new MySQL table before a specified date and delete them from the original table. Follow our step-by-step guide!
---
This video is based on the question https://stackoverflow.com/q/68126994/ asked by the user 'Mayur Kandalkar' ( https://stackoverflow.com/u/10971579/ ) and on the answer https://stackoverflow.com/a/68435046/ provided by the user 'FanoFN' ( https://stackoverflow.com/u/10910692/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Delete old records from table and backup those records in new backup table before date

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Backup and Delete Old Records from a MySQL Table

Managing data in databases is crucial for maintaining performance and ensuring that only relevant records are kept. In this guide, we're addressing a common scenario: you have a MySQL table and need to backup old records before a certain date, specifically before January 1, 2021, and also delete those records from the original table.

Here’s how to accomplish this task in a systematic way.

Step 1: Create a New Backup Table

Before modifying your existing data, the first step is to create a new table where the old records will be stored. This provides a safe way to back them up before deleting anything from the original table.

[[See Video to Reveal this Text or Code Snippet]]

This command generates a new table named new_table that has the same structure as your original_table.

Step 2: Insert Records into the Backup Table

Next, you’ll want to populate your new backup table (new_table) with the old records that are to be backed up. You’ll be selecting records where the created_on date is before January 1, 2021.

Use the following command:

[[See Video to Reveal this Text or Code Snippet]]

By using this query, you’ll efficiently move the designated records into your new table. Note that transferring historical records can sometimes be faster since they usually involve fewer entries than recent data.

Step 3: Rename the Tables

Once you have successfully backed up the old records, it’s time to swap the original table with the new one. This is where you’ll rename the tables.

Run these SQL commands:

[[See Video to Reveal this Text or Code Snippet]]

This action renames your original table to old_table1 and updates the new backup table to be the new original_table.

Step 4: Verify the Backup and Clean Up

Before you delete any old data, you want to ensure that all necessary records from the year 2021 onwards are present in the new original_table. Conduct a quick verification by running a count or check on the data.

If everything checks out, and you're confident that the records for 2021 are intact, you can proceed to delete the old records from the old_table1, or you may choose to keep them as a precautionary measure.

To delete the records, use:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

In conclusion, managing old records in MySQL doesn’t have to be daunting. By following the above steps, you can effectively create a backup of those records in a new table and clean up your original table. Regular maintenance like this keeps your database optimized and performs better over time.

Make sure you run these queries during a maintenance window or backup your database first, just in case something doesn’t go as planned. Happy SQL managing!