Author Archives: OpenSorceDBA

MySQL 5.7 user table: password_last_changed & password_lifetime

MySQL 5.7.4 has added two fields to the mysql.user table — password_last_changed, a timestamp and password_lifetime, a small but unsigned integer. Several blogs ago I started to cobble together a password expiration tracking script before these two columns were added. But I could see three ways of tracking expired passwords but none of them were palatable. Todd Farmer was working on a similar idea.

So when you run mysql_upgrade after upgrading to 5.7.4, you will find these two new columns. The password_last_changed will be set to the time you ran the upgrade and password_lifetime will be set to null.

You can set global password lifetime policy in the options file.
[mysqld]
default_password_lifetime=180

So 180 is about six months and zero would set a never expire policy.

ALTER USER 'dave'@localhost' PASSWORD EXPIRE INTERVAL 90 DAYS;
ALTER USER 'john'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jane'@'localhost' PASSWORD EXPIRE DEFAULT;

Leave a comment

Filed under MySQL Security, Security

MySQL Connect Call for Papers ENDS April 15th

The MySQL Connect Call for Papers ends April 15th. So submit ASAP!!

Below are examples of some of the broad areas of interest our Conference Review Committee may be seeking:

  • Customer Success Stories or Case Studies
  • Best Practices (based on experiences and insights acquired)
  • Tips and Tricks / How To Sessions (based on expertise in specific areas)
  • Deep Dives
  • Partner / Community Solutions
  • What’s New
  • Introductory, 101-type sessions
  • Upgrades

Write an abstract that is easy to read and describes the value of the presentation. Explicitly mention what is being discussed during the session rather than making a marketing or strategy pitch. For example, mention product demonstrations, case studies, customer/partner participation, quantitative facts, etc. Do not include proprietary or confidential material.

Standard presentation time slots are 45 minutes. We suggest allocating 10 minutes for a Q&A period within that timeframe.

Leave a comment

Filed under MySQL Connect 2014

Collaborate 14, Las Vegas, and the Naked Guy Occupying My Hotel Room

Colaborate is a big show for C-level executives and up for the three big Oracle User Groups. Last week at Percona Live was all MySQL, Open Source, and a few hundred close folks. This week is the biggest show, after Oracle Open World, on the Oracle user calendar and draws a huge crowd from around the globe. All the Fortune 500 are here or their contractors. collab14 In short, anything tangential to any Oracle product can be found along with companies that offer add-ons, support, consulting, and product. A lot of the attendees have MySQL in their computer rooms and that is why I am in Las Vegas.

Vegas is a convention city. This week Collaborate, The National Association of Broadcasters (100,000 strong), and a dozen other conventions are in town. Add in tourism, gambling, and shows to warm weather to produce a town that is literally buzzing.

My job for today is to make it from the airport, get to the hotel, set up the MySQL Demo-pod in the Oracle pavilion, get to my room, clean up, and work a couple of hours at the pod. Vegas specializes in getting travelers off the plane, into a cab, and to the hotels as quickly as possible. Of course the expo hall will be the furthest point away from where the cabs drop you to where the expo hall is located. So I end up with a hike to the expo hall. As a corollary to the cab drop off rule, the booth to be manned is at the far end of the ‘so large you can see the curvature of the Earth’ hall. The Demo-pod is a bank of large screens hanging off cabinets equipped with laptops to use for demos. But the slideshow I brought is on a USB drive and the two USBs on the laptop are in use for mouse and keyboard. But there is Internet and I was able to move the file needed onto the laptop. But of course, the latest version of the slide software has again changed the way to loop the presentation automatically. Then I add on Workbench, MySQL 5.6 Enterprise, and MySQL 5.7.4. I should also mention that during show setup, the air conditioning is off, and Las Vegas is getting very warm.

So at last, I head back to the hotel room for a quick shower and a little relaxation before the expo hall opens. But upon getting the door unlocked, I find the room already has an occupant who is a) sans clothing, b) was rather upset, and c) had ‘dibs’ on the room. So after another hike to registration and getting another room, I am almost ready for Collaborate 14. If you are at the show please drop by the MySQL Demo-pod.

Leave a comment

Filed under Collaborate

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

Getting started with Performance Schema and MySQL Workbench 6.1.2 Beta

MySQL’s Performance schema is a relatively new tool for measuring performance and MySQL Workbecn 6.1.2 is the latest beta of that software. I have not had a lot of time to play with performance schema but now I am taking my first steps with the help of Workbench. Startup Workbench and you will find under the Navigator an item labeled Performance Schema Setup. Flip the toggle from OFF to ON and then start exploring.

InnoDB Buffer stats by Schema are show here -- one of more than twenty pre-established metrics available.

InnoDB Buffer stats by Schema are show here — one of more than twenty pre-established metrics available.

Now you can run queries and see what the costs are, where the server is waiting, or what indexes remain unused. You can even use workbench to alter the options file to setup other Performance Schema instruments. Trying various settings for optimizer_search_depth is simple with the GUI.

Leave a comment

Filed under MySQL Workbench

MySQL 5.6 Beta Exam Results

Congratulations on earning your Oracle Certification credential! — I was happily surprised to see this on the first email of the day. My employer sends out a fair share of corporate correspondence and I will admit to being caffeine deprived early this morning when I started reading my emails. I was expecting some rote corporate communication and i had to read the first sentence twice. So it took a moment for it to sink in that I PASSED THE 5.6 MySQL DBA EXAM!!!!!

The exam was tough — much more comprehensive than any previous exam — and there were a handful of questions that left this long time user of MySQL wondering where the heck did they find that material.

So if you took a 5.6 beta exam, your results are probably in your inbox right now. Congrats to those who earned their new certifications!

The official 5.6 DBA Logo

The official 5.6 DBA Logo

4 Comments

Filed under MySQL Certification

MySQL Workbench as an administrative tool

MySQL Workbench is a handy administrative tool. Workbench provides server status information, client connection data, the best user admin interface, a browser for system variables, access to data export, an import/restore function, access to system logs, option file editing, performance reports, and startup/shutdown switch.

This is a snapshot of the dashboard on a laptop running a few very simplequeries.

This is a snapshot of the dashboard on a laptop running a few very simplequeries.

In the past I have either used the CLI or tools like PHPMyAdmin. Well, the CLI is often victim to my poor typing skills and PHPMyAdmin is not always installed when I need it. But if I can connect via the CLI, I can connect with Workbench.

The Server variables can be easily copied to a clipboard with one click

The Server variables can be easily copied to a clipbaord

Comparing server variables across systems can be painful but Workbench lets you copy the data to a clipboard so you can save the data to a text file that can be used with diff or similar tool to find discrepancies. This is also a hand trick for documenting instances. Over the past few releases, the Workbench Team have added items like this that show they know what information a DBA scrambles for in the wee hours of the morning when hunting problems.

A performance report on the most active files on the instance.

A performance report on the most active files on the instance.

Need Performance Schema info? It is there too. Calculating Runtimes in the 95th percentile used to be a ‘dark art’ that is now just a click away.

So if you are looking for a great admin tool, check out MySQL Workbench.

3 Comments

Filed under MySQL Workbench

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 Workbench Data Modeler

MySQL Workbench is a great tool when someone asks you to look at their schemas. It is hard to get the over-all view of data that you are not familiar with and this is a great aid for this situations. You an reverse engineer the database, make changes, and then roll them out but this blog is about creating the model only. The examples you see in this blog were made with MySQL Workbench 6.12, which is in beta. Beta means we want to you test the heck out of it and let us know how you broke it so we can make it better.

modeling tag

Click on the circled greater than sign next to model to begin.

Click on the circled greater than sign next to Models to begin. We want the Create EER model from database option.
We want to create a model using an existing database

We want to create a model using an existing database

Next we set up the connection to the server

Next we set up the connection to the server

Next we set up the connection to the server. Workbench will make the connection to the server and fetch a list of schemas. The schemas you will see depend on your privileges and I am willing to bet 99% of t hose reading this will be root anyway.

Pick the schema to use for the model

Pick the schema to use for the model

Here we will use the good ol’ World database that has been used in MySQL documentation, classes, and examples ad nauseum. The World schema bundled with the Windows install is the MyISAM version and I recommend loading the InnoDB version (Download at the link above, save to your disk, unzip data, and use Workbench to read the SQL file in a Query window) or you will not get the to see in person the results seen in the next picture (there are DROP TABLE IF EXISTS lines for the table creation so you do not have to drop the tables by hand).

model done

And here are the three tables in the World Database, note the foreign keys

And there is the model! You can drag and drop the various tables to arrange them to your liking. Note that mousing over the foreign key links shows the relationship between tables. Clicking on a column name will tell you the definition of the column. There is even an index arrow to click to show the carious indexes. Pretty slick for a free piece of software! Click om the table name and you see the columns and the foreign keys. wbmod07 So if you have never seen a schema before, you now have a wonderful (printable) diagram of the tables and their interrelations. My learning style is osmosis (or banging it through my thick skull) and having a graphical representation to reference helps me greatly.

Sakila

The Sakila Schema

Things can get visually busy as you can see with the nearby representation of the Sakila database but Workbench lets you zoom-in, zoom-out and see sections as you desire.

Don’t like the ‘chicken foot’ or need UML, you can change the model relationship model from a pull down. There are enough options here of value that you really do need an afternoon to explore them all.

So please download MySQL Workbench 6.1.2 (and MySQL 5.7 while you are at it) and give it a try. Our developers await you feedback!

Leave a comment

Filed under MySQL Workbench

Workbench Beta 6.1.2

MySQL Workbench is the second most popular download of all MySQL products and recently a new Beta version was made available for evaluation. Workbench is a Swiss Army Knife tool with three very sharp blades — query tool, data modeler, and administration. Download here and pick the Development Release. We are looking for feedback on the new version so kick the tires, do a long afternoon test drive, and run it through the paces PLEASE! The changes over the GA release are very impressive and you do really need to try this beta. Packages for all the usual players — Ubuntu, Fedora, RHEL/Oracle, OSX and Windows — are available. Windows users get a Zip archive they can unpack where desired and run from there while others get to use their regular rituals for their package manager. The first window you see

MySQL Workbench Opening Screen

Workbench is a multifaceted tool available FREE from http://dev.mysql.com/downloads/tools/workbench/

Next to MySQL Connections in the upper left hand corner of the main window is a circled plus sign. Click that to get a dialog box that will set up communications to the MySQL instance of choice. Give your connection a name and then enter your login information such as server, username, port, and password. Click on the button to test your connection to check for fat fingering. Then click on OK and the connection information is saved for later access. Click on your new connection to access the server. Connection Information In this example I selected the World schema to be used when I connected.

Now you are in the query tool.


Enter a simple query, in the example a select, and then click on the lightening bolt on the left right over the ‘S’ in select. Example SELECT The query will be executed and will produce output. Example Output

The data can be exported to a CSV format, filtered, or sorted.


But what about that icon with the lightening bolt with the magnifying glass over it? I have written about VISUAL EXPLAIN before and the Workbench crew have been very busy. Click on the lightening blot with the magnifying glass and you now get three options. The first is the latest VISUAL EXPLAIN

VISUAL EXPLAIN shows what the optimizer wants to do

ViSUAL EXPLAIN graphically explaining what the optimizer is doing to the query



The former ASCII style format is now available with 6.1

The former ASCII style format is now available with 6.1

The traditional ASCII-style format EXPLAIN information is also available under another tab.


But there is a new option to show what the optimizer is doing to optimize the query in detail. Optimizer details This will probably be the place you will spend you time optimizing queries in the future.

I will go over the other two main features of Workbench in a future blog.

Leave a comment

Filed under MySQL Workbench