List:Replication« Previous MessageNext Message »
From:Balogh Péter Date:February 23 2014 3:57pm
Subject:Re: Mysql replication setup
View as plain text  
Hi,

Thank you all for the quick reply and valuable insight!
I could try one of the file based methods, like percona or rsync, but 
I'm afraid, that the mysql version changes would end up causing some 
trouble...
Since my post, I've setup the replication with the script below, now I'm 
watching it for a few days, if it shows any issues
The only issue I found so far was that views got handled the same way as 
tables, and the order of execution could cause issues, but it can easily 
be fixed afterwards

Best regards,
Peter Balogh

On 2014.02.18. 15:07, Pankaj Joshi wrote:
> Hello Peter,
>
> why not use http://www.percona.com/doc/percona-xtrabackup/2.1/
>
> it provides you a way of taking online backup , its quick and safe in 
> terms of data consistency. as its online there will not be any impact 
> on the production. I have used it for my production environment and 
> for 550 GB it took close to 2 hours.
>
> the best things is as the backup is actually data files so you can 
> simply copy the backed up data files are new location say slave server 
> and set it up .
>
> Hope it helps .
>
> Thanks
> Pankaj
>
>
>
> On Tue, Feb 18, 2014 at 6:54 PM, Johan De Meersman <vegivamp@stripped 
> <mailto:vegivamp@stripped>> wrote:
>
>     Péter,
>
>     If there's sufficient storage on what is to be the master, you
>     might find it easier to use rsync to make a copy of the running
>     instance's datafiles, then "flush tables with read lock" and
>     /while that mysql session remains open/ update the copy with a
>     second rsync run and take the master position. After that, unlock
>     the tables again.
>
>     The second rsync run will take considerably less time because it
>     needs only copy the changed blocks. You can also flush the tables
>     before the first copy (but without the lock) to ensure a minimum
>     of changes needs to be copied by the second run. Doing the
>     procedure during a low-traffic period is also helpful, if you have
>     those.
>
>     Then simply stick the files under the (shut down, same version)
>     slave instance, and set up the slave to start from where the
>     master was during the locked rsync.
>
>     You could, of course, also rsync straight to the slave, but rsync
>     is considerably faster locally.
>
>     Hope that's useful to you,
>     Johan
>
>     ----- Original Message -----
>     > From: "Balogh Péter" <balogh.peter@stripped
>     <mailto:balogh.peter@stripped>>
>     > To: replication@stripped <mailto:replication@stripped>
>     > Sent: Monday, 17 February, 2014 6:36:45 PM
>     > Subject: Mysql replication setup
>     >
>     > 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
>     >
>     > --
>     > MySQL Replication Mailing List
>     > For list archives: http://lists.mysql.com/replication
>     > To unsubscribe: http://lists.mysql.com/replication
>     >
>     >
>
>     --
>     Unhappiness is discouraged and will be corrected with kitten pictures.
>
>     --
>     MySQL Replication Mailing List
>     For list archives: http://lists.mysql.com/replication
>     To unsubscribe: http://lists.mysql.com/replication
>
>


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