1

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.

|improve this question

migrated from stackoverflow.com Oct 12 '18 at 12:12

This question came from our site for professional and enthusiast programmers.

  • 1
    If you've 1000 customers, surely you can afford more than 1 machine? Please always include our edition of MySQL (5.x?, 8.?). – Vérace Oct 12 '18 at 12:13
  • 3
    Anda proper server. You may be surprised, but 500gb is something most peope here will consider SMALL. Ever seen a 20tb database? You basically have way too little IO - start expanding that one server. – TomTom Oct 12 '18 at 17:40
2

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.

|improve this answer
  • What about Percona's XtraBackup - no locking? – Vérace Oct 12 '18 at 12:17
  • @Vérace I'm not overly familiar with it, but it appears to do more or less the same as I've described above, but with hackery instead of an actual replicated MySQL slave. The beauty of the latter is that you can swap over to your slave at a moment's notice, with no further "restore" steps required if something happens to your master. I'm no DBA but my understanding is that this is how MySQL is meant to be used. – Lightness Races in Orbit Oct 12 '18 at 12:19
  • I didn't mean use XtraBackup for the live 500 GB backup - just from the slaves. The slaves idea is exactly what I was on about in my comment about purchasing new machines! Plus, if the OP has 1000 schemas, he should be able to back them up 1 by 1 in jig time - only 500MB each (on average!). – Vérace Oct 12 '18 at 12:26
  • @Vérace Ah right. Well that would work I guess. Not sure it's necessary but hey. – Lightness Races in Orbit Oct 12 '18 at 12:36
1

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.

|improve this answer
0

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

|improve this answer

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.