We are developing a web application (Spring mvc based app) that uses a MySQL DB. (let's call it the "original app")

The DB architecture uses a MASTER-SLAVE configuration with replication.
The web app uses the MASTER for the functionality.

We are now developing a Back-office application for our "original app". This back office is in itself a web app (spring mvc based).

In order to reduce the performance impact on the "original app" we would like to use the SLAVE replica as the DB for the back-office.

This replica DB will include unique tables (that will exists only on the SLAVE, to serve the back-office app).

Non of us ever worked in such architecture, and we are pretty intimidated by this, therefore we have some questions:

  1. Is this architecture even viable?
  2. In case of a replication-error , How hard will it be to recover? remember, that the SLAVE will consist of tables that are not in the MASTER
  3. Any other tips \ information we can use?
  4. We cannot reconstruct the slave-only tables from the replicated tables. No That is one of my biggest concerns. Is that a show stopper?
  • Not a show stopper, but you are asking for a non-standard setup (writing to slave; master != slave).
    – Rick James
    Commented Feb 25, 2015 at 21:32

1 Answer 1

  1. Yes

  2. It's not going to be a problem but of course it depends on the problem itself. Lets use the term - incident. Problem is already a problem by definition. Solve incidents, analyze the cause , prevent the problem!

  3. In case of an error, try to think what caused it. You are right about worrying, as it is a very common cause of issues.

Try to strictly separate the replicated objects from the unique ones in slave. Different DB Use some constraints and "tips" for the replication process, like:

  • --replicate-wild-ignore-table=db_name.tbl_name
  • include slave status verification in your backup procedures
  • redirect all reads to the slave (of course the important reads should be done in master and ONLY they)
  • backup the slave only
  • include the GTID (you might need it)

Be more AGILE with it. Do it, start testing and you will see how it goes. It's easy and you sound competent enough to do it.

Always monitor your replication. Using GTID and row-based replication may help you a bit with the consistency.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.