backup MySQL. WAN to LAN best method?

[Drenriza]

Hey all

I am currently thinking about how to best make a MySQL backup from a server on the WAN to server on the LAN (both behind firewalls).
And would like to ask the community what you think of my thoughts / solution so far, and what your own are.

My initial thoughts

• Create a WAN and LAN backup user with no added groups or permissions.
• Create a backup RSA key pair (WAN server) no passphrase.
• Setup (forward / redirect) FW (LAN) SSH rule
  
  • iptables -t nat -A PREROUTING -p tcp --sport [port-other-than-22] -j DNAT --to-destination [LAN server ip]:22
  
  
• Copy RSA public key to local server
  
  • ssh-copy-id -p [port] -i ~/[WAN-BACKUP-USER]/.ssh/backup.key.pub [LAN-BACKUP-USER]@LAN-FW-IP
  
  
• AppArmor
  
  • Give the LAN backup user permission to rw the backup directory (placed outside of the users home directory like /srv/mountpoint)
  • Give the WAN backup user permission to dump MySQL and nothing else.
  
  
• Stream MySQL dump
  
  • mysqldump ${mysqlOptions} -u $SQLUSER -p$SQLPASS $i | $NICE gzip -c | ssh ${remoteUser}@${LAN-FW-IP} "cat >> /backup/path/dump.sql.gz"
  
  

Current problems (that i see) in this solution so far
If an MySQL dump is several GB's in size and fail mid way, the backup would need to start over.
Is their a way for SSH or another tool to calculate

• What has been streamed
• What needs to be streamed
• Streams rest

Or is it straight up just a do-over?

I know rsync has this feature with data files but don't know if their is an equivalent for streams.

The reason for a stream versus doing a local dump and rsync it, is if a MySQL dump is bigger than the local storage available.

Thanks to all in advance who takes the time to read and or reply.
Kind regards.

[Lars Noodén]

The appending redirect >> probably should be a plain redirect > because adding to a gzipped file will just make garbage.

If an MySQL dump is several GB's in size and fail mid way, the backup would need to start over.
Is their a way for SSH or another tool to calculate

• What has been streamed
• What needs to be streamed
• Streams rest

Or is it straight up just a do-over?

I know rsync has this feature with data files but don't know if their is an equivalent for streams.

The reason for a stream versus doing a local dump and rsync it, is if a MySQL dump is bigger than the local storage available.

The pipes you chose above are also close to what I would try. But if the dump is bigger than the local storage then, in the event of an interruption, you are stuck starting the transfer over, as far as I know, a do-over as you call it. The ideal, I think, would be if there is space and then dump to disk and then use 'rsync' for the transfer.

I'm wondering if 'split' might be worked in somehow, but if you don't save the dump to disk then the content will likely be different each time anyway and not able to be resumed.

[Drenriza]

Thanks for the reply Lars.

The appending redirect >> probably should be a plain redirect > because adding to a gzipped file will just make garbage.

What do you mean with garbage? Do you might have a small example.

I used the >> instead of > after looking through the gzip doc, which under advanced usage says

ADVANCED USAGE
Multiple compressed files can be concatenated. In this case, gunzip will extract all members at once. For example:

gzip -c file1 > foo.gz
gzip -c file2 >> foo.gz

But if you have experience with this and garbage i would very much like to hear about it.

but if you don't save the dump to disk then the content will likely be different each time anyway and not able to be resumed.

That is most likely. I was thinking (hoping) that their might was a smart feature / script / other that could determine if their was changes from a failed dump and what would be needed to be downloaded to get the full backup.

Something like diffing the difference between the dump that failed and the mysql dump in progress and just take differences

[Lars Noodén]

What do you mean with garbage? Do you might have a small example.

I used the >> instead of > after looking through the gzip doc, which under advanced usage says


But if you have experience with this and garbage i would very much like to hear about it.

Yes, gzipped files can be concatenated. However, I was thinking of dealing with the SQL dump specifically. I could be wrong, I guess you'd have to try and see what really happens with the dump and concatenating partial, gzipped dumps.

Something like diffing the difference between the dump that failed and the mysql dump in progress and just take differences

If the database is being used, then it will be changing. Since the dump is a snapshot as it was at a particular time, if you do a new dump, you are getting a new snapshot. So I'm not sure if it is even theoretically possible, at least in that way. Depending on the database structure, you might be able to transfer one table at a time, but with records that exist across multiple tables, that is not possible.

There used to be a mysqlbackup program that claimed to do incremental backups but I've not used it.

[btindie]

You might want to take a look at https://launchpad.net/mydumper as an alternative way of backing up your MySQL database.

[Lars Noodén]

Another option might be to use the built-in replication capabilities. You'd have to have MySQL running on that second machine but there's no reason it would have to be available to anyone or anything.

[Drenriza]

Another option might be to use the built-in replication capabilities. You'd have to have MySQL running on that second machine but there's no reason it would have to be available to anyone or anything.

Thats an good idea.

But if you with that method how would MySQL2 (backup) react if data is deleted by mistake on MySQL1.
I suppose it would be replicated to MySQL2 and than loosing the backed up data.

Another solution also could be using ZFS (how good has ZFS on ubuntu become?).
WIth ZFS we can create a zpool and mount it to (for example) /mysql1
Than for keeping downtime low we can create a second zpool called /mysqlbackup

Make a snapshop from /mysql1 to /mysqlbackup and than backup /mysqlbackup.
Now i am not 100% sure BUT! zfs has a built in tool for backing up a zpool from one zfs machine to another

zfs send poolname/filesystem@snapname | ssh user@remote "zfs recv"

AND maybe this tool have some rsync features, that if the connection goes down in transfer you can pick up where you left of.

The awesome thing about the snapshot is that it only takes up disk space when changes happen.
So when you create any snapshot it initial takes up 0 MB's and as changes occure it takes more and more place.
But if u create a backup and than delete the snapshot all is good for low storage systems (low secondary storage).

You might want to take a look at https://launchpad.net/mydumper as an alternative way of backing up your MySQL database.

Awesome thanks will take a look at it

[lukeiamyourfather]

Another solution also could be using ZFS (how good has ZFS on ubuntu become?).

In a nutshell, pretty good. I've been using it now for a few years in a production environment with several machines and hundreds of terabytes of data. Use ZFS on Linux which is a kernel port of ZFS, not the old FUSE implementation which was slow and not feature complete.

http://zfsonlinux.org/
http://whenpicsfly.com/wp-content/up...S-on-Linux.pdf

Now i am not 100% sure BUT! zfs has a built in tool for backing up a zpool from one zfs machine to another

zfs send poolname/filesystem@snapname | ssh user@remote "zfs recv"

AND maybe this tool have some rsync features, that if the connection goes down in transfer you can pick up where you left of.

Snapshots can be sent incrementally like rsync. Last time I tried it interrupted transfers couldn't be resumed but that's changing so you can resume. I'm not sure if it has made it's way into ZFS on Linux yet but it will soon if it hasn't. If it's not in ZFS on Linux yet you can wrap the connection in something else that can resume after interruption. There are several shell scripts floating around to do this but I haven't tried any of them.

https://github.com/illumos/illumos-g...8a686d54b48ab8

[Lars Noodén]

But if you with that method how would MySQL2 (backup) react if data is deleted by mistake on MySQL1.
I suppose it would be replicated to MySQL2 and than losing the backed up data.

Yes, you could probably lose the data if it is deleted from the first and then the deletion is replicated to the second. But if you had enough space on the second, you could try the dump on it instead.

A problem with other options, including possibly ZFS, is that a non-MySQL-based snapshot could grab partial or broken records if only part of the record is written or deleted at the exact time of the snapshot. So I think you might have to focus on variations of the replication or dump.

[Drenriza]

Nothing