MySql Replication Scenario

Question

Hello,

I have a problem defining the best way to replicate and synchronize some information that I have in different mysql servers.

Let me explain:

Actual Scenario:

Server A
-Table A1 (read/write) Operations
-Table A2 (read/write) Operations

Server B
-Table B1 (write) Operations

Desired Scenario:

Server A
-Table A1 (read/write) Operations
-Table A2 (read/write) Operations
-Table replicated B1 (read) Operations

Server B
-Table B1 (write) Operations
-Table A1 replicated (read) Operations
-Table A2 replicated (read) Operations

How should I define my mysql replication configuration?

Should I define the two servers as Master?

Regards,
Pedro

Answer 1

You can do this kind of replication with MySQL. Please have a look at the following slave options

• replicate-do-db
• replicate-ignore-db
• replicate-do-table
• replicate-ignore-table
• replicate-ignore-table
• replicate-wild-do-table
• replicate-wild-ignore-table

and the corresponding annotations. With these options you can setup both servers to be mutual slaves and restrict the tables (databases) to be replicated. As MySQL is not able to do a multi-master replication you have to ensure that all writes are directed to the correct server and that nothing gets written into replicated tables.