List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:September 9 2011 10:02am
Subject:Re: running a duplicate database
View as plain text  
> Am 09.09.2011 11:09, schrieb Dave Dyer:
> > Is there a halfway house between a single database and a full
> > master-slave setup?
> > 
> > I have a database with one "piggish" table, and I'd like to direct
> > queries that search the pig to a duplicate database, where it won't
> > affect all the routine traffic.
> > 
> > I could definitely do this by setting up a slave server, but for my
> > purposes it would be just as effective, and lots easier, if mysql could
> > automatically maintain a duplicate of the database.  Presumably this
> > would work internally like an internal auto-slave, with a binary log of
> > changes to the master database self-consumed to maintain the duplicate.
> > 
> > As a bonus, I could backup the duplicate instead of the master, so that
> > won't affect the routine traffic either.
> 
> you can run as many slaves on the same machine as you want by
> using a different port for all instances and stop/backup/start
> one of them per script - doing this since years

No problem indeed.

> it makes no sense "maintain a duplicate of the database"for backups
> becasue mysqld have to be stooped for effective rsync-backups which
> are much faster as dumps and here are we again at the point using
> a slave on a different port

I concur that dumps are not an effective way of backup, they take ages when 
any decent size database.  We have had great experiences with Percona's 
Xtrabackup (http://www.percona.com/docs/wiki/percona-xtrabackup:start) for 
hotcopies, which also work with InnoDB. But on a heavily used db-server, it 
DOES make sense to run the backup on a (unused) slave, there's still some 
overhead & locking involved, and if your DB is running hot 24/7 you don't want 
that one to do anything that can be done somewhere else.
-- 
Rik Wasmus
Thread
running a duplicate databaseDave Dyer9 Sep
Re: running a duplicate databaseReindl Harald9 Sep
  • Re: running a duplicate databaseRik Wasmus9 Sep