Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

closed as off-topic by RolandoMySQLDBA, Mark Storey-Smith, Max Vernon, Jon Seigel, bluefeet Nov 7 '13 at 14:26

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – RolandoMySQLDBA, Mark Storey-Smith, Max Vernon, Jon Seigel, bluefeet
If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers 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.

share|improve this answer
    
Thank you very much for these informations. We will test Percona XtraBackup. –  Grey Goda Feb 14 '13 at 9:34

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:

share|improve this answer
    
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 '13 at 9:37

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