Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
A few questions: How many InnoDB tables? How much data do you have? Do you have ON DELETE CASCADE defined on your foreign key relationships? –  RolandoMySQLDBA Mar 19 '12 at 16:54
    
@Rolando : There are 150+ tables and and all of them are InnoDB.they don't have on delete cascade. –  Abdul Manaf Mar 19 '12 at 17:00
add comment

4 Answers

I would suggest looking at the pt-archiver tool in Percona Toolkit.

share|improve this answer
    
does pt-archiver will have any impact on the replication status..?Can you throw some light on the working on the tool i have gone through the documentation but was not able to understand fully..will you please explain that..? –  Abdul Manaf Mar 16 '12 at 5:28
    
pt-archiver nibbles at your big table and slowly/relatively safely moves your old data to another table/server/file or just deletes the old data entirely. –  Jonathan Mar 19 '12 at 11:19
add comment

You could probably still use MySQL Replication.

You would designate one of the Slaves as an ArchiveSlave.

On the ArchiveSlave, you would need to run a special tool from the Percona Toolkit called pt-slave-delay.

Here is an example from the Documentation

To hold slavehost one minute behind its master for ten minutes:

pt-slave-delay --delay 1m --interval 15s --run-time 10m slavehost

In your case, you may want to hold the ArchiveSlave one day behind every day

pt-slave-delay --delay 24h --interval 15s --run-time 86340s slavehost

This will hold the slave 24 hours behind for 23 hours 59 minutes

Try running this in a crontab at midnight

share|improve this answer
    
that's ok...but i want to move the old data from all other slaves as well as master...to somewhere else(As Archive Db)On daily basis.But the point is that the Replication should not be affected.So How will i achieve it –  Abdul Manaf Mar 16 '12 at 4:11
    
Rolando, could partitioning the data based on the date be used in this situation? I've seen it done in SQL Server, but not yet in MySQL. –  Craig Efrein Mar 16 '12 at 7:53
    
@Rolando : I want only last one year data to be on the servers and all other to be moved on the archiving syste. –  Abdul Manaf Mar 19 '12 at 6:55
add comment

Here is a method that we have implemented for a similar requirement. This set up has an advantage of no down time and the archive db always being up to date with most recent data.

  1. Set up a master and at least two slaves.

  2. Designate one of your slaves as a failover for master and the other slave as archive db. You could have multiple slaves for failover and multiple slaves acting as archive db.

  3. Optionally set up large tables with partitioning on timestamp columns.

  4. Create a stored procedure or a SQL script that deletes data without replicating the changes to slaves. (This script could also handle creating and dropping partitions as necessary.) This could be achieved by turning off binary logging for that transaction, doing the delete, and then turning binary logging back on:

    SET sql_log_bin = 0;
    -- DELETE statement(s).
    SET sql_log_bin = 1;

  5. Execute the delete SQL script or stored procedure via cron job or MySQL Event Scheduler on the master.

  6. Repeat above step on each slave server that is not designated as an archive db.

Make sure to have appropriate timestamp ranges in application queries to prevent locks, so that data over 1 year old is not going to be queried at the time the clean up script kicks in.

In our situation we set up partitions by month. Every month we run a stored procedure on the master and non-archive slaves and simply drop an oldest partition and create a new partition for future month's data.

share|improve this answer
add comment

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.

share|improve this answer
    
I like thinking outside-the-box. Your answer is more like thinking outside-the-universe. +1 !!! –  RolandoMySQLDBA Mar 26 '12 at 0:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.