Re: running a duplicate database
> 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
Re: running a duplicate database
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 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 signature.asc Description: OpenPGP digital signature
running a duplicate database
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
