Friday, December 16, 2011

In Case you missed these

Just in case you missed these posts....

Facebook has some interesting blogs on benchmarks, InnoDB and etc...

What is in the InnoDB buffer pool? 12/15/2011 02:14 PM

Dynamic padding for tpcc-mysql 11/21/2011 11:51 AM

The shutdown benchmark 11/15/2011 05:29 PM

The effect of page size on InnoDB compression 11/07/2011 11:40 AM

Bugs and Spam don't mix

A big thank you to the MySQL web team and engineers for helping combat spam and keeping the site spam free.

reCaptcha is now installed and is a requirement when adding a comment to a current bug. We want the community to be able to review and comment on bugs with little spam as possible getting in the way.

Sunday, December 11, 2011

MySQL Cluster to MySQL Server ... Part 2

I previously posted blog about the topic of a MySQL Cluster set up and replication to a MySQL Server. The first blog was just the installation, now lets test the cluster and get replication started. Both are extremely easy.
Some people might ask, “Why set up replication from a cluster? It is a HA system why replication?”
The ability to move data out of the cluster and allow others to do reporting, data exports, simple backups all can be done easily with it replicated into another MySQL server. Yes a Cluster can do all of those queries, but taking advantage of the replication to keep some users and queries out of your production database is rarely a bad thing.

Testing the Cluster Install. Of course more MySQL Documentation:

*************************** 1. row ***************************
Engine: ndbcluster
Support: YES
Comment: Clustered, fault-tolerant tables
Transactions: YES
Savepoints: NO

*************************** 3. row ***************************
Engine: ndbinfo
Support: YES
Comment: MySQL Cluster system information storage engine
Transactions: NO
Savepoints: NO

mysql> USE test;
Database changed
Query OK, 0 rows affected (0.28 sec)

mysql> SHOW CREATE TABLE ctest \G
*************************** 1. row ***************************
Table: ctest
Create Table: CREATE TABLE `ctest` (
`i` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

That was easy enough...Table is built and using the ndbcluster engine.
So lets set up replication from a cluster to a MySQL Innodb Server.
1st create a user for the replication.

flush privileges;

For this example I am jut using a Windows Virtual Box instance. The MySQL windows installer worked great. For a guy who ever runs windows, it was nice that this was so easy. (
On the Cluster server, I made sure to have my server_id and big logs set.

vi /etc/my.cnf
log-bin = /var/lib/mysql/mysql_demo-bin

restarted the sql server and confirmed it was running:

mysql> show master status\G<
*************************** 1. row ***************************
File: mysql_demo-bin.000001
Position: 112 Binlog_Do_DB:
Binlog_Ignore_DB: 1
row in set (0.00 sec)

So lets create a simple table that we can use for test later.
PLEASE take note... This is just a table to show it replicated. If you create tables for real use in a Cluster and replicated ... ALWAYS have a primary key!

Query OK, 0 rows affected (0.28 sec)

Now back on my windows mysql server. Since it came with workbench I used this to set the server_id (server_id =2) and restart the server. I then opened the sql editor and added the master settings.

via_mysql_workbench> CHANGE MASTER TO
-> MASTER_PORT=3306,
-> MASTER_USER='slave_user',
-> MASTER_PASSWORD='<password>';

via_mysql_workbench> start slave;
via_mysql_workbench>use test;
via_mysql_workbench> SHOW CREATE TABLE slave_test ;
'slave_test', 'CREATE TABLE `slave_test` (
`i` int(11) DEFAULT NULL

mysql> show slave status;

Slave_IO_Running, Yes
Slave_SQL_Running Yes

So now via these blog posts.. I have a cluster with a geolocated node both on Oracle Linux 6  being replicated to another remote location MySQL server running Windows via a Virtual box instance.  I can also now use Workbench to connect to the local windows server as well as the cluster if needed. This is not a production system but it does show that Cluster can be installed easily, remotely, and takes advantage of commodity systems.

Part 1 -- The Install
Part 2 -- Testing Cluster more and Replication Setup

Friday, December 9, 2011

Congrats to three more MySQL Oracle ACE members...

It is truly amazing the amount of knowledge and talent that work with and support MySQL and the MySQL community. Thanks to all of you.

Over the last month the MySQL ACE list has grown even more:

George J. Trujillo is now an Oracle ACE and a MySQL Oracle ACE.

Wagner Bianchi  became a MySQL Oracle ACE not long ago.

Roland Bouman is the latest addition as a MySQL Oracle ACE.

Congrats to all of them !

MySQL Cluster to MySQL Server Part 1

Recently I was working on a MySQL Cluster that is replicated to a MySQL server. I will create a few blog posts to show how all of this can be done. This is of course not the only way to do this.. Any feedback or other options are welcomed in comments for the community to use.

Part one: the install

First the MySQL Documentation about MySQL Cluster is very good I will make references to it for your convenience.
You can go about this a few different ways but this is a simple RPM install:

Download all the related rpms from and install.

MySQL Documentation ( is here to help you with the installs as well.

    rpm -ihv MySQL-Cluster-gpl-*.rpm

Ok that was easy enough ... Make sure we have what we expected...

rpm -qa | grep MySQL


Just so this can be a tiny bit of a more real world example, I also installed an external node in a remote (out of the subnet/datacenter) location.

[root@remote_cluster src]# rpm -ihv MySQL-Cluster-gpl-storage-*.rpm

OK software install is straight forward and easy, but what do you do now ?

MySQL Documentation ( on how to configure the cluster are straight forward and very easy to follow.

Based on that documentation, I have set all the ips in all the related configuration files ...

My Config file looks like this after removal of comments. I have a local node and a remote node for this simple example.

# Management process options:
hostname=         # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster # Directory for MGM node log files

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=4                         # Number of replicas
datadir=/var/lib/mysql-cluster # Directory for MGM node log files
DataMemory=80M                # How much memory to allocate for data storage
IndexMemory=18M               # How much memory to allocate for index storage






Nodes... Start your engines..... ok so really, lets get it started...

MySQL Documentation ( on how to start it up for the 1st time..

On Localhost

/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.1.56 ndb-7.1.17

On Localhost / Data Node

2011-12-08 12:21:16 [ndbd] INFO -- Angel connected to 'localhost:1186'
2011-12-08 12:21:16 [ndbd] INFO -- Angel allocated nodeid: 3

On remote node :

2011-12-08 12:25:39 [ndbd] INFO -- Angel connected to ''
2011-12-08 12:25:40 [ndbd] INFO -- Angel allocated nodeid: 6

On Localhost Start the sql server and check the nodes.

# /etc/init.d/mysql start

# /usr/local/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration

[ndbd(NDB)] 4 node(s)
id=3 @ (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=4 (not connected, accepting connect from mysql_demo.localdomain)
id=5 @ (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=6 @ (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @ (mysql-5.1.56 ndb-7.1.17)

[mysqld(API)] 3 node(s)
id=10 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=40 (not connected, accepting connect from any host)

OK lets log in and test it ?

# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.56-ndb-7.1.17-cluster-gpl MySQL Cluster Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select VERSION();
| 5.1.56-ndb-7.1.17-cluster-gpl |
1 row in set (0.00 sec)

Of course if you need to stop and restart (MySQL Documentation: a cluster, it is a simple process.

/usr/local/bin/ndb_mgm -e shutdown
/etc/init.d/mysql stop

Others posts on cluster installs:

Part 1 -- The Install

Part 2 -- Testing Cluster more and Replication Setup

Wednesday, November 30, 2011


Wow how things change quickly sometimes. As per a previous post I have recently moved to XFCE. It was great and things worked very well. Then, it just went nuts...

Active windows failed to work and I was stuck on a single desktop (I live with multi-desktop), mouse failures and etc. To quote the great Dr. Peter Venkman,  "Human sacrifice, dogs and cats living together... mass hysteria! "

Anyway, I needed to get something done at the moment and not fight with it so I did a quick yum install of LXDE. I had to go to the gnome session to do this, that is how crazy the XFCE went. But I am currently happy with the LXDE.

I will get back to XFCE and see if I can find out what in the world made it go nuts but everything takes time.

Now I am not going to say I am at home yet, after all I did that with XFCE and things went crazy. But it is working very well so far.

Tuesday, November 29, 2011

OTN MySQL User Forum

One thing I enjoy about this job is being able to meet face to face the with MySQL Community. We have a big community and it is hard to keep up ! Sadly, I will not be able to make the OTN MySQL User Forum in Brazil but I want to encourage the community to attend the event.

It is a free forum, full of technical sessions, taught by engineers and leaders of the MySQL open source community will also explore Oracle's strategy with regard to MySQL, as well as the new MySQL Enterprise innovation and what to expect from MySQL 5.6.

Best of luck to the MySQL Brazil Community.

Monday, November 28, 2011

MySQL and Debian Linux

It is available now ! MySQL 5.5.18 now offers Debian Linux (.deb) packages.

They are available via the site !

Finally the Debian community can upgrade easily to MySQL 5.5 !

Tuesday, November 22, 2011

Database Chain

I was at an event recently and the topic of replication stirred the curiosity of the audience. A few audience members had Master to Master but they wanted to move away from that. Others had multiple slaves but wanted little downtime and backend work if a master failed. Relayed replication or a database chain is an option to solve some of their issues. Another option would be Cluster but it depends on your infrastructure, budget and application, some of them are looking into this as well.  Is a chain the best solution for everyone, of course not. While I cannot do consultant work to help them, I can blog about it…
      A relayed replication environment allows you to, of course, have replicated databases but also have a replication environment that is still available if or when the master fails.  The image to the left shows a simple example of how the databases work together.
            One of the first things to keep in mind is that your application/environment needs to be able to handle a master switch. That means do not reference the master by IP or hostname.  Instead use NIS, LDAP, alias in host files, or just DNS.  Something simple as Server A == Master, then your MySQL clients and applications reference master when doing connections. This allows you to adjust the reference point quickly when a master fails. So if Server A crashed, adjust reference to Server B == Master. You still have 2 slaves running as well. You can then stop slave on Server B, examine Server A and gather any data you feel might have been lost. Sometimes starting the Slave on Server B to pull anything left over from Server A can work but auto increment ids could cause a conflict easily, so use caution when doing this. Once I am done with Server A, I move that to become a new slave of Server D, I now have four servers again.
            I like to have at least four servers when doing a chain. If I only had three servers then I am very dependant on Server B, the 1st slave.  If that box was to fail I am left building a slave from backups or off the master. If I have four and Server B crashes I can move the Master to Server C and still have a replicated system.  Now, this of course is entirely dependant on your slaves staying up to date. Building your application to process data that will not slow down a slave can help support this type of environment.
            Another nice feature coming soon with MySQL is timed delayed replication. This option can be used with a chain as well. The time delayed option pulls all data and only applies it to the db based on the threshold you set. So you could build out a live backup solution using a database chain. Server A is your slave, Server B can get time delayed set to a Day, Server C gets 6 Days (So a week back from master), Server D gets set to 24 Days back (~month from master). Now you have full access to historical data if needed. If a user drops a table that shouldn’t have, it is a lot easier to dump that table and import it rather than pulling it off a tape or disk 1st.
            Do not let the slave servers go to waste either.  Allow your database handles to use the all of the slaves accordingly.  Keep all the reads you can off the master. Create a script/class that uses a handle for your master that uses an account with appropriate access. Then create another script/class to handle connections to the slaves. This slave connection can be created to accomplish a few things. First allow it to have a default of seconds back from master per query passed to it.  Second create it to use the aliases of all the slaves and connect to check what is the seconds back, then use the valid server per threshold. This is a very fast check but the process can be enhanced to have times set on a schedule with another script, depends on your application. Now this allows your slave connection to take a query that has a threshold of 1 day (running a report for all of yesterday for example) and push that down to execute on Slave D as long as it is within the threshold.  

Monday, November 21, 2011

Gnome 3 to XFCE

So I recently moved my laptop onto Fedora 16. I had some issues with the previous Linux distro and figured I would go with this for my laptop.

Issues with Gnome 3 are not new but I figured I would collect a few links for everyone so hey can get past the issues with this.

1st - Get icons back on your desktop -

2nd - Get workspaces to work on more than 1 monitor. ( THIS DROVE ME CRAZY)

3rd. The wireless worked great so that was helpful.

4th  Set your shortcuts to switch to workspace because going to activities everytime to get anyplace drives me crazy. Shows some outside users testing was needed more.

So if this is enough for you then great!  But remember gnome 3 is not the only option :

A quick yum install of xfce and my life was so much better.

If your a long time Gnome user, like myself, KDE was not an option. No reason I am just not a KDE guy. However, I did come to find that I am a XFCE guy. Within seconds of entering the XFCE environment I was home.

Thursday, November 17, 2011

OTN Developer Day: MySQL - Minneapolis, MN

Thanks to the community for a great turn out at the OTN Developer Day: MySQL in Minneapolis, MN.

The room was packed and we had to bring more tables in to handle the crowd. Sessions covered enterprise, replication, overviews and perfomance.

I hope the local MySQL user group can support the new members in their area.

Best of luck to everyone and thanks again.

Tuesday, November 8, 2011

Easy Phone Number Checks

So this is a simple free example of how to check a phone number via a mysql database. Yes more complete validation options are available, some even have to pay for, but this is better than no check at all. 

The data is available here : wget -q

It loads a table like this:
CREATE TABLE `telcodata` (
`npa` int(3) NOT NULL,
`exchange` int(3) NOT NULL,
`thousands` int(3) NOT NULL DEFAULT '-1',
`company` text,
`ratecenter` text,
`clli` text,
`type` text,
`emaildomain` text,
`parentco` text,
`state` text,
`zip` text,
`lat` float(9,5) DEFAULT NULL,
`lon` float(9,5) DEFAULT NULL,
`latanum` int(11) DEFAULT NULL,
`ocn` varchar(4) NOT NULL,
`ilec` varchar(4) DEFAULT NULL,
`ilecname` text,
`tandem` text,
KEY `rcstate` (`ratecenter`(11),`state`(2)),
KEY `npaexch` (`npa`,`exchange`,`thousands`)

Once the data is loaded you can use this with data you have gathered. This could be from websites or data warehouse data who knows...

Then you can check to see what type of a phone number it is ?

SELECT npa,exchange,thousands, company , ratecenter , type, parentco, CONCAT(npa,exchange,'0000','@',emaildomain) as send_a_txt
FROM telcodata where npa=303 and exchange=335 and ( thousands=8 or thousands < 0 )  ORDER BY thousands desc LIMIT 1 \G

npa: 303
exchange: 335
thousands: 8
ratecenter: DENVER
parentco: Verizon
1 row in set (0.00 sec) 

I have placed 0000 in place of the thousands field, if you wanted a valid thousands option you would replace it with your data.

A none cell looks like this 

 SELECT npa,exchange,thousands, company , ratecenter , type, parentco ,  CONCAT(npa,exchange,'0000','@',emaildomain) as send_a_txt FROM telcodata where npa=303 and exchange=623 and ( thousands=9 or thousands < 0 ) \G
*************************** 1. row ***************************
       npa: 303
  exchange: 623
 thousands: -1
ratecenter: DENVER
      type: RBOC
  parentco: NULL
send_a_txt: NULL
1 row in set (0.05 sec)

In this case you can check to see if the data given is a cell phone or not by checking the type field. Your tests are only as good as your data but this is a start.

Friday, November 4, 2011

MySQL Utilities

I recently explored the mysql-utilities scripts available on launchpad ( ) The examples below are from my local test db.

The code is available via a quick bzr download after you sign in with your launchpad id of course.

bzr launchpad-login <user_id_ from_launchpad> 

bzr branch lp:~mysql/mysql-utilities/trunk 

MySQL Utilities code does have some requirements, Python between 2.6 and 3.x . So check your python version , some of the distributions have older versions. ( # python –version ) If you want the manuals, then Sphinx (which also requires Jinja) is also required. Of course a connector to the database would be helpful. You can get the latest python connector, via launchpad as well, at

If your curious about more of the options for setup the help commands will give you a full list. ( python –help-commands ). Otherwise a quick set up is as simple as python install. I happen to not have the /etc/profile.d/ on my system and the set up tossed an error. A simple touch of this file and all worked fine.

So you have it all installed, now what? You will find inside the scripts directory (../trunk/scripts) approximately 15 different python scripts. ,, , , , ,, , ,, , ,, ,

Some quick examples via the employee database

Index Checks:
./mysqlindexcheck –server=<username>:<password>@<HOST>:<PORT> employees

# Source on ... connected.
# The following indexes are duplicates or redundant for table employees.dept_emp:
CREATE INDEX emp_no ON employees.dept_emp (emp_no) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE employees.dept_emp ADD PRIMARY KEY (emp_no, dept_no)
# The following indexes are duplicates or redundant for table employees.dept_manager:
CREATE INDEX emp_no ON employees.dept_manager (emp_no) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE employees.dept_manager ADD PRIMARY KEY (emp_no, dept_no)
# The following indexes are duplicates or redundant for table employees.salaries:
CREATE INDEX emp_no ON employees.salaries (emp_no) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE employees.salaries ADD PRIMARY KEY (emp_no, from_date)
# The following indexes are duplicates or redundant for table employees.titles:
CREATE INDEX emp_no ON employees.titles (emp_no) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE employees.titles ADD PRIMARY KEY (emp_no, title, from_date)

Quick Server information:
./mysqlserverinfo --server=<username>:<password>@<HOST>:<PORT> employees
# Source on ... connected.
| server | version | datadir | basedir | plugin_dir | config_file | binary_log | binary_log_pos | relay_log | relay_log_pos |
+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+ | 5.6.3-m6-log | /var/lib/mysql/ | /usr | /usr/lib64/mysql/plugin | /etc/my.cnf | mysql-bin.000340 | 717 | None | None |

If you need grep the tables to find something?
./mysqlmetagrep --server=<username>:<password>@<HOST>:<PORT> --pattern=emp_no
| Connection | Object Type | Object Name | Database | Field Type | Matches |
| kdl:*@ | TABLE | dept_emp | employees | COLUMN | emp_no |
| kdl:*@ | TABLE | dept_manager | employees | COLUMN | emp_no |
| kdl:*@ | TABLE | employees | employees | COLUMN | emp_no |
| kdl:*@ | TABLE | salaries | employees | COLUMN | emp_no |
| kdl:*@ | TABLE | titles | employees | COLUMN | emp_no |

Disk Usage:
./mysqldiskusage --server=<username>:<password>@<HOST>:<PORT>
# Source on ... connected.
# Database totals:
| db_name | total |

Total database disk usage = 24,721,809,136 bytes or 23.00 GB

They are tools that are quick and easy to get installed and use to help and enhance your MySQL experience.

MySQL Utilities are part of the MySQL Workbench but they can be used as a stand alone option.
Also available is the testing suite “mut” which is available under the mysql-test directory.

# ./ --server=<username>:<password>@<HOST>:<PORT>
MySQL Utilities Testing - MUT
Parameters used:
Display Width = 75
Sorted = True
Force = False
Test directory = './t'
Utilities directory = '../scripts'
Starting port = 3310
Connecting to as user kdl on port 3306: CONNECTED
experimental.t1 [pass] 48
experimental.t2 [pass] 14
experimental.t3 [pass] 3
experimental.t4 [pass] 10
main.check_index [FAIL]
ERROR: Result file mismatch:
- # Source on localhost: ... connected.
+ # Source on ... connected.
- # Source on localhost: ... connected.
+ # Source on ... connected.
- # Source on localhost: ... connected.
+ # Source on ... connected.
- # Source on localhost: ... connected.
+ # Source on ... connected.
- # Source on localhost: ... connected.
+ # Source on ... connected.
- # Source on localhost: ... connected.
+ # Source on ... connected.
Testing completed: Friday 04 November 2011 14:38:02
4 of 75 tests completed.
The following tests failed or were skipped: check_index
Deleting temporary files...success.

More information is available :

You can of course find this and numerous other MySQL code instances, some are old, at

Thursday, November 3, 2011

Shlomi Noach and the MySQL Oracle ACE

 A big congrats to Shlomi Noach  for his MySQL Oracle ACE.

You can find Shlomi's code and blogs at

Monday, October 31, 2011

Don't forget to mysql_upgrade

I recently upgraded MySQL to the DMR release of 5.6.3.  Available here : 
I was reminded of a some questions about mysql_upgrade I have gotten lately. While it is easy to skip, do not forget to run
/usr/bin/mysql_upgrade  after you upgrade.

Yes it is typical to see clean tables.

Simple example:
employees.departments                              OK
employees.dept_emp                                 OK
employees.dept_manager                             OK
employees.employees                                OK
employees.salaries                                 OK
employees.titles                                   OK

I do not want to repeat what others have said so more on mysql_upgrade can be found by these community posts if needed:

Friday, October 14, 2011

Thank you RMOUG

I big thank you to the Rocky Mountain Oracle Users Group (RMOUG) because, they invited me out for a night of MySQL for Oracle DBA's. It was a great night. I took feedback from OOW and rebuilt a presentation just for them. I also built out a hands on lab for MySQL and let them loose...

Over those 2 hours we covered the basics of MySQL and everyone got to get their hands into the MySQL 5.5 Database via the command line. They then walked away with another hands on lab for MySQL Workbench.

I wish them all the best and I appreciate the positive feedback.


Thursday, October 13, 2011

Looking for feedback


We are looking for users of the MySQL Community Edition 5.5 and/or users who tested some of MySQL 5.6  (via the

Have you tested the MySQL utilities ?

Have you tested Cluster ?

What replication topologies do you prefer?

We want to know your thoughts on features and how it has helped you.  Your feedback helps drive the future! Don't be shy ! Please contact me with your feedback.

keith larson

Tuesday, October 11, 2011

Thanks for letting us know.

A recent post was brought to our attention. Thanks for letting us know. This has now been fixed.

Friday, October 7, 2011

Many thanks to the MySQL Community

Many thanks to the MySQL Community. The Oracle Open world sessions had veterans (Sheeri, Sarah, Giuseppe, Ronald, Baron, to just list a few) and a wealth of newcomers.  We evan had Oracle DBAs taking entire MySQL sessions at OOW! They also commented on how open the MySQL community is to everyone.

It was great to see the excitement about the future 5.6 Release of MySQL and 7.2 of MySQL Cluster.

Now that I have made it back I look forward to testing the DMR releases. Personally, I am excited about the enhancements with replication and the continued stability of InnoDB. Look for future posts on this.

Feel free to contact me for anything, (keith.larson[@]oracle[.]com)

And yes.. the Dolphin pictured was waiting for me when I arrived home.

Thursday, October 6, 2011

It's a WRAP with NetApp!

MySQL @ #OOW11 is complete and we finished off with a very interactive session with Karthikeyan from NetApp. This was the most interactive session I had seen @ #OOW this year.

Some topics covered included:
  • NetApp : 
    • Deduplication
      • 30% to 40% space savings for MySQL on NetApp
    •  Snapshot and instant point in time data copies with minimal storage space
    •  What is Snap Creator?
    • FlexClone
      • "A snapshot that is writable"
      • "Understanding Snap Creator and FlexClone is a career builder" -- Bill Heffelfinger
    • DataONTAP Compression
      • 75% compression in testing so far and they are eager to find more real world customers to test with.
    • Multitencacy
    • DataMotion
      • Movement of MySQL Data across storage systems
    • Protection Manager
      • Policy-Based Data Management
    •  NetApp & MySQL Customer Use Cases
      • Siemens IT Solutions and Services
      • Vantage Media
We ran a little long because but it was good to end OOW with an interactive session and not just an empty room.

Here is are a few related items for NetApp & MySQL:

NetApp Storage Solutions for MySQL® Enterprise

MySQL Enterprise Backup & Recovery

Best Practices Guidelines for MySQL

NetApp Solutions for MySQL


Very Encouraging

The last day of #OOW is here and MySQL events continue. Even after the big Sting concert last night. I am now sitting in a MySQL Crash Course talk given by Alexander Nozdrin and Dmitry Lenev. The room is packed (~100) ! A poll of the room  showed that the majority here are all new to MySQL. Very encouraging to see the MySQL community growing !

Tuesday, October 4, 2011

MySQL 5.6 Roadmap

The MySQL Roadamap session @ OOW was a wealth of information. 

Gier was able to talk some in-depth about the MySQL optimizer , ICP , Batched Key Access ("BKA") and Multi-Range Read ("MRR") , 'explain' updates, as well as traces. 

You can catch up on this as well with Rob Young's blog post available here.

Yoshinori also has a great blog post about 5.6.3:

Rob young also talked on MySQL Database Goals:

  •   Re-factored architecture
    • Pluggable feature set
    • Deprecate legacy "baggage"
    • Real data dictionary  (remove .frm files)
  •   Better Cloud, Hosting, Saas features
    • Auto-sharding, load balancing , automatic failover 
    • Online operations
    • Multi-tenancy, schema/catalogs, resource control, "elastic resources"
  • Windows platforms
    • Continue improvements
MySQL Cluster Goals
  • Ease of use
  • Enhanced API
  • Performance & Capacity increases
  • Wider Deployment options

Get MySQL 5.6.3 now and test out these great options !
MySQL 7.2.1 beta is available here.
Look for more blog posts on this topics as myself and the community tests and enjoys these features.


Today will be a busy day for MySQL @OOW.  The sessions today cover a wide range of MySQL users.

Sessions include:
  • Advanced MySQL Replication 
  • Getting to know the MySQL Enterprise Monitor
  • MySQL Performance Tuning
  • Introduction to InnoDB
  • Getting the most out of MySQL on Windows
  • MySQL for Beginners
  • The MySQL Roadmap
Today also includes a MySQL WorkBench Hands on Lab and we then get to end the day with the MySQL Community Reception

The wealth of knowledge here from MySQL is outstanding.  We have even heard from attendees that are here only for MySQL.

Ok time to get started...!

Monday, October 3, 2011

MySQL Resources

So Sunday & Monday have been great! I was glad to be able to visit and talk with so many people from the MySQL community. While talking with the community, we often directed people to Sheeri's QR tag, which directed them to ( She posted about this here. ) This simple page is a great way to get a lot of resources in front of people with very few clicks. I was curious why doesn't have something similar so we can help out. So, we made one.

So we took a quick look around and gathered some quick links for you. It is a simple list of resources that can be used to help direct people to top content about MySQL. Hopefully, you all find this page as useful as the QR tag was so far at OOW. You can find the new page here: it is also labeled community resources in the devzone.

Topics include:
Scripts and Code
Tips and tricks
Recommended books
MySQL Events
MySQL User Groups
MySQL Oracle ACEs
Contribute Code
Subscribe to the Newsletter
Bugs Database
MySQL Technical Support
Training and Certification
MySQL Community Relations Team

We are looking forward to your help/links to update these pages. If, like Sheeri, you maintain your own list of helpful MySQL Resources, or, if you have a favorite book, or a favorite script, or a favorite tool, please let us know ! Put the MySQL Community Team to work, we are here to help you!

Sunday, October 2, 2011

Oracle Open World Day 1

It was a good to put  face to the names today at day 1 of #oow11 .

Tried to spend most of the day supporting the MySQL Community Kiosk with Sherri, Sarah and Dave. I was able to step into a couple sessions to show support as well.

Day two the MySQL sessions will be at the Marriot Marquis Hotel. Moscone South will have MySQL demos at the Exhibition Hall and Moscone West will have the MySQL Community Kiosk. Come on down and support MySQL!

Friday, September 30, 2011

Focus on MySQL | Oracle OpenWorld 2011

Oracle OpenWorld is just a few days away now!

As a reminder, you can attend all of the Sunday MySQL sessions organized by IOUG and the MySQL community with a full OOW pass.

The MySQL community reception is also available to everyone on Tuesday .

See you soon !

Friday, September 23, 2011

Workbench Scripts

A was checking out the new Workbench today after I saw this blog post

It all worked great and very easy.  Nice clean code that helps out the PHP Developer.

$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
    or die ('Could not connect to the database server' . mysqli_connect_error());


$query = "SELECT * FROM exampledb";

if ($stmt = $con->prepare($query)) {
    $stmt->bind_result($field1, $field2);
    while ($stmt->fetch()) {
        //printf("%s, %s\n", $field1, $field2);

While this is a simple example it is a fantastic way for new developers to get started and learn how things are done.

A DBA can easily write a complex query and hand over PHP code to the developer. Nice work!

The ability to then write your own plugins really opens it up for some advanced developers. 

This is a step in the right direction and supports working together between DBA and developer.

MySQL Oracle Open World Session Calendar

With 47 MySQL sessions at Oracle Open World this year we are going to be busy. Keeping updated on them in a easy fashion will be best for all of us.

Sheeri has a matrix here:  

Oracle as a PDF here :

I went ahead and made public calendars so we can sync this information across our phones and etc....  I will update them when I find out  about any changes and then of course it should sync out to all of you who use them.









Come Swim with the Dolphins !

An Oracle Open World blog post for us ! It has highlights and links for you to see all the great MySQL content at Open World.

Wednesday, September 21, 2011

Oracle Open World MySQL Sessions

Just ten days until Oracle Open World and MySQL is going to have a big presence! 
“Focus on MySQL” overall schedule is available here:

Here are a couple thoughts of mine about the week:
Support and visit the MySQL Community Kiosk at Moscone West Hall Level 2 as much as possible.

Sunday @ Moscone West 
A great line-up of presentations by the IOUG/MySQL Community itself. It will be hard to pick which sessions to see. MySQL ACE members Ronald Bradford and Giuseppe Maxia are some examples of the experts talking on Sunday.

Monday @ Marriot Marquis
The MySQL keynote by Tomas Ulin, of course, should not be missed.
Catch sessions by Oracle engineers and MySQL ACE members such as Sherri Cabral, Sarah Novotny, and Yoshinori Matsunobu.

Tuesday @ Marriot Marquis
Looks to have great sessions on MySQL replication, MySQL for Oracle DBAs, MySQL on Windows, as well as InnoDB and the MySQL roadmap. All look to be good to see.

Also take note of the hands on labs and learn from the expert himself.
MySQL Workbench: Developing MySQL Applications on Windows
- Mike Zinner, Software Development Director, Oracle
Tuesday at 11:45am – Marriott Marquis Salon 10/11

The MySQL Community Reception is also open to everyone,  space is limited so register today.

Wednesday @ Marriot Marquis
NoSQL, backup,monitor, data recovery and protection, scalability and Oracle integration talks from the experts. Mat Keep, Andrew Morgan, Rob Young, Lars Thalmann just to name a few.

Thursday @ Marriot Marquis
NoSQL Interfaces to MySQL Cluster, by Craig Russell, and What causes MySQL downtime, by Baron Schwartz a MySQL ACE member,  both look like good sessions. Cloud Computing solutions for MySQL is another good one by Charles Bell.

These are just a few of the 47 MySQL Sessions. All of the sessions are going to be loaded with knowledge from MySQL experts and it will be tough to pick  which expert you want to listen and learn from. 

Thursday, September 15, 2011

Developer Day MySQL - Minneapolis

Another MySQL Developer Day is available. I will be joining the MySQL team in Minneapolis Thursday, November 17, 2011.

You can register here.

Friday, September 2, 2011

Colorado MySQL meetup group

I just started a Colorado MySQL meetup group. This is long overdue !

If your in the Colorado area feel free to join and we can work on getting some meetings started.

Thursday, September 1, 2011


Explain.... It is a very simple command that I feel is one of the most overlooked commands by new MySQL users. It is also a very valuable command available for MySQL. I realize I am preaching to the choir for a lot of MySQL users. However, for everyone who uses explain, we are bound to have many who do not. 
The MySQL documentation on this is great and available here and Optimizing Queries with EXPLAIN
Developer and a dba issues will continue for years,  but we can at least start on a level playing field. When writing a query, regardless of what it is, it is a good practice is to start it with explain first. This can achieve a couple things for you.
  • It checks your syntax to help you avoid mistakes.
  • Allows you to display information from the optimizer
Using the world example data for avoiding mistakes via explain. (innodb version)

mysql [localhost] {root} (world) > explain extended SELECT City.CountryCode , City.Name , Country.Name , Country.Capital , Country.Population FROM City , Country WHERE Country.Continent = 'North America' AND City.CountryCode = Country.Code;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | Country | ALL | PRIMARY | NULL | NULL | NULL | 200 | 100.00 | Using where |
| 1 | SIMPLE | City | ref | CountryCode | CountryCode | 3 | world.Country.Code | 18 | 100.00 | |
2 rows in set, 1 warning (0.00 sec)

This query does work but I would never write it this way. It works for such a small data set but a join would work better.

mysql [localhost] {root} (world) > explain extended SELECT C.CountryCode , C.Name , Y.Name , Y.Capital , Y.Population
-> FROM City C
-> INNER JOIN Country Y ON Y.Code = C.CountryCode ; WHERE Y.Continent = 'North America' ;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | Y | ALL | PRIMARY | NULL | NULL | NULL | 200 | 100.00 | |
| 1 | SIMPLE | C | ref | CountryCode | CountryCode | 3 | world.Y.Code | 18 | 100.00 | |
2 rows in set, 1 warning (0.00 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Y.Continent = 'North America'' at line 1
mysql [localhost] {root} (world) >

An error! Explain found this simple typo. While updating query to use a join, I adjusted my table references to aliases and a semicolon was placed before the where. Simple typo, but in the real world it could have been worse. What if your typo, enabled a full table scan across a table with billions of rows of data? Real world issues are usually related to “such an easy query” issues, so it is rushed. Then code gets pushed and nothing worked as planned. We can all write, clean, effective, and optimized queries, if we pay attention and understand what we are executing.

So error fixed .
mysql [localhost] {root} (world) > explain extended SELECT C.CountryCode , C.Name , Y.Name , Y.Capital , Y.Population
-> FROM City C
-> INNER JOIN Country Y ON Y.Code = C.CountryCode WHERE Y.Continent = 'North America' ;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | Y | ALL | PRIMARY | NULL | NULL | NULL | 200 | 100.00 | Using where |
| 1 | SIMPLE | C | ref | CountryCode | CountryCode | 3 | world.Y.Code | 18 | 100.00 | |
2 rows in set, 1 warning (0.01 sec)

Using the employee example data to display information from the optimizer via explain. (innodb version)

The real use and best use of explain is to “display information from the optimizer about the query execution plan.” (refman) When executing joins across tables you need to understand what your pulling and make sure the best indexes are being used.

mysql [localhost] {root} (employees) > explain SELECT e.first_name , e.last_name , e.gender , e.hire_date , t.title , s.salary
FROM employees e
INNER JOIN titles t ON t.emp_no = e.emp_no AND e.hire_date BETWEEN t.from_date and t.to_date
INNER JOIN salaries s ON s.emp_no = e.emp_no AND e.hire_date BETWEEN s.from_date and s.to_date
WHERE e.gender='M'
ORDER BY e.hire_date ASC;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | NULL | 300030 | Using filesort |
| 1 | SIMPLE | t | ref | PRIMARY,emp_no | PRIMARY | 4 | employees.e.emp_no | 1 | Using where |
| 1 | SIMPLE | s | ref | PRIMARY,emp_no | PRIMARY | 4 | employees.t.emp_no | 4 | Using where |
150291 rows in set (3.88 sec)

300030 rows ? NULL KEY ?  ick.

mysql [localhost] {root} (employees) > ALTER TABLE employees ADD KEY gender (gender);
mysql [localhost] {root} (employees) > explain SELECT e.first_name , e.last_name , e.gender , e.hire_date , t.title , s.salary
FROM employees e
INNER JOIN titles t ON t.emp_no = e.emp_no AND e.hire_date BETWEEN t.from_date and t.to_date
INNER JOIN salaries s ON s.emp_no = e.emp_no AND e.hire_date BETWEEN s.from_date and s.to_date
WHERE e.gender='M'
ORDER BY e.hire_date ASC;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | e | ref | PRIMARY,gender | gender | 1 | const | 150015 | Using where; Using filesort |
| 1 | SIMPLE | t | ref | PRIMARY,emp_no | PRIMARY | 4 | employees.e.emp_no | 1 | Using where |
| 1 | SIMPLE | s | ref | PRIMARY,emp_no | PRIMARY | 4 | employees.t.emp_no | 4 | Using where |
3 rows in set (0.00 sec)

Now I am not doing a full table scan but using the index on gender and only across 15k rows.
Explain shows this information so we can adjust when required.

These are simple examples I realize. Adding an index also has to be reviewed and just not put on everything.

Take the time to use explain. Make it a habit.

Other blog posts about explain over the years:

Wednesday, August 31, 2011

MySQL Community Reception at Oracle OpenWorld

Save the date ! Tuesday, October 04, 2011 7:00 PM – 9:00 PM
You’re all invited to Oracle’s MySQL Community Reception. This event is open to everyone. No OpenWorld registration is required to attend.

Wednesday, August 24, 2011

MySQL Developer Day in DC

Many thanks to all of those from MySQL Community who attended the Washington DC Developer day.
We had great questions from a full room of community users and we look forward to any feedback that you might have.

I look forward to the next one ! 

Tuesday, August 23, 2011

Excitement at Goscon

The Goscon event was cut short today due to the earthquake. The 5.9 quake was felt as we had the MySQL team was supporting the booth. Nothing like a little more excitement to awaken the show floor.

All was not lost, it was good to see and talk with Baron Schwartz from Percona.

Wednesday, August 17, 2011

Show your support for MySQL Workbench !

The 2011 DevProConnections CommunityChoice Awards has two categories that include nominations for MySQL Workbench !

You can support MySQL Workbench with your vote. The two categories that include Workbench are, “Component Set” (page 1 # 7) and “IDE” (page 2 #14).

The survey is available here and information about workbench is always available here:

Wednesday, August 10, 2011

Future of Open Source

Back in 1995 we had the Initial releases of the Apache Server, MySQL and PHP. Three major open source projects that still power the web today. Majority of them running on GNU/Linux platforms.
Apache then soon also added Tomcat as well to the mix to further enhance this powerful combination. So what is next?

Now that we are well into the 21st century, what will be the next stable projects of the Internet? What will change how we use the Internet? Certainly we have great projects like phpMyAdmin, Zimbra, Drupal, Joomla, Wordpress, SugarCRM, Mozilla and OpenOffice, osCommerce and memcached. I am sure I missed some.  I am not discrediting any of these projects, I have used and supported them. I am more curious about what we all think is the next big thing to change the way we relate and present things on the Internet. Will Cloudera , Owncloud , VoltDB, Postgresql, or OpenStack change how we do things ? Will the mobile market be revolutionized by open source software? Will cloud computing be the answer?

Time will tell but I am very curious about others opinions.

Tuesday, August 9, 2011

MySQL 5.6 Feedback

To follow up on a post by Dave --

The MySQL Community Team would like your feedback about MySQL 5.6 ! What are you excited to see and test? What have you tested and benchmarked? Did you previously see the advantages of going to MySQL 5.5 and now eager to try 5.6? What are your favorite new features: Full Text Search InnoDB tables, Binary Log API, Binary Log Group Commit, multi-thread slaves, memcached-to-InnoDB? Feel free to email keith.larson[@]oracle[.]com with feedback.

If you have been busy with development, and not realized that previews of MySQL 5.6 are available, then now is your chance. Previews of MySQL 5.6 are available at for you to evaluate.