Clever Geek Handbook

How do I keep synchronized databases from scratch?

My question is very similar to this one . However, I am in MySQL and I am looking for the "lowest technical solution" I can find.

The situation is that I have 2 databases that need to have the same data, but they get updated in the first place when they can't communicate with each other. I suspect there is some sort of clustering or master / slave thing that will be able to keep them in sync. However, in my cases, this is a big overkill, since this is just a pivot table for my own use.

What's a good way to do this?

My current approach is to have a federated table on one of them, and each time so often wiring data to the other using insert / select. He's a bit confusing trying to figure out primary keys and what not. ( insert ignore

doesn't seem to work correctly)

ps I can easily create a query that fetches the rows to pass.

+1


source to share


2 answers


My current solution

  • set up a federated table in the source field that captures the table in the target field
  • set up a view in the original field that selects the rows to update (as a join in a federated table).
  • set up another federated table in the target field that captures the view in the source field
  • enter INSERT...SELECT...ON DUPLICATE UPDATE

    in the target field to run pull.


I am guessing that I could just grab the original table and do everything in one snapshot, but based on the query logs I have seen, I am guessing that I will have about 20k queries running or about 100-300MB of data transfer depending on how it happens. The above setup resulted in roughly 4 requests and a little more data than needed.

0


source


MySQL built-in replication is very easy to set up and works well even when the databases are offline most of the time. I would say that setting this up would be much easier than any conventional solution.

See http://www.howtoforge.com/mysql_database_replication for instructions , you should be up and running in 10-15 minutes and you shouldn't have to think about it again.



The only drawback I can see is that it is asynchronous - i.e. you must have one assigned master who receives all changes.

+5


source






More articles:


All Articles