MySQL Workbench Tunneling to Socket File only servers

Brandon Johnson at Mozilla has posted a nice tip on how to use MySQL Workbench SSH tunneling with MySQL servers configured to accept only Unix Socket File connections (no TCP/IP). Head over there for more info about how to use the “socat” utility to bridge a SSH tunnel from WB to a socket only MySQL.

MySQL Workbench team hiring C++ and/or Python developers

The MySQL Developer Tools team at Oracle is looking for good C++ and/or Python developers to help with the MySQL Workbench project.

In the Workbench team, you’ll be responsible for developing features, maintaining the existing codebase and interact with the community to improve the project.

We’re looking for candidates to work from office at Guadalajara, Mexico or from home in Romania, Ukraine, Portugal, Poland or Czech Republic, with availability to start in the team in late May.

Job Description

Software Developer, MySQL Workbench

Software Development position responsible for development and maintenance
of Python and/or C++ code for the MySQL Workbench product.

Requirements

* 5+ years experience developing solid Python or C/C++ code
* Experience developing desktop GUI applications
* Good knowledge of the C/C++ language
* MySQL database development and administration

* Experience with one or more of the GUI platforms we use (gtkmm, Cocoa, .NET/Winforms) a plus

About the company

Oracle, the world’s premier database company, is hiring expert engineering staff for MySQL
– the world’s most popular open source database because of its consistent fast performance,
high reliability and ease of use. MySQL engineering organization is highly technical team
spread across different geographies and strive for excellence and high quality in day-to-day work processes.

To apply, search for MySQL Workbench in
http://irecruitment.oracle.com

MySQL Workbench: Introducing Utilities

MySQL has the well earned reputation for ease-of-use and “15-minutes-to-success”, since we continually focus making the server easy to use. MySQL Workbench provides the visual tools for database design, development, and administration. However, many DBAs prefer using the command-line, and there are many tasks that require the creation scripts for doing the job.

To make it easier to work with the server, the latest release of the MySQL Workbench—version 5.2.31—contain a set of Python scripts intended to make the life easier for DBAs by providing easy-to-use utilities for common tasks, which were introduced in the blog MySQL Workbench: Utilities. The set currently consists of just a few utilities, but will expand over time.

The utilities available in the Workbench are:

mysqldbcopy
Copy databases between servers.
mysqldbexport
Export databases to a file in different formats, including: SQL, comma-separated files, and tab-separated files (and some more).
mysqldbimport
Import object definitions and/or data from a file—in different formats, similar to mysqldbexport—into a database.
mysqlindexcheck
Check for redundant or duplicate indexes on a list of tables or databases. It can also generate DROP statements to
remove redundant indexes.
mysqlmetagrep

Search MySQL servers for objects containing fields matching a pattern.

mysqlprocgrep

Search MySQL servers for processes matching a pattern and perform actions.

mysqlreplicate

Setup replication between two servers.

mysqlserverclone

Start a new instance of a server to experiment with. This is used to test the utilities, but can be used whenever you need to set up a scratch server to test something.
mysqluserclone

Copy a MySQL user to one or more new users on another server

Finding stuff on servers with ease

In order to search for various things on servers—for example, searching objects and processes—there are two commands that can be used: mysqlprocgrep and mysqlmetagrep. The name “grep” is borrowed from Unix where the grep(1) commands that can be used to search inside files, but in this case you can search among processes and inside metadata on a server.

Example: searching processes using mysqlprocgrep

With mysqlprocgrep you can find all processes that match certain conditions and either just print them out, or kill either the connection or the query. So, for instance, to see all connections that have been idle for more than 2 minutes, we can use:

$ mysqlprocgrep --server=root:password@localhost --match-command=sleep --age=+2m
+------------------------+-----+-------+------------+-------+----------+-------+--------+-------+
| Connection             | Id  | User  | Host       | Db    | Command  | Time  | State  | Info  |
+------------------------+-----+-------+------------+-------+----------+-------+--------+-------+
| root:*@localhost:3306  | 39  | mats  | localhost  | None  | Sleep    | 248   |        | None  |
+------------------------+-----+-------+------------+-------+----------+-------+--------+-------+

In the example above, I would especially like you to note how the server connection information is provided. To provide information on what server to connect to and how, we have introduced a new syntax to represent the connection information which we call a connection specification:

user:password@host:port:socket

In the connection specification, the password, port, and socket are optional and can be excluded, which will make the default being used instead.

This is used instead of having separate switches (for example, –host and –user) that the server client programs are using. In addition to being easier to write, it also means that it is possible to provide multiple servers to command (where it makes sense, of course). You might be interested in knowing that both mysqlprocgrep and mysqlmetagrep accepts multiple servers.

If you now want to kill these idle connections, you can just add the –kill-connection option to the command, and the connection of all matching processes will be killed.

$ mysqlprocgrep --server=root:password@localhost \
> --match-command=sleep --age=+2m

In a similar way, if you have a long-running update from a special user (say, www-data), you can kill all the queries in one shot using the command:

$ mysqlprocgrep --server=root:password@localhost \
> --match-user=www-data --match-command=query    \
> --match-state=updating --age=+1m               \
> --kill-query

Example: finding objects using mysqlmetagrep

At times, you also find some odd reference to a column or index, you’re not quite sure, or you want to find out what objects are using a column named ‘db’. In those cases, mysqlmetagrep comes in handy.

The utility is used to find all objects that contain a field that matches the provided pattern. The pattern can be given either as a SQL simple pattern as defined by the SQL standard (this is what you usually use with LIKE), or using POSIX regular expressions (which is what you usually use with REGEXP in SQL). The default is to use the SQL simple pattern. So, to search for any objects having a column ‘host’, we can
use the command:

$ mysqlmetagrep --server=root:password@localhost --pattern=host --search=column
+------------------------+--------------+---------------+---------------------+-------------+----------+
| Connection             | Object Type  | Object Name   | Database            | Field Type  | Matches  |
+------------------------+--------------+---------------+---------------------+-------------+----------+
| root:*@localhost:3306  | TABLE        | PROCESSLIST   | information_schema  | COLUMN      | HOST     |
| root:*@localhost:3306  | TABLE        | columns_priv  | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | db            | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | host          | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | host          | mysql               | TABLE       | host     |
| root:*@localhost:3306  | TABLE        | procs_priv    | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | servers       | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | tables_priv   | mysql               | COLUMN      | Host     |
| root:*@localhost:3306  | TABLE        | user          | mysql               | COLUMN      | Host     |
+------------------------+--------------+---------------+---------------------+-------------+----------+

Since the SQL simple patterns are default, this require an exact match and you will only find objects with columns exactly named ‘host’. To find all column containing the word ‘host’, you have to add wildcards to the pattern:

$ mysqlmetagrep --server=root:password@localhost --pattern=%host% --search=column
+------------------------+--------------+---------------+---------------------+-------------+------------+
| Connection             | Object Type  | Object Name   | Database            | Field Type  | Matches    |
+------------------------+--------------+---------------+---------------------+-------------+------------+
| root:*@localhost:3306  | TABLE        | PROCESSLIST   | information_schema  | COLUMN      | HOST       |
| root:*@localhost:3306  | TABLE        | columns_priv  | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | db            | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | general_log   | mysql               | COLUMN      | user_host  |
| root:*@localhost:3306  | TABLE        | host          | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | procs_priv    | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | servers       | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | slow_log      | mysql               | COLUMN      | user_host  |
| root:*@localhost:3306  | TABLE        | tables_priv   | mysql               | COLUMN      | Host       |
| root:*@localhost:3306  | TABLE        | user          | mysql               | COLUMN      | Host       |
+------------------------+--------------+---------------+---------------------+-------------+------------+

Creating and configuring servers with ease

There are three utilites that I will just mention briefly, because they are not very complicated to use: mysqlserverclone, mysqlreplicate, and mysqluserclone.

To create a scratch servers using mysqlserverclone for testing something, it is as easy as:

$ mysqlserverclone --server=root:password@localhost \
> --new-data=/tmp/data
# WARNING: Root password for new instance has not been set.
# Cloning the MySQL server running on localhost.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
#...done.

It will create a new server from the original, copy the existing databases, and and start the server. You can supply a new port using the –new-port, but if you do not do that, it will pick the default port 3307.

If you want to set up replication quickly and easily, you can do that using mysqlreplicate:

$ mysqlreplicate --master=root:password@localhost \
> --slave=root@localhost:3307 --rpl-user=repl_user:xyzzy
# master on localhost: ... connected.
# slave on localhost: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

When setting up replication, the mysqlreplicate does some basic checking to ensure that replication will work. It checks that there is a server ID assigned and also checks that binary logging is enabled. If something is not right, it will abort the setup and report error.

The last utility that is useful in setting servers up is mysqluserclone. The utility is used to create new users based on an existing one. So, to create a new user ‘chuck’ with password ‘xyzzy’ on localhost from an existing user ‘mats@localhost’,
you can use the command:

$ mysqluserclone --source=root:password@localhost \
> --destination=root:password@localhost \
> mats@localhost chuck:xyzzy@localhost
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Cloning 1 users...
# Cloning mats@localhost to user chuck:xyzzy@localhost
# ...done.

Moving stuff around with ease

There are three utilities that can be used to move data around: mysqldbcopy, mysqldbexport, and mysqldbimport.

With mysqldbcopy, you can copy a database from one server to another, or several databases from one server to another. When copying a database, it not only copies the table definitions, but all associated objects such as triggers, events, routines, and also database-level grants.

With mysqldbexport you can export one or more databases into various formats, including (but not limited to) pure SQL, comma- and tab-separated values, and also a nice human-readable table.

With mysqldbimport you can import data in files into a database. In contast to using LOAD DATA INFILE, this will generate the INSERT statements to inject the data into the server.

The road ahead

The current set of utilities are just a small start, and we expect more utilities to be added over time and also improve on the existing utilities, so if you want to help, you can do that by:

We are very interested in feedback of any form—bug reports, suggestions for new utilities, suggestions for improving the existing utilities—so if you are a MySQL Expert DBA:

  • Let us know how to improve our utilities
  • Send us suggestions or ideas for new utilities
  • Write your own utilities
  • Contribute patches and/or new utilities

and if you are a Python programmer and/or developer:

  • Let us know how to be more Pythonic
  • Suggest improvements of the code
  • Build and/or contribute additional utilities on top of ones we provide

MySQL Workbench 5.2. Query Functionality Update

Few words about current status of 5.2 branch, its main feature Query Editor, and plans for nearest future about it. 5.2 is still in alpha stage, but we did good progress lately and plan to release beta version soon.
For v5.2.2 major efforts were concentrated on stabilization of existing functionality and code base. There are few improvements in Query Editor visible to end user, while a good bunch of bugs were revealed and fixed – thanks to community for bug reporting. Even more fixes/improvements were merged from 5.1 branch. While we continue to work on stabilization, we plan to add following features before transition to beta stage:

  • Client side sorting
  • Advanced editor for text fields of recordset
  • Saving/retrieving BLOB values
  • Option to propagate changes made to recordset to multiple DBMS instances

Keep an eye on new releases, your feedback is very valuable and helps a lot to come up with a development roadmap and reach product maturity much earlier. Please contribute your remarks for 5.2 and ideas for subsequent versions if you didn’t so yet: Forum: Collecting ideas for MySQL Workbench – SQL IDE

MySQL Workbench 5.2 Querying Quick-Tour

For anybody interested in trying out our new MySQL Workbench 5.2 Alpha2 I have prepared a short Quick-Tour that will show you the most important steps to successfully use WB to query your databases.

Manage Your Connections

MySQL Workbench 5.2 introduces a new Home Page that makes it very easy to access all your Database Connections and EER Models. It features the Workbench Central Panel, the Database Connections Panel and the Model Files Panel.

In order to be able to connect to your  MySQL server you have to create a new Database Connection so MySQL Workbench knows about your server instance. Follow the steps shown below to create your first Database Connection.

Click on the screen shot to see it at full size.WB Query QuickTour Screen 1 small

Connecting to and Working with the MySQL Server

Once you have created your connection, a new Connection Icon will appear on the Database Connections Panel. Double-click this icon to open a SQL Editor for this connection.The next screen-shot shows how to query the database.

Again, click on the image to see it at full size.WB Query QuickTour Screen 2 small

Apart from writing SQL statements in the editor, you can also load scripts via the tool bar buttons.

As you can see it is really straight forward to use the Database Connection management and the new Query Functionality inside of MySQL Workbench 5.2. The current Alpha2 is a very early release but it already shows off some of the potential the final WB 5.2 GA feature set release will offer.

Please give it a try and tell us which features you would like to see in the future. But please remember, this is an Alpha version, so do not work on critical production data.

MySQL Workbench speaks SQLite

As many of you already know, MySQL Workbench offers powerful scripting support and a plugin interface that allows anybody to extend the application or add new functionality.

The latest addition to the WB plugin family has been written by Thomas Henlich. It features SQLite compatible SQL export of your Workbench models.

Download it from his blog and please provide feedback on the forums.

We are working on improving the scripting support in WB 5.2 and finally adding proper documentation so even more people can contribute.

Give and Take

Have you ever looked at the source code of MySQL Workbench? There are quite some hidden gems there, e.g. the OpenGL canvas and our latest addtion: a new UI port of the famous open source edit control Scintilla. This port allows us to use this fantastic editor control natively on Mac OS X Leopard as a Cocoa framework.

Scintilla, native Cocoa port on OS X In fact, we are using Scintilla for a long time already, mainly on Windows. It allows us to give you a powerful editor interface for SQL code. This includes features like syntax highlighting, text markers (e.g. for marking syntax errors), code folding, code completion and many more. However, so far we could not use it on OS X. There is a Carbon port available, but that did not work well (particularly, if the rest of the UI is using Cocoa) so we decided to write our own port for Cocoa, taking the Carbon code as the starting point. This new framework is by far not complete but works already so well that we have it included in our recently published 5.2. alpha release. But we thought there should be more than that. Since we are committed to open source software (even though we have some productivity features in our SE version of MySQL Workbench, which are not available in source code, however the vast majority of the code is open) we decided to publish our port as a separate project, so that also users who do not use MySQL Workbench, but Scintilla, can make use of it. In the good tradition of publishing MySQL code on Launchpad we created a new project called Scintilla-Cocoa, where everybody can see what changed and can download the new code. Currently you have to use a Bazaar client to create a local branch of the Launchpad project in order to actually get the code.

Additionally, we sent the author of the Scintilla project Neil Hodgson a patch so that he can incorporate our code into the main distribution. If anybody of you is interested in helping to complete the Cocoa port let us know. We can give you write access to the Launchpad project where you can directly work on the actual code.

Free Workshop + Free Workbench SE at MySQL UC

Everybody attending the MySQL Users Conference this year please mark your calenders! This is your chance to become a MySQL schema design expert as well as learn how to use Workbench from the experts.

We are going to do a free Workshop called “Introduction to Data Modeling with MySQL Workbench” on Tuesday – Thursday 3 – 4:30 PM in the Santa Clara Ballroom.

Some of the topics that will be covered:

  • Database Schema Design
    • Get familiar with common concepts of database schema design
    • Learn to avoid typical mistakes (do-s & don’t-s)
    • Tricks how to resolve typical problems
    • Performance-tune your schemata
  • MySQL Workbench
    • Get introduced to MySQL Workbench
    • Learn how to use MySQL Workbench in real world scenarios
    • Make use of add-ons
    • Check out the MySQL Workbench Standard Edition
    • Learn about the MySQL Workbench roadmap

In addition, everybody attending the Workshop will receive a 12-month MySQL Workbench Standard Edition subscription ($99 value) for free.

There will be a limited number of seats available so make your reservation as soon as possible following this link:

We are also offering a full tutorial on this topic on Monday that will have even more advanced content. Learn more about this here.

Join the MySQL Workbench Team!

If you are a Mac developer, interested in a new professional challenge and like what we have done with MySQL Workbench so far, please consider applying for our open position in the Workbench team.We are looking for an Objective-C / UI coding wunderkind who is also not easily scared by C++.

You can be located anywhere around the world, only hard requirements are that you are comfortable with working from home, have a decent internet connection and are not too far from an airport.We would be happy to have you on the team.

More details about the job below. To speed up the hire process please send your CV to workbench@sun.com. We are preferring people with open source background so please do not forget to include links to your open source work.

Job Summary:

Sun Microsystems is looking for a strong Mac UI developer with in-depth knowledge of Objective-C, C++, X-Code and the Mac OS X 10.5 operating system. Responsibilities will include ownership of upcoming Mac versions of the MySQL Workbench product, user interface design and integration with C++ backend as well as extensive unit- and UI testing.

Job Description

Sun Microsystems, the developer of the world’s most popular open source database MySQL, is looking for a Software Engineer to join a growing team of professionals.  The ideal candidate must have excellent experience in Mac application- and user interface development with X-Code using Objective-C as well as good C++ knowledge.Join the #1 open-source software company, with great benefits, competitive salary, the flexibility to work from home, and the experience of interacting with a worldwide, multi-cultural workforce!

Responsibilities

  • Take responsibility for Mac versions of the MySQL Workbench product
  • Design and develop sophisticated Mac user interfaces
  • Work on the integration of C++ back-end implementation and Objective-C UI code
  • Develop and maintain automated unit-/UI- tests on the Mac platform

Qualifications

  • Minimum 6+ years hands-on Software Development on the Mac platform with strong focus on UI development work
  • Strong background in database technologies and previous experience in maintaining/administrating or developing for DBMS systems (MySQL, SQLite, Oracle, MS SQL Server, DB2 and others)
  • Strong background in SQL (experience developing applications using complex SQL structures), scripting languages (Lua / Python)
  • Ability to define, create, document and verify testing methods and results
  • Strong knowledge of the Mac operating system with in-depth experience of the current APIs found in OS X 10.5
  • Strong knowledge in Database concepts
  • Ability to work independently and remotely with limited supervision
  • Fluent written and spoken English
Desired:
  • Experience with C#/.Net and extensive knowledge of scripting languages Lua, Python, Ruby
  • Experience on the Linux platform
  • Background in QA methodologies and experience using UI test automation tools

MySQL Workbench Pre-Beta Hackfest – Bring It On!

Following our plan to release the MySQL Workbench 5.1 Beta1 later this week we have uploaded a last Alpha build for Ubuntu and the Mac for you to check for serious issues. The following binaries will not be published on our official download site but are only available trought the following links.

Please join us in our #workbench IRC channel on freenode to discuss issues or simply leave a comment here.

ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-oss-5.1.8-1-alpha-i386.deb

ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-oss-5.1.8-alpha.app.zip

To verify your downloads please check the md5 sums:

3feb64fd5dbd5384ad43c639228bb792  mysql-workbench-oss-5.1.8-1-alpha-i386.deb

08d0a13d1bbc09b27cb10cfde2f194ec mysql-workbench-oss-5.1.8-alpha.app.zip

To install use dpkg -i mysql-workbench-oss-5.1.8-1-alpha-i386.deb on Ubuntu 32bit or

unzip the file mysql-workbench-oss-5.1.8-alpha.app.zip on your Mac OS X 10.5 and drag the Workbench.app to your Applications directory.

If you want to test with an existing model file you can download the Sakila Workbench model file from here.

ftp://ftp.mysql.com/pub/mysql/download/gui-tools/sakila_full.mwb

Have fun!