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.