2

this question has kindof been answered in a number of ways. Nothing that quite fits the bill (as far as I understand it).

I have MySQL Table X on Server A. I would like an exact copy of this table, to be cloned on 3 other servers. Table X is quite big, and will get bigger. Currently 500,000 records.

Nothing gets deleted from that table, only added. So, don't really want to copy the whole big table across every time. Some kind of incremental solution, to pull across only the new record data on Table X, to Servers B, C and D. Is that possible?

I am running PHP on both servers. If it makes a difference - Server A is PHP4, and Servers B,C and D are PHP5.

If it were being copied over to only 1 other server - I could somehow mark records on Server A, as "already copied" - and only copy across records not flagged as such. I guess, I could add fields on Table X, to say "copied to Server B", "to Server C" etc. But, considering all this extra data will need to be copied to all other Servers too - it seems like a lot of dead data hanging around. Hopefully there is a better way.

I do not have root access or command line access. Some suggestions on Stackoverflow had solutions involving these.

Our old programmer, set up something across these servers before - where any change on Server A mysql, would immediately be mirrored on mysql on Server B. But, that was for the whole database, not 1 single table. And, he had access to command line etc. If there is a good solution there, I could speak to the server hosts and ask them to help me set it up?

Apologies if the question seems a bit dumb, I've never done this before (obviously). :) Any pointers or guidance you can offer will be appreciated.

Many thanks.

Improve this question
3
  • This is not something one should do at the programming language level.
    – Ignacio Vazquez-Abrams
    Commented May 17, 2013 at 8:19
  • Hi Ignacio thanks for your input. Any possibility, of suggesting what one SHOULD do?
    – BigTed
    Commented May 17, 2013 at 8:43
  • Master/slave replication.
    – Ignacio Vazquez-Abrams
    Commented May 17, 2013 at 8:45

1 Answer 1

Reset to default
1

It sounds like your old programmer had a master -> slave setup which is a really good way to back up your whole database; any command run on the master is then replicated over to the slave. This seems to fit what you need, not sure if you can specify it for just one table. However there is a viable argument for keeping your whole DB backed up.

Improve this answer
3
  • thanks for your input. Yes slave>master seems to ring a bell. We already have remote and hard backups of our MySQL. There's a huge amount of data on there, it would be a hard way to go, to copy the whole thing across, just to get that one table. Hopefully someone will post a suggestion about whether it is possible to do slave>master with a single table. Thanks again.
    – BigTed
    Commented May 17, 2013 at 8:46
  • well there is a solution to do it in PHP, depending how you have structured you PHP, if your database class/code has access to which table it is dealing with I.E. MVC pattern then you could do a string check on the table name and if it passes, copy the query to another DB connection(s).
    – MadDokMike
    Commented May 17, 2013 at 10:32
  • I know its a bit late but master->slave can be applied on one table too instead of whole database. check the FEDERATED table concept as explained on below link. dba.stackexchange.com/questions/20095/…
    – Ammy T
    Commented Dec 16, 2014 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.