Backup large database

[fsqueeen]

I have a database which is around 6gb. May I know which is the better way to backup my database?

1. Copy the MySQL data directory to create a backup
or
2. mysqldump?

I'm just worried that zipping up the data directory will make the MySQL hang as I'm goin to zip a big directory. But using mysqldump, will it consume many server resources?

Guide needed from you all... thanks a lot...

 

[KarveR]

Copying the whole of the data directory will ensure a complet backup, but you need to shut down mysql while you do that.

mysqldump will use all of your available memory unless you use the --quick option, but at least mysql remains running.

The question really, is do you have to worry about downtime.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[guelphdad]

mysqlhotcopy will allow you to copy the database without taking it down. I don't have any experience using it myself.

 

[KarveR]

only if you dont have any innodb tables.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[fsqueeen]

Thanks everyone...

Ya.. I am worrying about down time. Every second or every minute will have traffic come in. I am sure that I can't turn off the Mysql for the back up process.

My database has innodb tables... that's why I think mysqlhotcopy can't use for the back up.

So, does mysqldump with --quick option will ensure that the back up process does not use all my available memory? Will I need to turn off Mysql while back up?

 

[KarveR]

with the --quick option, records are written out 1 at a time, instead of the whole table being read into memory before being written to file, its still a fairly intensive process, especially if you are writing to the same disk mysql is reading from.

If downtime is a critical issue you could think about setting up a second mysql server which is replicated from the first, this gives you an online backup, and the ability to shut down completely to backup without affecting the main server.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.