Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 18 '13 at 11:52

This question came from our site for professional and enthusiast programmers.

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

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 May 17 '13 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 May 17 '13 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 Dec 16 at 10:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.