Fast MySQL Backup and Restore Using Mydumper and Myloader

https://wplobster.com/extremely-fast-mysql-backup-restore-using-mydumpermyloader/

by lyri787w • 9 years ago

66 23 9 years ago

boznz2• 9 years ago

Nice for my Linux (and possibly BSD) customers but some of my customers use MySQL on Windows servers, it would be nice to give them some love every now and then :-)

vlod• 9 years ago

Would bash-on-windows thingy work for you? (Assuming it works with it)

throwanem• 9 years ago

Should build it with the MySQL client library installed. So should Cygwin, if your Windows doesn't support the Linux subsystem.

cookiecaper2• 9 years ago

This is a logical backup tool. It doesn't have to run on the same server as the DB.

boznz2• 9 years ago

Thanks for the informative replies I'll give it a try

wanderr• 9 years ago

How does this compare to the percona tools?

barrkel• 9 years ago

Percona talk about these tools on their website / blogs. Basically, this thing is for logical backup, where you might want to eg only restore a single table. Logical also compresses much better. Innobackupex binary full restore will still be much faster though.

INTPenis• 9 years ago

My question precisely.

Ever since I started using mysql replication I've been mostly relying on replicated incremental backups with xtrabackup.

To workaround the downside of not being able to restore a single table I'm using some scripts I found online that perform automatic restore tests of the xtrabackup backups with docker. That way I can access the restored DB in the docker container and export/dump whatever I want.

This also enables me to run a restore test and data integrity test on the restored data every day and report the status to my monitoring platform.

dexterbt1• 9 years ago

There are no benchmark numbers to back the claim that it's fast. At least the OP should have done some measurements; I'm interested in anything that will speed up our DB's 300+M record tables.

The article also does not link to the official maintainer page/github page.

cookiecaper2• 9 years ago

I don't have numbers right now, but we've been using mydumper/myloader for a long time and it has been much faster for us (we have tables with 1B+ rows).

I should note that the version of `mydumper` in the Ubuntu repos is out of date. IIRC 0.9.1 is the latest and Ubuntu packages 0.6.1 on the latest LTS. 0.9.1 has some good improvements so it's worth building yourself (small utility so quick build time).

As always with database stuff, RTFM and use an abundance of caution. In particular, take care to ensure no bad locking behaviors occur.

falcolas2• 9 years ago

+1 to everything here. Use the version out of launchpad, and try it out on a copy of your DB first (though, being a standard mysql client as far as the DB is concerned, its not going to do much damage; especially if you create a read-only backup user first).

jaequery• 9 years ago

this is not really new as this has been out for atleast 7 years now. but this really is fast. i think atleast five times faster from what i remember.

whatnotests• 9 years ago

1. Shows password on CLI

2. Segfaults for me

I'll be using mysqldump for now...but doing a flush+lock+copy the innodbdata file(s) seems like it would be much, much faster.

mrweasel• 9 years ago

Yep, same for us, it's a great tool, except it crashes a little to often for a backup tool.

lathiat• 9 years ago

Watch out last time I looked most of these tools miss some things like stored procedures, though I haven't checked mydumper specifically recently. mysqlpdump certainly did, and I think I tried mydumper.

There's also other things to potentially worry about; events, functions, etc.

roselan2• 9 years ago

Mainly users.

I'm probably too dumb to find it, but I have found no utility to export/import db users and grants. Not that it's hard to roll your own thou.

disago• 9 years ago

Since version 0.9.1 mydumper supports all schema objects [1]

[1] https://www.percona.com/blog/2015/11/12/logical-mysql-backup...

roselan2• 9 years ago

Note that with 5.7 there is a new tool, "mysqlpump" that can do parallel export/import and direct compression.

I haven't test it yet because mysqldump is enough for our needs.

pizza234• 9 years ago

mysqlpump is a terrible product, both in the implementation and the design, in particular:

  - it will create broken backups for databases with triggers; the bug has been open for more than an year;
  - it breaks compatibility with the `mysqldump` output format, since it adds the database name to the table names (without the option to avoid this);
  - it has an unclear product placement: it's not a subset of mysqldump (it has functionalities added), but it's not a superset either (it has functionalities missing).

The status of the mysql dump/restore tools is, in general, very underwhelming.

The Percona mysqldump version adds a switch for improving innodb indexes re-creation on restore (by moving the ADD KEY after the records insertion), but it's never been merged upstream.

Also, the pattern table matches is extremely useful for cases where it's required, but it's not clear why they added it to mysqlpump rather than to mysqldump.

Mysqlimport has a limitation (bug?) which prevents tables with mixed UTF-8 data and JSON columns to be imported (!!).

falcolas2• 9 years ago

One of my colleagues from my time at Percona worked fairly extensively on Mydumper and Myloader; making the locking semantics better and the segemented files work. It's a pretty amazing tool, since it lets you dump tables in parallel, with very granular locks (something mydumper does not allow).

As a logical backup tool, it's invaluable. When combined with binary logs and regular binary backups, it makes for a nice and comprehensive MySQL backup solution. It even works with managed DB services like RDS.

YMMV of course, but given how easy it is to test for your own use cases, it's absolutely worth taking a look at.

alexcroox2• 9 years ago

What tool would you recommend to easily take frequent backups and pump them to S3 glacier storage for example? I don't want to spend hours configuring something as it's only for a small personal server.

vosper• 9 years ago

Regular MySQLdump + tar/bz2 + s3cmd or AWS CLI would do it. Probably just a few lines of bash or Python to put those together.

alexcroox2• 9 years ago

Found this https://gist.github.com/abulte/4079819

Fast MySQL Backup and Restore Using Mydumper and Myloader

Related Stories