For a bit fall cleaning, I am moving 25 tables between MySQL databases (different pieces of hardware). This is not the WHOLE database, just 25 tables out of a few hundred... These tables don't really belong in there, I won't go into why for NDA reasons.
Now, this is going to break a lot of code and sql queries.
What is the best way to go about doing this?
Move them all over at once.
Move them over 1 by 1
--
Moving them over all at once, is kind of nice. Might be some outages and broken code that I missed, but moving them as a block is much faster, less time spent in pushing code out.
Moving them over one by one is kind of nice, less chance of big stuff breaking, but a LOT more time will be spent micromanaging the work, redundant work, and deploying.
Is it possible for me to mirror the tables between two databases for a while? A federated table perhaps?
--
Misc info: There are 25 tables are all related by content to each other.
I cannot shutdown the databases for hours at a time, about 5 minutes of downtime would be acceptable.
--
What is the best way to go about moving all of this data and keeping the code, sql, and me in great shape?
Could I federate the tables as a way of replicating the tables to a new database?
-daniel
It seems like moving them over one at a time would be the way to go. That way you have a bunch of easy little problems instead of a big hard problem. I hope you have your system under a lot of automated tests to make sure all this shuffling doesn't break anything.
Another thing: you talk about shutting down the database. If you're making the changes in a development environment and only making the changes live when you're sure everything works, why would you need to have any downtime in production? I hope you're not thinking about making these changes in production without doing it in development first.