Author Archives: OpenSorceDBA

Joomla! World, PHP[World], and Live360

Joomla! and MySQL — Slightly Different Paths to the Same Target will be the subject of my presentation at Joomla! World and it will be the Sunday morning keynote. Then I fly to Washington, D.C. for the first ever PHP[World] where I hold a SQL for PHP Developers workshop and have a session on MySQL 5.7! And then I am off to Oralndo for Live 360 megaconference to show off MySQL to a very large gathering of Windows developers.

Leave a comment

Filed under community team

MySQL Marinate for the Holiday Season

Just a friendly reminder that you can pick up MySQL Marinate whenever you want! You can use the master list at http://www.meetup.com/Virtual-Tech-Self-Study/messages/boards/thread/38423162 for reference – just ignore the dates and work at your own pace!

We found that very few people were taking the dates to heart, so we stopped trying to organize around them. The message boards are still valid, so feel free to ask if you have any questions – we are here to help!

The above was a quick note from Sheeri Cabral about the wonderful MySQL Marinate program that arrived in my email. This is a great way for novices to learn MySQL and for old war horses to find some new insights.

Leave a comment

Filed under Basics, MySQL

MySQL Utilities — The General Operations

The MySQL Utilities are divided into five groups and this blog post covers the second group — General Operations. These include mysqldiskuseage, mysqlfrm, mysqlindexcehck, mysqlmetagrep, mysqlprocgrep, mysqluc, and mysqluserclone.

For checking the disk usage of your data, there is mysqldiskusage. The following example is a on a fresh install of 5.7.5 on Ubuntu.
mysqldiskusage --server=root:hidave@localhost -b -m -i
[sudo] password for dstokes:
# Source on localhost: ... connected.
# Database totals:
+---------------------+-------------+
| db_name | total |
+---------------------+-------------+
| mysql | 13,851,103 |
| performance_schema | 721,458 |
| sakila | 31,865,975 |
| sys | 0 |
| world | 1,783,833 |
+---------------------+-------------+

Total database disk usage = 48,307,605 bytes or 46.07 MB

# Binary logging is turned off on the server.
# InnoDB tablespace information:
+--------------+-------------+
| innodb_file | size |
+--------------+-------------+
| ib_logfile0 | 50,331,648 |
| ib_logfile1 | 50,331,648 |
| ibdata1 | 79,691,776 |
+--------------+-------------+

Total size of InnoDB files = 180,355,072 bytes or 172.00 MB

#...done.

Checking indexes is simple with mysqlindexcheck.
mysqlindexcheck --server=root:hidave@localhost world
# Source on localhost: ... connected.
# The following index is a duplicate or redundant for table world.CountryLanguage:
#
CREATE INDEX `CountryCode` ON `world`.`CountryLanguage` (`CountryCode`) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE `world`.`CountryLanguage` ADD PRIMARY KEY (`CountryCode`, `Language`)

Add -i to get a list of all indexes on the tables in the database or –stats to see the 5 worst performing indexes.

Ever need to look for a column name or trigger quickly then you need to check out mysqlmetagrep. For example lets search for a column named CountryCode.

mysqlmetagrep --pattern="CountryCode" --server=root:hidave@localhost
+------------------------+--------------+------------------+-----------+-------------+--------------+
| Connection | Object Type | Object Name | Database | Field Type | Matches |
+------------------------+--------------+------------------+-----------+-------------+--------------+
| root:*@localhost:3306 | TABLE | City | world | COLUMN | CountryCode |
| root:*@localhost:3306 | TABLE | CountryLanguage | world | COLUMN | CountryCode |
+------------------------+--------------+------------------+-----------+-------------+--------------

And yes it accepts regex!

The ability to search he server process list comes with mysqlpocgrep.
mysqlprocgrep --server=root:hidave@localhost
+------------------------+-----+-------+------------------+--------+----------+-------+------------+------------------------------------------------------------------------------------------------+
| Connection | Id | User | Host | Db | Command | Time | State | Info |
+------------------------+-----+-------+------------------+--------+----------+-------+------------+------------------------------------------------------------------------------------------------+
| root:*@localhost:3306 | 25 | root | localhost:52475 | None | Query | 0 | executing | SELECT
Id, User, Host, Db, Command, Time, State, Info
FROM
INFORMATION_SCHEMA.PROCESSLIST |
| root:*@localhost:3306 | 15 | root | localhost:52307 | None | Sleep | 3 | | None |
| root:*@localhost:3306 | 6 | root | localhost:52031 | world | Sleep | 132 | | None |
| root:*@localhost:3306 | 14 | root | localhost:52306 | None | Sleep | 911 | | None |
| root:*@localhost:3306 | 5 | root | localhost:52030 | world | Sleep | 132 | | None |
+------------------------+-----+-------+------------------+--------+----------+-------+------------+------------------------------------------------------------------------------------------------+

Where this utility shines is the ability look for age of a process in the current state (long running or hung process), process running a certain command, process running against a selected database or from a certain user, and possibly kill that process. Heck it will even output the SQL code for a stored procedure that could fed into the Event Manager.

To clone a user from the local or remote server or to get a list off accounts, there is mysqluserclone.

mysqluserclone --source=root:hidave@localhost --list
# Source on localhost: ... connected.
# All Users:
+----------+------------+
| user | host |
+----------+------------+
| dstokes | % |
| root | localhost |
+----------+------------+

And finally there is mysqluc which us the command client for running the MySQL Utilities, usually from MySQL Workbench.

Leave a comment

Filed under Administration, MySQL Utilities, MySQL Workbench

MySQL Utilities — The Database Operations

Version 1.5 of the MySQL Utilities are divided into five groups — Database Operations, General Operations, High Availability Operations, Server Operations, and Specialized Operations.

The Database Operations are mysqldbcompare, mysqldbcopy, mysqldbexport, mysqldbimport, and mysqldiff.

mysqldbcopy is used to copy databases between servers (or clone them on the same server. The default is to copy (or clone) everything or the –exclude option to exclude an object, change the storage engine with –new-storage-engine, and locking of the source databse can be turned off with –locking. There is even a –rpl option to pass replication statements so the the new copy a clone of the source server or sync the new copy to the same master as that the slave used for the source database. There is a –drop-first to make a fresh copy, -all to copy all databases

mysqldbcopy --source=root:hidave@localhost --destination=root:hidave@localhost world:world_copy
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database world renamed as world_copy
# Copying TABLE world.City
# Copying TABLE world.Country
# Copying TABLE world.CountryLanguage
# Copying data for TABLE world.City
# Copying data for TABLE world.Country
# Copying data for TABLE world.CountryLanguage
#...done.

More details on the man page.

After copying (or cloning) a database it is easy to double check
your work with mysqldbcompare.
mysqldbcompare --server1=root:hidave@localhost world:world_copy --run-all-tests
# server1 on localhost: ... connected.
# Checking databases world and world_copy on server1
#
# Object definitions differ. (--changes-for=server1)
#

--- `world`
+++ `world_copy`
@@ -1 +1 @@
-CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `world_copy` /*!40100 DEFAULT CHARACTER SET latin1 */

# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE City pass pass pass
# TABLE Country pass pass pass
# TABLE CountryLanguage pass pass pass

# Databases are consistent.
#
# ...done

More at the man page.

It is easy to use mysqldiff to check differences between two instances. For demonstration purposes, a dummy column was added to wold_copy.City.
mysqldiff --server1=root:hidave@localhost world:world_copy
# server1 on localhost: ... connected.
# Comparing `world` to `world_copy` [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- `world`
+++ `world_copy`
@@ -1 +1 @@
-CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `world_copy` /*!40100 DEFAULT CHARACTER SET latin1 */
Compare failed. One or more differences found.

With version 1.5 id comes the –diff to display the differences.

The mysqldbexport command exports metadata, data, or both. There is a –bulk-insert can be used with –export=DATA to export data from a database for bulk import.
mysqldbexport --server=root:hidave@localhost world
# Source on localhost: ... connected.
SET FOREIGN_KEY_CHECKS=0;
# Exporting metadata from world
DROP DATABASE IF EXISTS `world`;
CREATE DATABASE `world`;
USE `world`;
# TABLE: world.City
CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
# TABLE: world.Country
CREATE TABLE `Country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# TABLE: world.CountryLanguage
CREATE TABLE `CountryLanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#...done.
SET FOREIGN_KEY_CHECKS=1;

The man page.

And fianlly mysqldbimport is used to read metadata, data or both. Note that if an object exists on the destination server it will be dropped. If you need to copy only the data after a truncate table, use the –import=data option. See the man page for details.

Next time: The MySQL Utilities General Operations

Leave a comment

Filed under Uncategorized

Raleigh MySQL Tech Tour

MySQL Raleigh Tech Tour

Please join MySQL Sr. Sales Consultant Tony Darnell who will review latest and greatest updates in MySQL 5.6 and share his experience on how they improve performance, scalability and high availability. Topics include:

  • MySQL 5.6 — New Features Update
  • Security Improvements — Encryption
  • Fabric
  • MySQL Enterprise Tools Update
  • MySQL Utilities Update
  • New MySQL Plug-In for Oracle OEM
  • The Road Ahead — MySQL 5.7
  • Oracle Support for MySQL

To register you must have an Oracle account, if you don’t have an Oracle account, sign up is quick and easy.

We look forward to your participation at this exclusive seminar.

Oct 21, 2014
08:30 AM – 12:30 PM
Oracle Raleigh
8081 Arco Corporate Drive,
Suite 270
Raleigh, NC 27617

Featured Speaker:

Tony Darnell,
Sr Sales Consultant

David Stokes,
MySQL Community Manager

Leave a comment

Filed under Tech Tour, Uncategorized

Lightning Talks at MySQL Community Reception

Come dazzle us! Lightening talks will be part of the entertainment at the MySQL Community Reception Tuesday September 30th at 7:00 pm. (RSVP link for reception here). You do not need to be an Oracle Openworld or MySQL Central @ OpenWorld attendee to come to the reception but you do need to RSVP.

You will have five minutes to impress the audience and a chance to win a stuffed dolphin plush toy. There will be no slides, but props will not be discouraged. We are looking for you best material on your favourite subject and, after a full day of tech talk, we are looking to keep the content on the lighter side.

Send you name and the subject of your talk to david.stokes AT oracle.com with the email head of ‘Lightning Talk’.

Leave a comment

Filed under MySQL Central @ Oracle OpenWorld, MySQL Community Team Event

Fossetcon

Fossetcon starts today and the schedule features lots of MySQL Content. I have a MySQL Query Tuning for The Squeemish presentation after lunch. On Friday, MySQL is sponsoring breakfast and Ligaya Turmelle is presenting MySQL Server Performance Tuning. On Saturday I am also presenting on MySQL’s NoSQL Interface — Best of Both Worlds.

If you are in Orlando for the show, drop by Booth 202 and say hello.

Leave a comment

Filed under community team

Does MySQL need a mentoring program?

Does MySQL need a mentoring program? I get calls, emails, and other requests for trained MySQL DBAs and Developers. Human Resources, managers, team leads, and entrepreneurs have the need but can not find the bodies. It is easy enough to download MySQL, get it running, and use it. But the next steps seem problematic for many. There are programs like MySQL Marinate and Girl Develop It to provide some hands on help for beginners. Autodidacts can find tons of MySQL Books and on line information. But how do we take the beginners and get them to intermediate or beyond?

How do we support these new comers, give them a hand if needed, a shoulder to cry on, or just provide someone who has been there before to bounce ideas around when needed? How do we pull them into social networks to warn them of pitfalls, pass on information about new technologies, or just be there as a friendly voice when the air movement device is being impacted by non optimal material? How do we pass on best practices, professional guidance, and the norms of our community? There is only so much forums, IRC, and Stack Overflow can handle. Local users groups are good if you have a local user group.

A good place to start is to see what other Open Source projects are doing. PHP Mentorting is a formal, personal, long term, peer to peer mentorship organization focused on creating networks of skilled developers from all walks of life. Read their info and let me know if you think the MySQL Community needs something similar.

Being a mentor has benefits too. There is an old saying that you really do not know a subject until you can pass on your knowledge to someone else. It also helps bring along someone who could replace you if you decided to climb the corporate ladder. Plus you never know what you fledgling might teach you.

So do we need a MySQL mentoring program?

3 Comments

Filed under Basics, MySQL

Workbench 6.2, CakePHP’s new ORM, and OKC Meetup on Wednesday

In the beginning was the MySQL command line and it was good. Verbose yes, error prone yes, and even tedious. But it was good. The UPDATE USER set SELECT_PRIV=’Y’, DROP_PRIV_’Y”, UPDATE_PRIV=’Y’,……,LAST_BLOODY_PRIV=’Y”; type errors have caught of us old timers. But it was still good.

Then came some GUI tools that were okay. Not great but they had their uses.

And then came MySQL Workbench. And it was very good.

Now there is MySQL Workbench 6.2 and it is excellent. It came out in Beta last week and has built on the proven success of its predecessors. All the usual stuff is there – Server Status, Users & Privileges, Startup/Shutdown, logs, Dashboard, Performance Reports and more. But there area whole bunch of subtle little tweaks that make it easier to use.

For instance, the VISUAL EXPLAIN allows toggling between the visual display and the tabular display. Plus you can now get query stats like the number of rows processed, joins per table, sorting information, and even how many temporary tables were created. And it is easier to toggle between the explain panel and the results grid.

Now that I am back from Europe, I will write a little more on this in the following days.

CakePHP’s new ORMCakePHP 2014 attendees in Madrid

I attended Cakefest in Madrid to hear about the latest in CakePHP. Version 3.0 is almost ready to launch and has many new features that will help speed its adoption. Chief among them is a new ORM’s query builder that is well designed, intuitive, and easy to use. Under the covers is PDO prepared statements to help protect against SQL injection.

Most Object Relational Modelers remind me of a juggler balanced on a large ball while simultaneously swinging a Hulla-hoop around his middle while trying to order coffee in a crowded Starbucks. Too much action for something that can be done much simpler. Object Oriented programmers want everything as an object but the rigors most ORMs demand if much more complicated than writing good SQL in the first place.

The new CakePHP ORM is based on a data mapper pattern and easily interfaces with MySQL, Postgres and SQLserver. The query builder is designed to take advantage of MySQL 5.6 features like improved sub queries. They hope soon to offer Oracle database support. The design is light, easy to read, and can take advantage of sub queries. My preference is still for well written SQL but this query builder is not bad. I am sure it will catch the attention of those who fight other ORMs in their projects.

Next years is CakePHP’s fifteenth anniversary and their spirited crew is doing great things. If you have not investigated them in a while, you really need to spend some time with their new 3.0.

Oklahoma MySQL Meetup

And I will be heading up I35 to talk to the OKC MySQL Meetup tomorrow. Hope to see you if you are in the area!

1 Comment

Filed under DBA Tools, MySQL Workbench, Open Source, User Group

Containing your MySQL Instances

Virtual servers for MySQL are popular but are they the answer? Should we be containing our instances instead. An Updated Performance Comparison of
Virtual Machines and Linux Containers
is an interesting study of using containers over VMs by four brilliant folk from IBM Research in Austin. There are several benefits to containers that are detailed in the study.

So what is wrong with VMs? IBM has been using them since the 1970s. Well, VMs have a static number of virtual CPUs (vCPU) and a fixed amount of RAM that bound performance. And each vCPU can only use up to one real CPUs of clock cycles. Since each VM is a Linux process, resource management like the scheduler come into play.

Containers? They are built on the kernel namespaces. A container acts like its own little Linux box but shares the overhead with other containers and the host. Stuff ‘inside’ the container can not see outside. Containers can be set up to use resources between them. Plus they can be constrained to only use a defined amount of resources such as CPU, memory and I/O.

A good part of the study looks at using MySQL on native hardware, KVM, and three Docker configurations. It is very interesting that throughput with Docker was close to native hardware and much less overhead than the KVM. SysBench shows that KVm hs much higer overhead 40%+ in ll cases. It appears that the container loses 1.5% CPU utilization.

So maybe we need to stop spinning up VMs and start deploying containers. More on this later.

Leave a comment

Filed under Administration, MySQL