The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Backing up a busy DB

Discussion in 'Database Discussions' started by sp3ctre69, May 14, 2013.

  1. sp3ctre69

    sp3ctre69 Well-Known Member

    Joined:
    Aug 14, 2006
    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    6
    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
     
  2. sp3ctre69

    sp3ctre69 Well-Known Member

    Joined:
    Aug 14, 2006
    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    6
    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?
     
  3. markhard

    markhard Well-Known Member

    Joined:
    Apr 22, 2004
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    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
     
  4. sp3ctre69

    sp3ctre69 Well-Known Member

    Joined:
    Aug 14, 2006
    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    6
    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.
     
Loading...

Share This Page