2

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.

    Improve this question
    2
    • There is no reason for importing a dumpfile onto the master to break replication. Why do you think it will? I routinely import dumpfiles to production when launching new product sites, each of which has its own database.
      – Michael - sqlbot
      Commented May 23, 2017 at 2:39
    • Hi, It looks as if you tried to edit your own question. Please see how you can merge your accounts
      – Tom V
      Commented May 23, 2017 at 18:44

    2 Answers 2

    Reset to default
    0

    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.)

    Improve this answer
    0

    In my view your thoughts are in the right direction @user125340 - I don't see a point in loading a new 60GB database to master only to replicate and have huge binlogs & network traffic etc.

    You could do something along the lines of (Slave first):

    On the SLAVE:

    • mysql --init-command="SET SESSION sql_log_bin=0;" -uuser -p -BNe "CREATE DATABASE db_name"
    • mysql --init-command="SET SESSION sql_log_bin=0;" -uuser -p db_name < dump_file.sql

    On the MASTER:

    • Repeat steps above

    This way there will be no downtime, minimal impact on replication, no unnecessary binlogs/network traffic.

    Improve this answer

    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.