Portal Home > Knowledgebase > Articles Database > Paid backup solution for large MySQL database
Paid backup solution for large MySQL database
| Posted by sh4ka, 10-12-2011, 08:33 AM |
| Hello guys,
I'm managing a few big vbulletin boards with millions of posts, and even the servers have powerful CPUs, tons of ram and fasts disks, doing hot backups without downtime is almost impossible.
Tried mysqldump, mysqlhotbackup and xtrabackup and they all get high load and downtime during the backup process.
So, I was wondering about purchasing a paid mysql backup solution for this databases that doens't involve any downtime... Based on your experience, what do you suggest for MySQL 5.0.77 on CentOS?
Thanks.
|
| Posted by lynxus, 10-12-2011, 08:38 AM |
| Have you tried mysqldumping without locking?
http://stackoverflow.com/questions/1...locking-tables
|
| Posted by AWalrus, 10-12-2011, 09:02 AM |
| If you have the budget, run a slave copy of the database on a backup server. You can then take backups of the slave at your leisure. The slave would become a hot backup for you in the event of a failure. You can also run a slave of the slave that is several hours (or a day?) behind so you can easily recover if the master replicates a bad update/delete.
|
| Posted by sh4ka, 10-12-2011, 09:52 AM |
| Thanks for your reply guys.
I'm not sure about using the backups without locking, and about the slave copy, I need a solution for the same server that doesn't involve setting up another mysql slave server.
Any other suggestions?
Thanks.
|
| Posted by silasmoeckel, 10-12-2011, 11:18 AM |
| If your running on top of an LVM or similar snapshots works very well. http://www.lenzg.net/mylvmbackup/ is a script that works pretty well. It gets all the databases consistent then runs the snapshot you then makes a nice tarball for ya. Flushing is very quick, obviously you will put some load by dealing with the snapshot and backing it up but nothing like a mysqldump.
|
| Posted by Steven, 10-12-2011, 12:07 PM |
| My suggestion would be to replicate the database, stop the slave, backup, and start the slave up again.
|
| Posted by sh4ka, 10-12-2011, 12:10 PM |
| Can I create an slave on the same server? or I need an additional machine?
Thanks
|
| Posted by barbus, 10-12-2011, 01:36 PM |
| Yes, you can. But the more reliable solution would be with use of additional server. If first sever fails, you will be able to use second one.
|
| Posted by eva2000, 10-12-2011, 04:00 PM |
| as folks have stated for fast mysql database backups only way is via mysql replication and do mysql back ups on the slave server.
Sure can, been playing with Tungsten Replicator based MySQL master/slave replication on the same server for a test vBulletin forum just to see if it will work. Works fine for now, just needs more testing to familiarize myself with Tungsten Replicator based replication
Of course you can do the same with normal native MySQL replication too but there's limitations including slave lag to deal with and much more complicated MySQL master fail over. Issues which Tungsten Replicator based MySQL replication doesn't suffer from.
Also for faster backups also look into mydumper http://www.mydumper.org/ as an alternative to mysqldump as mydumper is multi-threaded for backup and restores and is around 3x to 10x faster than traditional mysqldump backups.
So while it's not going to totally eliminate the interruption, it will reduce the negative impact and duration dramatically. So a 60 minute mysqldump backup might take only 6-20 minutes to do with mydumper.
Just my AUD$0.02 cents
p.s. remember backup speed also is determined by how optimised your mysql settings are, if optimised properly it can be a dramatic difference in duration it takes and amount of server resource load it takes to backup large amounts of data.
Last edited by eva2000; 10-12-2011 at 04:08 PM.
|
| Posted by ddrager, 10-12-2011, 04:05 PM |
| You have a couple of ways to do this. Paying for a solution isn't going to make it much easier.
mysqlhotcopy will work but it requires MyISAM db type.
You can also use binary logs which basically record transactions on the database, and then back them up. Restore using these logs is much harder though.
The final option as others have said is to create a master/slave setup and backup the slave server. This also provides some redundancy for you.
|
| Posted by sh4ka, 10-13-2011, 12:46 PM |
| Thanks guys... I just mounted mysql replication with a slave server on another machine, connected via private lan and it works really fast without any downtime. That was the best solution I found
Thanks again to everybody!
|
| Posted by Steven, 10-13-2011, 01:40 PM |
| mysqlhotcopy will still lock tables and cause slowdowns.
|
| Posted by wartungsfenster, 10-14-2011, 12:26 PM |
| I'd recommend contacting Percona sales. They do consulting for MySQL and know how to make a really consistent online backup of InnoDB. Which other systems promise, but do not keep.
|
| Posted by wartungsfenster, 10-14-2011, 12:28 PM |
| If you read the "TODO" file in the source of mylvmbackup you'll see it can't get consistent InnoDB backups.
For MyISAM, it'll work though, and probably that's enough for forums, agreed.
|
| Posted by ultrabizweb, 10-14-2011, 09:10 PM |
| innobackupex in a custom bash script works great just make sure to pass the correct options when you run it there is even an option to throttle disk I/O so you don't slow everything down at once, haven't tried it though since my databases are small at the moment.
The Percona guys know there stuff they are experts for a reason.
Should be able to run innobackupex on the slave without problems.
|
| Posted by ovhresell, 10-15-2011, 07:59 AM |
| If this is a Vbulletin forum job, trust this way.
http://www.vbulletin.org/forum/showthread.php?t=256547
I have tried this with over 40GB + backups, works flawlessly no downtimes for forums and no overloading.
|
Add to Favourites 
Print this Article
Also Read
