Backing up mySQL

Am I doing it the best way?

[RussellC]

I am using mysqldump to backup my mySQL database. I have a cron job set up in linux to run it once a day then it gets backed up nightly on DLT. Is this the best way to go about backing up the database or is there a better way?

Thanks

[Nick_W]

Seems like the right tool for the job to me, though I'd be interested to see if Jatar or Andreas think differently...

Nick

[lorax]

Not unless your host provides nightly backups of your webspace - which most do. Between the two you should be pretty well covered.

[jatar_k]

That's the way I've done it. It seems to be the most comprehensive way to backup the mysql data and structure.

[RussellC]

I am my host, the DB is for our intranet...so I guess I am covered. Thanks. I was just checking if there was something that I wasn't doing correctly, or if I should watch out for any potential problems with my setup.

Thanks again.

[lorax]

If you're the host then I would encourage you to get a tape backup in place and get it to do the backups for you.

Heck, you could even get storage appliance (firewire or?) and use a cron job to copy the files to the drive at night. Then there's no messing around. Write the whole drive and all the files associated with the DB to the drive. If the production drive crashes - you've got a ready made spare.

[jmendenhall]

With the prices of drives so inexpensive, it is *much* better to backup to a local drive (for quick recovery) and a remote drive (for major disaster recovery). Tape is very inconvenient, takes more time to recover, and requires physical touching to manage. Not the best of breed for a backup solution.

JohnM

[RussellC]

I already do back-up to Tape (DLT). Linux is set up to run mysqldump once a day. Then the tape backup automatically runs at night on a Windows File Server and I have Veritas backup pick up the .sql file from the Linux Box as well as backups from our Exchange server as well.

[andreasfriedrich]

It certainly is a viable way. Personally I just copy all files from mysqld´s data directory. But then this is only my small little developement server.

Here is what the makers of MySQL have to say about Databse Backups [mysql.com]

Andreas

[ggrot]

I find mySQLdump to be pretty slow on large databases. If I turn off all running queries and run mySQLdump, ir takes a good 5-10 minutes. Granted I have millions of records, but still. If I leave queries running, it slows down to about a half hour and there is a read lock on the tables the whole time preventing any writes by scripts and really messing up the query queue. It also takes a long time to load these backup files back into the database as a big query dump. This is mainly because it is rebuilding the indexes during the whole process.

I need to experiment with hotcopy a bit, but I think copying the files themselves is the best option. The indexes would thus already be built.

[ggrot]

Also, you could try the update log option if you are really having problems. Just cron a copy of that log every so often, download it to another server and run those queries on the backup server. Since there are only write queries, no reads, it should be significantly faster.