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

MySQL Database Backup Ideas and Advice (Site Being Locked Whilst Backing Up)

Discussion in 'Server Configuration and Hosting' started by markku, Jun 13, 2015.

  1. markku

    markku Well-Known Member

    I use the following command to back up the XF database:
    mysqldump -hlocalhost -uxf -ppasswordcensored xfdb | gzip -9 > /home/backup/databases/xfdb.`/bin/date +\%Y\%m\%d`.sql.gz; chown backup:backup /home/backup/databases/*
    Whenever it is run, the forum is inaccessible (loading) for like 20-30 seconds.

    Why does it lock the site? Can that be avoided?

    Or is it a good thing it locks the site?

    Is there a way to backup the database regularly while maintaining consistent data but not having the site being inaccessible for a little while?

    Maybe setting up MySQL slave, replicating master to it and then backing up the slave?

    Thanks for any thoughts.
  2. Solidus

    Solidus Well-Known Member

    Use --single-transaction maybe.
    Mouth likes this.
  3. imthebest

    imthebest Formerly Super120

    That's the way I have been doing it for years and it works fine.

    You can get a cheap Kimsufi server for the slave DB: http://www.kimsufi.com/us/en/
  4. TDUBS

    TDUBS Active Member

    Not sure if you're looking for an add-on but you might want to check this one out https://xenforo.com/community/resources/solidmean-forumbackup.3605/. It's based on 2 cron jobs (one for entire forum backup and one for database backup) and of course you can set the time of backups and you can choose whether it puts your site on maintenance during backups or you can leave it on during backups. I personally use this for my forums and it works wonders. I've always been able to go back and restore the backups as needed without problems of it not fully backing up everything.
  5. RoldanLT

    RoldanLT Well-Known Member

    Yes for a small forum size, not for a large forum.
  6. rwm1962

    rwm1962 Active Member

    What would be the definition of small & large in this context?
  7. RoldanLT

    RoldanLT Well-Known Member

    1M post should be considered as large.
  8. duderuud

    duderuud Active Member

    Mr. Goodie2Shoes likes this.
  9. Moshe1010

    Moshe1010 Well-Known Member

    Just a note about Percona. It doesn't behave well with latest cPanel (latest Percona version broke my forum/I had many errors with it until I exchanged it for MariaDB).
  10. AndyB

    AndyB Well-Known Member

  11. thedude

    thedude Well-Known Member

    Same here. Slave db for zero downtime during backups.

    Whatever method you decide, make sure you do test restores (to a test db, not your live db) to ensure it's backing up as expected.
    Last edited: Jun 21, 2015
  12. duderuud

    duderuud Active Member

    You are referring to their MySQL fork, I'm only talking about their backup solution. (Just to clear things up)

Share This Page