SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: mysqldump

  1. #1
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysqldump

    When I'm making a backup of mysql databases, during the dump apache is unable to load pages because mysql is no longer responding to new requests.

    The backup is taking 5 minutes or so, which essentially brings down the website until it's finished. Has anyone got any suggestions on how to overcome this problem?

    I'm thinking maybe if I could quickly make a copy of the database to another database within mysql, then backup the copy that was just made.

    Thanks.

  2. #2
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    how are you doing your backups? 5 minutes is very unusual...
    I'm doing backups on the command line using mysqldump and it's always very fast.
    You can also do your backups in the middle of the night using a cronjob, which will do the backup job for example daily on 04:00. This time the five minutes won't be a problem, as many big websites have their website offline for a longer time.

    If you just copy your database, this will not wait for table locks or transactions to be finished, so soon or later you will have failures in the database.

    Greetings, Andreas

  3. #3
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How large is your database?
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  4. #4
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm using mysqldump via cron every 8 hours. I just timed it properly and it took 3 minutes, to dump a database of 1.5GB uncompressed.

    I'm thinking if I were to put up a maintenance message during the dump, then I can probably get away with a little downtime if it must be. If possible though, i'd prefer to be able to create a backup without any downtime.

  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)
    with myisam tables, it's usually faster to copy the database tables than it is to use mysql dump. first LOCK the tables in question, then FLUSH the tables. you can then copy the .frm, .myd and .myi files directly out of the mysql data directory. finally, UNLOCK your tables.

    mysql actually provides a script for doing this called mysqlhotcopy.

    if you're using innodb tables, you should use innodb hot backup instead.
    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


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
  •