Problem installing mysql server on 18.04

[Colin Law]

Thanks to everyone who posted suggestions and links, I have now
managed to make progress.  In order to make sure that it was not
messed up because of something I did I removed mysql and re-installed.

sudo apt-get remove --purge mysql-\*
sudo apt autoremove
sudo apt install mysql-server mysql-client

According to the documentation I found it should then have been
possible to connect using the anonymous user just by running mysql,
but this gave access denied for me at localhost.  I don't think the
default install has anonymous users any more

However it was possible to connect using
sudo mysql
which connects as the root user without entering a password.

I then ran
sudo mysql_secure_installation
which says it allows me to set a root password, and said it was
successful.  However I was still unable to connect as root specifying
the password, but was still able to connect just using sudo mysql,
which means that it had not set the root password.

I connected using sudo mysql and then did

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

But still I was not able connect as root using that password.
After much googling I eventually discovered that I had to use

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY
'mypassword';

I guess this is why mysql_secure_installation does not work, and why
most of the methods around on the internet also do not work an more.
I found an old bug for mysql_secure_installation [1].  It is most
unfortunate that it does not work when there is so much documentation
about on the web that says this is the way to do it and very little
describing the workaround.

Colin

[1] https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1610574

On 2 July 2018 at 23:00, Nataraj <incoming-ubuntu at rjl.com> wrote:
> On 07/02/2018 02:09 PM, Jim wrote:
>> On 07/02/2018 03:02 PM, Colin Law wrote:
>>> On 1 July 2018 at 12:12, Colin Law <clanlaw at gmail.com> wrote:
>>>
>>>> I have installed mysql-server and then run
>>>> sudo mysql_secure_installation
>>>> which prompts me to enter a root password.
>>>>
>>>> I then run
>>>> mysql -u root -p
>>>> and get prompted for the pasword but then I get
>>>> Access denied for user 'root'@'localhost'
>>>>
>>>
>>> Has anyone any suggestions on this or how to debug it?
>>> mysql_secure_connection says it is setting the root password but then I
>>> cannot connect.
>>>
>>> Colin
>>>
>
> Can you still connect with no password?  If so, connect and then issue a
> "FLUSH PRIVILEGES" command.  If the right configuration commands were
> given, this will cause them to take effect.  I ran into this problem
> several months ago, and if I remember correctly, that was what solved it
> for me.
>
> Nataraj
>
>
>
>
> --
> ubuntu-users mailing list
> ubuntu-users at lists.ubuntu.com
> Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users