SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 06:34 AM
| ||||
| ||||
 MYSQL data replication I have limited experience with MYSQL replication; which is why I am hoping others with more experience can answer a question or two. Let's say I have a MASTER MYSQL database. Let's say there are 50 - 60 other systems where I'd like to have MYSQL running on these "slave" systems. These slave systems need to be kept in synch with the Master, but it does NOT need to be anywhere near real time. The data in the slaves could lag as much a an hour or two. The amount of data in total in the MASTER is in the range of 100MB - 250MB The rate of changes to the data is in the range 2000 - 5000 DML per 24 hour day. We control the application so we can/will include date/time each record is created or modified. You can assume that no records ever get physically deleted; only INSERT & UPDATE (no DELETE). What are some alternative ways to keep the slave systems "current"? TIA & HAND!      |
|
02-28-2008, 06:34 AM
| |||
| |||
| Re: MYSQL data replication Hello, Although you state that there is no requirement for near real time synchronization, an alternative might be to look into DRBD. Which if you are not familiar with, is block-level replication. See: http://www.mysql.com/products/enterprise/drbd.html Combining DRBD with Linux Heartbeat also gives you failover capabilities. Mohd posted a blog on his experience yesterday. See: http://blog.irwan.name/?p=118 Jimmy Guerrero Sr Product Manager MySQL, Inc Houston, TX sol beach wrote: > I have limited experience with MYSQL replication; which is why I am hoping > others with more experience can answer a question or two. > Let's say I have a MASTER MYSQL database. > Let's say there are 50 - 60 other systems where I'd like to have MYSQL > running on these "slave" systems. > These slave systems need to be kept in synch with the Master, but it does > NOT need to be anywhere near real time. > The data in the slaves could lag as much a an hour or two. > The amount of data in total in the MASTER is in the range of 100MB - 250MB > The rate of changes to the data is in the range 2000 - 5000 DML per 24 hour > day. > We control the application so we can/will include date/time each record is > created or modified. > You can assume that no records ever get physically deleted; only INSERT & > UPDATE (no DELETE). > > What are some alternative ways to keep the slave systems "current"? > > TIA & HAND! > |
|
02-28-2008, 06:34 AM
| |||
| |||
| Re: MYSQL data replication Hi sol, sol beach wrote: > I have limited experience with MYSQL replication; which is why I am hoping > others with more experience can answer a question or two. > Let's say I have a MASTER MYSQL database. > Let's say there are 50 - 60 other systems where I'd like to have MYSQL > running on these "slave" systems. > These slave systems need to be kept in synch with the Master, but it does > NOT need to be anywhere near real time. > The data in the slaves could lag as much a an hour or two. > The amount of data in total in the MASTER is in the range of 100MB - 250MB > The rate of changes to the data is in the range 2000 - 5000 DML per 24 hour > day. > We control the application so we can/will include date/time each record is > created or modified. > You can assume that no records ever get physically deleted; only INSERT & > UPDATE (no DELETE). > > What are some alternative ways to keep the slave systems "current"? The best, easiest, simplest way to do this is just to use MySQL's built-in replication. Once you learn its (many) strengths and (relatively few) weaknesses, it works extremely well. If you want to avoid some of the gotchas, I have written about my experiences here: http://www.xaprb.com/blog/2007/01/20...tion-reliable/ Hand-rolling replication is a bad idea in my opinion, so I don't have any alternative methods to suggest. However, if you just need to sync some data efficiently, try MySQL Table Sync (http://mysqltoolkit.sourceforge.net/). Cheers Baron |
|
02-28-2008, 06:34 AM
| |||
| |||
| Re: MYSQL data replication Hello, Hmmm, I just reread your post and noticed the requirement of 50-60 slaves hanging off a single master. DRBD would NOT be the right solution here. -- Jimmy Jimmy Guerrero wrote: > Hello, > > Although you state that there is no requirement for near real time > synchronization, an alternative might be to look into DRBD. Which if you > are not familiar with, is block-level replication. > > See: http://www.mysql.com/products/enterprise/drbd.html > > Combining DRBD with Linux Heartbeat also gives you failover capabilities. > > Mohd posted a blog on his experience yesterday. > > See: http://blog.irwan.name/?p=118 > > Jimmy Guerrero > Sr Product Manager > MySQL, Inc > Houston, TX > > sol beach wrote: >> I have limited experience with MYSQL replication; which is why I am >> hoping >> others with more experience can answer a question or two. >> Let's say I have a MASTER MYSQL database. >> Let's say there are 50 - 60 other systems where I'd like to have MYSQL >> running on these "slave" systems. >> These slave systems need to be kept in synch with the Master, but it does >> NOT need to be anywhere near real time. >> The data in the slaves could lag as much a an hour or two. >> The amount of data in total in the MASTER is in the range of 100MB - >> 250MB >> The rate of changes to the data is in the range 2000 - 5000 DML per 24 >> hour >> day. >> We control the application so we can/will include date/time each >> record is >> created or modified. >> You can assume that no records ever get physically deleted; only INSERT & >> UPDATE (no DELETE). >> >> What are some alternative ways to keep the slave systems "current"? >> >> TIA & HAND! >> > |
|
02-28-2008, 06:34 AM
| ||||
| ||||
| Re: MYSQL data replication I don't run any master with that many slaves, for reasons that are irrelevant to this thread, but I know there is a point at which the slaves begin to place too much load on the master because they are all asking to read the binlog. I don't know what that point is exactly, but perhaps others can give some advice on that. I would expect, given the workload you describe, that 50 slaves might work okay -- but you should not listen to me. However, you can do multi-tier replication. Let's imagine the limit is ten slaves, just for a round number. You can chain ten slaves off the master, configure them with log-slave-updates, and then chain ten more slaves off each of them. Now you can scale the system to 110 slaves with two tiers. Baron sol beach wrote: > Can 1 Master scale to replicate to 50 - 60 slaves? > I have limit experience with Master/Slave replication & doubt I can > round up > the hardware to test a 50 node configuration. > > > On 6/8/07, Baron Schwartz <baron@xaprb.com> wrote: >> >> Hi sol, >> >> sol beach wrote: >> > I have limited experience with MYSQL replication; which is why I am >> hoping >> > others with more experience can answer a question or two. >> > Let's say I have a MASTER MYSQL database. >> > Let's say there are 50 - 60 other systems where I'd like to have MYSQL >> > running on these "slave" systems. >> > These slave systems need to be kept in synch with the Master, but it >> does >> > NOT need to be anywhere near real time. >> > The data in the slaves could lag as much a an hour or two. >> > The amount of data in total in the MASTER is in the range of 100MB - >> 250MB >> > The rate of changes to the data is in the range 2000 - 5000 DML per 24 >> hour >> > day. >> > We control the application so we can/will include date/time each record >> is >> > created or modified. >> > You can assume that no records ever get physically deleted; only INSERT >> & >> > UPDATE (no DELETE). >> > >> > What are some alternative ways to keep the slave systems "current"? >> >> The best, easiest, simplest way to do this is just to use MySQL's >> built-in >> replication. >> Once you learn its (many) strengths and (relatively few) weaknesses, it >> works >> extremely well. >> >> If you want to avoid some of the gotchas, I have written about my >> experiences here: >> >> http://www.xaprb.com/blog/2007/01/20...tion-reliable/ >> >> >> Hand-rolling replication is a bad idea in my opinion, so I don't have any >> alternative >> methods to suggest. However, if you just need to sync some data >> efficiently, try MySQL >> Table Sync (http://mysqltoolkit.sourceforge.net/). >> >> Cheers >> Baron >> > |
Linear Mode
