Best way to backup a large database

[marklcfc]

Whats the best way to backup a large database?

I used to be able to do it via phpmyadmin but it seems to time out during download now. My database is over 1GB now.

[+Nik Louch Subscriber²]

Have it backup to the webserver, then download.

[marklcfc]

How do I do that? Putty?

[Borbus]

If you have the mysql client on the box you should have mysqldump. man mysqldump. It's quite easy. I would definitely not use http or ftp to download a 1GB database dump, though. If you really have to then make sure you take proper hashes of the file before and after to check for integrity. A much better option would be to use SFTP, which I assume you can since you mentioned use of PuTTY (use WinSCP on windows). SFTP has built in error checking which will ensure integrity of your dump.

edit: Oh and I forgot to mention, a database dump will compress very well (assuming it is mostly text not blobs). If you run gzip on that you can probably reduce the size by 50%.

[marklcfc]

I can use putty.

How exactly would I go about doing the SFTP way?

[Borbus]

You can use Putty to run mysqldump and gzip etc. yes. You can use WinSCP to then transfer the dump to your PC. In fact you can do it all with WinSCP because it is based on Putty and you can just open a shell in it.

[marklcfc]

You can use Putty to run mysqldump and gzip etc. yes. You can use WinSCP to then transfer the dump to your PC. In fact you can do it all with WinSCP because it is based on Putty and you can just open a shell in it.

I have run putty and got a gzip of my database, it's just under 200mb. I've downloaded WinSCP, and am copying the file to my hard drive, theres no need to change any settings on WinSCP is there?

[Borbus]

No, you don't need to change any settings.

In future what you can do is decompress the dump on your hdd and use rsync every day to sync up with latest dump on your server (rsync will use scp to do the transfer). rsync is very efficient at syncing up things like database dumps. Then every week or something gzip it and keep it in your archive if you keep one (or even keep rsync diffs every day or something). You can just run that as a cron job and have it send you an email every time it does it etc.