How long would it take to backup/restore a 170GB database

My customer has a 170GB MySQL database on a Linux server. The database is usually only used during the day, by maybe 30 logged in users per day, using a Lucee application.
The provider does a MySQLDump of the database every day which is better than nothing. But judging from my experience with the 10GB database of another customer, a restore of a 10GB MySQLDump database can already take hours. Restoring this customer’s 170GB database in an emergency situation would probably take days which is unacceptable.
So we are planning to set up a Percona backup/restore, hoping it will perform much faster but it would be good to know beforehand what we can expect. How long would a backup take? and more important: how long would a restore take?

1 Like

It depends. :smile:

Seriously, it depends on what type of disks you have (HDD/SDD/NVME/Flash/etc), how much CPU capacity you have, what the configuration of MySQL is, etc, etc.

You can benchmark the read capacity of your your disks to get a sense of how long it will take to backup, and then benchmark the write capacity to get a sense of restore.

Physical backups, like Percona Xtrabackup, will always be magnitudes faster than logical dumps.

mysqldump is the worst due to its single-threaded nature. You should look at GitHub - mydumper/mydumper: Official MyDumper project for far faster logical dump/restore, but Xtrabackup is still going to be the fastest.

2 Likes

Database restore of 200GB+ to Server which has 3.2TB NVME SSD from Ceph storage (spinning disks) within the same datacenter ~1 hour using xtrabackup or Percona Distribution for MySQL Operator.
Best way to find out is to test out.

2 Likes