0

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 ? – RolandoMySQLDBA Mar 11 '16 at 18:20
  • What good is a backup that is sitting on the same disk that might fail? – Rick James Mar 12 '16 at 4:19
  • @RolandoMySQLDBA, 1. Totally 46 databases. 2. Average 20 tables per database. 3. Multitenant - All applications are used by within our organization, by different departments and groups. 4. Yes we are compressing the database. I really liked your answers in this forum, I usually look for your answers in this forum. thank you. – rathishDBA Mar 12 '16 at 16:06
  • @RolandoMySQLDBA kindly check here dba.stackexchange.com/questions/132074/… – rathishDBA Mar 12 '16 at 16:58
1

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. – rathishDBA Mar 12 '16 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 '16 at 16:06
  • Kindly have a look at this question: dba.stackexchange.com/questions/132074/… – rathishDBA Mar 12 '16 at 16:57
  • @akuzminsky Would it be possible to set up XtraBackup to work under Cygwin? – Vérace Mar 12 '16 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 '16 at 20:15
-2

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, privacy policy and cookie policy

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