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

markku

Well-known member
I use the following command to back up the XF database:
Code:
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.
 
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.
 
Maybe setting up MySQL slave, replicating master to it and then backing up the slave?

Thanks for any thoughts.

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/
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:
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).

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