• 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?

Tim Jay

Active member
#1
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.
 

Brogan

XenForo moderator
Staff member
#4
This is what I use.

Code:
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.

Code:
tar -czf database_name_$(date +%d.%m.%y).tar.gz /path/to/backup/database_name_$(date +%d.%m.%y).sql
 

Tim Jay

Active member
#5
This is what I use.

Code:
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.

Code:
tar -czf database_name_$(date +%d.%m.%y).tar.gz /path/to/backup/database_name_$(date +%d.%m.%y).sql
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
 

D.O.A.

Well-known member
#7
If you're using mysqldump, you can look at the --single-transaction option.
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
Code:
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: