SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    What happens while I backup my database?

    Hello!

    I perform daily database backups to my production database. Currently the database is small so the backup takes just a second or two. However, there's a potential for a sudden spike in users over the coming months and I began wondering about what happens when I'm backing up my database with a mysqldump? Are users locked out of the database during that process? Is there some some sort of "queue" that gets handled after the dump? Currently I use shared hosting which may or may not affect the response.

    Thank you,

    Eric

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,868
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    If you don't use the --lock-tables parameter then the database will not be locked while the backup is running. Any updates in progress while the backup is being made have the potential to cause problems with only part of an update being recorded in the backup.

    Provided all the tables support transaction processing (eg. you only use innoDB tables) then you can apply a --single-transaction flag to the backup and it will generate a checkpoint prior to starting the backup and only backup to the checkpoint. Any updates that occur while the backup is running will be after the checkpoint and not backed up.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for a very clear and informative answer.

    -Eric


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
  •