I hope you all with dedicated or even VPS servers are backing up the MySQL /data directory instead of doing cumbersome error riddled SQL dumps

I hardly ever have trouble with

mysqldump -uXXXX -pXXXXXX database > /full_path/back-ups/good_idea.sql

You just have to repair/optimize the database. For vBulletin, I do that every time I'm about to make a back-up.

This is much better than using phpMyAdmin.

Why would they be error riddled? I've never had a problem with mysqldump.

1) dump:
mysqldump -uXXXX -p > dump.sql

2) transfer:
scp dump.sql user@NEW_Server_IP:/path/to/newsite

3) Restore
mysql -uXXXX -p < dump.sql

yay!

Copying a directory is certainly the fastest way to do a restore and I've never had any issues with the integrity although I imagine mysqdump is better to use if you are transferring from one version to another. This is much more of an issue for large sites where a mysqldump import takes hours compared to a few minutes to move a compressed folder backup.

Just be sure to turn off mysql before backing up the folder!

Originally Posted by GeorgeB

I hope you all with dedicated or even VPS servers are backing up the MySQL /data directory instead of doing cumbersome error riddled SQL dumps

What's wrong with doing it that way?

That said I backup my mysql database completely, though I'm not on a dedicated.

Never had a problem using mysqldump.

Yeah I guess I should have stated that this is for larger sites. For smaller sites like forums with less than say 700 to 800 thousand posts I guess an SQL dump would be OK although there is still too much of a chance for something to go wrong IMO.

But for larger sites a mysql dump can take a LONG time and choke your server. They can even stop mid way.

The way I do it, simply tar'ing a directory and untarring it when you need to use it is the safest most efficient way to do it. No issues with import errors and not getting all your data.