SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2003
    Location
    Chile
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Backup with mysqldump takes hours, database is not that big

    Hello, I've been having some problems making database backups. I've searched all around and I notice that to most people it only takes a few minutes to backup huge databases (1+ gb).

    My database is around 350mb and it can take up to 4-5 hours to backup, which I guess it not normal at all.

    My server has a dual Xenon with 4gb ram, using mysql 5.0.27-1.fc6 and php 5.1.6-3.6.fc6. I am using Fedora Core6 with Plesk.

    The database is around 350mb, with around 1.1 million rows. To backup I use

    Code:
    mysqldump -uroot -p database > backup.sql
    As I said, the above takes hours. I guess it should take minutes?
    What am I doing wrong?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    mysqldump is not an appropriate method for regular mysql backups.

    are we talking myisam, innodb, or both?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2003
    Location
    Chile
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know its not the best way. What I find odd is that I've been googling around and people with databases around 50gb in size only take about 40 minutes to backup using mysqldump, and my 350mb database takes HOURS.

    Its MyIsam mostly.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, how about a zero-downtime backup solution for a mixed table set? set up a slave server. when you want to back up, shut down the slave process and take a snapshot of the files.

    there's a thread around here where i lay out the proper backup methods. let me see if i can find it...
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    http://www.sitepoint.com/forums/showthread.php?t=369890
    http://www.sitepoint.com/forums/showthread.php?t=510087
    http://www.sitepoint.com/forums/showthread.php?t=515752
    http://www.sitepoint.com/forums/showthread.php?t=501446

    also, i just realized that i had intended to write a "best practice" post about backing up a mysql server but i never got around to it. i'll have to get on that.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2003
    Location
    Chile
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure, but shouldnt mysqldump take only a few minutes on a such small database? I mean, if its taking hours its because something is wrong, and I'd like to know what.

  7. #7
    SitePoint Member
    Join Date
    Jan 2006
    Location
    USA
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried optimizing and gzipping the database?

    mysqlcheck -uroot -p -A -r -o

    mysqldump --opt -uroot -p database | gzip > filename.sql.gz
    Live each day as if it was your last.

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mijae View Post
    Sure, but shouldnt mysqldump take only a few minutes on a such small database? I mean, if its taking hours its because something is wrong, and I'd like to know what.
    Run `top` and look at the io/wa (iowait) stat. If it's high, then it's your disks or disk subsystem that is being saturated. If your sites are disk intensive, then you may just not have enough IO bandwidth to copy large files fast. Having the MySQL data directory and the backup destination on separate physical disks can help.

  9. #9
    SitePoint Zealot
    Join Date
    Aug 2003
    Location
    Chile
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its at 1.3% at most.

    And yes, I've optimized and tried to gzip/bzip2

  10. #10
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    1.3% during the backup? That's actually really low. What about load on the database, is it doing a lot of other stuff (like active websites) while it's trying to copy the tables?

  11. #11
    SitePoint Zealot
    Join Date
    Aug 2003
    Location
    Chile
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, not doing anything else except the backup.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •