MySQL backup with no downtime

Question

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?

Answer 1

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.

Answer 2

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;

Answer 3

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.