Problem installing mysql server on 18.04

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem installing mysql server on 18.04

Colin Law
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'

I have gone round a couple of times being ultra careful with the pwd entry to no avail.  The log below shows a server restart and login attempt and I can't see anything wrong.

Any suggestions gratefully received.

Colin

2018-07-01T11:05:15.498133Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-07-01T11:05:15.499689Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.22-0ubuntu18.04.1) starting as process 15056 ...
2018-07-01T11:05:15.504173Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-07-01T11:05:15.504224Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-01T11:05:15.504230Z 0 [Note] InnoDB: Uses event mutexes
2018-07-01T11:05:15.504235Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-07-01T11:05:15.504240Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-07-01T11:05:15.504246Z 0 [Note] InnoDB: Using Linux native AIO
2018-07-01T11:05:15.504491Z 0 [Note] InnoDB: Number of pools: 1
2018-07-01T11:05:15.504586Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-07-01T11:05:15.506091Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-07-01T11:05:15.514132Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-07-01T11:05:15.516438Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-07-01T11:05:15.528769Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-07-01T11:05:15.583667Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-07-01T11:05:15.583842Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-07-01T11:05:15.762178Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-07-01T11:05:15.763759Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-07-01T11:05:15.763783Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-07-01T11:05:15.764577Z 0 [Note] InnoDB: Waiting for purge to start
2018-07-01T11:05:15.814863Z 0 [Note] InnoDB: 5.7.22 started; log sequence number 2590746
2018-07-01T11:05:15.815213Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-07-01T11:05:15.815625Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-07-01T11:05:15.818372Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180701 12:05:15
2018-07-01T11:05:15.822701Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-07-01T11:05:15.822750Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2018-07-01T11:05:15.822771Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2018-07-01T11:05:15.822823Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2018-07-01T11:05:15.833548Z 0 [Note] Event Scheduler: Loaded 0 events
2018-07-01T11:05:15.833993Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.22-0ubuntu18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2018-07-01T11:05:26.799596Z 2 [Note] Access denied for user 'root'@'localhost'

--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem installing mysql server on 18.04

albert zhang
grep 'temporary password' /var/log/mysqld.log  or mysqld_safe --skip-grant-tables

2018-07-01 19:12 GMT+08:00 Colin Law <[hidden email]>:
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'

I have gone round a couple of times being ultra careful with the pwd entry to no avail.  The log below shows a server restart and login attempt and I can't see anything wrong.

Any suggestions gratefully received.

Colin

2018-07-01T11:05:15.498133Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-07-01T11:05:15.499689Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.22-0ubuntu18.04.1) starting as process 15056 ...
2018-07-01T11:05:15.504173Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-07-01T11:05:15.504224Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-01T11:05:15.504230Z 0 [Note] InnoDB: Uses event mutexes
2018-07-01T11:05:15.504235Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-07-01T11:05:15.504240Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-07-01T11:05:15.504246Z 0 [Note] InnoDB: Using Linux native AIO
2018-07-01T11:05:15.504491Z 0 [Note] InnoDB: Number of pools: 1
2018-07-01T11:05:15.504586Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-07-01T11:05:15.506091Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-07-01T11:05:15.514132Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-07-01T11:05:15.516438Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-07-01T11:05:15.528769Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-07-01T11:05:15.583667Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-07-01T11:05:15.583842Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-07-01T11:05:15.762178Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-07-01T11:05:15.763759Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-07-01T11:05:15.763783Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-07-01T11:05:15.764577Z 0 [Note] InnoDB: Waiting for purge to start
2018-07-01T11:05:15.814863Z 0 [Note] InnoDB: 5.7.22 started; log sequence number 2590746
2018-07-01T11:05:15.815213Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-07-01T11:05:15.815625Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-07-01T11:05:15.818372Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180701 12:05:15
2018-07-01T11:05:15.822701Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-07-01T11:05:15.822750Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2018-07-01T11:05:15.822771Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2018-07-01T11:05:15.822823Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2018-07-01T11:05:15.833548Z 0 [Note] Event Scheduler: Loaded 0 events
2018-07-01T11:05:15.833993Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.22-0ubuntu18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2018-07-01T11:05:26.799596Z 2 [Note] Access denied for user 'root'@'localhost'

--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users



--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem installing mysql server on 18.04

Tom H-4
In reply to this post by Colin Law
On Sun, Jul 1, 2018 at 12:14 PM Colin Law <[hidden email]> 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'
>
> I have gone round a couple of times being ultra careful with the pwd entry to no avail.  The log below shows a server restart and login attempt and I can't see anything wrong.
>
> 2018-07-01T11:05:15.822701Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

mysql_ssl_rsa_setup

--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem installing mysql server on 18.04

Colin Law
On 1 July 2018 at 15:13, Tom H <[hidden email]> wrote:
On Sun, Jul 1, 2018 at 12:14 PM Colin Law <[hidden email]> 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'
>
> I have gone round a couple of times being ultra careful with the pwd entry to no avail.  The log below shows a server restart and login attempt and I can't see anything wrong.
>
> 2018-07-01T11:05:15.822701Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

mysql_ssl_rsa_setup

I assumed that was only a warning, not something that would prevent connection.  Am I wrong?

Colin
 

--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users


--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem installing mysql server on 18.04

Colin Law
In reply to this post by albert zhang
On 1 July 2018 at 15:13, albert zhang <[hidden email]> wrote:
grep 'temporary password' /var/log/mysqld.log  or mysqld_safe --skip-grant-tables

I thought mysql_secure_installation [1] superseded that method.  There is no /var/log/mysqld.log.

sudo mysqld_safe --skip-grant-tables
2018-07-01T14:26:54.768012Z mysqld_safe Logging to syslog.
2018-07-01T14:26:54.773997Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2018-07-01T14:26:54.778846Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.


Colin




2018-07-01 19:12 GMT+08:00 Colin Law <[hidden email]>:
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'

I have gone round a couple of times being ultra careful with the pwd entry to no avail.  The log below shows a server restart and login attempt and I can't see anything wrong.

Any suggestions gratefully received.

Colin

2018-07-01T11:05:15.498133Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-07-01T11:05:15.499689Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.22-0ubuntu18.04.1) starting as process 15056 ...
2018-07-01T11:05:15.504173Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-07-01T11:05:15.504224Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-01T11:05:15.504230Z 0 [Note] InnoDB: Uses event mutexes
2018-07-01T11:05:15.504235Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-07-01T11:05:15.504240Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-07-01T11:05:15.504246Z 0 [Note] InnoDB: Using Linux native AIO
2018-07-01T11:05:15.504491Z 0 [Note] InnoDB: Number of pools: 1
2018-07-01T11:05:15.504586Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-07-01T11:05:15.506091Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-07-01T11:05:15.514132Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-07-01T11:05:15.516438Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-07-01T11:05:15.528769Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-07-01T11:05:15.583667Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-07-01T11:05:15.583842Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-07-01T11:05:15.762178Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-07-01T11:05:15.763759Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-07-01T11:05:15.763783Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-07-01T11:05:15.764577Z 0 [Note] InnoDB: Waiting for purge to start
2018-07-01T11:05:15.814863Z 0 [Note] InnoDB: 5.7.22 started; log sequence number 2590746
2018-07-01T11:05:15.815213Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-07-01T11:05:15.815625Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-07-01T11:05:15.818372Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180701 12:05:15
2018-07-01T11:05:15.822701Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-07-01T11:05:15.822750Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2018-07-01T11:05:15.822771Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2018-07-01T11:05:15.822823Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2018-07-01T11:05:15.833548Z 0 [Note] Event Scheduler: Loaded 0 events
2018-07-01T11:05:15.833993Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.22-0ubuntu18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2018-07-01T11:05:26.799596Z 2 [Note] Access denied for user 'root'@'localhost'

--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users



--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users



--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem installing mysql server on 18.04

Colin Law
In reply to this post by Colin Law
On 1 July 2018 at 12:12, Colin Law <[hidden email]> 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

 

I have gone round a couple of times being ultra careful with the pwd entry to no avail.  The log below shows a server restart and login attempt and I can't see anything wrong.

Any suggestions gratefully received.

Colin

2018-07-01T11:05:15.498133Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-07-01T11:05:15.499689Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.22-0ubuntu18.04.1) starting as process 15056 ...
2018-07-01T11:05:15.504173Z 0 [Note] InnoDB: PUNCH HOLE support available
2018-07-01T11:05:15.504224Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-01T11:05:15.504230Z 0 [Note] InnoDB: Uses event mutexes
2018-07-01T11:05:15.504235Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-07-01T11:05:15.504240Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-07-01T11:05:15.504246Z 0 [Note] InnoDB: Using Linux native AIO
2018-07-01T11:05:15.504491Z 0 [Note] InnoDB: Number of pools: 1
2018-07-01T11:05:15.504586Z 0 [Note] InnoDB: Using CPU crc32 instructions
2018-07-01T11:05:15.506091Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2018-07-01T11:05:15.514132Z 0 [Note] InnoDB: Completed initialization of buffer pool
2018-07-01T11:05:15.516438Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2018-07-01T11:05:15.528769Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2018-07-01T11:05:15.583667Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-07-01T11:05:15.583842Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2018-07-01T11:05:15.762178Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2018-07-01T11:05:15.763759Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2018-07-01T11:05:15.763783Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2018-07-01T11:05:15.764577Z 0 [Note] InnoDB: Waiting for purge to start
2018-07-01T11:05:15.814863Z 0 [Note] InnoDB: 5.7.22 started; log sequence number 2590746
2018-07-01T11:05:15.815213Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2018-07-01T11:05:15.815625Z 0 [Note] Plugin 'FEDERATED' is disabled.
2018-07-01T11:05:15.818372Z 0 [Note] InnoDB: Buffer pool(s) load completed at 180701 12:05:15
2018-07-01T11:05:15.822701Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-07-01T11:05:15.822750Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2018-07-01T11:05:15.822771Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2018-07-01T11:05:15.822823Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2018-07-01T11:05:15.833548Z 0 [Note] Event Scheduler: Loaded 0 events
2018-07-01T11:05:15.833993Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.22-0ubuntu18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2018-07-01T11:05:26.799596Z 2 [Note] Access denied for user 'root'@'localhost'


--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem installing mysql server on 18.04

Jim Byrnes-4
On 07/02/2018 03:02 PM, Colin Law wrote:

> On 1 July 2018 at 12:12, Colin Law <[hidden email]> 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
>

Have you tried any of the suggestions in this Stackoverflow thread?

https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost

Also this manual page talks about this problem:

https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html

A number years ago  I had and solved this problem, unfortunately I can't
remember what I did to fix it.  I must have googled or read something
that helped me fix it. Sorry I can't be more help.

regards,  Jim



--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem installing mysql server on 18.04

Nataraj-7
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 <[hidden email]> 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
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users
Reply | Threaded
Open this post in threaded view
|

Re: 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@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 <[hidden email]> 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 <[hidden email]> 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
> [hidden email]
> Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

--
ubuntu-users mailing list
[hidden email]
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users