Category Archives: 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

Expiring MySQL Passwords and Setting Password Strength

MySQL 5.6 introduced the ability to expire passwords. Many work environments have rules where it is mandatory to change passwords on a regular basis. It is easy to expire a single account with a ALTER USER 'dave'@'localhost' PASSWWORD EXPIRE; command.

mysql.user tabe

The mysql.user table

The mysql.user table now has a PASSWORD_EXPIRED column. A user attempting to login with an expired account using a client that supports, they will be prompted to change their password.

Forced to change password

An example of being forced into sandbox mode and changing the password. Note that user does not have SUPER or other admin level privs to change passwords.

SET PASSWORD does not check to see if you are reusing your old password.

You will not want to expire passwords for accounts used in applications automatically. It would be possible to write an application smart enough to handle sandbox mode and implement an acceptable password but my opinion (and probably mine alone) is that would be less hassle to plan manual updates on a regular basis.

There is a password validation plug-in plugin that lets you tune password complexity and provides for a password dictionary (a black list of words NOT to use).

An example of a MEDIUM strength password setting where one upper case, one lower case, one numeric,  and one special character are in the pass phase (of a settable length).

An example of a MEDIUM strength password setting where one upper case, one lower case, one numeric, and one special character are in the pass phase (of a settable length).

There are three levels of password checking — LOW, MEDIUM, and STRONG with MEDIUM being the default. Change the value of validate_password_policy as required. These represent increasingly strict password tests. The following descriptions refer to default parameter values; these can be modified by changing the appropriate system variables.

LOW policy tests password length only and it must be at least 8 characters long.

MEDIUM policy adds to LOW with the additional conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.

STRONG policy compounds the MEDIUM setting with the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.

So what do you do if corporate rules require that interactive passwords are changed every XX days? Well, come back to the next entry of this blog.


Filed under Administration, DBA Tools, MySQL

Oracle Linux 6.5, MySQL 5.5, 5.6 & 5.7

So how do you get MySQL 5.5, 5.6, or 5.7 on the latest Oracle Linux? Morgan had a great post on Installing MySQL 5.7 DMR3 with the official yum repos. This blog is about uprading from Oracle Linux 6.4 to 6.5 and getting a recent version of MySQL installed.

The first step, if you are running Oracle Linux 6.4, is to type yum install to upgrade to 6.5. Be sure to read the release notes and that you are pointing to the Public Yum Repository.

Use can use the Add/Remove Software tool to install MySQL 5.1. But who wants to run an old version of MySQL on a new, hot Linux. But let’s pretend you did install 5.1 when you installed 6.4 and now you are all sixes and sevens. So what do you do?

I highly recommend Morgan’s approach as it is slick. But I have found RPMs fussy and did the install manually. By the way, you DO have to upgrade 5.1 -> 5.5 -> 5.6 -> 5.7. My personal preference is to remove the older version RPMs, install the newer RPMs, and then run mysql_upgrade. Do not forget to run mysql_upgrade. As speaketh the manual mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

It might seem a bit of a pain but you do want to follow protocol.

  • 5.1 to 5.5
    1. Remove 5.1 RPMS
    2. Install 5.5 RPMs
    3. Run mysql_upgrade
  • 5.5 to 5.6
    1. Remove 5.5 RPMs
    2. Install 5.6 RPMS
    3. Run mysql_update
  • 5.6 to 5.7
    1. Remove 5.6 RPMS
    2. Install 5. RPMS
    3. Run mysql_update

Remember 5.7 is not ready for product yet and should only be used for evaluation.

Leave a comment

Filed under MySQL, Oracle Linux

How to get the MySQL Community Team at Your Local Event

December is half way through the budget year for the MySQL Community Team and we carefully meter out our small budget to make the most effective use from it. But December is also the time when existing conferences start the tasks of preparing for next year and brave souls who have never been part of a conference start out from scratch to create a new show. The MySQL Community Team would love to help with your conference.

First we need to know about your conference. We do scour the announcement sites like and but your show may have been inadvertently skipped. Tell us the dates, the location, how many people you are expecting, your target audience (PHP Developers, DBAs, left handed actuaries from the Pacific Rim), and what makes your show unique (at least for your area). We spread the word about shows to our MySQL ACEs, MySQL Employees, and anyone else we can reach. Left us know when your Call for Papers opens and closes!

Secondly, what sponsorship levels are available and what comes with those levels? How many people can we have in our booth, if we get a booth? Do we still need to buy entry tickets? Do we need to rent tables, carpet, electrical outlets, and chairs for the booth from a specified vendor? Can we have folks from the local MySQL users group in the booth with us? Oracle covers our travel bills but sometimes you need X bodies in N hotel rooms for conference room discount Z, so let us know if you need us to be at your conference hotel.

We love to speak about MySQL and if you have special subjects you want covered for your audience, be sure to let us know! Some of the best conference are the small, intimate events. We also try to visit local MySQL User Groups so having on of those nearby your conference helps too.

Payment can be tricky. If you are in the Oracle PO system (If we have sponsored your event in the past you probably are in there under your DBA — ‘Doing Business As’ or your legal name), it can be a fairly simple process. If you are not in the PO System, the corporate credit car d is used which can get tricky outside of the US or you want to be paid through Paypal(1) and this may take some extra time plus effort on both our parts.

Lastly we have only three MySQL Community Managers and we work hard to split up the shows between us. However we can only cover so much territory. Also Oracle expects us to be at certain events like Oracle Open World, MySQL Connect, Collaborate and a few others so hopefully you do not over lap in the calendar with those. We may be able to fall back on the MySQL ACEs in a few cases. So the earlier we know about your show, the better.

So we on the MySQL Community Team look forward to seeing you at your event, handing swag away from our booth, and talking about MySQL to your attendees. Ping me at Dave.Stokes AT please.Bertran

  1. Paypal sometimes has odd fees or requirements for payments to third parties especially those with new or unverified accounts. I love Paypal and am a long time customer but you may want to set up some other credit card processing procedures when you sent up the accounts for your conference.

Leave a comment

Filed under community team, MySQL

MySQL and Password Security

The first thing any MySQL user learns is mysql -u user database -p to use the mysql client program to connect with an instance. In this case the user will be prompted for the password and the given password will be encrypted before being passed to the server as part of the authentication process. Some folks (and the majority of scripts) will use -p password on the com
I yanked out the binary atamand line which is less secure as the password is in plain text for anyone privileged or sneaky to see. You could put the password in the file but once again the password is there in plain text.

MySQL 5.6 introduced the mysql_config_editor that stores authentication credentials in an encptryed file named .mylogin.cnf in your home directory. MySQL client programs can read this file. No exposure of the password on the command line or in an environmental variable. This file is readable only by the user and not by others.

As found in other MySQL option files, the .mylogin.cnf file is made up of option groups and each group is a login-path. This file is read FIRST which means you need to make sure that your other option files do not reset what you have set.

To look at the file, use mysql_config_editor print -all
mysql_config_editor print --all
user = dstokes
host = localhost
user = root
host = localhost

A simple concatenation will show gibberish.

Each section, in the previous example local and root, has the needed credentials for connection to a server. Note the password is there and encrypted but not displayed.

Invoking mysql –login-path=root world passes on the information from the appropriate login-path. This information can contain all the usual players such as hostname, socket, and port.

So let us set up a root connection to a test server.

mysql_config_editor set --login-path=testserver --user=dave --password
Enter password:
$mysql_config_editor print --all
user = dstokes
host = localhost
user = root
host = localhost
user = dave
password = *****
host =

There are corresponding arguments to remove entries, or to reset the file.

So you get better security or an easier way to set credentials for your users.

1 Comment

Filed under MySQL

Kuali — Open Source Model Evolves

Kauli – Open Source Grown Up

Kuali is proof that the Open Source model works for more than nerds and geeks. Kuali is a movement where colleges and universities pool resources to develop the software they need to run their institutions. The parties involved provide money and bodies to work on the various projects. And if an school needs a feature in a hurry, they provide money or bodies to get the work done. This is a highly collaborative effort spans the globe.

Many of these same schools found themselves locked into very expensive software that had to be extensively tailored to meet their needs. Each upgrade was an expensive and time consuming process in an era of shrinking staffs and budgets. Regular upgrades were needed to support changes in regulations or latent needs. The cost of upgrades and customization was taking too much of scarce funds. This drove them into an open source model where all contributed and participated.

Kuali is made of of several major, standalone modules that handle items like student admissions, research tracking, or finances. The software it written to be ‘agnostic’ to avoid any vendor lock-in. For instance, the code does not depend on any particular relational database and strives to be as neutral as possible. Many develop on MySQL and I am happy to report that over a dozen have or are about to go into production with MySQL.

The value of the open source model past the LAMP stack world is just now emerging. The schools involved not only get software that meets their needs but also get access to the best minds working on their problem.

Leave a comment

Filed under MySQL, Open Source


Every MySQL DBA has at least peeked at a mysql.user table. But with the latest versions come some changes that many may have not noticed. The last three of the forty three columns — plugin, authentication_string, and password_expired — fields deserve a closer look.

First off, lets look at the entire table that is the output of DESC mysq.user run in MySQL Workbench and only the last few lines are shown for the sake of clarity.

The password_expired field is simply set to ‘N’ if the password is expired.
ALTER USER 'joeuser'@'localhost' PASSWORD EXPIRE;
The use will receive a message that their password has expired and they need to set a new one IF their client supports resetting password. The account is is “sandbox” mode where the use has only the privileges needed to reset the password. Using SET PASSWORD will turn off the password expired flag. See

What if the client being used can not handle “sandbox mode”? That depends on your setting for disconnect_on_expired_password. By default this setting is on and the server will reject the connection with an ER_MUST_CHANGE_PASSWORD error. See

The authentication_string and plugin settings work together for Authentication Plugins ( The plugin named is then used to authenticate the user by communicating with a plugin on the server. The authentication_string provides information on how to pass information to the server side plugin. This allows DBAs to let users authentication via LDAP, PAM, Windows auth service, or a custom written plugin.

And it lets DBAs use proxies for authentication and I will go into that in a future posting.

1 Comment

Filed under MySQL

Copying MySQL Data to Hadoop with Minimal Loss of Blood Part 2

I have spent the better part of the last month at Big Data conferences trying to see behind the $2.5 million in marketing smoke to see what is really going to be showing up on the to-do list of DBAs. The first bit of news is that half the vendors at shows like Strata or Big Data Techon will probably be gone by this time next year. So picking a vendor right now is a little iffy. Hadoop’s ecosystem is flourishing and will surely be around for some time but the vendors are playing musical chairs.

But we are Open Source and we do not need vendors! Well, yes and no. The good folks at Cloudera and Horton Works have done you a big favor by providing wonderful tutorials that are worth your time to see. Recently two former MySQL-ers, Sarah Sproehnle and Ian Wrigley, have put together Udacity that concisely teaches Hadoop technology and Cloudera deserves a round of applause for this well produced effort. While you can put together your own Hadoop cluster from the various Apache projects, it is often easier to get them from a vendor. You have only so many working hours and it is nice to be able to lean on someone for help occasionally.

Sadly many Big Data projects look like middle school science projects. They were done for the sake of having a big data project and not for a particular business need. MySQL has been sold for years are working great on commodity hardware and many Big Data projects are also being sold that they work great on commodity hardware — and lots of it. So a poor DBA ends up with scads of commodity boxes for MySQL shards, Hadoop clusters, and what ever else tech gets adopted. (Hint: For Christmas ask for stock in commodity hardware companies, disk drive manufactures, and electrical utilities)

Can you guess what the following does:

START a=node(*),
bacon=node:node_auto_index(name="Kevin Bacon")
MATCH p=shortestPath((a)-[:KNOWS*]->(bacon))
RETURN extract(n in nodes(p) :;

It is Neo4j code for find The Six Degrees of Kevin Bacon. Finding the shortest path of friendship relationships with SQL is going to be nasty. But it can be done easily with the right NoSQL world. So there is utility to these types of approaches and ‘Joe Average DBA’ should start planning on learning more as you will be asked to implement these technologies in 2014.

When I started the first part of this column, I did not intend to drag out the length of time it took to get the second part posted. I have been investigating the various ways of bulk and real time loading of Hadoop filesystems. 80% of Hadoop clusters are feed from MySQL and the most popular batch loader is Squoop. Grab a copy of the Apache Squoop Cookbook as it details how to copy data from your MySQL instances to HDFS. Squoop uses a JDBC connector to communicate with your instances.

$ sqoop import --connect jdbc:mysql://localhost/bigdb --username dave --table BigProject

Batch load of bulk data can get tedious. I have been building the MySQL Hadoop Applier from so that I can get updates to HDFS as simply as MySQL Replication. Sadly my cmake is being fussy. But I love the idea of being able to have data flow automatically over a bulk load.
MySQL Hadoop Applier
The concept that my Hadoop cluster is simply another MySQL Replication feed appeals to me.


Filed under Big Data, Hadoop, MySQL

See you in Raleigh, NYC and Irving!

This week I am presenting at the All Things Open conference (be sure to drop by the booth for some MySQl stickers!), the next is Strata in NYC, and then the North Texas MySQL Users Group on November 4th.

1 Comment

Filed under community team, MySQL

North Texas MySQL Users Group Meeting November 4th

Come learn how to tune MySQL 5.6 servers, the basics of query optimization, and learn how to use the new Visual Explain with Workbench 6.0.

Date: Monday, November 04, 2013
Time: 6:00 PM – 8:00 PM

Oracle Corporation
6031 Connection Drive, Suite 900
Irving, TX 75039-

Event is free to the public but please RSVP
Pizza at 5:30 p.m. Presentation at 6:00 p.m.

Presented by: Dave Stokes, MySQL Community Manager North America, Oracle Corporation

1 Comment

Filed under MySQL, User Group