rsync with a MYSQL DB dump

Portal Home > Knowledgebase > Articles Database > rsync with a MYSQL DB dump

Posted by egsi, 06-11-2010, 10:08 AM
I backup all my data (files + mysql DB dump) to a remote location. Using rsync for this which is fine as it only transfers what data has changed. The problem though is that when I take a dump of the DB and zip it, rsync transfers the entire dump. Which ads to the bandwidth prices every month! Just wondering if there is any way to sync a mysql DB for remote backup purposes ?
Posted by Crashus, 06-11-2010, 10:34 AM
you can probably sync bin logs but it your zipped dump takes much space then binlogs will be even more bigger unfortunately
Posted by xtrac568, 06-11-2010, 12:24 PM
you might get better results for rsync speedup with using uncompressed tar archive instead of compressed zip archive. it is easier for rsync to calculate data differencies when using uncompressed data versus compressed data.
Posted by Crashus, 06-11-2010, 12:26 PM
yeah and more traffic, nice try but you might want to read his initial problem. And I barely can imagine rsync transfering something bigger faster only because it is different file format. OP - you need to use --exclude mysql option maybe?
Posted by egsi, 06-11-2010, 08:12 PM
Thanks guys. The only problem using rsync with --exclude Crashus is that how do I transfer my MSQL dump across? Basically I run two rsync commands every day (as part of a daily remote backup): 1. To backup /home/someuser/public_html/* (all the public web files that the web server, well serves (around 1.6GB in total under that directory, but only transfers around 3MB each day) 2. To backup a dump of my MYSQL DB (around 350MB bzipped, and hence transfers 350MB each day). So with just the MYSQL DB backup via rsync, that accounts for around 10GB of traffic per month!
Posted by Crashus, 06-11-2010, 08:18 PM
You might want to use --exclude with mysql, not rsync So you can exclude some solid tables that are changed rarely. And 10Gb is not a big BW at all even for a home-user, you still can rent a VPS for $5 and store your backups there. Good luck!
Posted by egsi, 06-11-2010, 08:30 PM
Excuse my ignorance but I just checked the man pages for mysql and mysqldump and didn't see an --exclude option ? 10GB isn't 'a lot' but the site's total bandwidth is around 200GB / month (around 5% of total bandwidth). So that's the concern, just want to cut down the bandwidth used by backups.
Posted by Crashus, 06-11-2010, 08:34 PM
Sorry misguided you a bit, here you go: --ignore-table=db_name.tbl_name Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times. This option also can be used to ignore views. SO if you will reduce backups BW you will barely help overall BW, maybe there is a reason and a way to reduce site BW? Just asking
Posted by egsi, 06-11-2010, 08:48 PM
Ahh ok thanks. TBH that sounds like a bit of work (especially when it comes to restoring backups and having to ensure all tables are restored). But yes, you make a valid point about overall bandwidth. I think it might be time to look at reducing overall bandwidth. Guess I now have to get to work on making sure all my static content is cached well with a far future date. Thanks
Posted by dlxlb, 07-23-2011, 10:41 AM
Just use gzip with --rsyncable parameter.
Posted by WholesaleBackup, 07-25-2011, 03:17 PM
1 on dlxlb's post: compression on files makes it much harder for rsync to do a differential on the file itself, so sometimes compression means that rsync actually will move *more* data than if you do not compress, which allows rsync to do an efficient diff of the file in question. When we wrote our online backup software, we realized that we had to do tests on each file to get the best results, and so you are now seeing that issue as well. There is also a command rdiff that you can use to compare two versions of a file to determine how big the patches (the parts that are transmitted) will be. Here's a good thread with some more tests that this user ran on the same isssue: http://www.backupcentral.com/phpBB2/...ificati-63134/ Cheers,
Posted by luki, 07-25-2011, 08:43 PM
gzip with --rsyncable is the way to go. Alternatively (less safe, but OK in 99% of the cases) you can do: 1) mysqladmin flush-tables 2) rsync the raw mysql data in /var/lib/mysql Yes, yes, people will tell you that this is not safe, but it works well in practice. That is if your rsync only takes a short time and not hours. Or use mysqlhotcopy to make a copy of a database on the fly (simply a combination of flush tables, lock tables and cp), which you can rsync and then delete.
Posted by m4rc3, 07-25-2011, 09:31 PM
rsync will transfer the entire file every time. If a file changed rsync will not update the old one with the new content but copy the file again. You should get some backup space in the DC where that mysql box is so you can backup via internal network ( it's faster and free )
Posted by foobic, 07-25-2011, 10:21 PM
Incorrect. The rsync algorithm allows for transferring only the differences between similar files, with minimal overhead. Luki's suggestions are on the mark, but I'd suggest one more: Just rsync the db dump files (uncompressed SQL), as you're probably doing already. Using the -z option in rsync means the data you transfer is compressed with gzip anyway. Considering that 1. only the differences are transferred, and 2. the data transferred is compressed, you probably aren't going to do much better.
Posted by WinsNexus, 07-25-2011, 10:31 PM
If MySQL backups are the culprit when doing backups, setup a slave mysql server and do replication. This way you will always have a hot copy "online" at another location and the only bandwidth that is used is the incremental changes at the table level. No since in dumping data you might have that never changes. If you are concerned in regards to security of the replication and you are not within a secure (same VLAN) then do this over SSL and limit 3306 to a specific user server/server. http://www.option-c.com/xwiki/MySQL_...ation_with_SSL Saw it mentioned to rsync /var/lib/mysql, not a good idea. Looking for table level corruption unless you want to stop the main db server while this is performed every time. Last edited by WinsNexus; 07-25-2011 at 10:43 PM. Reason: past not plural
Posted by 8088, 07-25-2011, 10:32 PM
That's no backup in my book.
Posted by m4rc3, 07-26-2011, 08:25 AM
Yeah, I forgot that rsync will copy the entire file for local transfers only.
Posted by luki, 07-26-2011, 09:01 PM
The offsite mySQL slave idea is good and works well. I was going to suggest that as we're doing that through a OpenVPN tunnel (for encryption and transparent compression). However, from our traffic graphs, you won't be saving much in terms of transfer volume... we are pushing a constant ~50 kbps (including overhead) with 5.5 inserts/updated per second, so that's about ~500 MB per day on a ~2 GB database.
Posted by whmcsguru, 07-28-2011, 02:57 PM
Here's a little bash script I've worked up to backup all databases over the years. If you've got root access to the server, it'll help out. Yes, it does gzip the database, which saves a TON on bandwidth: Hopefully that helps out a bit!
Posted by WholesaleBackup, 08-01-2011, 02:57 PM
linux-tech: nice script, but it gzips the sql backup before the rsync, which is what got the OP in hot water in the first place. The best solution in the thread above is to use the rsyncable flag in the gzip.
Posted by whmcsguru, 08-01-2011, 03:03 PM
Actually, if you read the OP, that's just what the OP wanted, something to zip the db THEN sync it, as the other way around is taking up too much bandwidth
Posted by WholesaleBackup, 08-01-2011, 06:24 PM
linux-tech. . .here's the OP: "The problem though is that when I take a dump of the DB and zip it, rsync transfers the entire dump. Which ads to the bandwidth prices every month!" The problem, as I read it, is precisely the opposite of what you state: "something to zip the db THEN sync it." He needs to either zip it with the right flag to allow for efficient diffs with rsync, or not zip it. Please refer to the thread above if that's not clear. . .zipping conflicts with block-level diffing in many cases, as would encryption.
Posted by CoderJosh, 08-02-2011, 10:00 AM
Unless I'm missing something or if disk space is an issue, the following solution should be good: Don't gzip the MySQL dump file, so that rsync's diff algorithm works better, which helps avoid transmitting data that is already on the backup server. This works great on DB dumps. Turn on rsync's compression (rsync -z), so that the data that has to be transmitted is compressed. This saves bandwidth and time.
Posted by mugo, 08-03-2011, 11:26 PM
Another option for the databases...on your backup machine, if you can also run mysql, just setup master-slave, and backup locally. If it's an option for you, that may help.
Posted by falconinternet, 03-06-2012, 01:03 PM
I know this is an old topic but I wanted to add R1Soft. Yes, it prices money, but it does block-level backups the entire server as well as MySQL with zero downtime. If you are serious about your database, I highly recommend it. It also offers scheduled restores, so you could automatically restore the DB to a different server every hour or whatever. Works with Linux and Windows. http://www.r1soft.com/windows-cdp/cd...edition/mysql/