News for hackers. Stuff that matters.
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by dexterbt1 on Saturday July 22, 2017 @10:35PM (#14830614)
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.

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by cookiecaper on Saturday July 22, 2017 @11:56PM (#14830829)
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.

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by falcolas on Sunday July 23, 2017 @10:07AM (#14832315)
+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).
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by wanderr on Saturday July 22, 2017 @10:04PM (#14830536)
How does this compare to the percona tools?
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by barrkel on Saturday July 22, 2017 @11:35PM (#14830768)
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.
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by INTPenis on Sunday July 23, 2017 @05:57AM (#14831585)
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.

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by whatnotests on Sunday July 23, 2017 @12:20AM (#14830882)
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.

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by mrweasel on Sunday July 23, 2017 @11:27AM (#14832673)
Yep, same for us, it's a great tool, except it crashes a little to often for a backup tool.
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by lathiat on Sunday July 23, 2017 @12:21AM (#14830883)
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.

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by disago on Sunday July 23, 2017 @03:15AM (#14831221)
Since version 0.9.1 mydumper supports all schema objects [1]

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

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by roselan on Sunday July 23, 2017 @03:10AM (#14831211)
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.

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by falcolas on Sunday July 23, 2017 @10:04AM (#14832307)
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.

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by roselan on Sunday July 23, 2017 @03:24AM (#14831245)
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.

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by pizza234 on Sunday July 23, 2017 @04:07AM (#14831350)
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 (!!).

Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by jaequery on Saturday July 22, 2017 @11:55PM (#14830827)
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.
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by alexcroox on Sunday July 23, 2017 @10:17AM (#14832347)
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.
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by vosper on Sunday July 23, 2017 @12:12PM (#14832952)
Regular MySQLdump + tar/bz2 + s3cmd or AWS CLI would do it. Probably just a few lines of bash or Python to put those together.
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by alexcroox on Sunday July 23, 2017 @01:24PM (#14833305)
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by boznz on Saturday July 22, 2017 @09:48PM (#14830497)
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 :-)
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by cookiecaper on Saturday July 22, 2017 @11:57PM (#14830834)
This is a logical backup tool. It doesn't have to run on the same server as the DB.
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by vlod on Saturday July 22, 2017 @10:16PM (#14830565)
Would bash-on-windows thingy work for you? (Assuming it works with it)
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by throwanem on Saturday July 22, 2017 @11:51PM (#14830810)
Should build it with the MySQL client library installed. So should Cygwin, if your Windows doesn't support the Linux subsystem.
Re:Fast MySQL Backup and Restore Using Mydumper and Myloader by boznz on Sunday July 23, 2017 @03:04AM (#14831191)
Thanks for the informative replies I'll give it a try