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

[markku]

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.

 

[m1ne]

Use --single-transaction maybe.

 

[imthebest]

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

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/

 

[TDUBS]

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.

 

[rdn]

it works wonders

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

 

[rwm1962]

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

 

[rdn]

1M post should be considered as large.

 

[duderuud]

If you got shell access, take a look at Percona: https://www.percona.com/software/percona-xtrabackup

We use it for our forum (25m posts)

 

[Moshe1010]

If you got shell access, take a look at Percona: https://www.percona.com/software/percona-xtrabackup

We use it for our forum (25m posts)

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

 

[AndyB]

I think for best results you should close your forum for a few minutes during the backup process. This add-on closes and opens the forum using a XenForo Cron:

https://xenforo.com/community/resources/board-active-cron.3997/

 

[thedude]

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.

 

[duderuud]

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)