runtime replication

Posted on

Question :

We have a master-master replication setup with 3 databases being replicated between both masters; ‘database a’, ‘database b’, ‘database c’. Replication is row-based.

‘database c’ had an issue and we created a copy of it named ‘database c-2’ on master 1. We would like to:

  1. replicate ‘database c-2’ to master 2.
  2. Once replication is confirmed, we remove ‘database c’
  3. rename ‘database c-2’ to ‘database c’

2 Questions:

  • Is it possible to achieve this without restarting the mysql service ?
  • Is it possible to rename a currently replicated database on the fly without breaking replication ?

Thanks in advance.


Answer :

Probably not as you describe.

On the Master with c-2:

  • USE c; — So that replication knows that the following should be replicated.
  • Foreach table t in database c-2:
    ** DROP TABLE c.t;
    ** CREATE TABLE c.t LIKE c-2.t;
    ** INSERT INTO c.t SELECT * FROM c-2.t;

Eventually DROP DATABASE c-2;

True, this does not give you the safety window. And this blocks usage of each table as it is rebuilt.

Leave a Reply

Your email address will not be published. Required fields are marked *