SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Logical Replication Database Design

    Hello.

    I'm setting up a replication system for my website and I've taken a look at what the MySQL replication service can offer.

    I found that the master MySQL server cannot filter out the to-be-replicated content on a per-table basis; however, the slave may be configured to replicate what it needs.

    BUT, the master CAN filter out what the database will replicate. So I figured that why not redesign the database architecture into two schemas which consist of data which will be replicated and data which will not.

    So Schema1 is: Data that will always be replicated.
    And Schema2 is: Data that doesn't need to replicated every minute of every day (but will be backed up on a daily basis with a simple mysql dump).

    Why do you ask I want to segment my data like this? Well my database does have some static data that never changes and there is also a lot of bulky data (messages, comments, forum posts) that don't really need to be backed up every minute. Also I really don't want the server to choke due to massive binlog writing.

    So what do you guys think of this solution? Is this common in database design?
    I can't believe I ate the whole thing

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that's an OK approach. but if you don't want those tables "backed up every minute", then when do you want them replicated?

    also, if you have some static tables that never change, there will be NO performance impact by keeping them included by the replication filter.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well the static tables were a bad example.

    The non-"backed up every minute" tables will simply be backed up say once or twice a day.

    I just don't wanna have the bulky data constantly being updated in the biglog.
    I can't believe I ate the whole thing

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    are these servers on the same network? if so, replication shouldn't be that big of a resource hog.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    are these servers on the same network? if so, replication shouldn't be that big of a resource hog.
    Yes, they're on the same rack.
    I can't believe I ate the whole thing

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    are you thinking that the whole binlog is being sent after each update? because that's not the case. it's only the new additions to the binlog that are being sent.

    do you currently have a capacity problem?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


Bookmarks

Posting Permissions

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