What Is MySQL Workbench and How to Use It: Create a Database and Tables

Sunucun 5 min read

Short answer: MySQL Workbench is Oracle’s official desktop tool for managing MySQL databases through a graphical interface. It brings writing and running SQL queries, creating databases and tables, designing schemas (data modelling), managing users and taking backups into one program. The Community Edition is free and runs on Windows, macOS and Linux.

Illustration: MySQL

What Is MySQL Workbench Used For?

According to the official MySQL documentation, Workbench’s functionality falls into five main areas:

AreaWhat it does
SQL developmentManages server connections; writes and runs SQL queries and shows results in an editable grid
Data modellingDesigns the database schema as a diagram; generates a database from the model (forward engineering) or a model from a database (reverse engineering)
Server administrationManages users and privileges, backs up and restores, monitors server health and performance
Data migrationMoves tables and data from Microsoft SQL Server, Access, PostgreSQL, SQLite and others to MySQL
Enterprise featuresMySQL Enterprise Backup, Firewall and Audit support in the paid Commercial Edition

If you’re new to MySQL and databases, start with our what is MySQL article.

How to Download and Install MySQL Workbench

  1. Download from the official page: choose your operating system at dev.mysql.com/downloads/workbench (Windows MSI, macOS, Debian/Ubuntu and generic Linux packages). If you’re asked to sign in with an Oracle account, use the “No thanks, just start my download” link to download without one.
  2. Windows prerequisites: the download page states that Windows needs Microsoft .NET Framework 4.5 and the Visual C++ Redistributable (Visual Studio 2019 or later); the installer fails if they’re missing.
  3. Install: on Windows run the MSI and choose “Complete”; on macOS drag the app from the .dmg into Applications; on Ubuntu install the downloaded .deb with sudo apt install ./package-name.deb.
  4. The server is separate: Workbench is only a client; you need a MySQL server to connect to. To install a server on your own computer, follow our how to install MySQL guide.

Connecting to a Server

When you open Workbench, click the + icon next to “MySQL Connections” on the home screen and enter the connection details:

FieldLocal MySQLRemote server (VDS/VPS)
Connection MethodStandard (TCP/IP)Standard TCP/IP over SSH (recommended)
SSH Hostname / Username—Server IP address:22 and your SSH user
MySQL Hostname127.0.0.1127.0.0.1 (through the SSH tunnel)
Port33063306
Usernameroot or your own userYour database user

Use “Test Connection” to check it, then save. Connecting over SSH gives you a secure connection without exposing port 3306 to the internet; leaving the MySQL port open to everyone invites brute-force attacks.

Creating a Database in MySQL Workbench

  1. Open the connection. In the Navigator panel on the left, switch to the Schemas tab (in MySQL, “schema” and “database” mean the same thing).
  2. Right-click an empty area and choose Create Schema…, or click the database icon on the toolbar.
  3. Enter the database name. For Charset/Collation choose utf8mb4, which fully supports all languages and emoji (the default collation in MySQL 8 is utf8mb4_0900_ai_ci).
  4. Click Apply. Workbench shows the SQL it will run; confirm with Apply again and Finish.

The command Workbench runs in the background looks like this, and you can type the same thing in the SQL editor yourself:

CREATE SCHEMA `shop` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Creating Tables and Running Queries

  1. Expand your database in the Navigator, right-click Tables and choose Create Table….
  2. Enter a table name and add columns. For the ID column, tick PK (primary key), NN (not null) and AI (auto increment).
  3. Click Apply to create it. Right-click the table and choose Select Rows to see the data in a grid and edit it directly.

You can do the same in the SQL editor. After writing the query, press Ctrl+Shift+Enter to run everything, or Ctrl+Enter to run only the statement under the cursor (Cmd instead of Ctrl on macOS):

USE shop;

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

INSERT INTO products (name, price) VALUES ('Keyboard', 750.00), ('Mouse', 320.50);

SELECT * FROM products WHERE price > 500;

Note: Workbench opens in Safe Updates mode by default and rejects UPDATE and DELETE queries whose WHERE clause doesn’t use a key column. This protects you from accidentally wiping a whole table; if you really need to, you can turn it off under Edit > Preferences > SQL Editor.

Creating a Database from the Command Line

On a server without a graphical interface, you do the same from the command line:

mysql -u root -p
CREATE DATABASE shop CHARACTER SET utf8mb4;
SHOW DATABASES;

Backing Up and Restoring

  • Backup: in Server > Data Export, select the database and use “Export to Self-Contained File” to save it as a single .sql file. Workbench uses mysqldump in the background for this.
  • Restore: in Server > Data Import, choose “Import from Self-Contained File”, select the .sql file and set the target database.

MySQL Workbench or phpMyAdmin?

MySQL WorkbenchphpMyAdmin
Where it runsDesktop application installed on your computerWeb interface running on the server (in the browser)
StrengthData modelling, advanced SQL editor, performance monitoringNo installation; comes ready in hosting panels (cPanel, Plesk)
Remote connectionDirectly through an SSH tunnelThrough the panel or the web
Best forDevelopers and database administratorsShared hosting users

Frequently Asked Questions

Is MySQL Workbench free?

Yes, the Community Edition is free. The Commercial Edition adds features for MySQL Enterprise products.

Does MySQL Workbench work with MariaDB?

Basic tasks (connecting, running queries, editing tables) usually work; but since Workbench is built for MySQL, you may see compatibility warnings on some administration screens.

Why do I get “Cannot connect to database server”?

The most common causes are: the MySQL service isn’t running, a wrong port or password, a firewall blocking 3306, or a user that may only connect from localhost. Connecting through an SSH tunnel to a remote server removes the last two problems.

Conclusion

MySQL Workbench is a free tool that brings MySQL work, from creating databases to schema design and backups, into one interface. If you want to run your application’s database on your own server, you can install MySQL on a VDS server and manage it securely with Workbench over an SSH tunnel.

Categories

Latest guides