
Fast MySQL Backup and Restore Using Mydumper and Myloader - lyri787w
https://wplobster.com/extremely-fast-mysql-backup-restore-using-mydumpermyloader/
======
dexterbt1
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.

~~~
cookiecaper
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.

~~~
falcolas
+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).

------
wanderr
How does this compare to the percona tools?

~~~
barrkel
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.

------
whatnotests
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
Yep, same for us, it's a great tool, except it crashes a little to often for a
backup tool.

------
lathiat
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.

~~~
disago
Since version 0.9.1 mydumper supports all schema objects [1]

[1] [https://www.percona.com/blog/2015/11/12/logical-mysql-
backup...](https://www.percona.com/blog/2015/11/12/logical-mysql-backup-tool-
mydumper-0-9-1-now-available/)

------
falcolas
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.

------
roselan
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
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 (!!).

------
jaequery
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.

------
alexcroox
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
Regular MySQLdump + tar/bz2 + s3cmd or AWS CLI would do it. Probably just a
few lines of bash or Python to put those together.

~~~
alexcroox
Found this
[https://gist.github.com/abulte/4079819](https://gist.github.com/abulte/4079819)

------
boznz
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
Would bash-on-windows thingy work for you? (Assuming it works with it)

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

