website inaccessible during large MySQL dump

Discussion in 'MySQL' started by christianweb, Jul 12, 2012.

0
  1. #1
    MySQL backups using mysqldump are about 1.5 GB and gradually getting larger.

    The last dump made the website inaccessible for ten minutes while the mysqldump was in progress. I assume the mysql server was totally busy with the mysqldump? Any suggestions? It's a windows server by the way.
     
    christianweb, Jul 12, 2012 IP
  2. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #2
    Hi, try running mysqldump with --skip-lock-tables option if your tables are MyISAM. For InnoDB you can try --single-transaction
     
    koko5, Jul 12, 2012 IP
  3. christianweb

    christianweb Active Member

    Messages:
    101
    Likes Received:
    18
    Best Answers:
    0
    Trophy Points:
    73
    #3
    Thanks koko5, I will look into that.
     
    christianweb, Jul 12, 2012 IP
  4. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,740
    Likes Received:
    28
    Best Answers:
    13
    Trophy Points:
    175
    #4
    hi, if the option above isn't available, you also could setup a cronjob that will run on times that you sites has less visitors?
     
    EricBruggema, Jul 13, 2012 IP
  5. WordPressBlogNetwork

    WordPressBlogNetwork Member

    Messages:
    29
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    46
    #5
    Convert to InnoDB
    When backup use this command

    mysqldump --single-transaction yourbd > yourdb.sql
     
    WordPressBlogNetwork, Jul 19, 2012 IP