2

Ok The situation is this:

We currently use mysqldump and then bzip2 compress the result and scp it back to our backup server. This is a time consuming manual process and there are no snapshots created.

I am currently experimenting with rsync transferring the differences between the old and new dump files but the compression is much less efficient.

Any other suggestions would be welcome.

Sekenre
  • 2,943
  • 1
  • 20
  • 17

5 Answers5

2

One way to do it would be to set up database replication to a backup server and create the backups there.

If this is not feasible in your environment your second best chances are rsync on the plain SQL dump (don't forget --compress) or on a gzip of the same, which has been compressed with --rsyncable. I don't know how well rsync fares on this, since inserted/deleted values in the dump file will cause a "shift" in the file which rsync needs to detect to prevent the retransfer of data which has not been changed.

When you run rsync with --stats it should report how many bytes it actually sent over the network, to give you some figures.

Michael Renner
  • 1,750
  • 13
  • 17
  • Cool, I didn't know about --rsyncable. Also bzip2 with a small block size might be ok. As far as the "shift" in the file goes, rsync is built to handle that. Database replication would be nice but not possible in my environment. I will try --compress – Sekenre May 31 '09 at 02:04
  • The more I think of it the more I'm convinced that your best bet is plain rsync with --compress. Both gzip and rsync can produce completely different output when the input is shifted (far enough into their respective blocksize).
    I don't know how the pattern search algorithms in rsync exactly work, if you find anything on this, please do tell ;).     – Michael Renner May 31 '09 at 02:09
  • I think you should stick with replication and handle your backups on the slave. – Dave Cheney May 31 '09 at 03:55
1

You tried the following options?...

rsync -a --compress --compress-level=9

In fact this should perform better (higher compression ratios) than using a compressing-remote-shell or compressing-transport (see rsync(1)).

khosrow
  • 4,163
  • 3
  • 27
  • 33
1

The solution I've always used:

  1. Replicate to another server, preferably in the same physical location (so that if you need to, you can do a manual failover).
  2. Set up a shell script to take backups on that server, using --master-data --single-transaction (assuming you're using InnoDB) so that you can get the master log's position as well as ensuring transactional consistency in your DB dump. I wrote this one, which may suit your purposes or not.
  3. Upload your backup to wherever it is you want your backup to go. I upload mine to Mosso Cloud Files (which is incredibly fast if you're on a Mosso server) using another script that I put together.
  4. Run this backup on a period representative of how important your data is to you. We run ours every two hours.
  5. Take a vacation.
Dan Udey
  • 1,468
  • 12
  • 17
0

Not sure if you're using Windows, but I've had good luck with MyWitch administering MySQL databases on remote servers, and they have a product (that I haven't used) called DumpTimer that will let you schedule mysqldumps and download them. Shareware, so you'd have to pay for the full version that would let you run as a Windows service.

Link to DumpTimer: www.richtsoft.com/mysql_17_backup.html

(cut/paste since new users can't add hyperlinks)

elint
  • 82
  • 1
  • 2
  • 10
0

You could dump the data in each table to a CSV file, ordered by a primary key (or something). Then use split to split it based on lines. Then use rsync to copy those files across. So if the first 1,000 lines (ie rows) haven't changed that file won't be rsynced. You could leave them unencrypted on the server and client and get rsync to do the compression on the network. That way it'd be quick for it it know "Oh I add 10 lines here", etc.

Amandasaurus
  • 31,471
  • 65
  • 192
  • 253