Written by 07:53 MySQL, Tools & technologies

How to Connect and Manage MariaDB via MySQL WorkBench

To use MySQL Workbench as a GUI for MariaDB?

If you’re filled with disbelief right now, you’re not alone. Though Michael “Monty” Widenius “fathered” both MySQL and MariaDB, they’re getting more differences in each iteration, which also affects MySQL Workbench as a GUI tool for MariaDB.

This article will tackle compatibility between the two so that you can decide if you’re all in for MySQL Workbench for MariaDB.

And if it’s not, what’s the best MySQL Workbench alternative for MariaDB?

Let’s begin.

download dbForge Studio for MySQL

What is MariaDB?

Is it even a question? I know you’re here to see if MySQL Workbench will work for MariaDB. But it starts with understanding what MariaDB is.

MariaDB is a fork of MySQL. They share similar code bases at some point, and that’s why it’s logical to ask if MySQL Workbench will work perfectly fine with MariaDB.

If you don’t know these facts about the two amazing databases, you might get into trouble. It happened to me when I installed MariaDB in my Linux box with MySQL. I downloaded it and installed it immediately. The result? It destroyed the MySQL databases I had, and I lost the working WordPress I used for creating articles. Good thing I had a backup of the Linux VM. But this doesn’t have to happen to you.

MariaDB became a thing because of acquisitions.

First, MySQL, the big sister, started under MySQL AB in 1994. Later, Sun Microsystems acquired it in 2008. Finally, Oracle acquired Sun Microsystems along with MySQL in 2010. The day Oracle announced the acquisition, Michael Widenius forked MySQL, and MariaDB was born.

MariaDB remains free and it will stay that way, while MySQL has a dual license under Oracle.

It maintains a high level of compatibility with MySQL. Most applications running in MySQL will work in MariaDB. But will MySQL Workbench work with MariaDB?

What is MySQL Workbench?

MySQL Workbench is a GUI tool for database design, development, and administration.

It provides graphical diagrams for data modeling and an SQL editor to code and run SQL scripts and queries. On the administration side, you can monitor server health and performance, and manage users and permissions. You can also do data migration to transfer data from other SQL databases to MySQL.

It works under all major operating systems. See the below screenshot of the MySQL Workbench Server Status of a MariaDB instance in Windows:

Don’t be fooled by the MySQL Server icon. Check the Version and Server Directories that show it’s MariaDB.

MySQL Workbench comes in 3 flavors:

  • Community Edition, which is free and open-source.
  • Standard Edition which extends the Community Edition. Added features are automated database documentation and schema/model validation.
  • Enterprise Edition extending the Standard Edition. Features added are the GUI for Enterprise backup, audit, and firewall.

There is a detailed feature comparison of these 3 editions if you need more information.

But what about MariaDB and MySQL Workbench – can we use the default MySQL GUI, or the Workbench is not compatible? The question is more complex than it seems.

Prerequisites to Using MariaDB with MySQL Workbench

Where MySQL runs, MariaDB runs too. That pretty much sums it up. If you are you looking for hardware requirements, a machine with pre-2000 capability will do. The database management systems run on Windows, Linux, Mac OS X, Solaris, and FreeBSD.

Here, the first requirement for using MySQL Workbench with MariaDB is to have MariaDB.

Meanwhile, MySQL Workbench works on Windows, Linux, and Mac OS X. You saw a screenshot of the Windows installation earlier.

Is MySQL Workbench Compatible with MariaDB?

Although MariaDB maintains a high level of compatibility with MySQL, there are differences. Thus, if you want to apply MySQL Workbench for MariaDB, issues are sure to occur.

If you migrate a MySQL database to MariaDB, some MySQL functions and server variables won’t work. It is the same for the MySQL Workbench MariaDB compatibility: it does not cover all you need as a GUI client for MariaDB due to the presence of incompatibility issues.

Moreover, these incompatibilities affect the MySQL Workbench work greatly.

The question is: What will work?

Database Connection

You can connect to MariaDB with MySQL Workbench appropriately. Further, we’ll illustrate the process.

Queries and Commands

Simple, day-to-day queries and commands will work. See a sample:

CREATE DATABASE testdb;

USE testdb;

CREATE TABLE testtable
(
 id int not null primary key,
 name varchar(20) not null,
 lastupdate timestamp not null
 );
 
INSERT INTO testtable
 (id, name, lastupdate)
 values (1,'Sample name','2022-09-22 18:53');
 
INSERT INTO testtable
 (id, name, lastupdate)
 values (2,'Sample name 2','2022-09-22 18:54');
 
UPDATE testtable
 set name = 'updated name'
 where id=1;

DELETE FROM testtable
 where id = 4;
 
SELECT * FROM testtable;

Try pasting the code above on a MariaDB server using MySQL Workbench. Then, run it. It will succeed.

Expect a simple CREATE DATABASE, CREATE TABLE, INSERT, UPDATE, DELETE, and SELECT to work in your favor. See the Query and Output window below and note that each statement in the Output window has green “success” check marks.

Now, try a stored procedure:

DELIMITER $$
CREATE PROCEDURE `insert_record`(IN vid int, IN vname varchar(20))
BEGIN
   insert into testtable
   (id, name, lastupdate)
   values (vid,vname, NOW());
END$$
DELIMITER ;

It will succeed too.

CALL `insert_record`(4,'test name');

As long as you don’t touch the incompatibilities of MariaDB with MySQL, you’re good with MySQL Workbench.

But here’s one issue:

CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;

MariaDB does not support CREATE TABLESPACE.

From the GUI Tool

Certain areas in the GUI will work fine like the Query window. But here are other things that won’t work.

From MySQL Workbench, click Clent Connections. Nothing will appear. But if you click Refresh, an error will occur:

As an alternative, you can issue this command in a Query window in MySQL Workbench:

SHOW FULL PROCESSLIST;

Another problem of the MySQL Workbench compatibility with MariaDB is referring to Users and Privileges.

In the Navigator, click Users and Privileges > Add Account. Enter the required information and click Apply.

The error appears: you can’t create a new user account.

You need to type the command into the Query window to create users.

Check the list of incompatibilities between MySQL 8 and MariaDB 10.8 to learn what more can make you struggle.

How to Connect to MariaDB Using MySQL Workbench

Connecting to MariaDB through MySQL Workbench is the same as for MySQL:

1. Run MySQL Workbench

If you’re using Windows 11, you can either look manually in the Start menu or use Search:

However, if you’re using Ubuntu 22.04, click Show Applications > MySQL Workbench:

2. Create a New Connection

To establish the MySQL Workbench MariaDB connection, open the client and click the + button:

3. Setup the New MariaDB Connection

The Setup New Connection window will appear. It’s the same as setting up a connection to MySQL.

We now need to establish the MySQL Workbench connection to MariaDB, so, do the following:

  • Enter the Connection Name.
  • Select the Connection Method. This depends on what method the MariaDB server accepts. Standard (TCP/IP) is common, and we choose that.
  • Enter a Hostname and Port. If you’re connecting to the same machine, the default 127.0.0.1 should suffice, and the default port is the same as MySQL (3306).
  • Finally, enter the credentials like the Username, and click Store in Vault for the Password.

Click Test Connection. A warning will appear because it is not a MySQL server:

You can connect to MariaDB, so click Continue Anyway. It will bring a successful connection message. Click OK. You have connected to MariaDB using MySQL Workbench.

Advantages of Using MySQL Workbench for MariaDB

MySQL Workbench is free to use, and it can level up the productivity of database architects, developers, and administrators. Besides, it is 100% working for MySQL.

What can Workbench do on MariaDB?

Design Databases Visually

Gone are the ER models on paper. You can create ER models and physical databases and change them both simultaneously.

Do you have an existing database without a model? Do you want to get its tables, columns, indexes, and relationships in a visual format? No problem. Reverse engineer the database and create a model.

And yes, visual diagramming works for MariaDB in MySQL Workbench.

Modern SQL Editor

You can create and edit queries with colored syntax highlighting and use the code auto-completion feature. You noticed this option in the Query Window screenshot earlier. You can also display results in tabs with data you can export to CSV and other formats.

But if you want to examine the query execution plan, only the tabular EXPLAIN works. Visual EXPLAIN doesn’t work with MariaDB. Given that limitation, the SQL editor is good enough for day-to-day query tasks.

Graphical Server Administration

Earlier you saw the Server Status for MariaDB. There are more GUI tools for MariaDB administration.

Note: MySQL Workbench users can use the Administration Dashboard for MariaDB. The MariaDB connection is boxed so you won’t mix it with MySQL. But all other captions show MySQL which makes it out of place.

Data Migration and Reverse Engineer also work. As you can see in the above image, tables from the MySQL Sakila sample database are migrated to MariaDB. Below is the EER Diagram result of the Sakila database reverse engineering.

However, Data Export and Data Import are problematic.

Alternative GUI Tool for MariaDB

The good news is, if you are looking for the MariaDB MySQL Workbench alternative, you can have it right now. Meet dbForge Studio for MySQL and MariaDB from the popular dbForge Studio product line. These solutions are winning the Database Trends and Applications (DBTA) Readers Choice Award for the fourth year in a row.

Advantages of Using dbForge Studio for MySQL

What will you get with the Studio as a convenient alternative to MySQL Workbench to do the database tasks? A lot of things!

  • Increase coding productivity by more than 50%
  • Migrate data 16x faster
  • Generate test data and scripts faster by more than 200%
  • Develop databases 2x faster
  • Reduce time spent in routine database work by 50%
  • Speed up deployment by 200%
  • Reduce the learning curve by 50%
  • Reduce errors by 99%

Bold claims? I can show you more things you can do with this MariaDB MySQL Workbench alternative during your daily routines:

Query Builder

Aside from typing SQL code, you can visually create your queries using the Query Builder.

Copy Database

Using the Copy Database option in dbForge Studio makes the task like copying a file to another location.

Data Compare

During data migration, we always check if the target has the exact copy of the data from the source. Data Compare makes it easy.

Schema Compare

The tool named Schema Compare allows you to identify all differences between 2 different MariaDB or MySQL instances, in tables, columns, views, and more.

Generating Sample Data

It’s a breeze to generate sample data with the Data Generator of dbForge Studio for MySQL.

Database Documenter

Documenting a database sometimes feels like a chore. But it doesn’t have to be if you are using Documenter that does it all automatically:

The above image illustrates generating the PDF database documentation. For more details, visit the product page of dbForge Studio for MySQL.

It was always a popular MySQL Workbench alternative for Windows MariaDB instances. Now, it provides Linux support too. So, if you are a devotee of this great OS, dbForge Studio for MySQL and MariaDB will accompany you in that environment.

MySQL Workbench vs dbForge Studio for MySQL: Feature Comparison Table

FeatureMySQL WorkbenchdbForge Studio for MySQL
Database Connectivity using SSH/SSLYesYes
Support for HTTPNoYes
Support for Embedded DBNoYes
Rich SQL Code EditingLimitedYes
Code CompletionLimitedYes
Visual Query BuilderNoYes
Visual Database DesignerYesYes
Table DesignerYesYes
Copy ColumnYesNo
MySQL DebuggerNoYes
Database ExplorerYesYes
Data EditorLimitedYes
Schema/Data Compare and SyncNoYes
Data Import and ExportLimited formatsYes
Database ProjectNoYes
Data Report DesignerNoYes
Visual EXPLAINYesNo
Session StatsYesYes
Query profiling history and comparisonNoYes
Performance DashboardYesNo
Top Performance ReportsYesNo
Sample Data GeneratorNoYes
Database DocumenterLimitedYes
Database Service ControlYesYes
Server Status and HealthYesNo
Backup and RestoreYesYes
Replication ConfigurationYesNo
Security ManagerYesYes
UI Skins and CustomizationNoYes
LicenseFree and CommercialCommercial with Free Trial and Free Express Edition

This table names the most fundamental differences, but there are more of them. So, get familiar with all differences between MySQL Workbench and dbForge Studio for MySQL to know for sure what you can expect from each of them.

Takeaways

MySQL Workbench is a great GUI tool for MySQL that is favored by experts. But when dealing with MariaDB, it might not be as great as one would expect. MariaDB, despite being similar to MySQL, requires specific treatment in some aspects.

MariaDB does not have its own, “native” default GUI client, but there are functional third-party solutions available on the market that allow you to do all you need on MariaDB.

Though we are proud of dbForge Studio for MySQL, don’t take our word for granted – try it free of charge and see it in action yourself.

download dbForge Studio for MySQL
Tags: Last modified: November 07, 2022
Close