Category Archives: Tools

What does pt-show-grants look like?

[note: A broken link from a defunct blog made me dig up this post from and re-post it here; Beginners may find this informative!]

The OurSQL Podcast did an episode on some of the lesser-known but very useful tools in the Percona Toolkit. pt-show-grants is one of those tools that I use pretty frequently. While the manual page has an explanation of all the features and a few examples, you dont really see the output, and often you decide whether or not to use a tool based on what it gives you as output.

So here is a small example of an actual command I did today using pt-show-grants. I wanted to find the grants for a particular user. To do that without pt-show-grants, Id have to login to MySQL, run

mysql> SELECT host FROM mysql.user WHERE user='aus4_dev';

And then use that host information in a SHOW GRANTS statement:

mysql> SHOW GRANTS FOR aus4_dev@HOST;

But I would have to do this for each HOST if there were 2 hosts, Id have to run the SHOW GRANTS command twice.

Happily, pt-show-grants has an option called only, which will show you all user@host combinations for the username you specify. I have login information stored in a .my.cnf on this particular dev machine, and except for the password and host, this is an exact copy/paste of what I typed and the output:

[scabral@dev1.db ~]$ bin/pt-show-grants --only aus4_dev
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.1.52-log at 2012-03-01 08:52:01
-- Grants for 'aus4_dev'@''
GRANT USAGE ON *.* TO 'aus4_dev'@'' IDENTIFIED BY PASSWORD '*1234567890ABCDEF1234567890ABCDEF12345678';
GRANT ALL PRIVILEGES ON `aus4_dev`.* TO 'aus4_dev'@'';
-- Grants for 'aus4_dev'@''
GRANT USAGE ON *.* TO 'aus4_dev'@'' IDENTIFIED BY PASSWORD '*1234567890ABCDEF1234567890ABCDEF12345678';
GRANT ALL PRIVILEGES ON `aus4_dev`.* TO 'aus4_dev'@'';

By default, if I did not put in the only, it would show me all the users that I was allowed to see. There is also an ignore option, so if you want to show all users except a particular username, you can do that as well.

Being able to find all user@host users and their grants given a particular username is very handy and eliminates the need to go into the database to find the hostnames.

[original post date: 3/1/2012]

Generating a MySQL Password

One of the services our database engineers provide is adding users to MySQL. We have some nice Chef recipes, so all I have to do is update a few files, including adding in the MySQL password hash.

Now, when I added myself, I just logged into MySQL and generated a password hash. But when my SRE (systems reliability engineer) colleague needed to generate a password, he did not have a MySQL system he could login to.

The good news is it’s easy to generate a MySQL password hash. The MySQL password hash is simply a SHA1 hash of a SHA1 hash, with * at the beginning. Which means you do not need a MySQL database to create a MySQL password hash – all you need is a programming language that has a SHA1 function (well, and a concatenate function).

And I found it, of course, on this post at StackExchange ( So you don’t have to click through, here is what it says – and I have tested all these methods and I get the same password hash. I have changed their example of “right” to “PASSWORD HERE” so it’s more readable and obvious where the password goes, in case you copy and paste from here.

Some one-liners:

**MySQL** (may require you add -u(user) -p):

mysql -NBe "select password('PASSWORD HERE')"


python -c 'from hashlib import sha1; print "*" + sha1(sha1("PASSWORD HERE").digest()).hexdigest().upper()'


perl -MDigest::SHA1=sha1_hex -MDigest::SHA1=sha1 -le ‘print “*”. uc sha1_hex(sha1(“PASSWORD HERE”))’


php -r 'echo "*" . strtoupper(sha1(sha1("PASSWORD HERE", TRUE))). "\n";'

Hopefully these help you – they enabled my colleagues to easily generate what’s needed without having to find (or create) a MySQL instance that they can already login to.

Put Me To Work For You

(The Executive Summary: I left my job last week, and I start working at The Pythian Group on Monday. Go to their website if you’d like to work with me, or with people just as knowledgeable as me.)

I get inquiries all the time about consulting. Folks are madly searching for experienced MySQL DBAs. The lure of a new environment is always tempting, however, working for any one environment has its quirks. In October I realized I was coming up on having worked 2 years at my job. That’s not a very long period of time, but it certainly was long enough for me to learn the environment and get stuck in a rut — mostly my rut was doing more systems work than database work.

I looked around for other places of work, and had a wonderful interview at an awesome company to boot. However, they were also a product company, and I’d decided that I wanted to move to a service company. That is, a company that provides database services to a wide variety of environments. A company that sells products still usually has one environment, or at least one environment that I would work in. With a service-oriented company, such as a consulting firm, I can gain the experience of many environments and many setups.

Learning from my co-workers is important too — too many shops have only 1 or 2 MySQL DBA positions, which leaves me with 1 or 0 MySQL colleagues at work. Being the big fish in a small pond has its advantages, but it’s also too easy to get caught in my own ways of doing things and not see another side of things.

So, I thought about where I’d want to work. There are many great MySQL consulting firms out there. I looked at the ones I knew about — most of the ones run by Planet MySQLers I ruled out because either I didn’t have the right skillset (I’m not a programmer, C or otherwise!) or because I thought there might be personality conflicts, or because I’d have to move.

That was a big one — having to move. I have to stay in the Boston area, at least for now. My husband and I discussed things, and we’re not willing to move right now. Also, I hate too much travel. Anything more than 10% really wears me down. And that, sadly, put MySQL’s own consulting out of the running. I just cannot travel that much.

So where does that leave me? Well, I called up The Pythian Group and we had a few hours of great phone conversations.

Next week, I fly to Ottawa for training! There will be a Boston office opening, but that’s after I finish 2-3 weeks of training.

I’m very excited to learn more about how The Pythian Group operates, as well as getting down and dirty with different environments, and solving lots of problems.

What’s in a Name? Everything!

Peter makes an interesting post about the MySQL company’s trademarks at

The point is that Peter is not selling “MySQL Support” — he is selling “Support *for* MySQL”. “MySQL Support” is the name of a product that MySQL offers. Even if some other consulting company used the name before the MySQL company ever did, MySQL still has the rights to the name.

I chose to name my podcast “OurSQL: The MySQL Database Podcast for the Community, By the Community.” I chose every word carefully. For instance, I call it “the MySQL Database Podcast” so that anyone looking for a podcast about “database” will find it.

I could have just called it “MySQL Podcast”. But if the company MySQL (AB or Inc) ever makes a podcast, they would do the same thing to me that they do to you. I have to distinguish it’s “a podcast about MySQL”, not “MySQL’s podcast”. In fact if you look for “oursql” references, there is actually software released in September 2001 called “oursql”, but it was only released once and I have only ever found a handful of e-mails about it.

Similarly with “Technocation, Inc”. I googled around for it and found that a Baltimore, MD USA paper has a column called “technocation”, and it’s similar to why I picked the name — technology + education. But there’s no way anyone would confuse the two.

Same thing as MySQL would do if they made their own toolkit. “MySQL Toolkit” is in fact a really bad name because it’s so generic. Right now there’s no confusion, because MySQL doesn’t have a toolkit. Same with the “MySQL Magazine”. If MySQL ever puts out a magazine, they’ll send a letter right away. I was actually worried that “The MySQL Guy Podcast” at would get a letter from them. After all, there are plenty of “MySQL guys” out there, and he doesn’t work for the company……(hence why I’m the “She-BA”, not “MySQL Gal”).

In fact, Microsoft seems to do this on purpose. They named their database engine “SQL Server”. I’ve been frustrated when I get Microsoft pages when I’m just looking for “something relating to SQL”. I’d much rather get something related to the SQL standard. Same with their “Windows Mobile” platform. Check out their list of servers on the right-hand side of the page at — if you’re looking for “security” on a Windows server, chances are most of your search result will be for the “Security Server” that Microsoft offers. Ditto with “Content Management Server” and “Data Protection Manager” and “Speech Server” and “Virtual Server” and “Small Business Server”…etc.

If you have questions about Intellectual Property (IP) or Patents in the United States, I highly recommend retaining services from the law firm of Bakos and Kritzer — It’s not just a law firm where my brother is a partner, it’s also a damn good one.

(Speaking of name, I will likely be changing my name in the near future to “Sheeri K. Cabral”, so if you see it around, don’t get confused. You can always find me at )

One Size Fits All

I was pointed to an article about how the “one size fits all” database model doesn’t work anymore — how Oracle, DB2 and Ingres were written so long ago, they’d have to be rewritten to meet the needs of today’s database users. Jacob Nikom pointed the article to me; apparently he contacted the author and started to explain how MySQL meets that criteria, but the author disagreed.

Read the article for yourself:

Anyone else notice the irony of saying “all those other DBMS’s aren’t a one-size-fits-all solution, but this one is?” I think that MySQL comes the closest to a DBMS that is NOT “one size fits all”, given the multiple storage engines available. What other DBMS will allow you to use your Amazon S3 account as a table? What other DBMS will allow you to use a .csv file as a table without importing? It’s not perfect, because there’s still a basic layer of functional implementation that the MySQL server handles (and must), but it’s the closest.

Note that the article is written by the founder and CTO of the software solution mentioned.

What If?

What if last week’s announcement by MySQL had been the following?

Some major MySQL distributors have brought some issues to our attention. These distributors have been legally distributing the Enterprise version of MySQL to folks that do not have a MySQL support contract, without their knowledge or consent.

When a user installs an open source operating system and includes MySQL, should that user get the Community version or the Enterprise version? Since the source code was split back in October 2006, MySQL AB has felt that users should use the Enterprise version only if they intend to. We have made the source easily downloadable, so folks that want to use Enterprise, are able to use Enterprise.

However, given that distributors have been doling out Enterprise to unsuspecting users, we decided to change a few things. Firstly, we recognize that folks may be downloading Enterprise because they do not understand that Community is what they need. MySQL has very good binary builds available, with very little to be gained by users compiling their own binaries. We hope that folks have not been frustrated by a build process when they could have downloaded Community.

Secondly, the MySQL distributors have brought about some issues which would need to be fixed if we want them to distribute MySQL Community. Those issues are:

1) Scheduled releases of MySQL Community code — we will now schedule Community code releases 4 times per year, to meet this request. In addition, we will release binaries for Community twice a year, as needed.

2) Feature stability — we thought we could implement community-driven features in current releases. Sticking in a new feature to a current release jeopardizes the stability of the feature in that release. Also, we haven’t quite managed how to do that, and it will be easier to patch a feature into a non-GA release.

3) Newly GA releases will have monthly binary builds — A release that recently went from alpha or beta to GA will continue to receive monthly binary builds and releases until such time as the release is stable. We expect this to be for several months after the GA is announced.

You might notice that the Enterprise source binaries have been taken down off This is because we wanted to make sure that the folks that were downloading Enterprise were doing so because they wanted to download Enterprise, not because they went to the download screen and thought “Enterprise” sounded better than “Community”.

How would you have felt if that were the announcement? Because that is how I read it. Maybe it’s too little, too late, but I really feel as though the negativity associated with the announcement came from folks who used the announcement as a reason to talk about why the split is a bad idea. There’s plenty of work to do to make the split better, but the announcement last week was so simple, and didn’t deserve all the attention and negativity it garnered.

OurSQL Episode 20: The Pythian Group

In this less technical episode we interview Paul Vallee, where he explains in depth about the Pythian Group.

Special thanks to folks who have linked to the podcast:

Episode 34, July 9th, in which Michael mentions OurSQL and hopes I haven’t “gone dark”. Nope!

Kristina Hadges, a web designer, linked to the podcast


The Pythian Group

Direct play this episode at:



call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:

Or use the Technocation forums:

Free As In Water

So, the open source community/mentality/legacy/mindset tends to be attached to the idea:

“Free as in beer” — for comparison’s sake, another meaning could be, “free as in speech”.

Wikipedia has a good explanation of this, making “free as in beer” equivalent to “gratis,” meaning “free of cost.” Whereas “free as in speech” is equivalent to “libre,” free of restrictons.

Now, I understand why some things cost no money but are restricted. I also understand why some things cost no money and are not restricted. I do not have a particular religion either way, I think each product’s business model can be different.

So I’ll present a third concept: “Free as in water.”

Water is a privilege. In many places, we turn a handle and clear, potable, disease-free water comes out. We can drink it, we can wash ourselves with it, and we can luxuriate in it, as in a bath. However, many people in those places take it for granted that clean, potable, disease-free water will always come out when that handle is turned. Water is a precious commodity, though very often not treated as such. Many of us in the first world have not experienced how precious water is, as have those in the third world (“developing world” is the politically correct term these days).

I was reading randomly the other day, and came across a blog post where someone asked, “should water be free in a restaurant?”

There were a few repeated responses:

1) No, water should not be free in a restaurant. The water does actually cost money, and owners of properties typically pay a water bill.
2) No, water should not be free in a restaurant. While the water may be “free”, the glass is not, and neither is the waitstaff who bring you the glass and refill the water.
3) Yes, water should be free in a restaurant. The amount of water used does not cost that much, and the waitstaff is there anyway, so do not cost more.
4) Yes, water should be free in a restaurant. It puts me off when water is not, and I do not go back to those restaurants.

As I was reading, I immediately thought of open source business models. Because open source is, at its core, “free as in water”. The product is the water — whether it’s server-side software like Apache or MySQL, desktop software (aka “freeware”), an operating system such as Linux, or programming software such as PHP, Ruby or Perl. Much like water in a restaurant, as compared to other drinks, the cost is negligible. For instance, the cost of water compared to the cost of a soft drink is much less in most places.

Similarly, the monetary cost of MySQL compared to Oracle, or the cost of Linux compared to Microsoft, is much less.

Should all software be free? Certainly, I don’t think everything on a drink menu should be free. Oracle and Microsoft have a lot of overhead, and at the end of the day families need to be fed. Much like in a restaurant, though, open source software is the result of one or more folks doing work. Some of that work is programmatic, and yes, like water to soda, the programmatic cost is often less than the programmatic cost of the commercial entities. However, the waitstaff is analogous to support. Why is it that nobody thinks “free refills on drinks” is a ploy to get a bigger tip for waitstaff, yet many people think offering a free product with for-pay support means that the product is shoddy and of course you will need support?

That clean glass your water comes in is equivalent to QA. And yes, it’s not perfect — occasionally we find a spot of lipstick on the glass from a previous customer, and send the glass back.

So I believe the open source legacy should be that it is “free as in water” — it should be ubiquitous, though we should treasure it because it is truly valuable and if it did become rare, we would lose a lot of quality of life.

Nobody thinks “If you give away free water, nobody will ever buy a drink!” Yes, free water is a bit disruptive, but there will always be folks that use SQL Server, with it’s fabulous data analytics, and Oracle, with its long history and name recognition — just as there will always be folks willing to pay US $10 for an alcoholic drink. It’s not that someone paying $10 for a drink, or $5 for a beer, or even $2 for a soda is dumb because they could have just had water for free. It’s that sometimes you need water, and other times you want a $10 drink.

Now, MySQL has the potential to turn water into wine, and in that case, it’s even more disruptive…….

So, thoughts on the “free as in water” concept?

Work With the She-BA

You’ve heard me on the MySQL Podcast at, now come work with me, the “She”-BA!

The company I work for is an online social networking/dating site. Our main product is for men seeking men in 87 countries throughout the world. We’re looking for another MySQL DBA, as designing schemas maintaining data integrity for our 1 million users (and growing fast!). The salary is dependent upon experience of course, but the company I work for pays on the high side of the industry standard for the Boston area.

Application Instructions
Please send cover letter, résumé and sample schema to, with “MySQL DBA” as your subject. The
sample schema should reflect your abilities, so if you send along a schema you would like to see improved, include a description of what you would love to do to that schema to make it better.

The fun “requirements”:

  • A schema of 1 database with 85 tables — all of which have an auto-increment id as their primary key — makes you cringe
  • Knowing the difference between InnoDB and MyISAM storage engines and when you might use them
  • You cry when you see field names like “ExtraInfo1” and “ExtraInfo2”
  • You want to poke your own eyes out when you see schemas full of varchar(20) default NULL
  • When someone says, “Can you add a [type, ie, INT, BLOB] field to the table for me?” your first response is, “Sure, why do you need it?”
  • You know that “data warehouse” is not a synonym for “replicated copy of the database” — and if you did not, your first thought upon reading that was “It isn’t? I wonder how the schemas would be different?”
  • When designing a schema, you draw out an ER diagram first (or are willing to learn how)
  • Thinking about what data will be stored is the most important thing to you when you’re creating a table, not how the application will access it
  • You are comfortable with a job that does not involve writing code in a procedural language, but are comfortable enough if you have to help debug code written in a procedural language that you could.
  • You answer the question ‘Do images belong in the filesystem or the database?’ with ‘it depends’ and can go through scenarios of when each one is appropriate. (Alternatively you say “I have no idea” and do some research)

On to the more boring description…..
Continue reading

OurSQL Episode 16: The Art of Innovation, Guy Kawasaki

I take the easy way out again this week by sharing Guy Kawasaki (of the How To Change the World blog) and his irreverent and truthful keynote at the 2007 MySQL Users Conference.

Kawasaki will challenge your thoughts about being an entrepreneur in the technology industry.

The big news is that soon I’ll be able to announce that the videos from the conference sessions are up….stay tuned!

Show Notes:
Guy Kawasaki’s Blog: How to Change the World

Direct play this episode at:

Download all podcasts at:

Subscribe to the podcast at:



call the comment line at +1 617-674-2369

use Odeo to leave a voice mail through your computer:

Or use the Technocation forums: