Re: From production to archive ..
Posted by:
Rick James
Date: October 01, 2012 07:22PM
You have posed one problem, but you have another problem waiting to kill the system. I'll discuss solving both at the same time.
A system will die of old age long before 11 years.
Rumor has it that the Space Shuttle had to scrounge in junk yards for spare parts for the onboard computers.
Plan on migrating to new hardware and software NOW. And again in a few years.
What is the access pattern for this data? Do people usually access only the recent data?
Let's see SHOW CREATE TABLE.
Here's a rough plan; it may or or may not work for your situation.
1. Purchase a machine with at least 4TB of disk (SAN / RAID / whatever).
2. Install a recent OS and MySQL 5.5 on it. (5.6 is not quite GA).
3. Improve the schema -- Fix things that you have put off; PARTITION BY RANGE on date; shrink datasizes where practical; normalize more; whatever.
4. Write a script to gradually move data from the old server to the new. Read 5000 oldest rows from old box; adjust for new schema; write them to new box. And DELETE them from old box. (If you are paranoid, copy to somewhere else also.) This step MUST go fast enough to keep from filling up the old disk; else you will be hosed.
5. When you get close to "now", take some downtime to finish up the copy, move the ingestion to the new box, and move the clients to the new box. (All along, work on updating the client to the new schema, and preparing for this switchover.)
Even 5.5 will be EOL'd before your 11 years are over.
If you decide to stick with 5.1 (or 5.5 for the 11 years), check into SkySql and MontyProgram for support. currently they are among the few who will provide support for dead versions.
PARTITION BY RANGE is a suggestion to help with an "archive" technique in the future.
Beware of FOREIGN KEY CONSTRAINTs, they could be a big problem in the data migration.
If you already have Master-Slave replication setup, then there are other alternatives, such as cloning the Slave to make a bigger 5.5 slave, then failing over, then creating another big slave.
If you are not Master-Slave now, what is your BCP plan? That is, what will happen if your current disk dies and cannot be recovered? Strongly suggest you buy 2 new machines and switch to M-S during this migration.