Backing up a busy DB

[sp3ctre69]

Hi,

I have a very busy forum (phpBB) that I need to make sure is backed up properly. It was originally installed as MyISAM but I have had issues with the backups not being valid due to the file changing whilst it is in use.

My question is, if I switch to using InnoDB will the WHM backup routine take a hot copy or will I have to script that myself so I essentially have a backup within the site, that gets backed up (as a file) in the overnight routine?

I know taking a backup via the command line is a lot easier using innoDB, I just wondered if WHM was capable of doing similar?

If anyone could shed some light on this it'd be much appreciated, as I am a little nervous that my DB backups may be invalid if the data changes during the backup routine.

Thanks

 

[sp3ctre69]

Ok, looks like I had only half the info. I hadn't realised you needed a commercial licence to do hot InnoDB backups within MySQL.

Now it seems the way forward is xtrabackup. There seems limited info on here about installing it. Is it just a case of adding it to CentOS by apt or yum, or are there other factors to consider?

 

[markhard]

if you still using MyISAM, have you tried mysqlhotcopy script? more detail here: MySQL :: MySQL 5.0 Reference Manual :: 4.6.9 mysqlhotcopy

for InnoDB hot copy indeed you'll need commercial license.

Percona supposed to be drop in replacement for MySQL, so it should be a matter of uninstallin MySQL and installing Percona. but i advise you to make multiple backups (cpanel backup, database dump, /var/lib/mysql copy, etc.) just to be save before you do the migration. also make sure you use the same version number, for example if you use MySQL 5.1 you should migrate to Percona 5.1

 

[sp3ctre69]

Hi,

After doing a bit more reading it seems xtrabackup works ok but it a bit complicated to use. I have tried mysqldump with --single-transaction on innodb and that seems to work fine and ended up with a good backup.