nixCraft

Hello,
I knew mysqldump is the safest way to backup data, but it uses lot my system resources and the system goes ideal at the time mysqldump.So i need to get alternative solutions for mysqldump.I am heard of mysqlhotcopy and direct copy of mysql(/var/lib/mysql).But i want to know redundant solution for this.
Please kindly advise me on this...

Thanks,
Ephdia

How big is your database? mysqldump works very nicely.

__________________
Vivek | My personal blog
Linux Evangelist
Play hard stay cool

Chances are your database dumps are competing for resources. One of the awesome things about mysql is that it comes with replication. I suggest setting up a slave server for DR and backups duty.

Depending on which engine or engines you want to use, you can skip the mysql dumps and just backup the filesystem, however, that's not without it's gotchas:
. innodb table files are not stable unless you do a clean shutdown
. mysql tables need to flushed before copying

If you are using LVM to manage your partitions, you could consider just using brief downtime to take snapshots of your data partition. This is, IME, a 30 second interruption with the possibility of a large load penatly during restart. I've watched 8-core systems get a restart on a snapshotted volume "rebound" spike up to load 100 - 120. This typically means your application will suffer, drop connects, etc, while all tables that are commonly written to, copy the extents they own as part of the copy-on-write snapshot process. Remeber, you have to shut down mysql in order to get a clean copy of those datafiles, esp if you're running innodb.

Please think about how fast your application is being adopted (getting more popular?) and how fast your data-set is growing. If your app is suffering when you do mysql dumps, chances are you're facing the need to scale out.

I perform site replication and do backups from a server in my office that mirrors my production system.

Good luck, I hope you have a budget.