MySQL Workbench 5.2 Quick-Start Tutorial

MySQL Workbench 5.2 introduces a lot of new functionality and therefore this short tutorial will help you to get started quickly.

The Home Screen

The most prominent new addition in respect to previous Workbench releases is the new Home Screen. It allows you to access the main features of Workbench in a nice and easy way and is divided into 4 parts.

The upper Workbench Central panel features a few Links and Action Buttons to quickly access common resources.

The lower Workspace panel shows the main feature sets, grouped horizontally.

  • SQL Development allows editing and execution of SQL queries and scripts, create or alter database objects and edit table data.
  • Data Modeling covers the EER Modeling functionality you might already be familiar with from previous MySQL Workbench releases.
  • Server Administration offers administrative tasks like starting/stopping the server, edit the database server configuration, create user accounts, do data dumps and much more.

To quickly get started, use the following steps.

Home Screen Tutorial
Home Screen Tutorial
  1. Create a new Connection – Before you can start any other tasks — using the SQL Editor, forward engineering your model, or managing your database server, you need to create a new Database Connection. You can do that by using the New Connection Wizard or – if you are an advanced user – by using the Manage Connections dialog.You will have to enter the usual connection parameters, like IP address and port the server is running on as well as your username and password.
  2. Open Editor – After you have created a new connection, it will be displayed in the list of available Database Connections. Simply double-click on the list entry to open a SQL Editor and start querying.
  3. Create a new model – If you want to start your work by designing a visual database design first, click the Create new EER Model action item. An empty model will be created for you, only featuring the my_db schema which you can easily rename to the desired name.
  4. Create a New Server Profile – In order to administer your database server you have to register it within MySQL Workbench first.  Use the New Server Profile Wizard or – if you are an advanced user – use the Manage Server Profiles dialog.
  5. Open Admin – Once the new Server Profile is registered and appears in the list box you can double-click it to open the Admin.

Let’s take a closer look at the SQL Editor.

SQL Editor

The SQL Editor features a straight forward interface that gets you going, immediately.

SQL Editor Tutorial
SQL Editor Tutorial
  1. To give you instant access to your database objects we have added the Live Schema Overview panel to the SQL Editor. To start editing the data of an existing table, insert new data rows or manipulate existing data, simply double-click on the table.
  2. You can also modify the table structure by right-clicking on the table and select Alter … . This also works for any other database object.
  3. Enter your SQL queries and scripts on the SQL Statement panel. You can execute a single or multiple statements at the same time by clicking the Execution Arrow toolbar icon or hitting Ctrl+Return on the keyboard.
  4. If you create new objects by using SQL statements make sure to click the Refresh toolbar icon to update the Live Schema Overview panel and the Schema Tree.

We assume you already know how to use the EER Modeling and therefore we proceed with the Admin.

Admin

The Admin also features a very clear design that makes it easy to access the desired features.

Admin Tutorial
Admin Tutorial
  1. After opening the Admin you can see the Server Status panel at the top. Use this panel to verify the current server status and to check the current Server Health graphs.
  2. Select the corresponding Configuration tab that fits your current task.
  3. After selecting the correct Configuration tab perform the necessary actions on the Configuration page.

After studying this tutorial you should have a basic idea how the use MySQL Workbench 5.2 most efficiently. Please write a comment if you have a tip for other users.

MySQL Workbench Administrator Plugin

Recently we have released Workbench 5.2.4 Alpha. This version has some new features, and amongst them there is Workbench Administrator plugin or WBA plugin for short.

The plugin aims to ease the managing process of server instances. What we offer with WBA is a simple way to check status, configure and control one server instance. Some parts will resemble discontinued MySQL Administrator.

And here is a short summary of what we will have in WBA:

  • Start/Stop server
  • Edit server configuration (my.cnf)
  • Manage user accounts
  • Monitor user connections
  • Status/Server variables browsing
  • Log browsing
  • Dump/Restore

In this alpha we have implemented a subset, which targets local usage only. Remote management and administration is upcoming. The subset includes:

  • Add new Server Profiles
  • Start/Stop the Server
  • Edit Server Configuration (my.cnf)
  • Monitor user connections
  • Status/Server variables browsing

To launch the plugin you need to run Workbench 5.2.4, register a server instance by double-clicking the Manage Icon. Once the server instance is registered you can double click the icon of the server instance. But for the moment let’s dive into what is important to specify at the server instance creation stage.

Our target is ‘System Profile’ tab of ‘Manage Server Instances’ form. Mostly, the whole process should be simple selecting the right profile. Nevertheless, for now (I hope you still remember it is an alpha release), one of the vitally important things is to check location of ini or cnf file. Also you may check commands used to start, stop and query server status. For example OS X profile has that line to check for status: ps -cxa | grep mysqld.

All the commands specified in these entries are run as a sub-processes and sometimes they may require super-user privileges, for that purpose there is ‘use sudo’ checkbox. Normally, command run has exit code which WBA checks for to determine success of failure of the command.

When adding a new Server Profile you also need to select a Connection to the server (You should create that connection in the Connection Management dialog first). That will allow you to browse the Connections and Server Variables in the WBA. It will also be used to update the Server Status periodically every second and show the current state (that is in next alpha).

The way to change the my.cnf/my.ini config file is pretty straightforward. First, if you want a value to be written to the config file, you have to enable the checkbox left to each option. After all changes have been made, you can either press Apply or Discard button. Apply will display a Dialog which shows several things: what has changed, resulting file, commands which are used to save file. If changes are correct, press Apply on the form. That is it.

Stay tuned for the updates on WBA topic.

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.

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.

Nice Blog Postings About MySQL Workbench

Quite a few Blog postings pop up recently, let me outline two of them.

Gerry Narvaja talks about multi-page printing by using the MySQL Workbench community edition utilizing the PDF output. Find his posting here.

Weizh posted a nice step-by-step tutorial on how to use Workbench to show differences between two databases. Find it here.

Both workflows get you what you want by using the Community Edition. For those of you who want everything on a silver plate there is the Standard Edition of course, which simplifies these tasks even more.

On a side note: To get more information about the printing topic Gerry joined us on our IRC #workbench channel on irc.freenode.net. Somebody from the team usually hangs out there from 10:00 am European time till 18:00 EST. Don’t be shy and join us there if you have a WB-related question. We might also be able to answer general MySQL questions but the better channel for that is #mysql. For more information see this page.

Printing in Workbench

A common misunderstanding seems to be that the only way to increase the available “paper space” for a diagram is by increasing the size of the paper. But there is another way to do that, which is in the Model -> Diagram Size dialog, where you can set the number of pages your diagram has, vertically and horizontally (perhaps it’s not very obvious to first time users, so we’ll be working on making it more easily accessible).

model menu

diagram size

Another recurring issue is that of printing in the community version. Direct printing is currently only available in Workbench SE, but that doesn’t mean you cannot print using the OSS version. You can still export the model to a variety of formats, like PDF, PNG and SVG. And once you export as an image you can print it with any tool that can handle such files, like Acrobat Reader or something similar.

Good MySQL Workbench Article

Please find a nice article by Djoni Darmawikarta here. He gives a nice step-by-step tutorial on how to use MySQL Workbench to create a simple model and then use the Forward Engineering functionality to create the actual database schema.

If you have written such a tutorial yourself or plan to write one, please drop us a email at workbench@mysql.com and we will be happy to feature you on our official Workbench Blog!

MySQL Workbench Community Edition: Guide to Schema Synchronization

As you can see in the MySQL Workbench Edition feature grid, Live Schema Synchronization is a Standard Edition feature only. But that does not mean that you cannot make use of the same functionality in the OSS Edition in an offline scenario – which is even more preferable in some cases.

  1. Create an SQL CREATE script from your model
    You might already have the SQL CREATE script if you started your model with an import of an existing schema. If you started designing your model from scratch inside Workbench, you are going to export an SQL CREATE script anyway – in order to create the initial schema on the database server.
  2. Update your Workbench model
    At this point your database is already running. But as we all know you always have to make changes to your first design. Do the necessary changes to the model.
  3. Export SQL ALTER script
    Normally you would have to write the ALTER TABLE statements by hand now, reflecting your changes you made to the model. But you can be smart and let Workbench do the hard work for you. Simply select File -> Export -> Forward Engineer SQL ALTER Script… from the main menu. This will prompt you to specify the SQL CREATE file the current model should be compared to.Select the SQL CREATE script from step 1. The tool will then generate the ALTER TABLE script for you and you can execute this script against your database to bring it up to date. You can do this using the MySQL Query Browser or the mysql client.Voila! Your model and database have now been synchronized!
  4. Export SQL CREATE script
    In order to be able to repeat step 3 you will need a fresh SQL CREATE script for your next synchronization process. Also do this from inside the Workbench by selecting the File -> Export -> Forward Engineer SQL CREATE Script…  from the main menu. If you should forget to do this step at some point you can always use MySQL Administrator or mysqldump to create a new SQL CREATE script with the backup/dump functionality to get a new SQL CREATE script of your live database.
  5. Repeat steps 3-4
    If you repeat these steps for all your changes you never have to write any SQL to modify your database structure. And in addition you get a nice documentation of all your changes.

Of course, with MySQL Workbench SE, all the above manual steps are automated.  See the demo here.