July 5, 2006
About a month and a half to two months ago, I transitioned my site to a dedicated host.
I thought I had put in all my crons to backup my mysql databases.
Well, I went ahead and did a knucklehead SQL commit that changed a column I didn’t mean to! I was using the commandline mysql client and was executing a statement but had forgotten to escape a quote in the SQL command so it assumed the where restriction was part of the SQL and, it changed all the rows of the table to have the same exact text for that column.
First thing I said was “no big deal…I’ll just grab last night’s mysqldump since I hadn’t added much data since then…that’s when I found out my backups weren’t there…I looked up my old mysql files on the old server I had transitioned off of but there was a significant amount of data missing.
Thankfully, I was able to extract all the SQL from the mysqlbinlogs. The only problem was that the SQL statements had some updates and some inserts…and the inserts did not reference any sort of primary key within the table (they were auto_incremented).
I ended up writing a perl script that parsed the SQL statements, used other columns that, when combined, should be unique, and used those to regenerate and execute SQL against the table to update (rather than reinsert) the data.
I could have also used the older mysql files off of the old server and just rerun all the SQL from the binlogs but I didn’t want to risk losing an inserts I may have had during transitions or when I was moving around mysql files when I was optimizing my database when I first moved the files over.
Anyway, everything was 100% recovered, thank goodness!