Professional Web Applications Themes

mysql Online backups - MySQL

Hi Group, I am a DB2 DBA that is taking the dive into mysql 5.0.24a. My tables are all innodb. I require to take an online backup, flush the logs once I take it, then be able to restore from the image and replay the logs. I use the following backup command: mysqldump --skip-opt --add-drop-database --add-drop-table --add-locks --complete-insert --create-options -B [db_name] -e -F -h [IP_ADDRESS] -p -P 3306 -q --skip-quote-names -r [dumFile] --single-transaction -u root -v This works great, since I can then restore the database with: mysql -u {user] -p[password] < [dumpFile] And replay the binary log files with: ...

  1. #1

    Default mysql Online backups

    Hi Group,

    I am a DB2 DBA that is taking the dive into mysql 5.0.24a.

    My tables are all innodb. I require to take an online backup, flush the
    logs once I take it, then be able to restore from the image and replay
    the logs.

    I use the following backup command:
    mysqldump --skip-opt --add-drop-database --add-drop-table --add-locks
    --complete-insert --create-options -B [db_name] -e -F -h [IP_ADDRESS] -p
    -P 3306 -q --skip-quote-names -r [dumFile] --single-transaction -u root -v

    This works great, since I can then restore the database with:
    mysql -u {user] -p[password] < [dumpFile]

    And replay the binary log files with:
    mysqlbinlog [lbinary log] | mysql -u {user] -p[password]

    The above works fine, however I fear that it will not scale when my DB
    reaches several GBs of data (which we plan on it reaching), because I am
    doing it all in a single transaction to achieve a consistent DB backup.

    What are DBA's doing in production systems to have an online backup and
    then allow the replaying of the logs to reach a point-in-time consisten
    state?

    Thanks is advance!

    Christos Kalantzis
    Senior Database Administrator, System Administrator
    Intertrade Systems Inc.
    (450)786-8929
    com
    Christos Guest

  2. #2

    Default Re: mysql Online backups

    Christos Kalantzis <com> wrote: 

    No need to replay the logs. Mysqldump creates a consistent snapshot
    (think: full backup). A bunch of binlogs, starting at the time of a
    snapshot can be used to create another consistent snapshot (think:
    incremental backup).

    This is different from other databases where you get a dirty snapshot
    of the tablespaces at first and then use the logs to create a clean
    (consistent) state of the tablespace.
     

    The key is --single-transaction
     

    Aka restoring a full backup.
     

    Aka applying an incremental backup (roll forward).
     

    You're completely right. Logical backup like described above has
    several problems:

    1. it is single threaded - does not use the hardware well
    2. indexes have to be rebuilt; restore may take *very* long

    Depending on your hardware there is a database size limit for logical
    backup (and even more: restore). Of course this depends on your data.
    Very rough figure: expect problems at 10GB and more. If you have only
    few indexes and big columns (BLOBs?) even 100GB my be OK.
     

    The answer is physical backup. You could use some form of snapshot
    technology to get a snapshot of the tablespace and transaction logs.
    InnoDB will be able to recover from a dirty snapshot; if you manage
    to flush & lock all tables for the time to create the snapshot, even
    better. A very common backup strategy for MySQL uses tion to
    create a mirror of the database. This mirror can then be used to take
    i.e. cold backups.

    Especially for InnoDB tables there is a commercial hotbackup tool
    available from http://www.innodb.com.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

Similar Threads

  1. backups
    By Antoine Junod in forum MySQL
    Replies: 3
    Last Post: July 22nd, 07:40 AM
  2. HDR & backups
    By Dirk Moolman in forum Informix
    Replies: 2
    Last Post: October 1st, 09:11 AM
  3. Onbar Backups on 9.4
    By Venkatesh Konnur in forum Informix
    Replies: 0
    Last Post: August 15th, 04:12 PM
  4. BACKUPS
    By Harry in forum Windows XP/2000/ME
    Replies: 3
    Last Post: July 23rd, 01:14 AM
  5. RAC, CFS and online backups
    By Connor McDonald in forum Oracle Server
    Replies: 5
    Last Post: December 9th, 07:10 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139