1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.3 Is it ok to backup db while the site is live?

Discussion in 'XenForo Questions and Support' started by Tim Jay, Sep 4, 2014.

  1. Tim Jay

    Tim Jay Active Member

    I'm wondering what the best course of action is when backing up the site. The files are not a problem, but I am curious about the database. I could normally back vbulletin database while it was live and the site would just be inaccessible meanwhile due to MyISAM table locks.

    Anyone know how this would work with xenforo InnoDB tables?

    I have a previous backup script that grabs and backs up every single mysql DB. It wasn't really a problem for vbulletin but I'm wondering if it's bad practice to do it while xenforo is live and open.
  2. Brogan

    Brogan XenForo Moderator Staff Member

    I never close the site when backing it up but it wouldn't hurt to do so.
  3. Mike

    Mike XenForo Developer Staff Member

    If you're using mysqldump, you can look at the --single-transaction option.
    Tim Jay likes this.
  4. Brogan

    Brogan XenForo Moderator Staff Member

    This is what I use.

    mysqldump -udatabase_user -p --single-transaction --skip-lock-tables database_name > /path/to/backup/database_name_$(date +%d.%m.%y).sql

    Then to zip it.

    tar -czf database_name_$(date +%d.%m.%y).tar.gz /path/to/backup/database_name_$(date +%d.%m.%y).sql
    Andrej and Tim Jay like this.
  5. Tim Jay

    Tim Jay Active Member

    The mysqldump worked great, but running the tar -czf command you posted is giving me this:
    tar -czf database_name_$(date +%d.%m.%y).tar.gz /home/database_name_$(date +%d.%m.%y).sql
    tar: Removing leading `/' from member names
  6. Brogan

    Brogan XenForo Moderator Staff Member

    You can ignore that.
    The dump should still be zipped.
  7. D.O.A.

    D.O.A. Well-Known Member

    But not the best with mixed tables? or are myisam all for reading http://serverfault.com/questions/41...ocked-during-mysqldump-when-mixed-with-myisam

    Using --single-transaction against an all-InnoDB MySQL Instance creates a checkpoint and dumps all tables from the same point-in-time. Once a MyISAM table is encountered, all bets are off. It could cause all InnoDB tables after the MyISAM to be dumped from a different point-in-time.

    To have a consistent point-in-time dump for a mixture of InnoDB and MyISAM you have there options

    OPTION #1
    Restart mysql so that no one else can login via TCP/IP and then mysqldump
    service mysql restart --skip-networking --skip-grant-tables
    mysqldump --routines --triggers --all-databases > MySQLData.sql
    service mysql restart
    OPTION #2
    If all MyISAM tables are for reading only, just mysqldump using --single-transaction

    OPTION #3
    If any MyISAM tables are are being written, --single-transaction is not enough
    Last edited: Sep 20, 2014

Share This Page