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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm running a M/S replication of MySQL 5.5 / innoDB (over ubuntu).

My DB is pretty large: ~250GB.

I'm looking for a backup tool/service that will do the following:

  1. full backup
  2. no downtime / locks on the servers
  3. automated as possible

any suggestions?

share|improve this question

closed as off-topic by Paul White, Phil, dezso, bluefeet, Aaron Bertrand Jan 26 '15 at 14:42

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." – Paul White, Phil, dezso, bluefeet, Aaron Bertrand
If this question can be reworded to fit the rules in the help center, please edit the question.

I may make this an answer later, but Percona's xtrabackup fits all your requirements – Aaron Brown Jul 10 '12 at 11:54

One of the options is to have an additional slave MySQL server specifically for the backup purpose which can be stopped/locked without affecting the production DB.

share|improve this answer
The best part about slaves, is that you can change the primary engine (if you choose) and you can do intensive backups or reads off of them, without affecting production data. We used to use a slave cluster to get copies of production data in MyISAM so we could do JDBC reports against a non-production, read-optimized database. We also used a slave to create daily dump files, for movement to development machines, etc. – MaddHacker Jul 10 '12 at 11:46
Yep! Plus it is possible to perform "cold" backups instead of using mysqldump: just stop the slave and copy whatever it is in /var/lib/mysql. For a big database such backup can be restored much quicker. – Aleh Jul 10 '12 at 14:43

Just using mysqldump, you can perform some interesting backup schemes.

I wrote a post back in April 17, 2011 on automation scripts for parallel mysqldumps : please see How can I optimize a mysqldump of a large database?

I also mentioned alternatives in that post, including XtraBackup.

In your 250G database, if one table is 200G my solution may be inadequate.

Please run this query to find out your 5 biggest tables

SELECT table_schema,table_name,data_length,index_length
FROM information_schema.tables ORDER BY data_length DESC LIMIT 5;

Based on your pastebin, here are the 5 biggest tables

mysql> select num,format(num/power(1024,3),2) num_gb
    -> from (
    ->     select 18455986176 num union
    ->     select 15031681024 union
    ->     select 14825160704 union
    ->     select 10196353024 union
    ->     select 9934143488
    -> ) A;
| num         | num_gb |
| 18455986176 | 17.19  |
| 15031681024 | 14.00  |
| 14825160704 | 13.81  |
| 10196353024 | 9.50   |
|  9934143488 | 9.25   |
5 rows in set (0.02 sec)


You could use mysqldump set in parallel on the Slave only : please see How can I optimize a mysqldump of a large database?

The basic paradigm is to perform this sequence on the Slave:

  • For every group of 10 DB
    • parallel dump all 10 DBs in a group in separate files
share|improve this answer
here is the output of the query: – Ran Jul 10 '12 at 13:58

Another option is to upload the database to Amazon RDS, which is a hosted database service. There is an option to get large disk storage on an RDS instance:

For each DB Instance class, Amazon RDS provides you the ability to select from 5 GB to 1 TB of associated storage capacity for your primary data set.

So you could put your 250GB on the hard disk and have only the crucial stuff run in-memory. Amazon would then take care of backups, high availability and failover for you. Could be costly but it will save you the work of automating mysqldump, etc.

share|improve this answer
I would not rely on Amazon. During their most recent AWS outage, people actually lost data. Horrible. – randy melder Jul 17 '12 at 15:37

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