•  

Announcement

Announcement Module
Collapse
No announcement yet.

New Approach for Backups

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • New Approach for Backups

    #1
    Hi,

    I'm currently in the process of designing a backup strategy for my environment. My env is currently a Master/Slave cluster running innodb.

    I have read about the following approaches:

    * Use a write lock and a FS which allows snapshots
    * Use MySQLdump
    * Use some commercial tool for innodb hot backup

    However, I have "found" an approach that has to
    my best knowledge not been discussed on mysqlperformanceblog.com.
    And here it is:

    Use a low-end machine as another slave, when a backup is due, stop the replication on exactly this machine.
    Flush tables with write lock and conduct the backup.
    Then restart the replication -- the slave will catch
    up, even if the backup takes hours.

    This has one main advantage:
    There is no need to write lock the master or
    slave which are in live operation.

    I have not yet tried it, but the below statement in
    the MySQL docs seem to support it:



    mysql> STOP SLAVE IO_THREAD;

    Stopping the SQL thread can be useful if you want to perform a backup or other task on a slave that only processes events from the master. The IO thread will continue to be read from the master, but not executed, which will make it easier for the slave to catch up when you start slave operations again.

    What do you think? Will it work?
    Tags: None

  • #2
    It could work, but only in rare cases when your database has not so write-intensive workload. Then your low-end backup server would be able to keep up with replication stream. But in really many cases when your slave is huge machine with pretty large innodb_buffer_pool, your low end server would not be able to keep up with replication w/o horrible lags (replicated statements are executed with one thread only, buffer pool is small, cpu is slower).

    So, you can try this approach, but it would be useful in too small subset of cases. Especially weird here is that on your low end machine when you'll perform backups your slave lag would grow and then it could take too much time to catch up with replication.

    So, I'd stick with LVM-snapshot based backups made from some powerful slave.

    Comment


    • #3
      Hi,

      ok, I think in my case, my approach would work
      because I have 90% read access.

      However, all the time I read about the "LVM method"
      I ask myself, what happens to inserts/updates durinng
      the write lock? Do they throw an exception or are they just
      delayed?

      thanks

      Comment


      • #4
        RHAT wrote on Sun, 18 November 2007 10:43

        Hi,
        However, all the time I read about the "LVM method"
        I ask myself, what happens to inserts/updates durinng
        the write lock? Do they throw an exception or are they just
        delayed?
        thanks


        When you do LVM backups, DB is locked (write queries are delayed) only for a second or even fraction of second while you're creating your snapshot. Then, when snapshot is created, your db is available for writes.

        Comment


        • #5
          You can even stop Mysql on the slave completly and do a binary backup.

          Comment

          Working...
          X