How To Install MySQL server and MySQL Workbench on Ubuntu-Mate 18.04

Hallo

I ran both of these programs on my Ubuntu-Mate 16.04 computer. When I installed them on my fresh Ubuntu-Mate 18.04 system I could not connect the MySQL Workbench to the MySQL server.

I spent a lot of time tracking down this post which is the basis of the following set-up guide:

Following the procedure indicated in that post I ran these commands this morning and can confirm that they work.

To remove any previously installed MySQL server:

sudo apt-get remove --purge mysql-common* mysql-client* mysql-server* 

Install the MySQL server from the repositories.

sudo apt-get install mysql-server

Note that you will not be asked to enter a password for the root user of the MySQL server! And this is part of the failed “Workbench”-“Server” connection problem.

When installing the MySQL server on Ubuntu-Mate 16.04 you were asked to set the root-user password during the installation process, however, with Ubuntu-Mate 18.04 this is no longer the case. The way the MySQL server is set up at the moment, the root-user can log-in without entering a password.

Log-in to the MySQL server:

sudo mysql -u root -p

Enter your sudo password if required.

At the mysql-prompt (mysql>) type the following commands singly, one after another, pressing “return” after each one:

USE mysql;

UPDATE user SET plugin='mysql_native_password'

WHERE User='root';

FLUSH PRIVILEGES;

exit;

Now we need to set the root-user password. In the terminal enter the following:

sudo systemctl restart mysql.service

After a second or two of nothing seeming to be happening the terminal prompt reappears.

To begin the process of setting up a new root-user password and securing the MySQL server enter:

sudo mysql_secure_installation

The following question appears:

Would you like to setup VALIDATE PASSWORD plugin?

Reply yes by pressing “y”

The following text appears:

There are three levels of password validation policy:
LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 

Enter you choice. The following appears:
Please set the password for root here.
New password:

Enter your password for the MySQL server root-user. You will be asked to confirm it:

Re-enter new password: 
Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : 

Answer with “y” for yes.

During the following sequence of questions a short text often appears explaining why you are going to be asked the question.

These are the questions and the replies you should enter, unless of course you have a reason to choose different options:

Remove anonymous users? (Press y|Y for Yes, any other key for No) : 

Answer with “y” for yes.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 

Answer with “y” for yes.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 

Answer with “y” for yes.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 

Answer with “y” for yes.

You will be congratulated with the following message and logged-out of the MySQL server:

Success.
All done! 

As a final check to make sure all is well log-in to the MySQL server from the terminal:

sudo mysql -u root -p

Enter your MySQL root-user password when prompted. You can log-out of the MySQL sever by typing “quit” after the mysql-prompt.
Your MySQL server is now installed in a way that will allow MySQL Workbench to connect to it.

To install MySQL Workbench enter:

sudo apt-get install mysql-workbench

To connect MySQL Workbench to your MySQL server launch the MySQL Workbench via the applications menu and follow the standard procedure.

Explanation:
MySQL does not use passwords but rather a software package called “unix_socket_plugin” to authenticate users. This means that even if you set a password, it will be ignored. The steps above allowed us to re-enable password authentication. MySQL Workbench cannot connect to the MySQL server unless the root password has been set and enforced.

Many thanks to “!robot” for posting the solution. :slight_smile: :slight_smile:

1 Like

Here are the steps to install MySQL server and MySQL Workbench on Ubuntu-Mate 18.04:

Install MySQL Server:

Open the terminal and update the package list with the command: sudo apt update
Install MySQL server using the command: sudo apt install mysql-server
During the installation process, you will be prompted to set a password for the MySQL root user. This password will be required to log in to the MySQL server.

Configure MySQL Server:

Run the command: sudo mysql_secure_installation
Follow the prompts to configure MySQL server security. This includes setting a new password for the MySQL root user, removing anonymous users, disallowing root login remotely, and removing the test database.

Install MySQL Workbench:

Open the terminal and update the package list with the command: sudo apt update
Install MySQL Workbench using the command: sudo apt install mysql-workbench
Connect to MySQL Server with MySQL Workbench:

Open MySQL Workbench from the Applications menu.
Click on the "+" button in the "MySQL Connections" window to create a new connection.
Enter the connection details, including the host, username, and password. The host should be "localhost" if the MySQL server is installed on the same machine.
Click "Test Connection" to verify the connection.
Once the connection is verified, click "OK" to save the connection details.
You should now be able to use MySQL Workbench to manage your MySQL server.

1 Like

Welcome @adelenoble to the community!