[solved] Unable to login to mysql

Lockheed · 2014-08-05 06:07:14

I discovered the problem when I tried adding a new database with adminer. It would not let me log in with root user.

So I logged in through ssh to the server, stopped mysqld service, and run

mysqld_safe –skip-grant-table

then, I run

# mysql -u root

which in fact could just as well be a non-existent user

# mysql -u pinkelephant

because it logs me in just as well...

Anyway, I try to set the password for root:

MariaDB [(none)]> SET PASSWORD FOR root@'localhost' = PASSWORD('newpassword');
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

I also tried doing something else first, but it failed, too:

MariaDB [(none)]> SET PASSWORD FOR root@'localhost' = PASSWORD('newpassword');
ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to change passwords

and this is where I no longer know what else to try.

Last edited by Lockheed (2014-08-27 15:32:33)

ewaller · 2014-08-05 15:45:01

Are you logged in as root? What if you do all of this as a regular user?

Lockheed · 2014-08-06 17:31:59

I can't run mysqld as regular user because

$ mysqld_safe –skip-grant-table
140806 19:29:40 mysqld_safe Logging to '/var/lib/mysql/malina.err'.
touch: cannot touch '/var/lib/mysql/malina.err': Permission denied
chmod: cannot access '/var/lib/mysql/malina.err': Permission denied
140806 19:29:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/bin/mysqld_safe: line 138: /var/lib/mysql/malina.err: Permission denied
rm: cannot remove '/var/lib/mysql/malina.pid': Permission denied
/usr/bin/mysqld_safe: line 182: /var/lib/mysql/malina.err: Permission denied
touch: cannot touch '/var/lib/mysql/malina.err': Permission denied
chown: cannot access '/var/lib/mysql/malina.err': Permission denied
chmod: cannot access '/var/lib/mysql/malina.err': Permission denied
140806 19:29:41 mysqld_safe mysqld from pid file /var/lib/mysql/malina.pid ended
/usr/bin/mysqld_safe: line 138: /var/lib/mysql/malina.err: Permission denied

and if I change ownership to the user, it quits after a short while:

$ mysqld_safe –skip-grant-tablel/
140806 19:30:38 mysqld_safe Logging to '/var/lib/mysql/malina.err'.
140806 19:30:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140806 19:30:58 mysqld_safe mysqld from pid file /var/lib/mysql/malina.pid ended

Which now also happens for root user.

Lockheed · 2014-08-26 23:34:32

Well, I am still stuck with this problem. The server works, I just can't login to it to manage databases.

nobicycle · 2014-08-27 15:29:27

/usr/bin/mysql_secure_installation

Login as root into MYSQL:
then SET PASSWORD FOR user PASSWORD=('pwd')

Best wishes

Lockheed · 2014-08-27 15:32:17

I solved it by editing the mysqld.service file to add the "no privileges" option. I was unable to run it otherwise, because it must be run as mysql user.

I was then able to change root password and flush privileges in the regular way.

nitai · 2016-08-25 09:54:40

I have been trying to solve the same issue without success.

Would you mind detailing the "no privileges" option that you used to solve your issue? I have been unable to find references to it elsewhere.

ewaller · 2016-08-25 14:11:20

nitai,

Welcome to the Arch Linux Forums. As this thread is two years old and had been marked [solved], I am going to ask you to go ahead and ask you to start a new thread that you will own. If you think this thread is relevant, go ahead and link back to it using BBCode url tags. Thanks.

I am going to go ahead and close this old thread.

Arch Linux

#1 2014-08-05 06:07:14

[solved] Unable to login to mysql

#2 2014-08-05 15:45:01

Re: [solved] Unable to login to mysql

#3 2014-08-06 17:31:59

Re: [solved] Unable to login to mysql

#4 2014-08-26 23:34:32

Re: [solved] Unable to login to mysql

#5 2014-08-27 15:29:27

Re: [solved] Unable to login to mysql

#6 2014-08-27 15:32:17

Re: [solved] Unable to login to mysql

#7 2016-08-25 09:54:40

Re: [solved] Unable to login to mysql

#8 2016-08-25 14:11:20

Re: [solved] Unable to login to mysql

Board footer