I have written a little c program that will rotate tables and keep older tables for specified time. I use this program in a few environments where we have data retention requirements. This is to allow the tables not to grow absurdly large. This is my first C program please leave me feedback thank you.

USAGE:
Set up cron job to call the script once a week passing 3 variables
  1. Weeks to keep older tables
  2. Database Name
  3. Table Name
0 0 * * 0 /bin/lt_rotate_tables 3 testdb testing

// This code written by Lee Thompson with debugging by Dave Allmon//
// This program will rotate definded table and keep backup tables for n ammount of weeks //
// The inital table must exist first!! //
// to compile **** gcc lt_rotate_tables.c -o lt_rotate_tables  `mysql_config --cflags --libs` ***//
// Cron ***  *** //

#include <time.h>
#include <my_global.h>
#include <mysql.h>

#define ONE_WEEK (86400 * 7)

// This code will create a new backup and delete the backup that is n weeks old.
// Ex: This is week 21. Running this code with weeks=4 will create a table_21 table and delete a writes_17 table.

char tableNameNew[64];
char tableNameDrop[64];
char tmpTable[64];
char tmpDBname[64];

// Creates two suffixes based on the week of the year:
//  1. This week for new backup.
//  2. (weeks) ago to be deleted.

void CreateTargetDateStrs( int weeks )
{
  time_t thisTime;
  struct tm * lt;

  memset( tableNameNew, 0, sizeof ( tableNameNew ));
  memset( tableNameDrop, 0, sizeof ( tableNameDrop ));

  thisTime = time( NULL );
  lt = localtime( &thisTime );
  strftime(( char * ) & tableNameNew, sizeof ( tableNameNew ), "%B_%d", lt );

  thisTime -= ( ONE_WEEK * weeks );
  lt = localtime( &thisTime );
  strftime(( char * ) & tableNameDrop, sizeof ( tableNameDrop ), "%B_%d", lt );
}

int main(int argc, char **argv)
{
  char tmpStr[256];
  MYSQL *conn;

  if ( argc == 4 )
  {
    CreateTargetDateStrs( atoi( argv[1] ));
    sprintf(tmpTable,(argv[3]));
    sprintf(tmpDBname,(argv[2]));
  }
  else
  {
    printf( "\nUsage: ./lt_rotate_tables <weeks> <database name> <table name>\n" );
    exit( 1 );
  }
  conn = mysql_init(NULL);
  /////////////// Make only change here ////////////////////////
  //Set connection here change <user_name>  <password>
  mysql_real_connect(conn, "localhost", "<user_name>", "<password>", tmpDBname, 0, NULL, 0);

  // Drop oldest saved table.
  sprintf( tmpStr, "DROP TABLE %s_%s", tmpTable, tableNameDrop );
  mysql_query(conn, tmpStr );

  // Rename current table to new name.
  sprintf( tmpStr, "RENAME TABLE %s to %s_%s",tmpTable, tmpTable, tableNameNew );
  mysql_query(conn, tmpStr );

  // Create a new table like the last table.
  sprintf( tmpStr, "CREATE TABLE %s like %s_%s", tmpTable, tmpTable, tableNameNew );
  mysql_query(conn, tmpStr );

  mysql_close(conn);
}