Mysql – Importing a large thesql dump while replication is running

master-slave-replicationMySQLmysql-5.1mysqldump

So we have a simple master/slave mysql replication running between two CentOS Servers.

The master has multiple databases. eg..

  • Database1
  • Database2
  • Database3

    • The issue is we have a mysql dumpfile of a new 60GB database (Database4).

    What's the best way to import Database4 without breaking replication?

    I was thinking we could stop replication, and import the mysqldump onto both master and slave. Then restart replication, but was hoping there was an alternate way that would minimize downtime.

    Best Answer

    I don't think there needs to be any downtime on the Master. See how this feels.

    1. Configure it so that you have Master-Master. That is, turn on the binlog and a few flags on the Slave so that it also acts like a Master; plus do CHANGE MASTER on the Master so that it is also a slave. (But no traffic will yet flow from the Slave to the Master.)

    2. Load the new database on the Slave. It will replicate to the Master (and clog up replication in that direction).

    I think that is all. You could turn off M-M and leave it just M-S, but I prefer keeping "M-M, but writing only to one M".

    (Caveat: I have not tried anything close to this.)

    Related Question