Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Unanswered: 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; some *nix variant
    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 the Master by as much as 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!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  2. #2
    Join Date
    Jun 2007
    Posts
    9

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am currently running V5 MYSQL in a single Master/Slave replication mode.
    I was wondering if can it scale to where 1 MASTER feeds 60+ slaves?
    I doubt if I can get the necessary hardware to actually benchmark 60 slaves.

    If MYSQL replication can't scale to this size, what are my other alternatives?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2007
    Posts
    9
    You can write script. It will be run every 5-10 minutes. And synchronize db.

    Algoritm do not hard:
    1.Select last record from slave db.
    2.Select all records newer than last.
    3.Insert into remotely db.

    Also you must copy updated record.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Thank you.
    Excellent suggestion.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I would recommend NOT using scripting to do what you need.
    You can set up Master -> 60 slaves replication which will work in the same guise as writing a script to find the last set of records.

    If you decide to create a script bear in mind that this script has to know what all the databases/tables are and should they change you will have to alter ALL 60 scripts to get the new correct data.

    MySQL replication is your best bet. I would question why you are trying to replicate 1 -> 60 however. Especially if these slaves are not being used for reads...

    Your other option is to replicate 1 Master -> 1 Slave and then have that slave take a backup (mysqldump) each hour and distribute that to the other 59 other servers over tcp/ip where they can dump the information back in. Not a pleasant solution however.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I would question why you are trying to replicate 1 -> 60 however. Especially if these slaves are not being used for reads...

    I work for an Application Software Provider.
    We scale our application horizontally across multiple *nix systems.
    One or more customers utilize a specific machine in any layer of the application.
    Currently the layer being discussed contains 56 servers; which all perform the same function but each has their own set of customers.
    At the present we are pushing around/across 150MB flatfiles & are considering replacing the flatfile with MYSQL DB.
    The idea being that rather than moving 150MB (most of which has not changed); we only "move" the changed data.
    This can not be done with a flatfile, but can be done within MYSQL.
    The data (whether flatfile or MYSQL) is being read & utilized by our application software running in each "slave" system.
    Our application reads the data & caches it & processes until next reload.

    TIA & HTH
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •