Binary Logs and saving data

Back when I started working with computers, you actually carved each character with a machine into oddly shaped pieces of paper. My FORTRAN class required up to two dozen of these carefully manicured pieces of paper for each program. The folks in the RPG class had litterally stacks of data one or two feet deep and my mind boggled at the amount of data these poor folks had to carry around with them. My best guess now is that they may have had up to two thousand records of up to 80 characters. My lowly FORTAN class saw the RPG programming class as demi-gods as they trudged along with trays of cards under their arms.

And then one day it rained. Data cards were sensitive to humidity especially the type of humidity you find in the bottom of a rain filled gutter, 12-edge down. My fellow students and I commiserated with one of those formerly envied RPG students as we viewed what was now a paper mache lump. The object of our empathy had tripped and dropped their card tray while trying to extend an umbrella on an rare rainy day in El Cajon, California.

The data recovery process was fairly simple. Fresh IBM cards were purchased at the campus book store and then feed into a bin the card duplicator. Then another student's card deck was placed in another bin. The card duplicator was then turned on and it made a loud staccatto blast as it attempted to make exact copies of every card. But the card duplicator was an old, electromechanical device that made what could be call a 'best effort'. Sometimes the card reading device would not find all the holes in the card. But to be fair, it would often inject unneeded holes in other cards in a frustrating karmic balance.

So, a few feet of cards were hand checked. It took the better part of a day but the RPG student did recover the data. The student was very lucky in that she had access to a replica of the original data.

So how does a MySQL DBA recover from the modern equivilent, say the programmer who forgets to add the WHERE ID_number = '999' to the DELETE FROM huge_dataset statement? Use the binary log!

MySQL's binary log records 'events' that could change a database. So SELECTs are not recorded but UPDATEs, DELETEs, and other statements are. Enabling the binary log will allow the server to record these changes into a file. You can read the file mysqlbinlog program.

So the programmer forgets the last half of the above statement and you have to rebuild huge_dataset.

Step 1. Stop the server. Make sure users can not connect while work to restore the table by the process you and company favor.

Step 2. Restore the needed table(s) from you last backup (you do backup your data, right?).

Step 3. Find the binary logs files that cover the time period from the last backup to shutdown. Pipe the output of mysqlbinlog to a text file. Append log output to this file. This text file contains the SQL statements that have potential to change the data.

Step 4. Find the offending DELETE FROM huge_dataset statement and remove it from the text file created in Step 3.

Step 5. Use the text file (\. text-file) as input.

And you are back in business.

Of course my five steps are a simplification. But the general idea is there. You may also want to check out the MySQL manual on 'safe mode' if you want to avoid 'DELETE *' issues like the one above.

MAX_ROWS

How well do you know MAX_ROWS?


mysql> CREATE TABLE cube2 ( cube INT(1)) MAX_ROWS=2;
Query OK, 0 rows affected (0.12 sec)


So I inserted a row INSERT INTO cube2 values(3); and then another with INSERT INTO cub2(4);. So what happened when I tried to insert a third row?

Did the server complain I was trying to fit a third row in a two row table? Did one of the existing values in the table get replaced?

Nope. The server gladly took the third row.

MAX_ROWS is usually used with MyISAM tables to change the row pointer size from the default to access more disk space. Using a MAX_ROW value absurdly lower than this pointer size is politely ignored by the server.

Drat! I I thought I had a cute trick to use on a certification exam.

Branded Guinea Pigs! Ye-hawwwwwwwww

My last post asking what people are looking for in being part of the Certified MySQL Community brought me a good deal of email. I will pursue looking into training and service contract discounts with my bosses. Mugs and t-shirts seem to be popular requests also.

I even had someone who saw my mention of beta exam testers as 'guinea pigs' and requested branded guinea pigs. Now, I live in Texas and two miles from my house there is a heard of Bison. A few folks in the area have cattle, mainly longhorns. A lot of livestock these days actually get implanted microchips but branding irons are still used for the recognition of livestock on the days the cowboys leave the RFID scanner out of the saddlebags. So I asked about branding.

My consultants on the subject were not inclined to favorably recommend branding Cavia porcellus. Having Sakila, the MySQL dolphin, and/or the letters 'MySQL' would probably be too busy of a design, especially on an animal that weighs about a kilogram.

So sorry, no branded guinea pigs.


Any other ideas?

LinkedIn's MySQL Certified Professionals Community

Mark Schoonover has asked me to take over the LinkedIn Group for MySQL Certified Professionals. We are still working out the details but I would like to ask the almost 200 members of that group and any potentially interested others what they would like to see this professional community become.

Personally, I am going to need a platoon of MySQL Exam beta test guinea pigs and this group looks like a great source for talent people.

But what do the members want?

Cisco gives accounts with CCIEs on staff a percentage discount on support contracts? They also get an escallated queue for any support calls. The logic behind that is that a CCIE would not call on something trival and would have triaged the problem. Is this something for this group?

Or do we need t-shirts? Mugs? A special room (rubber padded, drool absorbant carpet) reserved for us at the 2009 Users Conference?

What would you like?

So, feel free to respond. I will post this on the LinedIn group as well as my blog (appologies to those who have to read it twice!).

---

For those interested in joining, go to LinkedIn and search for the MySQL Certified Professionals Group.

August Certifications

August was a great month for MySQL Certifications. There are now fourteen new Developers, Thirty-four new Database Administrators, three Cluster DBAs, and a dozen new Associates! This was our best single month without a MySQL Users Conference.

So what about the rest of you without your MySQL Certifications? Try the sample questions for the Associate, Developer, DBA or Cluster DBA for free. Maybe you are closer than you realize.