0

We replicate our production MySQL databases (120 GB on production) to a dedicated local desktop machine. The local slave is a 2014-era machine with 8 GB of RAM.

A full mysqldump of the databases on the slave takes just under 48 hours. The resulting dumps, after zipping, are 5.4 GB. This process yields usable dumps, but the duration of the dump process prevents us from doing daily dumps of the database.

At most, it seems that we can only expect to achieve a full dump every other day with our current setup. Something needs to change in our approach in order to facilitate daily dumps again.

One possibility is simply adding another slave and having the two slaves alternate days of dumping.

A different approach (which I loathe, but could technically work) would be to do a full dump every, say, week, and then also keep all of the binary logs in case more fine-grained data is needed. (We could restore the weekly dump and then execute the binary logs up until a certain timestamp)

I suppose a totally different approach would be to use a RAID array and hot-swap out drives to effectively get a "snapshot" of the slave at the time the drive was pulled. (This seems like it may work but also feels quite risky and like a gross abuse of what RAID is supposed to do)

Another completely different approach would be to simply "prioritize" certain tables/databases that are dumped every day, while other "less important" tables/databases are dumped every week.

What am I missing? Is there a "canonical" solution for archiving daily snapshots of "large" MySQL slaves? This seems like something that would be encountered quite frequently by those maintaining larger databases.

Is the best option really to just continue throwing more hardware at the problem?

  • Maybe using XtraBackup instead of mysqldump could help? (It seems it may have better support for incremental backups) – rinogo Dec 28 '16 at 0:24
  • I take daily backups of that size and larger on some unimpressive machines, and 48 hours seems much too long, so here are some questions: Is mysqldump running in the same machine or elsewhere? What compression program are you using (gz, bzip2, xz)? Are you compressing while dumping, or later? – Michael - sqlbot Dec 28 '16 at 12:00
  • mysqldump is running on the same machine, compressing after dumping (gzip). One thing I have noticed is that mysqldump tends to "slow down". It starts (judging by the size of the dump file) very quickly, but as the dump proceeds, it seems to slow down... Perhaps it just seems to slow down, but if it does, would this be indicative of a problem? – rinogo Dec 28 '16 at 15:48
  • In all seriousness, try time mysqldump --verbose [options] > /dev/null to make -- and simultaneously discard -- a backup... in order to see how long just the backup portion of the task's workload requires. This will eliminate the disk I/O of storing the backup and the CPU time of compressing it, and should give some idea of a direction to proceed. – Michael - sqlbot Dec 28 '16 at 20:39
1

With 120GB, there are two ways to get a daily dump. (Or even hourly.)

With Replication, a Slave machine can be always tracking what is on the Master. That is, it is an up-to-the-minute dump of the Master, even without pushing the "dump now" button.

With LVM (available on some OSs), you can take a full disk "snapshot" 'instantly', then at your leisure copy the dump to somewhere else.

Perhaps the faster way to use mysqldump with multiple machines is to use

mysqldump ... | gzip | something-to-copy-to-other-machine

By zipping the file before hitting the network, you 'minimize' the bandwidth needed.

See also Percona's xtrabackup for other techniques -- especially full versus incremental backups.

  • Thanks for your recommendation about LVM - I'll look into it! I'm not sure how the gzip approach is relevant in our situation, though - we already have master-slave replication set up. We're doing dumps on the replicated slave. So, network traffic is a non-issue. Am I overlooking something? – rinogo Dec 28 '16 at 3:50
  • Take the dump from a slave. The point of the piping is to avoid writing to disk, which would add to the execution time. Since you have a Slave, I don't see why a nightly backup is so critical. – Rick James Dec 28 '16 at 5:31
  • Good point about a nightly backup being of questionable necessity. The motivation for the daily dumps is less for recovery from things like hardware failures and more for recovery from user-generated problems. (e.g. "I know you told me not to do X, but I did X, and now we need to undo it!") In cases like this, a slave is useless since it will similarly have the same user-generated problems. – rinogo Dec 28 '16 at 15:44
  • 1
    There is a way to say that a slave is to stay N hours behind. Look into this as a way to have a Slave ready to recover from such. Seems like there is a way to say "replicate until X", which lets you move it up to the "drop database". Then set the skip counter. (or something like that) – Rick James Dec 28 '16 at 18:44
  • I had no idea this option existed (Delayed Replication). Thanks! – rinogo Dec 28 '16 at 19:53
1

I suggest look at Percona Innobackupex - https://www.percona.com/software/mysql-database/percona-xtrabackup.

It also possible run from slave (best if replication format ROW).

One more variant fastest from any other than LVM:

  • stop slave
  • copy all datadir
  • start slave
  • continue zip and transfer

it is fasted way for restore, it could take same time as mysqldump for backup, but would be dramatically faster for restore.

You can combine methods. Disadvantages of percona backup or file level cold backup - You are restore all databases at once and will need operate all time with 120+Gb of data. If You will make periodical dumps by database - You can restore them separately on any server.

  • When doing a "file level cold backup", wouldn't you need to do a FLUSH TABLES, too? – rinogo Dec 28 '16 at 21:24
  • it real cold backup - it mean stop slave and stop mysql (on slave). It have it own disadvantages of course. – a_vlad Dec 28 '16 at 22:06
  • I tried and tried to get XtraBackup working, but it was very difficult to get installed on our production DB server. Version 2.4 is essentially incompatible with cPanel/WHM (which our server uses), but I got Version 2.0 to install. However, I kept on getting "Too many open files" errors even after increasing the limit (our DB has a lot of tables, but it should have been able to handle them all...). I would have continued experimenting, but XtraBackup was crashing mysqld. Not a good thing to be messing around with on production. Seems like an awesome tool; I just couldn't get it working. – rinogo Dec 29 '16 at 0:37
  • You need increase number of files for user which run Xtrabackup, in any case - You need start from test environment, than plan - how to install it on production. Because always better migrate for last version. – a_vlad Dec 29 '16 at 2:12

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.