MySQL Backup Strategy

Question

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.

Answer 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

Answer 2

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