homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

Backing up mySQL
Am I doing it the best way?

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

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?




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

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



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

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)

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)

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)

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)

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.



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

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)

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]



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

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)

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.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved