> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org