Welcome to WebmasterWorld Guest from 54.162.226.212

Forum Moderators: coopster & jatar k & phranque

Message Too Old, No Replies

Backing up mySQL

Am I doing it the best way?

     
3:14 pm on Jan 10, 2003 (gmt 0)

10+ Year Member



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

3:17 pm on Jan 10, 2003 (gmt 0)

WebmasterWorld Senior Member nick_w is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

Nick

4:59 pm on Jan 10, 2003 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Not unless your host provides nightly backups of your webspace - which most do. Between the two you should be pretty well covered.
5:05 pm on Jan 10, 2003 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



That's the way I've done it. It seems to be the most comprehensive way to backup the mysql data and structure.
5:17 pm on Jan 10, 2003 (gmt 0)

10+ Year Member



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.

5:22 pm on Jan 10, 2003 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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.

5:25 pm on Jan 10, 2003 (gmt 0)

10+ Year Member



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

8:30 pm on Jan 10, 2003 (gmt 0)

10+ Year Member



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.
6:01 pm on Jan 11, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



It certainly is a viable way. Personally I just copy all files from mysqlds 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

6:52 pm on Jan 11, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.

6:54 pm on Jan 11, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month