patternsqlMinor

Creating Archiving database for old data in MySQL

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

creatingarchivingdatadatabasemysqlforold

Problem

I Have a Master and Various Slave Servers.

There is a huge amount of the data on all the servers and I wish to create an Archiving system for our Data.

Scenario is :
I want that only Last one year data to be on the servers and all other previous data to be move to archiving system.

Most of the tables are InnoDB and there may also be referential integrity between the tables. (When I purge/Delete the old data from the Servers, which i have moved to the Archive the referential integrity also may fail).

Since I have this data in the Production Servers, what steps should I take to accomplish this with no downtime?

Solution

I actually wrote a stored procedure to do this for me. I'll try and provide an example; actually I wrote two. One to create tables and the other to copy data.

I have two "foreign keys" that are used in pretty much every table they are called vhostid and mid. So this is what I did to back up tables I wrote:

CREATE DEFINER = 'mydbuser'@'%' PROCEDURE `sp_create_backup_tables`()
     NOT DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COMMENT ''
 BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE table_to_create, database_name VARCHAR(255);

     DECLARE backup_tables_cursor CURSOR FOR
      SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
  INNER JOIN INFORMATION_SCHEMA.TABLES
          ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA
         AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
       WHERE INFORMATION_SCHEMA.TABLES.TABLE_TYPE <> 'VIEW'
         AND (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'vhostid'
              OR
              INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid')
         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA <> 'db_archive'
         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'mydbname%';

     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

     CREATE DATABASE IF NOT EXISTS db_archive;

     SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME
       INTO database_name
       FROM INFORMATION_SCHEMA.SCHEMATA
      WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'mydbname%'
        AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'db_archive';

     OPEN backup_tables_cursor;

     create_tables_loop: LOOP
         FETCH backup_tables_cursor INTO table_to_create;
         IF done THEN
             LEAVE create_tables_loop;
         END IF;

         SET @backup_table_name = CONCAT('db_archive.', table_to_create, '_archive');
         SET @SQL = CONCAT('CREATE TABLE IF NOT EXISTS ', @backup_table_name, ' LIKE ', database_name, '.', table_to_create);

         PREPARE stmt FROM @SQL;
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;

     END LOOP;

     CLOSE backup_tables_cursor;
 END;

So this essentially creates all the backup tables you should need.

Next step let's get all the records. Unfortunately I'm unable to find that stored procedure at the moment but it works very much the same way, all my rows that I care about have a datetime column of creation date. So I traverse every table that's in my db_backup table and select all rows where the creation date is less than now(); I then extract all the row ids from my key tables, vhosts and media and store those ids in a cursor.

Next I go through all the other tables that might maintain that foreign key relationship and copy those rows over to the new database. It is fairly cpu intensive on a large db but it works and I execute at a low point in traffic over night.

Lastly, I have a variable that's passed to the trigger on whether or not to delete the rows I've copied over, if I do, then I destroy the rows in the various tables either as I go along.

Hopefully that helps, I wish I could find that stored procedure I wrote to copy over rows but for some reason it seems to be missing.

Code Snippets

CREATE DEFINER = 'mydbuser'@'%' PROCEDURE `sp_create_backup_tables`()
     NOT DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COMMENT ''
 BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE table_to_create, database_name VARCHAR(255);

     DECLARE backup_tables_cursor CURSOR FOR
      SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
  INNER JOIN INFORMATION_SCHEMA.TABLES
          ON INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA
         AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
       WHERE INFORMATION_SCHEMA.TABLES.TABLE_TYPE <> 'VIEW'
         AND (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'vhostid'
              OR
              INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid')
         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA <> 'db_archive'
         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'mydbname%';


     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

     CREATE DATABASE IF NOT EXISTS db_archive;

     SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME
       INTO database_name
       FROM INFORMATION_SCHEMA.SCHEMATA
      WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'mydbname%'
        AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'db_archive';

     OPEN backup_tables_cursor;

     create_tables_loop: LOOP
         FETCH backup_tables_cursor INTO table_to_create;
         IF done THEN
             LEAVE create_tables_loop;
         END IF;

         SET @backup_table_name = CONCAT('db_archive.', table_to_create, '_archive');
         SET @SQL = CONCAT('CREATE TABLE IF NOT EXISTS ', @backup_table_name, ' LIKE ', database_name, '.', table_to_create);

         PREPARE stmt FROM @SQL;
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;

     END LOOP;

     CLOSE backup_tables_cursor;
 END;

Context

StackExchange Database Administrators Q#15050, answer score: 3

Revisions (0)

No revisions yet.