homepage Welcome to WebmasterWorld Guest from 23.22.173.58
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
quick and dirty mysql backup question
can i copy the main directory and tar/compress it?
seoboy

10+ Year Member



 
Msg#: 3256009 posted 7:39 am on Mar 31, 2004 (gmt 0)

i've got a big mysql database (4G+) that has hit its default size cap, meaning i'm going to have to reorganize the database to accomodate the larger size (doh!). before i start the process, i kinda want to backup the db.

i know the preferred way to do this is via mysqldump but i think that will take forever given the db size. can i just copy the db data files to a new backup directory and tar -czvf that directory?

what are the major risks associated with this quick and dirty approach?
i'm trying to avoid having to get in my car and go down to the hosting center to physically download backups, in order to clear space... :)

thanks in advance for any input!

seoboy

 

SeanW

10+ Year Member



 
Msg#: 3256009 posted 1:51 pm on Mar 31, 2004 (gmt 0)

Hi,

If you shut down the database before tarring it you'll be all right.

Sean

martin

10+ Year Member



 
Msg#: 3256009 posted 7:28 pm on Apr 1, 2004 (gmt 0)

You can also use mysqlhotcopy - it's a perl script that does something similar while your database is running. It basicly flushes tables and locks them for the current process.

VectorJ

10+ Year Member



 
Msg#: 3256009 posted 3:00 am on Apr 3, 2004 (gmt 0)

rsync also works well for backing up the database files. Run rsync to back everything up, then lock down the database and run rsync once more to get everything current.

martin

10+ Year Member



 
Msg#: 3256009 posted 8:59 am on Apr 3, 2004 (gmt 0)

> ...then lock down

I'm not sure how good rsync is at diffing binary files but I wouldn't count on this speeding things up.

trillianjedi

WebmasterWorld Senior Member trillianjedi us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3256009 posted 10:41 am on Feb 18, 2007 (gmt 0)

Old thread, but this is a current topic for me and you can never talk too much about backing stuff up, so I'm bumping it.

I'm not sure how good rsync is at diffing binary files

It's brilliant.

I wouldn't count on this speeding things up.

Between 100 and 400 times faster.


wrote 4394533 bytes read 104210 bytes 101095.35 bytes/sec
total size is 628733048 speedup is 139.76
Finished at Sun Feb 18 07:44:33 GMT 2007

I'm doing a read lock flush, then rsync to a spare server.

Fastest backup system I've ever had for MySQL, and a lot less messy and more reliable than syncronisation which I've also played with (under MySQL 4.* anyway).

TJ

[edited by: trillianjedi at 11:49 am (utc) on Feb. 18, 2007]

mack

WebmasterWorld Administrator mack us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3256009 posted 11:10 am on Feb 18, 2007 (gmt 0)

Great thread.

Rsync is possible the only option when you're database is really big. Once had to back up an 80+ gig mysql db and rsync was the only option that seamed to work.

Started process, went to sleep, woke up to backup completed.

Mack.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
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