We are running the backup from the replication server and it is working good, since we are not facing any other issues, some information for better understanding our strategy:

Backup Type: Full Backup
Server : MySQL 5.5
Backup Tool: mysqldump
Interval: Every 24 hours.
Data size at present : 2 TB.

Actually we are in need to store the back up of 13 months but we have only 2 months. The data is growing around 6% in every month. We are running out of Storage.

Recently, we thought of moving into mysql incremental backup. The decided plan is we need to run full backup on every week interval and incremental backup at every 24 hours.

Our main concern is storage, since our data is growing every month, I need to know the answer for following questions.

  1. What is the best possible solution for backup and storing the data considering our situation?
  2. Whether the incremental backup strategy will solve our storage issue?
  3. Is there any other better way to handle this situation?

Any suggestions welcome, thank you in advance.

  • QUESTIONS : 1) How many databases ? 2) How many tables per database ? 3) Is this a multitenant database ? 4) Are you gzipping the mysqldumps ? Mar 11, 2016 at 18:20
  • What good is a backup that is sitting on the same disk that might fail?
    – Rick James
    Mar 12, 2016 at 4:19
  • @RolandoMySQLDBA kindly check here dba.stackexchange.com/questions/132074/… Mar 12, 2016 at 16:58

2 Answers 2


2 TB sounds too much for mysqldump. Did you try to restore a database from the dump? How much time does it take?

For large databases I would suggest XtraBackup. Not only it's faster, especially when it comes to restoring but it also supports incremental backups.

Weekly full copies sounds reasonable. For daily backups you have two options:

  • Incremental copy containing changes since last day.
  • Incremental copy containing changes since last full copy (sometimes this type is called differential backups). I prefer this one because of smaller restore time and higher chance to successfully restore the database.

If you need to store 13 month of data then you'd probably need to store 13 full copies. In that case storage requirements would be similar.

What you can do is to take full copy at month zero and then take a differential copy every moth. Then assuming 2TB initial size and 6% monthly growth the total storage size would be ~4TB.

Month   Database size, TB   Delta, TB
1   1.00    0.00
2   1.06    0.06
3   1.12    0.06
4   1.19    0.07
5   1.26    0.07
6   1.34    0.08
7   1.42    0.08
8   1.50    0.09
9   1.59    0.09
10  1.69    0.10
11  1.79    0.10
12  1.90    0.11
13  2.01    0.11
    Total delta, TB 1.01
  • Thank you so much, I just need to know, whether can we use xtrabackup on windows environment. Also I think incremental copy every month will be good choice for me. Mar 12, 2016 at 16:01
  • 1
    Unfortunately XtraBackup doesn't work on Windows (except some ancient version I don't recommend to use). Oracle's enterprise backup works on Windows and can do the same, but it's not free.
    – akuzminsky
    Mar 12, 2016 at 16:06
  • Kindly have a look at this question: dba.stackexchange.com/questions/132074/… Mar 12, 2016 at 16:57
  • @akuzminsky Would it be possible to set up XtraBackup to work under Cygwin?
    – Vérace
    Mar 12, 2016 at 17:09
  • @Vérace, I never tried, but my gut feeling it'd be easier to run MySQL in a Linux VM
    – akuzminsky
    Mar 12, 2016 at 20:15

Do you need daily backups? Why not keep the latest few days, and the latest few weeks, and only monthly before that?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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