MySQL is an open-source SQL database management system developed and distributed by Oracle Corporation. MySql is fast, reliable, scalable, and easy to use. MySQL is a relational database meaning data is stored in a structured format in tables linked together using relationships. It uses the Structured Query Language (SQL) to create databases and users while also storing, retrieving, and modifying the database. It is used alongside other tools, Apache Webserver and PHP to create a powerful package.

It is the most popular open-source database in the world as it offers multiple user access to query databases. Companies like Fujitsu, Adobe, Polystar, Paypal, Flash Networks, Intel, Verizon, etc use MySQL in their operations.

MySQL can be accessed on several modes.

  • Command Line Interface (CLI) – terminal-based programs on the Operating system to execute MySQL commands.
  • Desktop tools – Graphical tools that can be used to access MySQL and create databases.

There are several desktop applications in place to access MySQL including MySQL Workbench, PhpMyAdmin, TurboDBAdmin, XAMPP, PopSQL, and DBeaver.

MySQL Workbench

MySQL Workbench is a visual tool used by Database Administrators and developers for working with MySQL databases and servers. It covers several topics including Server administration, Data Modelling, Data Migration, Enterprise Support, and SQL Development which involves creating databases and users. MySQL is available in two editions, Community edition which is free, and Commercial Edition which is paid and provides additional enterprise features like MySQL Firewall and MySQL Audit. The commercial edition is further subdivided into Standard Edition and Enterprise Edition.

Features of MySQL Workbench

  • SQL Editor that is used to write, run and debug SQL statements
  • SQL Code Completion – Comprehensive list of SQL Keywords and codes for more efficient coding.
  • Color Syntax Highlighting and SQL code generation.
  • Reuse old SQL snippets and run SQL history.
  • Visual Database Administration enables you to start and stop the server locally and remotely.
  • Performance dashboard where you can view key performance indicators.
  • Data management with regards to importing and exporting MySQL dump files.
  • Visual Data Modelling by use of Entity-Relationship (ER) diagram.
  • Reverse engineering where you create a model from SQL text scripts.
  • Supports Python Plugins.
  • Schema Synchronization.
  • Database Migration solution.

This guide will show you how to install and use MySQL Workbench on KDE Neon|Kubuntu.

Install MySQL Workbench on KDE Neon / Kubuntu

Update your system packages.

### Kubuntu ###
sudo apt update && sudo apt upgrade -y

### KDE Neon ###
sudo apt update && sudo pkcon update -y

Step 1. Install MySQL on Kubuntu|KDE Neon

Use the following command to install MySQL

sudo apt install mysql-server

Start and enable MySQL on boot time.

sudo systemctl start mysql
sudo systemctl enable mysql

Edit the root user password to allow access to the database.

$ sudo mysql

Alter the password using the following commands.

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'StrongPassword';
mysql > FLUSH PRIVILEGES;
mysql > exit;

Step 2. Install MySQL-Workbench on KDE Neon|Kubuntu

First, download and install the latest MySQL APT repository from the Official page to add to the sources list..

wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.22-1_all.deb

For KDE Neon, The neon focal system is not supported by MySQL. It is compatible with Ubuntu, so you can choose one of the supported systems from the list shown.

Then select the version of MySQL APT Repo. Once satisfied, select Ok to continue the installation.

The installation is successful. Update the repository and install MySQL Workbench using the following commands.

sudo apt update
sudo apt install mysql-workbench-community

With both systems, install the gnome-keyring.

sudo apt install gnome-keyring

Edit the remote configuration file to include the mysqld section.

sudo nano /etc/mysql/my.cnf

Edit the file to appear as below.

!includedir /etc/mysql/conf.d/
[mysqld]
!includedir /etc/mysql/mysql.conf.d/

Save and exit the file.

Launch MySQL Workbench using the following command

$ /usr/bin/mysql-workbench

You can also launch it from the application Launcher

Using MySQL Workbench on Kubuntu / KDE Neon

The application opens as shown below.

Setup a New Connection

To set up a new connection, click on the ‘+’ sign which will open the following window. Enter the name of the connection.

You can click on click on Test Connection to avoid going through the configuration settings. But if you wish to review the settings, click on Configure Server Management to open the Configure Local Management Wizard.

Click on Next to define the connection parameters. You will be prompted for the password of the user to connect. Enter the password you altered for the root user above.

You should see that the connection was successful. Click Next.

The next section is to specify the installation type for your Operating System.

Click on Next. The wizard will now check its ability to access the start and stop commands and the MySQL configuration file. Click on Next.

Review Configurations settings, You may choose to review and change the parameters or click on Continue to close the Configuration Management wizard.

After the Configuration wizard closes, click on Test Connection and then click OK to create the new MySQL connection.

The connection will now appear on the screen

Clicking on it opens the SQL editor as shown below.

Creating Database

To create a database, click on the Fourth icon that shows create the schema. Enter the name of the Schema and click Apply.

Another window opens that shows the SQL script for creating the schema. Click on Apply to execute the SQL.

The new schema will show up on the list under Schemas.

Create Tables

To create tables, right-click on the newly created Schema and select the option to ‘set Schema as Default’. Then click on the Fifth icon to create a table. Enter the name and add the columns to the table. I have 4 columns with column ‘id’ being my Primary Key. Click on Apply.

The create table SQL script will show up. Click on Apply to execute the script.

And that is how you create a table. follow the same procedure to create more tables.

Create Users and Assign Privileges

To create a user, on the administration Tab, click on Users and Privileges. to open a new tab as shown below. Click on Add Account to add a new user.

A new section opens allowing you to add the user’s name and password.

You can add a role to the user. select from the ones available.

When you click on Schema Privileges, It shows the following section.

Click on Add Entry to define the schema for which the user will have privileges. Click OK.

Then select the user rights to the schema selected. Once down, click on Apply. The new user will show under the User accounts tab.

Data input and Query

Let us add some users to our previously created table. Use the following codes to add users to the table.

INSERT INTO ITDept
  ( id, FirstName, LastName, Role )
VALUES
  (123, 'John', 'McGavin', 'Database Admin'), 
  (456, 'Jane', 'Kamau', 'System Admin'), 
  (789, 'Billy', 'Walter', 'Network Admin'),
  (101, 'Miranda', 'Spice', 'Project Manager');

To execute the query, click on the ‘lightning‘ icon as shown below.

To view the newly added users, use the following SELECT command.

SELECT * FROM ITDept

It will sow the list of users added as shown below.

There are different options to query a database and this is just a general method.

Conclusion

From this guide, we have gone through the basics of MySQL database and MySQL Workbench and its features. We have also gone through installing MySQL Workbench on KDE Neon|Kubuntu systems and how to launch the application from the terminal and application launcher. We have also seen how to set up a new connection and configure its settings. We have also seen how to create a database and users and grant permission on the MySQL Workbench application.

Click to see more guides:

RELATED ARTICLESMORE FROM AUTHOR

LEAVE A REPLY

Please enter your comment!
Please enter your name here