Thread: how to do MYSQL online backup of huge database

hello
This is Manish. Well I want to do mysql online backup of databases up to 120GB size. I tried mysqldump and mysqlhotcopy and also mysqlzrm. but none of them works and the version of mysql is 4.X. on gentoo. So is there any opensource soultion for the mysql database backup.

Thanking you
Manish

Yes large or huge database is problem for live backup. If you have performance problems with your server while making hot / live backups, try any one of the solution

#1. Set up replication and perform backups on the slave rather than on the master ... see this tutorial ONLamp.com -- Live Backups of MySQL Using Replication

#2. Use a Veritas filesystem to make snapshots (you need Veritas products)

ZRM for MySQL Enterprise and Cluster Editions does work with large databases you need to purchase this software, it is not a free version. Zmanda Recovery Manager for MySQL - Enterprise Edition. You can also use free version which requires a lot of config option. I suggest try replication option as suggested by rockdalinux

for online backup, if u r going to use replication, then u need huge amount of RAM, by which ur application, queries, and so on... and even live backup takes place.

so look at it if have enough amount of RAM, my opinion is 8GB RAM will be fair for this task.

but why don't u a shell script or use any user tweakable source application in which the process is like this

1) first it make dump compressed with tar.gz then transfer to storage.
ofcourse it will also take much memory but with less bandwidth.

Sounds like you need regular downtime (those tables must be terribly slow to repair, too.)

Have you considered doing LVM snapshotting? The big drawback to that is the load-spike caused by copying extends once you restart and data starts getting written again. If you're doing a lot of data collection using the ARCHIVE engine, then that load spike won't be so big.

But--if you're not using replication now, I very much think you're going to want to soon. Come up with an LVM snapshot process and rsync the data to a slave ... that will be the fastest way to get replication started.

Good luck!