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

[Tim Jay]

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.

 

[Paul B]

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

 

[Mike]

If you're using mysqldump, you can look at the --single-transaction option.

 

[Paul B]

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

 

[Tim Jay]

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

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

 

[Paul B]

You can ignore that.
The dump should still be zipped.

 

[D.O.A.]

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

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