Post reply

Adding new files to a log-shipped database (SQL Spackle)

  • June 7, 2013 at 8:51 am

    #1622103

    Thats one of the things I always liked about MySQL. The logs contained essentially just plain old SQL. If you needed to undo something that someone did you could just restore/recover and skip that statement in the logs. Unless subsequent changes were affected by that change (which was rare) you could back what you needed pretty easily.

  • June 7, 2013 at 2:58 pm

    #1622192

    chuck.hamilton (6/7/2013)

    Thats one of the things I always liked about MySQL. The logs contained essentially just plain old SQL. If you needed to undo something that someone did you could just restore/recover and skip that statement in the logs. Unless subsequent changes were affected by that change (which was rare) you could back what you needed pretty easily.

    I'm pretty beat right now from a rough week, but I can't see how that kind of logging can ensure rigorous data recoverability in the event of a restore. Different hardware can have VASTLY different performance characteristics (or even the same hardware depending on concurrent usage) and just allowing a stream of sql statements to replay sure sounds like it could allow stuff to COMPLETE out of order, leading to invalid data states.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • June 10, 2013 at 7:31 am

    #1622430

    I'ts really not much different than the way SQL or Oracle does it. DDL and DML is recorded in the log in the order in which it occurred. Recover it in the same order and you should end up with a database in the same condition as before recovery in regards to committed transactions.

    I don't think it's actually ASCII text that it uses to record the statements. Its actually stored as a binary file but there's a command used to translate it into SQL commands that get piped into the command line program.

    If you're thinking about non-deterministic updates, the log takes that into account as well. IOW statements like "update account set balance = balance + @x" will be converted to a deterministic value.

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply