List:Replication« Previous MessageNext Message »
From:shawn l.green Date:February 17 2014 6:55pm
Subject:Re: Mysql replication setup
View as plain text  
Hello Balogh,

On 2/17/2014 12:36 PM, Balogh Péter wrote:
> Hi,
>
> I'm building a mysql replication solution to one of our clients, and I
> would like to minimize the downtime during the setup
> We've dealt with replication before, but in this case I would like to
> avoid doing a full locked dump of the db if possible, since the database
> is quite large
> My idea is to start the slave sync with one table, and add all of the
> databases / tables one by one.
> This way, the only down time would be while 1 table gets dumped
>
> In theory, it should work, does anybody has any experience with a
> solution like this?
>
> I've put together a script to add a table to a running replication:
>
> I assume, that the replication is already working for one table, and the
> databases are created ahead
>
> I've removed the safety checks to make it shorter
>
> #!/bin/bash
> echo "Step 1. stop slave @ Server2"
> echo STOP SLAVE SQL_THREAD | mysql
>
> echo "Step 2. export table with locks in place with --master-data option
> @ Server1 and move dump to Server2"
> ssh -i sshkey user@Server1 mysqldump --lock-tables --single-transaction
> --flush-logs --hex-blob --master-data=2 $1 $2>/opt/$1.$2.sql
>
> binlogFile=`head -n 25 /opt/$1.$2.sql | grep "CHANGE MASTER" | cut -d
> "'" -f 2-2`
> binlogLine=`head -n 25 /opt/$1.$2.sql | grep "CHANGE MASTER" | cut -d
> "=" -f 3- | cut -d ";" -f -1`
> head -n 25 /opt/$1.$2.sql | grep "CHANGE MASTER"
> echo "Parsed file:"$binlogFile" and line:"$binlogLine"."
>
> echo "Step 3a. run slave until slave sync point @ S2"
> echo START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE = \'$binlogFile\',
> MASTER_LOG_POS = $binlogLine | mysql
>
> echo "Step 3b. load table dump"
> mysql $1 < /opt/$1.$2.sql
>
> echo "Step 3c. add Table1 to my.cnf"
> echo replicate-do-table=$1.$2 >> /etc/mysql/my.cnf
>
> echo "Step 4. wait until slave finished"
> while [ `echo SHOW SLAVE STATUS \\\\G | mysql | grep "Slave_SQL_Running:
> No" | wc -l` -ne 1 ];do
>          echo "."
>          sleep 1
> done
>
> echo "Step 5. restart mysql"
> /etc/init.d/mysql restart
>
> Thank you for your help,
> Best regards,
> Peter Balogh
>

The only thing I would add to this is to have your master operating with 
--binlog-format=ROW.  This way you are not relying on missing tables in 
your partially-migrated schema to be present in order to execute any SQL 
commands as you would get if you had --binlog-format=STATEMENT or MIXED. 
Using ROW also ensures that your replication filters (you may also 
consider using "replicate-wild-do-table") will be able to operate 
cleanly against the destination of the change, not the source of the 
change.

Yours,

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Mysql replication setupBalogh Péter17 Feb 2014
  • Re: Mysql replication setupshawn l.green17 Feb 2014
    • Re: Mysql replication setupJohan De Meersman18 Feb 2014
      • Re: Mysql replication setupshawn l.green19 Feb 2014
        • Re: Mysql replication setupJohan De Meersman19 Feb 2014
          • Re: Mysql replication setupshawn l.green19 Feb 2014
            • Re: Mysql replication setupJohan De Meersman19 Feb 2014
  • Re: Mysql replication setupJohan De Meersman18 Feb 2014
Re: Mysql replication setupJohan De Meersman19 Feb 2014
Re: Mysql replication setupBalogh Péter23 Feb 2014