Professional Web Applications Themes

Inconsisten Backup with mysqldump - MySQL

Hi, I'm running mysql version 4.1.20, with all tables in InnoDB. I'm having problems getting consistent backups with mysqlbindump and binary logs. I'm running mysqlbindump with the following options: "--quick --single-transaction --flush-logs --add-drop-table --master-data" I run mysqldump separately for each database, and then flush logs one last time. The databases need to be backed up separately because I may occasionally need to restore one database at a time. I am trying to use these backups to build a slave. So what I do is take all these dumps and load them into the database and then run the binlog that ...

  1. #1

    Default Inconsisten Backup with mysqldump

    Hi,
    I'm running mysql version 4.1.20, with all tables in InnoDB. I'm having
    problems getting consistent backups with mysqlbindump and binary logs.
    I'm running mysqlbindump with the following options:
    "--quick --single-transaction --flush-logs --add-drop-table
    --master-data"

    I run mysqldump separately for each database, and then flush logs one
    last time. The databases need to be backed up separately because I may
    occasionally need to restore one database at a time.

    I am trying to use these backups to build a slave.

    So what I do is take all these dumps and load them into the database
    and then run the binlog that flushed for that dump. Then when thats
    done I can start the slave at the final binlog and things are good.

    The problem I am having is that occasionally the backup is not
    consistent. When restoring on the slave I will get Duplicate Key errors
    running the bin log, so I look in the dump and in the log, and the row
    exists in the dump and is also inserted in the bin log. I though that
    the --master-data flag was supposed to make the log flush consistent
    with the dump, so this sort of thing shouldn't happen. I suspect that I
    am also having problems the other way, where a row is not in either the
    dump or the binlog, but I this is a lot harder to verify than looking
    at the same row in both locations.

    So, am i doing something obviously wrong, or making an assumption I
    shouldn't be?

    Thanks,
    magicrobotmonkey

    magicrobotmonkey Guest

  2. #2

    Default Re: Inconsisten Backup with mysqldump

    "magicrobotmonkey" <com> wrote:
     

    So far, so good.
     

    That's the problem. The binlog writer does not care about databases,
    it writes all transactions into a single log. If you need consistency
    between your tables and the binlog you have to backup all at once.
     

    You can split the dumpfile later. It's just plain text.
     

    What you could do:

    Image you have databases A, B and C. You backup those in this order.
    Since you use --flush-logs, MySQL begins a new binlog right after the
    point in time when a backup was taken. So a new binlog starts after
    you took the backup of A, B and C. Lets call those binlogs logA, logB
    and logC.

    To restore the complete database (A+B+C) you have to

    1. restore A
    2. replay logA (ignoring errors about nonexisting B+C)
    3. restore B
    4. replay logB (ignoring errors about nonexisting C)
    5. restore C
    6. start tion at logC

    To restore just A+C you have to

    1. restore A
    2. replay logA (ignoring errors about nonexisting B+C)
    3. replay logB (ignoring errors about nonexisting B+C)
    4. restore C
    5. start tion at logC

    I guess you got the idea now.


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

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

  3. #3

    Default Re: Inconsisten Backup with mysqldump

    Hmm... I think the way we restore it now is basically the same, as we
    restore the databases in order, and after each one run all the
    binlogs, but restricted to the database we just restored, so we dont
    get any errors.

    I thought, though, that running mysqlbinlog with those options makes
    the dump atomic with the log flush, so I can't figure out why the row
    would be in the dump and in the binlog. It seems like I should be able
    to restore from the dump and start the binlog at the position
    specified in the output when using --master-data, but that is not the
    case here.

    The other thing I forgot to mention that was really strange was that
    in the specific case where I first figured out what was going on, the
    last row in the dump was like the 300th statement in the binlog. I
    thought that was strange, because it seemed like it would be closer to
    the start of the binlog.

    I'm not really sure why it makes a difference if I back them all up at
    once or one at a time but I 'll give it a shot and see if that makes a
    difference. The difficulty is that this doesnt happen everytime I run
    a backup and backups take several hours to run.

    Thanks for the Reply,

    Magicrobotmonkey

    On Jan 28, 5:19 am, Axel Schwenke <de> wrote: 

    > it writes all transactions into a single log. If you need consistency
    > between your tables and the binlog you have to backup all at once.



    >
    > Image you have databases A, B and C. You backup those in this order.
    > Since you use --flush-logs, MySQL begins a new binlog right after the
    > point in time when a backup was taken. So a new binlog starts after
    > you took the backup of A, B and C. Lets call those binlogs logA, logB
    > and logC.
    >
    > To restore the complete database (A+B+C) you have to
    >
    > 1. restore A
    > 2. replay logA (ignoring errors about nonexisting B+C)
    > 3. restore B
    > 4. replay logB (ignoring errors about nonexisting C)
    > 5. restore C
    > 6. start tion at logC
    >
    > To restore just A+C you have to
    >
    > 1. restore A
    > 2. replay logA (ignoring errors about nonexisting B+C)
    > 3. replay logB (ignoring errors about nonexisting B+C)
    > 4. restore C
    > 5. start tion at logC
    >
    > I guess you got the idea now.
    >
    > XL
    > --
    > Axel Schwenke, Support Engineer, MySQL AB
    >
    > Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
    > MySQL User Forums: http://forums.mysql.com/[/ref]

    magicrobotmonkey Guest

  4. #4

    Default Re: Inconsisten Backup with mysqldump

    I don't know if it's defaulted on your installation but the 'extended
    inserts' mysqldump switch makes for much faster backups and restores,
    using the insert into TABLE.

    On 28 jan, 13:51, "magicrobotmonkey" <com> wrote: [/ref]

    > > it writes all transactions into a single log. If you need consistency
    > > between your tables and the binlog you have to backup all at once.[/ref]
    > [/ref]
    > [/ref]
    > [/ref]







    > [/ref]

    mastersox Guest

Similar Threads

  1. Backup & restore MySQL, not using mysqldump
    By Jerry T in forum PHP Development
    Replies: 2
    Last Post: November 15th, 09:49 PM
  2. Need mysqldump help
    By SergioQ in forum MySQL
    Replies: 0
    Last Post: September 25th, 06:06 PM
  3. Binary data transfer with socket.send inconsisten
    By drZZ in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 23rd, 12:55 PM
  4. [PHP] mysqldump
    By Marios Adamantopoulos in forum PHP Development
    Replies: 2
    Last Post: July 25th, 05:53 PM
  5. mysqldump
    By Marios Adamantopoulos in forum PHP Development
    Replies: 2
    Last Post: July 25th, 03:53 PM

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