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'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
1  
I may make this an answer later, but Percona's xtrabackup fits all your requirements –  Aaron Brown Jul 10 '12 at 11:54

3 Answers 3

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
1  
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)

mysql>

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:

  • STOP SLAVE;
  • For every group of 10 DB
    • parallel dump all 10 DBs in a group in separate files
  • START SLAVE;
share|improve this answer
    
here is the output of the query: pastebin.com/nE1LyDv4 –  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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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