Home / server / Questions / 15978
Accepted
Sekenre
Asked: 2009-05-31 17:31:26 +0800 CST

What's the most efficient way of taking backup snapshots of a mysql database over the internet?

  • 772

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.

backup mysql

5 Answers

  1. Best Answer

    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.

    • 2

  2. 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)).

    • 1

  3. 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.
    • 1

  4. 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)

    • 0

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

    • 0