1

I have a very specific problem that requires multiple MYSQL DB instances, and I need to "sync" all data from each DB/table into one DB/table.

Basically, [tableA.db1, tableB.db2, tableC.db3] into [TableAll.db4].

Some of the DB instances are on the same machine, and some are on a separate machine. About 80,000 rows are added to a table per day, and there are 3 tables(DB). So, about 240,000 would be "synced" to a single table per day.

I've just been using Event Schedule to copy the data from each DB into the "All-For-One" DB every hour. However, I've been wondering lately if that's the best solution. I considered using Trigger, but I've been told it puts heavy burden on DB. Using statement trigger may be better, but it depends too much on how the statement is formed. Then I heard about Federated (in Oracle term, "DBLink"), and I thought I could use it to link each table and create a VIEW table on those tables. But I don't know much about databases, so I don't really know the implication of each method.

So, my question is.. Considering the "All-For-One" DB only needs to be Read-Only, which method would be better, performance and resource wise, in order to copy data from multiple databases into one database regularly?

Thanks!

Harplife
  • 37
  • 7
  • Think about the replication. Each server which hosts some source database (hosts 1, 2 and 3) acts as master and the server which hosts combined database acts as slave to each of master. So each source database is replicated to its copy on slave "on the fly". Then these 3 copies are combined into database 4 within one slave server using any local technique. – Akina Dec 28 '20 at 06:50

0 Answers0