[rsnapshot-discuss] Any good mysql backup tools to compress tsv on the fly?

[Nico Kadel-Garcia]

I'm getting nowhere with MySQL documentaiton and users, and am hoping
that one of my old rsnapshot friends has run into this.

I've got some very bulky MySQL environments to back up to rsnapshot.
But the local backups of the MySQL tables, in order to rsnapshot them,
take so long and so many resources that they interfere with the
business use of the MySQL server.

It would be much faster, and tak emuch less space on the MySQL server,
if I can gracefully export the tsv files as compressed files, but
MySQL seems to have no direct facility for this. The "mysqldump --tab"
option, and the mysql "select * from table into outfile 'filename'"
both fail if the file already exists, so I can't just output to a pipe
tied to gzip or bzip2 as I might for *loading* from a bulky csv or tsv
file.

Does anyone from this backup community have a really good script or
wrapper for doing this? I'm finding a lot of pretty poor quality
tools, many of which do not gracefully handle binary data which may
contain "tab" or "comma" characters, and are not able to gracefully
handle binary fields that might contain those characters and fail to
follow the well defined MySQL output formats to quote them properly.

[Sam Pinkus]

------------------------------------------------------------------------------
What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic
patterns at an interface-level. Reveals which users, apps, and protocols are
consuming the most bandwidth. Provides multi-vendor support for NetFlow,
J-Flow, sFlow and other flows. Make informed decisions using capacity
planning reports. https://ad.doubleclick.net/ddm/clk/305295220;132659582;e

[Nico Kadel-Garcia]

MySQL flush; MySQL lock; take LVM snapshot; MySQL unlock; backup of a
MySQL databases.
- Sam.

They compress better, they usually load faster, and they're easier to
scan for information *without* loading them into a database. They're
also much easier to transfer to a different type of database, and to
compare side by side with previous backups.

I've certainly used LVM snapshots. They're handy. For me right now,
they'd require taking the relevant servers offline to re-partition
them with enough space to hold the snapshots, and actually using them
would mean something like this familiar procedure:

* Write lock MySQL
* Flush logs in MySQL
* Run "sync" a few times, to ensure paged files are written to disk.
This part involves a certain amount of prayer.
* Make the LVM snapshot.
* Release the write lock.
* Mount the snapshot.
* Run rsnapshot
* * This is likely to take much longer because you're transferring
raw binary and potentially version incompatible MySQL tables
* * Or make a compressed tarball and rsnapshot that, and it's likely
to be at least twice as bulky because it contains the indexes, not
just the data.
* Try to ensure that you're done with the snapshot and delete it
before running out of LVM snapshot space and corrupting the snapshot.

I've had pretty good success in the somewhat distant past with that
approach. It does require the write locks, at least, to be certain of
not backing up MySQL in an inconsistent state. It does require a write
lock in what can be a production sensitive databae.

If wishes were fishes and I could have a pony, I'd go back and turn
all the database servers to clustered MySQL and backup a member of the
cluster, with good quality clusteriing tools like Percona.