I'm a systems admin, and I'm not a dba, I'm wondering if I could use MySQL stored procedure to Query main database and move records by month to another database located on the same system.

This way we can maintain database small main database, and have data moved to individual database by month.

Thank you

  • 2
    It's kind of hard to answer this question because deleting data by month is really a business decision. The implementation of such a Stored Procedure would depend on which tables have a timestamp column. Tables that do not have a timestamp column must be copied as is. – RolandoMySQLDBA Jul 11 '13 at 19:37

I would advice you to take a look at partitioning if you are running a recent version of MySQL and see if this could solve your problem.

Partitioning does not result in a smaller database but offers many of the same benefits with less administration once it is set up, and you will still have access to historic data.

| improve this answer | |

Rather than a stored procedure, I would suggest a script or batch file doing the trick. You can do the following.

Rename the current db as oldDB(Name goes with your choice).

RENAME DATABASE database_name TO old_database_name;

Then restore the structure from the recently renamed db to create original db

mysqldump -u [username] –p[password] –no-data [old_database_name] > | mysql -u [username] –p[password] -C [database_name]

Have a try.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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