MySQL Tutorial: Archiving and Loading Archived Table Data
Let’s say you have a really large table and you want to archive the data to a new table so that your main table is smaller again. This is useful with things like chat logs or game logs which grow large really quickly but you don’t want to lose the data or you want to keep a history of it. Making an archive table from the main table allows you to truncate the main table for faster queries.
Create the Archive Table
If you want to save the contents of the table into another archive table you’ll need to create a copy of the original table with all the same indexes and schema:
CREATE TABLE your_new_archive_table LIKE the_table_you_want_to_archive;
Dump the Data into the Archive Table
Now you have an archive table that matches your original table let’s dump the data into it:
INSERT your_new_archive_table * FROM the_table_you_want_to_archive;
Dump the Data into a SQL file
If you prefer to save your data to a file on the server rather than in another table you can archive it this way instead. Make sure your file has saved and has data in it before you truncate your original table.
SELECT * FROM the_table_you_want_to_archive INTO OUTFILE '/save_path/your_new_archive_table .sql';
Test the Archive Table
Make sure your table has data in it by looking at your new archive table:
SELECT * FROM your_new_archive_table LIMIT 100;
Test the Archive File
Go to the command line and check to make sure you file was created and has data in it by typing the following:
cat /save_path/your_new_archive_table.sql
Empty the Original Table
Make sure your archive table or archive file has data in it before you empty your original table.
TRUNCATE TABLE the_table_you_want_to_archive;
Load an Archive Table Back Into the Original Table
If you decide you want to load your archived data back into the original table you can do the following.
INSERT the_table_you_want_to_archive * FROM your_new_archive_table;
Load an Archive File Back Into the Original Table
Once you have an archive file you can load it back into your database by doing the following:
LOAD DATA INFILE '/save_path/your_new_archive_table.sql' INTO TABLE the_table_you_want_to_archive;
