MySQL Cluster to Hadoop

How do you get data from a MySQL Cluster into Hadoop? Easy, replicate from the cluster to a stand alone MySQL instance and from there use the MySQL Hadoop Applier to HDFS. MySQL data to Hadoop via MySQL Hadoop Applier

This question came from a long time MySQL user who has jumped into the Big Data world.

1 Comment

Filed under Big Data, MySQl Cluster

Los Angeles MySQL Users Group and Southernalifornia Linux Expo SCaLE 12x

Big week for Southern California! I will be speaking at the LA MySQL Users Group on Thursday at 7PM in the Oracle Office on 200 N Sepulveda Blvd, El Segundo, CA. We will be in Room 4116 to talk about MySQL 5.7!

SCaLE 12x is THE big opensource show in Southern California and one of the more impressive conference on the planet. It may see a shame to waste temperatures in the low 80s by staying indoor but this show is amazing. So see you the 21st, 22nd, and 23rd at the LAX Hilton.

Leave a comment

Filed under User Group

MySQL February Webcasts

Monitoring MySQL at Scale
Wednesday, February 19, 2014

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

http://www.mysql.com/news-and-events/web-seminars/developing-windows-applications-with-mysql-part-iii-migrating-from-microsoft-sql-server-to-mysql/

Thursday, February 27, 2014

Leave a comment

Filed under MySQL

MySQL Tech Tours — Miami, Pleasanton, Santa Cara and New York City

Please join us for our MySQL Enterprise Monitor hands-on lab event hosted by Oracle MySQL experts. This hands-on lab will take you through the best practices for installing MySQL Enterprise Monitor, real-time MySQL performance & availability monitoring, and managing advisors. This will be an informative session for anyone who needs to tune, monitor, and manage MySQL.

Leave a comment

Filed under Tech Tour

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 forums.mysql.com 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.

Leave a comment

Filed under MySQL

Watch Todd Farmer at San Francisco MySQL User Group

Todd Farmer’s MySQL 5.7 talk can been seen at http://www.youtube.com/user/sfmysql. 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.

Leave a comment

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;
CREATE TABLE meta (password_lifespan SMALLINT UNSIGNED NOT NULL DEFAULT 90);

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.

CREATE TABLE user (User CHAR(16) COLLATE utf8_bin NOT NULL DERFAULT ''.
password_last_set DATE,
PRIMARY KEY (User));

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,
PRIMARY KEY (User));

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