MySQL Workbench — Complete Tutorial

A practical guide to MySQL Workbench: connecting to databases, writing queries, designing schemas, managing servers, and importing/exporting data. No fluff — just what you need to use it.

Table of Contents

• Introduction
• Create a Connection
• Workbench Interface
• SQL Editor
• Data Modeling
• Server Administration
• Export and Import
• Keyboard Shortcuts
• Preferences
• Tips and Tricks

Introduction

MySQL Workbench is a graphical tool for working with MySQL databases. It replaces the command-line with visual editors for queries, schema design, and server management.

You get three main work areas: SQL development (write and run queries), data modeling (design databases visually), and server administration (users, backups, configs).

Available on Windows, macOS, and Linux. Download it from dev.mysql.com/downloads/workbench.

Create a Connection

A connection stores the details needed to reach a MySQL server — host, port, user, password. You create one before doing anything else.

Steps

1. Open MySQL Workbench.
2. Click the + button next to "MySQL Connections" on the home screen.
3. Fill in:
   • Connection Name — anything you like (Local Dev, Production, etc.)
   • Hostname — 127.0.0.1 for local server, or a remote IP
   • Port — default is 3306
   • Username — typically root
4. Click Test Connection.
5. Enter the password when prompted.
6. Click OK to save.

Connection Parameters Table

Storing vs Prompting for Password

• Store in Keychain — convenient, no prompt every time. Use only on your personal machine.
• Ask for password — more secure, prompts each connection. Better on shared machines.

Once saved, the connection appears on the home screen as a tile. Double-click it to connect.

Troubleshooting: If "Test Connection" fails, check that the MySQL service is running (sudo systemctl status mysql on Linux, or check Services on Windows) and that the port isn't blocked by a firewall.

Workbench Interface

When you open a connection, the interface splits into several areas.

Navigator Panel (Left Sidebar)

• Management — server status, users, logs, export/import, options file
• Schemas — lists all databases on the server. Expand a database to see tables, views, stored procedures, and functions

Right-click any item in the Navigator for quick actions: SELECT Rows, Copy to Clipboard, Drop Table, Alter Table, etc.

Main Area (Center)

Where query tabs, model editors, and administration panels open. You can have multiple tabs open at once, like a browser.

Output Area (Bottom)

• Action Output — logs of recent operations (schema changes, imports, exports)
• History — previously run queries
• Notifications — server warnings and messages

Status Bar (Bottom-Right)

Shows connection details: which user you're logged in as, which schema is active, and the server version.

Toolbar (Top)

Common actions: open SQL file, save, execute query, commit/rollback, create new connection, open model.

SQL Editor

The SQL Editor is where you write and run queries. You get syntax highlighting, auto-complete, and query history.

Writing and Running Queries

Type your SQL in the editor tab. Some ways to execute:

Result Grid

Results appear in a grid below the editor. You can:

• Sort columns by clicking the header
• Edit cell values directly (click a value, type, press Tab — then click Apply to commit)
• Copy rows as tab-separated, JSON, or SQL insert statements
• Export results to CSV or JSON (right-click → Export Recordset)

Auto-Commit

By default, INSERT, UPDATE, and DELETE run in auto-commit mode — changes are permanent immediately. To group changes into a transaction:

SET AUTOCOMMIT = 0;
-- or click the "Auto-Commit" toggle button in the toolbar

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK to undo

Query History

The History tab in the output panel shows every query you've run in the session. Double-click one to re-run it. Use the search box to find past queries.

Saving Queries

Use Ctrl + S to save the current editor content as a .sql file. Open one later with Ctrl + Shift + O.

Data Modeling

Data modeling lets you design your database visually using Entity-Relationship (E-R) diagrams. You create tables, define columns, set relationships, and forward-engineer the design into actual SQL.

Creating a Model

From the home screen, click Create New EER Model. You get a tab with:

• Model Overview — lists all schemas in the model
• EER Diagram — the visual canvas
• Physical Schemas — table definitions

Adding Tables

1. In the EER Diagram tab, click the Table icon in the toolbar (or press T).
2. Click anywhere on the canvas. A new table appears.
3. Double-click the table to edit its properties:

Columns tab:

• PK = Primary Key
• NN = Not Null
• UQ = Unique
• AI = Auto Increment
• Default = Default value expression

Creating Relationships

Use the relationship toolbar or press:

Click the relationship tool, then click the parent table first, then the child table. A line appears between them. Double-click the line to edit foreign key options:

• Update/Delete rules: RESTRICT, CASCADE, SET NULL, NO ACTION
• CASCADE — if a parent row is deleted, child rows are deleted too
• RESTRICT — prevents deletion of a parent if child rows exist

Forward Engineering

Turns your model into real database tables:

1. Database → Forward Engineer (or Ctrl + G)
2. Select the target connection
3. Review the generated SQL script
4. Click Execute

The SQL is shown before execution — you can save it as a file instead of running it.

Reverse Engineering

Creates a model from an existing database:

1. Database → Reverse Engineer (Ctrl + L)
2. Select the connection and database
3. Choose which tables to import
4. The EER diagram is generated automatically

Synchronize Model

If you change the model after the database already exists, use Database → Synchronize Model to update the real database to match the model (or vice versa). It shows a diff and lets you pick what to apply.

Server Administration

These tools live under the Management section in the Navigator panel.

Server Status

Shows at a glance:

• Whether the server is running and how long (uptime)
• Connection count
• Traffic and query statistics
• Version and configuration file location

Startup / Shutdown

A button to start or stop the MySQL server. Handy on Windows where you'd otherwise use the Services panel.

Users and Privileges

Manage database users without writing GRANT statements.

1. Click Users and Privileges under Management
2. Add Account — give it a name and password
3. Under Administrative Roles — assign superuser, DBA, or limited roles
4. Under Schema Privileges — grant permissions per database:
   • ALL — full access to that schema
   • SELECT, INSERT, UPDATE, DELETE — read/write but no schema changes
   • SELECT ONLY — read-only

Status and System Variables

Shows all SHOW VARIABLES in a searchable table. Useful for checking:

• max_connections — how many simultaneous connections allowed
• innodb_buffer_pool_size — memory allocated to InnoDB
• wait_timeout — seconds before idle connections are dropped

Server Logs

View error log, general query log, and slow query log from the interface. For the slow query log, you can set a threshold (e.g., 2 seconds) to find poorly performing queries.

Options File

A text editor for my.cnf / my.ini with a visual form. Change settings like:

• Port number
• Character set (utf8mb4 recommended)
• SQL mode
• Binary logging

Changes take effect after a server restart.

Export and Import

Data Export

1. Management → Data Export
2. Select which schemas (databases) to export
3. Choose objects: tables, stored procedures, events, triggers
4. Options:
   • Export to Self-Contained File — single .sql file (portable, good for backups)
   • Export to Dump Project Folder — one file per table (good for large databases, easier to restore individual tables)
5. Advanced options:
   • Include Create Database — adds CREATE DATABASE IF NOT EXISTS at the top
   • Include Drop Table — adds DROP TABLE IF EXISTS before each CREATE TABLE
   • Include Inserts — includes the data as INSERT statements
   • Skip Table Data — export structure only (no data)
6. Click Start Export

Data Import / Restore

1. Management → Data Import / Restore
2. Select Import from Self-Contained File or Import from Project Folder
3. Choose the .sql file or the dump folder
4. Pick the target schema (or let it create one from the dump)
5. Click Start Import

Default Schema: If your dump file includes CREATE DATABASE, leave "Default Schema" blank. If not, select the target database first.

Exporting Query Results to CSV

1. Run a query in the SQL Editor
2. In the result grid, right-click → Export Recordset
3. Choose CSV format
4. Pick a save location

Keyboard Shortcuts

Query Execution

Editing

Navigation

Formatting

Find & Replace

Most shortcuts can be customized in Edit → Preferences → Keyboard Shortcuts.

Preferences

Open preferences via Edit → Preferences (Windows/Linux) or MySQL Workbench → Preferences (macOS).

General

• Default Schema — auto-select a database when connecting
• Path to mysql executable — for command-line integration
• SSH path — for SSH tunneling to remote servers

SQL Editor

• Tab size — number of spaces per tab (default 4)
• Line numbers — show/hide on the left gutter
• Syntax color theme — pick a highlighting scheme
• Query History Size — how many queries to remember (default 100)
• Safe Updates — when enabled, blocks UPDATE/DELETE without a WHERE clause that uses a key. Prevents accidental mass updates. Disable it if you truly intend to update all rows.
• DBMS connection timeout — seconds before a connection attempt is abandoned

Fonts & Colors

• Editor font and size
• Result grid font
• Color theme — light or dark. The dark theme is easier on the eyes for long sessions.

Shortcut Keys

Lists every shortcut in a searchable table. Click a row then press a new key combination to rebind it.

Modeling

• Default column datatype — what new columns default to (INT or VARCHAR(45))
• Auto-make columns with specific names into foreign keys — when naming a column table_id, Workbench can auto-detect it as a foreign key

Tips and Tricks

Safe Mode When Editing Tables

When you modify a table structure (add/remove columns, change types) in the table editor, Workbench generates an ALTER TABLE script. Review it before applying — the "Review SQL" dialog shows exactly what will run. If Workbench needs to drop and recreate the table (which can happen with some column type changes), you'll see a warning.

Using Explain for Slow Queries

Run a query, then click the Explain button (or press Ctrl + I). The execution plan shows how MySQL processes the query:

• ALL in the type column = full table scan (bad, needs an index)
• Using temporary in Extra = temp table created for sorting (consider optimizing)
• Using filesort = sorting without using an index

The WHERE Builder

In the SQL Editor, right-click → Build WHERE Clause gives you a visual filter builder. Pick columns, operators, and values — it generates the WHERE clause for you. Good for complex conditions.

Managing Multiple Connections

You can have multiple connections open in different tabs. Each tab is independent. Use the connection color (set when editing the connection) to visually distinguish production (red) from development (green) connections at a glance.

Snippets

Right-click in the editor → Snippets → Insert a template:

• SELECT — basic select
• INSERT — insert template
• CREATE TABLE — create table template
• User Snippets — you can define your own

The Table Inspector

Right-click a table in the Navigator → Table Inspector → Info tab gives you a dashboard:

• Row count (estimated and exact)
• Table size in MB
• Index sizes
• Engine type (InnoDB / MyISAM)
• Collation
• Created and updated timestamps

Drag and Drop Tables

Drag a table from the Navigator into the SQL Editor — it inserts the table name. Drag it while holding Shift — it inserts SELECT * FROM table. Drag it into an EER Diagram canvas — it reverse-engineers that table into the model.

Comparing Databases

Database → Synchronize Model can also compare two schemas:

1. Select source schema (the reference)
2. Select target schema (the one to update)
3. Workbench shows the differences and generates migration SQL

Useful for checking that development, staging, and production are in sync.

Automatic Backup Scheduling

Workbench doesn't have built-in scheduler, but you can:

1. Configure a Data Export with the settings you want
2. Go to Schedule → New Event (at the bottom of the Export tab)
3. Set time and frequency — this uses the OS task scheduler (Windows) or cron (macOS/Linux) behind the scenes

Favorites and Tagging

Right-click a connection → Add to Favorites. Favorites appear at the top of the connection list. You can also tag connections with custom colors for quick identification.

Resetting the Layout

If you drag panels around and lose something, go to View → Restore Default View. It resets all panels to their original positions without affecting your connections or saved files.