List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:January 7 2009 3:08pm
Subject:Re: Setup a replication slave without stopping master
View as plain text  
Claudio,

> ehm....the problems is exactly that. On production server you cannot stop or
> lock the server so I need
> the replication slave mainly for backups (actually MySQL replication is
> simply great for this)

Just don't rely on the slave to BE the backup.  You can use it to make
it easier to take backups, but don't fall into the "slave==backup"
trap.  An accidental DROP TABLE can show you why slave!=backup.

> NOTE:
> I have tried Innodb Hotbackup Tool today but it was locking the production
> server!

Right, it does that :)

So it sounds like you have a mixture of InnoDB and MyISAM tables,
which is what I was trying to ask you in my previous message.  In this
case you have to do some dirty tricks.  It depends on your situation.
For example, you can get the InnoDB data out with
--single-transaction; you can get the MyISAM data with
flush-and-rsync, then repair and replace missing rows, etc.  There is
no "good" solution.  But with a little work, and some knowledge of
your workload, you can get most of the data out without too much
trouble, leave the troublesome ones for last, roll up your sleeves for
those, and then fix the differences with mk-table-checksum and/or
other approaches.  Have fun.

-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
Thread
Setup a replication slave without stopping masterClaudio Nanni6 Jan
  • Re: Setup a replication slave without stopping masterJake Maul6 Jan
    • Re: Setup a replication slave without stopping masterBaron Schwartz6 Jan
    • Re: Setup a replication slave without stopping masterClaudio Nanni6 Jan
      • Re: Setup a replication slave without stopping masterJake Maul7 Jan
      • Re: Setup a replication slave without stopping masterBaron Schwartz7 Jan
  • Re: Setup a replication slave without stopping masterJed Reynolds6 Jan
    • Re: Setup a replication slave without stopping masterPaul Choi7 Jan
    • Re: Setup a replication slave without stopping masterBaron Schwartz7 Jan
      • Re: Setup a replication slave without stopping masterJed Reynolds8 Jan
  • RE: Setup a replication slave without stopping masterTheodore Petkantchin7 Jan