5

I manage a PHP/MySQL website :

  • max 500 simultaneous users / average of 4000 per hour in update/read
  • 1 MySQL Community Server (5.1) - (Xeon / 16 Go RAM)
  • InnoDB Storage engine on 100 tables for 1.4 Go of data

Actually, every 30mn a cronjob make a full dump in file... But since several, it's cause fatal errors on PHP (because of timeout)

So I would like to have a tool to do real Hot Backup... I googled and "stackoverflowed" and it seems that " products seems to do the job : - Percona XTraBackup (free) - Zmanda Recovery Manager Community (free) - Zmanda Recovery Manager Enterprise (500$)

But I can't see what are really the difference and what could be the criteria. My need seems to be simple : made full (or incremental) backup to restore my database if a crash occurs without putting my website down... I'm not a DBA so I need a "simple" product.

Many thanks for you help.

2 Answers 2

2

Disclaimer: I work for Percona and am one of the Percona XtraBackup developers.

You'd need to try the options and see what works best for you.

Given your needs I'd evaluate Percona XtraBackup first. It will perform the full and incremental hot backups just fine. In fact, Zmanda Recovery Manager uses Percona XtraBackup as its backend for the hot InnoDB backups.

1
  • Thank you very much for these informations. We will test Percona XtraBackup.
    – Grey Goda
    Feb 14, 2013 at 9:34
1

Just reading your question and without any additional details, I worry about you not having any form of High Availability or DB Clustering. At the very least, you should set up MySQL Replication using a commodity server as the Slave.

With 1.4GB of total data, you could do many different things:

  • From the Slave, perform a single transaction mysqldump crontab'd at midnight.
  • From the Slave, mysqldump individual tables as follows:
    • STOP SLAVE;
    • mysqldump each table into a separate file
    • START SLAVE;
  • From the Slave, use Percona XtraBackup (+1 for Laurynas since he mentioned it first)
  • From the Master, use Percona XtraBackup
  • From the Master, perform a single transaction mysqldump crontab'd at midnight.

I really can't see purchasing a commercial product when you can script some solutions or use Open Source solutions such as XtraBackup.

CAVEAT

XtraBackup would make more sense and should be very quick for 1.4GB of InnoDB data. However, when running it, make sure you don't get slammed by server load or any DB/Site performance degradation. Without additional info, the only way to know that XtraBackup is the solution is to set it up and run it on the Master. If you do experience DB/Site performance issues, go with setting up MySQL Replication and do either mysqldump or XtraBackup on the Slave.

Here are my past posts on setting up mysqldumps for individual tables:

1
  • Many thanks for your detailed explanations and links. We're going to test Percona XtraBackup on our server + single transaction mysqldump crontab in the night when our site is down. We will investigate the master/slave configuration too.
    – Grey Goda
    Feb 14, 2013 at 9:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.