Yolinux.com Tutorial

MySQL Workbench:

Oracle MySQL Workbench installation and use: MySQL Workbench is a SQL database GUI interface tool specifically for the MySQL databases. This tutorial will cover the use of MySQL Workbench with a MySQL database.

Note that this is not the SQL Workbench/J Java GUI generic SQL database tool.
For more on this tool see our SQL Workbench/J tutorial.

Tutorial Table of Contents:

Related YoLinux Tutorials:

°Linux and MySQL

°MySQL InnoDB, Transactions & Foreign Keys

°List of Linux Databases

°PostgreSQL

°MySQL C API

°SQLite

°Apache, Tomcat, MySQL and Java

°YoLinux Tutorials Index




Free Information Technology Magazines and Document Downloads
TradePub link image


Description:

The MySQL Workbench "community edition" is an open source GUI data modeling, schema design, SQL development and database admin client for the MySQL database. It allows easy point and click discovery and investigation of any MySQL database.


MySQL Workbench Installation:

Oracle MySQL Workbench installation on Linux and Microsoft Windows platforms.

  1. Download MySQL Workbench: downloads


Linux:

RHEL 6:
  1. Dependencies/Prerequisites:
    • Pexpect: from RHEL6 Server DVD (not on the RHEL6 Workstation DVD)
      /media/RHEL_6.0 x86_64 Disc 1/Packages/pexpect-2.3-6.el6.noarch.rpm
    • LibZip: libzip-0.9-3.1.el6.x86_64.rpm
      rpm -ivh libzip-0.9-3.1.el6.x86_64.rpm
  2. Download MySQL Workbench: mysql-workbench-gpl-5.2.33b-1el6.x86_64.rpm
  3. Install: rpm -ivh mysql-workbench-gpl-5.2.33b-1el6.x86_64.rpm
  4. Start: ./sqlworkbench.sh


Microsoft Windows:

  1. Unzip each download.

  2. Start MySQL Workbench:

MySQL Workbench main screen:


MySQL Workbench Configuration:

One must configure MySQL Workbench to attach to the MySQL database.

  1. Configure a "New Connection":

    Select "File" + "New Connection":
    • Hostname: Be sure to assign a name to your configuration where the "hostname" is the network node name, its' IP address or "localhost" if the database is running locally.
    • The port is probably the MySQL default "3306".
    • Specify the login and password
    • Specify the database name ("Default Schema").

    Select "Store in Vault..." to enter password

  2. Test Connection (optional: select this button to test)
  3. Storing passwords (optional):

    Hit OK and then "Store password in vault"

The MySQL database can be configured for remote client access by both granting remote access to the database user (all hosts "%") explicitly and the MySQL "bind-address" configuration in /etc/my.cnf.
For more see the YoLinux.com Linux and MySQL Tutorial (check out the security section to configure for remote access)


Using MySQL Workbench:

Reverse Engineer an existing Database:

Generate a Database Schema Diagram from an existing MySQL database:

In this example we are looking at the Nagios MySQL database schema.

Create and store a MySQL connection configuration as shown above.

  1. From the toolbar select "Database" + "Reverse Engineer ..."
    Choose your "Stored Connection"

    Select "Next"

  2. A new dialog box will appear:
    Select "Next"

  3. On this dialog box select the database schema to be processed: "nagios"
    (All databases which have been generated within your MySQL instance will be displayed)

    Select "Next"

  4. The following panel appears (default options shown):

    Select "Next"

  5. The following dialog panel allows you to filter the database schema by table. The default is to select all:

    If you choose "Show Filter" this will allow you to choose which tables are displayed in the diagram.
    Select "Execute"

  6. A new dialog box will appear:
    Select "Next"

  7. A Results summary panel will display:

    Select "Close"

The schema model for the database will display. Drag and arrange as you wish.

You can select from the toolbar "Arrange" + "Autolayout"


Generate a Database Schema Diagram from a SQL file:

  • Generate a Workbench model:
    1. From the toolbar:
      • File + New Model
      • File + Import + Reverse Engineer MySQL. Create script...
      • This opens dialog box. "Browse" or enter the SQL file name. Select "Execute" button
      OR
    2. Select "Create EER Model from SQL Script" under the heading "Data Modeling"

    Reverse Engineer SQL

  • This action launches a new dialog box.
    Reverse Engineer SQL
    Select "Next". In the next dialog box, select "Close"

  • Select "Model" + "Create Diagram from Catalog Objects"
    (This will generate a full data model diagram. You can now rearrange the layout for the most appropriate presentation.)

  • Generate image file: File + Export + Export as PNG...

MySQL Workbench Tips:

Specify layout size: (required for large schema models with many tables requireing allot of space for the diagram.)
  • From the toolbar select "Model"
  • Select "Diagram Properties and Size"
  • Set the number of pages for the layout heigt and width.

Links:


Books:

book cover image "MySQL, Second edition"
by Paul DuBois
ISBN # 0735712123, New Riders Publishing

Amazon.com
book cover image "Managing and Using MySQL"
by George Reese, Randy Jay Yarger, Tim King
ISBN # 0596002114, O'Reilly

Amazon.com
book cover image "MySQL Cookbook"
by Paul DuBois
ISBN # 0596001452, O'Reilly

Amazon.com
book cover image "High Performance MySQL"
by Jeremy D. Zawodny, Derek J. Balling
ISBN # 0596003064, O'Reilly

Amazon.com
book cover image "The Definitive Guide to MySQL, Second Edition"
by Michael Kofler
ISBN # 1590591445, APress

Amazon.com
book cover image "MySQL Certification Study Guide"
by Paul Dubois, Stefan Hinz, Carsten Pedersen
ISBN # 0672326329, Sams

Amazon.com

   

    Bookmark and Share


Advertisements




Copyright © 2011 by Greg Ippolito