Category Archives: MySQL

The Reborn MySQL Community

The Percona Live MySQL Conference and Expo ends today but it signals a rebirth of the MySQL Community. 2014 has been the most vibrant, upbeat, and cheerful show in many years. A multitude of new technology, approaches and energies emerged this year.

WebScale is partnership of several of MySQL’s biggest users to pool patches to make a bigger, badder server. Frankly these companies have more resources than MySQL in some areas and will be able to add in features very quickly. And Oracle really wants to add these changes quickly.

Fusion-IO is changing the way we think about writing to disks. The costs of a write is low and the speed is very high. For those of us having cut our teeth on systems where you had to plan for rotational delay and disk arm movements, this is almost spooky. SSDs are going to change many design ideas in the database world and Fusion-IO is working hard with Percona, Oracle, and MariaDB to remove what was a major choke point for performance. I urge you to check out Nisha Talagala keynote, especially if you are not hardware savvy, to get a better understanding of this eveolution.

Oracle’s investment in MySQL was shown by the many new features announced. MySQL Fabric, Workbench 6.1, and the approaching 5.7 are vibrant tools that the community needs.

Peter Z and Robert Hodges both were very frank that users need to upgrade to MySQL 5.6 as it fixes many old lingering problems and provides better performance. By the way, Percona is doing very well and a growing their businesses and Continuent has added Hadoop feeding features to their product.

And MariaDB had their new release ready for this show. Everyone has been very busy.

For the consumer spectrum of the MySQL Community, this is a golden age where competition and renewed innovation are making the latest products much, much better. The addition of WebScale will provide yet again more access to better performance.

Many folks said this year, the Percona Conference felt like a family reunion. But this year, it seems that a new generation is being welcomed into the MySQL Community.

Leave a comment

Filed under MySQL

MySQL and the Entity Framework

I am in Las Vegas this week at the Live 360 Conference for Microsoft Developers. There is a lot of interest in using MySQL with the Entity Framework object-relational mapper and I have been pointing to Using an Entity Framework Entity as a Windows Forms Data Source. So for all you who asked, here is the blog post I promised with relevant link. Entity Framework AND MySQL

1 Comment

Filed under MySQL

MySQL February Webcasts

Monitoring MySQL at Scale
Wednesday, February 19, 2014

50 Tips to Boost MySQL Performance
Wednesday, February 26, 2014

Thursday, February 27, 2014

Leave a comment

Filed under MySQL

Looking to Hire a MySQL DBA or Developer?

“Why can’t I find an MySQL DBAs or Developers?” This morning I got a message from a very perplexed Human Resources person on why their ads on Linkedin were not getting any results. Several such emails, calls, or messages make it to me each week and I would like take this opportunity to cover this subject. MySQL DBAs and Developers are out there but there are reasons why they are not interested in your job posting.

1. Provide details — “Exciting new position in rapidly growing start up in an expensive city and we want you to know how to program in every programming language, be a recent university graduate (hopefully PhD or higher), with ten years of experience but please be under twenty three years of age. Must prefer stock options and left over pizza crusts over a regular salary. And be flexible.” No, most ads are not quite that bad but several are very, very close.

Spell out what you want — A DBA or Developer is judging you on your ad. Companies that write poor ads tend to have poor mission statements, memos, and other general communication faults. Put in the general requirements such as number of servers, seven-twenty four three sixty five operations, general industry, and what good/service you provide. Then go heavy on what your candidate should have for skills. If you need a Developer to write in Java, Perl, C++, and have a smattering of Javascript as a need state those up front and put your want list later.

2. Be realistic — “Great opportunity in McMurdo Sound Antartica, but no relocation.” If you are in a small market, you need to chase down local and nearby talent pools. Check with local user groups — MySQL, PHP, Linux, etc. Sometimes someone with experience with another database is looking for greener pastures. Check with local schools, consultants, and ASK YOUR STAFF.

3. Grow your own — Does someone in your organization have some skills over lap or, more importantly, an interest in the subject? Training someone already hired may seem expensive because your training budget is cut to the bone but it can be cost effective if you look at the time and costs of drug screens, background checks, on-boarding process paperwork, etc.

Looking only for the pot of gold means you will miss a lot of rainbows.

4. Developers like to develop. Many prefer to maintain or re-factor older code. If you are looking for a bright shining programmer and stick them with legacy code of dubious worth, you will take the shine right of them. Other Developers love to create from scratch. Realistically present how much new coding versus support code is required or you will lose the Developer and have to start again from square one.

DBAs like to admin databases. If your schemas have been de-normalized, broken apart, reassembled, and then de-normalized again but you expect a DBA to wave a magic wand to provide high performance during their first hour on the jobs — you need an attitude adjustment. DBAs can work miracles but having to be Sisyphus ten hours a day will not attract the talent you want. If things have ‘gone to Hell in a hand basket’ you need to mention this in the pre-interview or interview.

5. Do not stick with ‘the DBA, he must’ — Many ads are written using male pronouns exclusively. I am a big fan of the epicine ‘he’ but many ads are written in a way that instantly turns off female job seekers. There are many talented women who want a chance but are not going to single handily battle a Neanderthal group when there is more demand than supply. This is not political correctness — it is keeping you from cutting off a high percentage of qualified candidates.

6. Yes, mention pay scale. MySQL DBAs and Developers are in demand and spending a day interviewing only to find that the jobs pays very low is frustrating. MySQL is not a toy database and going cheap is going to cost you sooner than later.

7. Answer your responses. If someone answers your ad, send an acknowledgement even if it is a simple ‘We got your resume’ reply. Hearing nothing back while their resume makes the circuit of HR, Senior Staff, Project Staff, Junior Management, and hiring managers for a few months will discourage anyone. The candidate will more than likely take another job while you wait for the feedback from the manager who is on an extended tour of the other hemisphere.

8. Target your ad — “The DBA must be able to program in C, C++, C#, RPG, COBOL, Javascript, Ecmascript, PHP, ALGOL, assembler (68000 and 6502), and R” is not going to catch the eye of any DBA. IF you want a Developer who can also admin your MySQL instances, please state that in your ad.” If you want someone to mainly be a DBA but be able to help find fault in code, you need to acknowledge that.

9. If you ad draws ZERO responses, it may be your ad not the group where you posted.

I generally refer folks to the Jobs board on and their local user groups. Linkedin is good but some of the various MySQL-centric groups seems to have almost as many HR staffers as MySQL professionals.

Leave a comment

Filed under MySQL

MySQL 5.7 & Workbench 6.1 Query Plans

MySQl 5.7 and Workbench 6.1 Query Plan

MySQL 5.7 and Workbench 6.1 work together to provide an even prettier version of a query plan than the impressive stuff from the 5.6/6.0 combo

Here is a sneak peek at MySQL Workbench 6.1′s VISUAL EXPLAIN.

Recently I was demonstrating the difference between using EXPLAIN and VISUAL EXPLAIN to a full room at the fantastic SkiPHP Conference in Salt Lake City. MySQL 5.6 and Workbench 6.0 combine to make an easy to read graphic that aids in understanding the Query Plan Generated by the Optimizer. All in the audience agrees that the ASCII-ish output of EXPLAIN paled in comparison to VISUAL EXPLAIN. Now MySQL 5.7 and Workbench 6.1 work together to provide an even better VISUAL EXPLAIN.

I really meant to test 5.7/6.0 on the plane on the way to SLC but did not get around to it. If I had known, I would have covered the new VISUAL EXPLAIN at SkiPHP. So those who attended, please accept my apology!

For those who do not know, VISUAL EXPLAIN takes a query, such as
SELECT Country.Name, City.Name, Language
FROM CountryLanguage
JOIN Country on (CountryLanguage.CountryCode = Country.Code)
JOIN City on (City.CountryCode = Country.Code)
WHERE IsOfficial = 'T' AND City.Population > 1000000;

and generates a graphic as seen at the start of this blog post. There are lots of great docs on using EXPLAIN, and some on VISUAL explain. Now I am eagerly awaiting the Workbench 6.1 docs to learn more.


Filed under MySQL

Watch Todd Farmer at San Francisco MySQL User Group

Todd Farmer’s MySQL 5.7 talk can been seen at For those few of you who did not stay up to watch Todd live, you get TWO slide decks in just over ninety minutes — it is almost the next best as being at the San Francisco Users Group in person.

Leave a comment

Filed under MySQL, User Group

How to get MySQL Critical Patch Updates and Security Alerts notices

Beware of bugs in the above code; I have only proved it correct, not tried it.
Donald Knuth

Bugs in software are a fact of life. MySQL, as part of Oracle, issues of Critical Patch Updates and Security Alerts notices. You may have seen Daniel van Eeden‘s blog on the January announcement.

Daniel’s summary:

For MySQL 5.6 you should upgrade to 5.6.15
For MySQL 5.5 you should upgrade to 5.5.35
For MySQL 5.1 you should upgrade to 5.1.73

But you probably missed the executive summary.

But how do YOU get this information when it become available? Subscribe here for Critical Patch Update Alert E-mails. You will need an Oracle Technology Network account (free) and please note that there are more than just MySQL information in the alerts as it covers all Oracle products.

Example of subscribing for alerts

Example of subscribing for alerts

It will take you just a few moments to sign up.


Filed under Administration, MySQL, Security

Upcoming MySQL Talks

It is the start of the traveling season for the MySQL Community Team and many of the engineering teams. I will be at Skiphp this weekend,
Sunshinephp, The Los Angeles MySQL User Group on Feb. 20th, and SCaLE. Morgan Tocker will be at Confoo, FOSDEM, and PHPUK.

You can see some of our engineers at Percona Live.

    Oracle talks at Percona Live

  • “MySQL 5.7: Core Server Improvements,” Morgan Tocker and Rune Humborstad, Oracle
  • “MySQL 5.7: InnoDB – What’s New,” Sunny Bains, Oracle
  • “Sharding and Scale-out using MySQL Fabric,” Mats Kindahl, Oracle
  • “MySQL 5.7: Performance Schema Improvements,” Mark Leith, Oracle
  • “MySQL 5.7: Performance & Scalability Benchmarks,” Dimitri Kravtchuk, Oracle

And do not forget to stop by the MySQL Demopod at Collaborate in Las Vegas this April.

Leave a comment

Filed under community team, MySQL

Even More on MySQL Password Expiration

So how would a DBA set up a system to expire the password of interactive MySQL users? Some kind folks have been reading the last few entries of this blog and asked me to flesh out how to go from concept to production and we will work this out together (feedback please!) over the next few postings.

The first step is to find out what information we have and then determine what information do not have. We have the account name, the password, and if the password is expired. But we do not know the interval for password expiration. We might want to keep track of when the last change was made, possibly keep a list of the last N passwords to check against to discourage reuse, and maybe have a black list of words not to use as passwords.

In the mysql.user table we find the account name, the password, and if the password is expired or not. But we need some new tables to hold the data we do not have yet. I have seen some customers customize their instances by adding this sort of data into their mysql database. Logically it is a great place to keep the information but you kill yourself keeping these new tables and fields updated at upgrade time. You never know what conflicts you will ran into in the future. So we will grab what we can from the mysql database and start another database.

CREATE DATABASE password_meta;
USE password_meta;

So we now have a table for meta data, created an entry for the life span of an password (password_lifespan), and set up a default value of 90. We can add other columns for information about any password work we need to store later.

Next we will need a list of users. More specifically a list of users who have passwords that have to be changed. Do not have application passwords included as your applications are most likely not going to know how to change a password (but is a good idea to change ALL passwords on a regular basis). So where we end up with a table that will have the user or account name that we will grab from mysql.user and will be a primary key. As we create this from scratch, we can simply copy this data from the user table by hand, if there are a few users, or utilize some sort of script. Once we get this into a production ready mode, we will need a way to make sure new interactive users get added to this new table via some sorted scripted event, stored procedure, or a TRIGGER to take the burden off the DBA to add new accounts to the new table(1).

So we have a list of users who have password that expire. Obviously we will need some sort of date that records when the password was set? Or do we calculate 90 days from the day the account was established? And yes, you could store both. My preference is to keep the last change and calculate the expiration date.

password_last_set DATE,

Do we need to keep track of old passwords? We will need a table that keeps track of the username, old password (hashed), and lets keep a date. Later we can check for the last N records of old passwords sorted by most recent date to cut down on password reuse

CREATE TABLE old_password (User char(16) COLLAGE utf8_bin NOT NULL,
password_change DATE,
Password char(11) CHARACTER SET Latin1 COLLATE latin1_bin NOT NULL,

You probably noticed the collation information in the last two tables. These were copied from the mysql.user table on the instance being used. You probably should be storing data in UTF8 as a general principal. Someday all the old Latin1 qualifiers will have to make room for the richer character sets. And when Latin1 is no longer a standard, the above code will need to be changed to reflect these changes.

But what if you are using a password plug-in that stores more than 11 characters worth of data? Well, you will have to modify the old_password table to match your environment.

So we now have a very flimsy set of armatures to hang some data from and next time we will start adding some data and some rough code as our next step.

  1. The idea is to work smarter not harder and adding an extra step to your account creation process is usually not the way to work smarter.

Leave a comment

Filed under MySQL

More MySQL Password Expiration

MySQL Passwords have evolved with versions 5.6 and 5.7 and we now have ways to ensure strength and expiration. There are some ‘tinker toy’s missing that keep it from being a complete system.

We do have a way of expiring passwords and forcing a user to change their password. But there is now way for the database to warn users that their password is about to expire or has expired. There is no way to check to see if if user changed their password and then changed it back to their ol’ favorite. There is no way to see when the password was changed last or any time before. There is no way to force these changes every X period or make sure some accounts do not change (root, accounts used for applications). Now of this is extremely complex to create and over a few blog posts, you will get a chance to help design such a system.

But for now we have to determine some items are engineered such as do we keep users from reusing passwords (and for how long)? Do we note changes someplace (SYSLOG, table)? Can we use the new password dictionary to block easy to guess passwords? How much warning do we give for changes and what do we gather for management reporting? Should we ad tables to the mysql database or create something new? I know the MySQL Community is not shy so please share your views. So sound off! Let me know your views (or needs) and we get something roughed out logically next time.

1 Comment

Filed under MySQL