Very large MySQL backup

Question

At our company we have a very large MySQL server with almost 1.000 databases with a total size of 500 GB. Every customer has is own database. And each database has about 120-125 tables!

In the past we made backups with mysqldump. The server is very slow while creating backups and it almost takes 5 hours to take backups. The databases have for 99% Innodb tables.

I understand taking backups of a lot of data takes a lot of time. But what is the best solution to take backups? The problem is that our customers are working 24/7 with our webapplications. The system is very slow while taking backups.

Answer 1

The conventional approach is to set up multiple replicated slaves.

You then backup from these slaves periodically (mysqldump is probably fine for this part still).

The master need not be blocked while the slaves are being backed-up; it will simply continue to replicate once those backups are done. Stagger the backups so you always have an up-to-date slave.

Replication is not CPU-expensive (unless you have a crazy level of database activity), and the slaves' backup process won't impact the master at all.

And everything should have redundant storage.

---

Anecdote: At a previous job we had a 600GB database that was backed up in the same way that you're doing it, but over a 10Mbit pipe to the target, and it would block the entire database for 11 hours every night! Eesh. Never did get replication in place there (lol) but upgrading the networking to 100Mbit meant it could be done in 2 hours or so instead, which was "fine". Of course, with the database locked during the backup (which it should be!), and thus the actual service unavailable for that duration, a performance hit from the backup process was not even observable from the outside. In your case you have multiple distinct databases so the situation isn't quite the same — you don't have to lock everything for the whole time.

Answer 2

At this point it's probably time to setup a slave and take backup from it. if you do so make sure the master and slave are fully in-sync meaning the data on the master and slave is same. That would be a job for pt-table-checksum.

If you take backups from the slave performance problems on the master are solved. You have to make sure the slave server is big enough so the slave doesn't fall behind.

Now, the tool. It sounds like you're familiar with mysqldump. It'll work, but 500G sounds too much for it. I suggest you measure restore time. You may be surprised how slow restore from mysqldump copy. If the restore time is acceptable for you - your problem is solved. If not - you next best bet is Xtradbackup. It copies MySQL files opposite to mysqldump that takes logical backup. With Xtrabackup restore time will be much lower.

As an author I would also recommend TwinDB Backup. It uses XtraBackup, but on top of that solves problems of retention policy, backup to S3, scheduling etc.

Answer 3

Yes to Slave(s). But, then, you can argue that using mysqldump is unnecessary since you have a copy of sitting on the Slave.

Another approach -- Cycle through the users. Dump one customer at a time to its own backup file. It will take more than 5 hours, but so what. The goal is to backup everyone once a week? Then stretch out the 1000 dumps across the whole week. This way, the impact on the system won't be continual, but rather sporadic. To get smarter, determine the 'lightest' time for each customer and schedule his backup then.

There are better dump programs than mysqldump:

• Percona's XtraBackup.
• LVM - snapshot of the disk. Virtually zero downtime, no impact on MySQL; mostly I/O and network time to copy the snapshot to some other place. Possibly less than 5 hours.

More ideas/details: https://dba.stackexchange.com/search?tab=newest&q=lvm%20xtrabackup%20user%3a1876