Backing up a MySQL Database

[thing3b]

How do people back up their MySQL Databases?

Until recently I have been backing up my database through PHPMyAdmin, but it is talking more and more time to generate and download the file.

It seems strange for me to use something like MySQL Dump, or other command to take a full copy of the database every week.

I know that MySQL is capable of managing replication it's self, but as my server is not under my control I can not do this.

My webhost offers SSH based MySQL access, and I was thinking that I would be able to syncronise a local copy using that.

I have tried programs like:
- EngInSite MySQL Client
- Navicat MySQL
- SQLyog Enterprise
but all on them seem to have a problem with slow internet (and cost money). They worked fine on a faster connection.

Do people have any recomendations for backing up a MySQL database across an Slow Dialup Internet Connection?

(Is there anyone else here still on dialup, 56k internet?)

[txbakers]

I cringe at the thought of using dialup ever again.

I'd rather read a book and wait for the broadband to come back on.

I use mySQLDump to make a full dump every night at 2:00 AM, then put that in a zip file and send it off site in the morning.

replication is OK if you are only using myISAM tables, and even still might not be accurate as many replication programs won't read open files.

[zomega42]

I used MySQLdump until the database got too big, then switched over to mysqlHotCopy (which requires access to the raw files on the db server but is much faster)

[thing3b]

until the database got too big

zomega42, could you tell me how big was too big?
Are we talking 100MB, 500MB or 1GB as being too big?

[zomega42]

I stopped using mysqldump after about 500 MB. It didn't actually stop working -- it just got too slow. It was eating up the db server for minutes. With mysqlhotcopy, it just locks the tables, copies the raw files, then unlocks the tables. Mysql server doesn't need to be restarted, and the whole backup goes as fast as your drive can copy the file.

Hotcopy would be useless over a network connection -- you would have to hotcopy it onto the server disk, then download it from there.

[thing3b]

Thanks for all the help guys. I have been scheming and have decided that:

Depending on what my webhost allows me to do, I can create a zip file using mysqlHotCopy (or mySQLDump if I have no other choice) via a cron job-ish thing.

I can then have hosted backup file storage (or second website) with a different host (Maybe even my local ISP, gasp). Once a week I can update my backup host (should be fast) and then when I get very bored, I can spending the week downloading a backup, or if I do not get it downloaded, at least I have an off site copy.