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 only takes a minute to sign up.
Sign up to join this community
I have a master mysql server (M1) with two replicating slaves behind it. I also have another master mysql server (M2) with it's two slave servers. The slave servers currently replicate everything from it's respective master.
I would need to change one master server (M1) so that it replicates some tables from M2. The tables it (M1) will replicate will be set as read-only (user privilege will be changed).
This is a production set up. I would like to know whether it is possible? If so, how?
Yes it should be possible.
You can configure M1 the same way you configured the slaves of M2, but additonally add the option replicate-do-db and replicate-do-table to only have the tables you need be replicated into M1.
Please note there are a lot of "gotchas" with these two options, so it is best to read the linked help pages carefully.
In Master server you should use these two options replicate-do-db and replicate-do-table the later one is more feasible as it replicates cross database and default database statements only problem is you have to specify multiple tables in the list and this list can be huge.
Cross database statement example:
UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database in a Master server
Default database statement example:
If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
