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


#1

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:


LibreOffice Base and MySQL-server connection problem solved for Ubuntu-Mate 18.04