MySQL Training

Last week I had the great opportunity to sit in on a DBA class session taught by George Trujillo. I know that most of the folks administrating and programming MySQL databases are self taught. Learning by the 'RFTM' has benefits but many lessons come directly from the education institution know as The School of Hard Knocks. And some knocks are much harder and larger than other.

Self-teaching is a valuable way to learn but a lot depends on the quality of the materials you can discover. The MySQL DBA class is well structured so that concepts are presented in easy to understand portions and build on each other to cover the information. It really beats trying to read the online documentation at three in the morning.

I was shocked and somewhat jealous that in a few minutes the lucky students in the Dallas class were carefully guided by George on the subject MySQL grants and privileges. My early DBA work was always in 'get it working and worry about how it works later' mode. This often left me struggling at odd hours trying to figure out why X worked for A but not for B, usually with B breathing over my shoulder and griping about a deadline. Learning the hard way is just that -- hard. The information that George so easily communicated to the class would have saved me hours.

The class manual is a thick book filled with information, drills, and best practices. Combined with George's dynamic teaching style, the lucky students were receiving a big boost in become better DBAs. Believe me, this beats getting X working for B late at night while B doubts your ability and parentage.

George introduced me to the class and had a small break to allow the students to ask questions about MySQL Certification. From the quality of their questions, it was obvious that this DBA class was giving them the skills and knowledge they would need to be very good MySQL DBAs.

So check out the MySQL Training pages and see if there is a class near you. The classes are carefully crafted and well taught. The time and money spent on these classes will pay dividends later hen you do not have to flounder around looking for the correct man page or blog entry.

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.

Updates, Labor Day, and Scotland

I am about to tackle updating the MySQL Certification Web Pages and need some help. The site needs a good cleaning, dusting, a little paint, and some re decorating. Please feel free to add your input.

The pages need to be streamlined to make it easier for candidates to find information about MySQL Certification. The promotion of MySQL certifications to recruiters, hiring managers, and personnel departments is one of my goals. And I want to start more of a community for those with certifications.

So I am going through my 'to-do' list, the emailed gripes, and all the other suggestions. But I am offering you a chance to add thoughts.

--

No North Texas MySQL Meetup with Labor Day next Monday.

--

I just returned from a week in Edinburgh with the MySQL trainers, ciriculum developers, and the folks that work behind the scenes to make it all work. Lots of goodies are being developed for you that will knock you socks off. These folks know their stuff and I recomend that if you are thinking of signing up for class that you will be in for a treat.

North Texas MySQL Meetup Aug 4th

The next meeting of the North Texas MySQL Meetup will be held on Monday the 4th of August at the Sun offices off the Dallas Tollway. We are covering MySQL basics by following the material for the Certified MySQL Associate exam.

We welcome all and details can be found at http://mysql.meetup.com/250/

Event Scheduler

Part of updating the MySQL Certifications is trying out all the new features that have appeared with 5.1 and 6.0. Currently I am working with 5.1.24-rc on a Mac and 6.0 on Ubuntu and looking for items to add to the exams. The Event Scheduler is sure to become one of those Swiss Army Knife tools that we will wonder how we ever accomplished anything without.

I usually experiment with very simple tasks.

CREATE EVENT x1_event ON SCHEDULE AT '2008-07-16 14:01:01' DO INSERT INTO x1 VALUES (9,'nine');

SHOW EVENTS did exactly that. And just after 14:01, I found the new row in my x1 table.

So now I am thinking of a dozen think I used to do with cron(1) or at(1) that I could do with the event scheduler.

Warning: There are ramifications with backups and the event scheduler that I will not go into details here. But for now do not try to run your backups with event scheduler. The engineers are doing their magic right now and will surely dazzle us with their brilliance.

So hit the Event Scheduler documentation at http://dev.mysql.com/doc/refman/6.0/en/events.html

Cluster Study Guide, Edition 2

My new edition of the MySQL 5.1 Cluster DBA Certification Study Guide arrived today. It features a bigger format, corrections, and much better graphics. And it is $10 less expensive.

The MySQL Cluster is just different enough from the 'old fashioned' server to be confusing to long time MySQL DBAs. This book points out common problems people have getting their clusters configured. So if you are curious about how clusters work and want more than the man pages as a reference, order you copy today!