MySQL - replicate some tables between transaction and report database

Question

I have two different database (on two different hosts). One database is transactional and another database is reporting. Let us say, it stores information about departments and their employees.

Transactional Database has one department table which stores employee data of all departments. This table has foreign and primary keys. This table has no partitions. Disk size is above 250 gb.

Reporting Database has one department table which is partitioned by department id. This table has no primary or foreign keys. Otherwise, structurally this table is same as transactional one.

Is there any way to replicate the department table between transactional and reporting.

Answer 1

You can replicate Transaction to Reporting without any issues are it's "independent" but Reporting to Transactional may cause issues depending on your foreign-key settings.

You can easily replicate single table with the help of replication filters (in your case replicate-do-table or replicate-wild-do-table).

There are plenty of blogs for replication setup instructions, say this one from Percona.

Answer 2

Your best bet to get some well researched guidance on this topic is to look up "Extract, Transform, and Load" processing, abbreviated ETL.

ETL processing has been extensively studied, especially as it relates to data warehouses and data marts. Your reporting database is a little like a data mart. There are tools for this that you can buy and use off the shelf. But even more valuable, IMO, is the guidance you can get if you choose to roll your own ETL tool.

Here's a starter: https://en.wikipedia.org/wiki/Extract,_transform,_load