You may want a master-master replication scheme with primary key offsets. This means DB 1 can do writes to any table and the auto id increment is something like 10, and it's offset is 1. So DB 1's primary key assignment sequence goes something like 1, 11, 21, 31, etc. DB 2 has an offset of 2 and keys get assigned as 2, 12, 22, 32, etc. Then the logs replay in a round-robin scheme like that so everyone gets the appropriate updates (you just can't write the same thing to two databases if you have a unique constraint).
You do have to be clever in how you heal errors. For instance, logs play through on DB1 get replayed on DB2 and then get pushed back to DB1, so you have to set DB1 to ignore updates from itself, and then if DB3 goes down, you have to point DB2 -> DB4 or everything up the chain doesn't get updates, and replay log files start reaching maximum capacity. The advantage to this strategy means you can point whatever consumer of the DB to any of the DB nodes for reading or writing and treat it like it is the entire database. The disadvantage is you can't shard anything out, but (no offense to MySQL, it's great for what it is) since you're using MySQL I'm guessing your DB is handling a lot of requests, as opposed to not fitting within a few terabytes. This is not the solution for you if you're much bigger than that. -Tod Hansmann Problem Solver www.phonejanitor.com 801-618-0059 On Thu, Feb 4, 2016 at 3:15 PM, Derek Caswell <[email protected]> wrote: > I am having an issue right now where I have multiple databases that have > the same structure. They have tables for storing a reservation such as res, > res_passengers, res_bags, etc. In certain circumstances these reservation > tables will be duplicated across the databases. I am creating a script that > synchronizes the data from one of the databases to the others. The problem > is that the primary ID's are not going to be the same so there isn't > currently a way in the structure to say that passenger 1 on the first > database is the same as passenger 3 on the second database. We are trying > to figure out how to change the structure so that we can tie those > together. We have thought of a couple options: > > 1. Create an associative table that ties them together but because the > number of passengers is unknown, the number of databases is unknown, and > there are about 15 tables we are going to have to synchronize, that seemed > to get really complicated really quickly. > 2. Another idea we had was to add a field to each table that acts as a > foreign key. For example, passenger 1 on database 1 would have a field that > contains 12 and passenger 3 on database 2 would also have 12 in that field. > So all passengers would have the same number in that field. The only issue > with that is we don't have a table that contains a primary key that can be > used as the foreign key. We don't really want to create a table that just > has a primary key. We are leaning towards this idea because it would make > our queries simpler to write and more efficient but we aren't sure how to > manage those foreign key ID's if we don't have a table to keep track of > them. > We are using MySQL and we are trying to take into account scalability and > query efficiency as well. > > I am writing to you guys to see what approach you would take on this. We > are open to any ideas you might have. > > Thanks for any help you might be able to give. > > Derek > > _______________________________________________ > > UPHPU mailing list > [email protected] > http://uphpu.org/mailman/listinfo/uphpu > IRC: #uphpu on irc.freenode.net > _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
