SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb MySQL replication questions

    Hi,

    I've done some Googling on MySQL replication and so far have simple Master/Slave replication setup.

    What I would like to know is, when you add more slaves to the setup, should the master be locked until the slave is up and running and reading from the binary logs or can I unlock the master tables immediately after I take dump of its contents?

    If the tables are unlocked after I make a copy, but before I get my slave running and reading from the binary log, wouldn't the slave get out of sync if it misses some queries that happen during that timeframe?

    Thanks!

    Jared
    My links: [ Blog ] - [ deviantArt ]

  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)
    the binary log is a complete record of all changes since the last binary log flush. so provided you don't flush the binary log between the time you dump your data and load it on the slave, you're ok.

  3. #3
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, ok.

    The reason I was asking that was because after I unlocked the master, a few inserts occured on the master. When I dumped the snapshot into the slave (without the new inserts, because they happened after i created the snapshot) and started the slave, it didn't get any of the new inserts, but anything I did after I started the slave worked fine.
    My links: [ Blog ] - [ deviantArt ]

  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)
    ok, you need to start over with the moving of the data from one server to the other. you skipped a step or two:
    Quote Originally Posted by http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
    While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and offset on the master:

    mysql > SHOW MASTER STATUS;
    +---------------+----------+--------------+------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +---------------+----------+--------------+------------------+
    | mysql-bin.003 | 73 | test | manual,mysql |
    +---------------+----------+--------------+------------------+

    The File column shows the name of the log, while Position shows the offset. In this example, the binary log value is mysql-bin.003 and the offset is 73. Record the values. You need to use them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.

    After you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master:

    mysql> UNLOCK TABLES;

  5. #5
    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)
    and then after you have loaded the data on the slave, you have to:
    Execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

    mysql> CHANGE MASTER TO
    -> MASTER_HOST='master_host_name',
    -> MASTER_USER='replication_user_name',
    -> MASTER_PASSWORD='replication_password',
    -> MASTER_LOG_FILE='recorded_log_file_name',
    -> MASTER_LOG_POS=recorded_log_position;

  6. #6
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I locked the master, took a snapshot and recorded the info, etc, and unlocked it. That's when a few inserts occurred.

    Afterwards, I setup the slave exactly how you stated (I followed the MySQL guide) and got it running. It didn't update with the previous inserts, but when I inserted more, it updated those.

    So basically it was working fine, minus the few inserts that happened in between.
    My links: [ Blog ] - [ deviantArt ]

  7. #7
    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)
    did you START SLAVE before or after CHANGE MASTER command?

  8. #8
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    after.

    CHANGE MASTER won't work if the slave is running, it gives an error about doing STOP SLAVE first
    My links: [ Blog ] - [ deviantArt ]

  9. #9
    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)
    hm... weird. any chance you can try to repeat with exactly the same steps? if you get the same behavior, i would submit it as a bug. make sure to record the commands you use and the resulting output to include in the bug report.

  10. #10
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've setup another server, but this time I just used LOAD DATA FROM MASTER so something like that wouldn't happen.

    If I need to setup another server, I'll try to retrace my steps and post my findings.

    Thanks!
    My links: [ Blog ] - [ deviantArt ]

  11. #11
    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)
    ah, i love LOAD DATA FROM MASTER.

  12. #12
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's pretty handy, but puts a lock on the master. so if you're running a live site, i guess it's best to put the site into maintenance mode for about 10-15 mins while you add new slaves and make them LOAD DATA FROM MASTER. then when that's done, check to see if replication is working and turn the site live again.
    My links: [ Blog ] - [ deviantArt ]

  13. #13
    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)
    make the master inno and the slaves myisam. you get the speed of myisam on the slaves and adding a slave doesn't need a lock on the master.

  14. #14
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! I need to look into how to let MySQL setup InnoDB tables on my master so I can do that!

    So with InnoDB, when slaves run LOAD DATA FROM MASTER it won't lock and it's ok if a live site has writing going on?
    My links: [ Blog ] - [ deviantArt ]

  15. #15
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got my tables converted to InnoDB on the master! That was quick

    I have one question though. Now when I setup slaves, can I just LOAD DATA FROM MASTER instead of copying a sql dump? Or do I need to create the database and tables on the slave first? If it creates the tables, will it also create them as InnoDB?
    My links: [ Blog ] - [ deviantArt ]

  16. #16
    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)
    oh crap, i may have given you a bum steer. only the development version of mysql allows for LOAD DATA FROM MASTER from innodb tables, not the current production versions. so this is probably not an option for you.

    i'm setting up some replication here to see if i can duplicate your results. what exact version of mysql are you using?

  17. #17
    SitePoint Zealot Packetloss's Avatar
    Join Date
    Aug 2003
    Location
    Behind You
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql 5.0.15 i believe
    My links: [ Blog ] - [ deviantArt ]


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
  •