Mysql Root User Problem

English Install/Boot/Login
#1

Hi,

just installed OpenSuse 13.1 and I have a problem in MySQL

I can not login using “mysql -u root”:

ERROR 1045 (28000): Access denied for user ‘root’ @ ‘localhost’ (using password: NO)

When I start the server using “mysqld_safe - skip-grant-tables &” to reset the root password, I can not, because there is no user in user;

MariaDB [mysql]> select * from user;
Empty set (0.00 sec)

Also I can not add a user, since I started the server with - skip-grant-tables;

MariaDB [mysql]> CREATE USER ‘root’@‘localhost’ IDENTIFIED BY ‘root’;
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

Anyone have any suggestions?

Thanks!

#2

I tried using this procedure:

MySQL :: MySQL 5.1 Reference Manual :: C.5.4.1 How to Reset the Root Password

Using the following init-file:

CREATE USER ‘root’ @ ‘localhost’ IDENTIFIED BY ‘root’;
GRANT ALL PRIVILEGES ON *. * TO ‘root’ @ ‘localhost’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

did not work!

#3

Try inserting the row directly. Going from an old memory, maybe something
like this:

Code:

INSERT INTO user (Host, User, Password … ) VALUES (‘127.0.0.1’, ‘root’,
PASSWORD(‘newpassword’), ‘Y’, ‘Y’, …);

Be sure to include all of the columns in there and be sure to give the
right access for all of them ‘Y’ I think, or something similar to indicate
the right is granted). This should insert nicely and then restarting
MySQL/MariaDB should magically work if you login from 127.0.0.1 as ‘root’
with the specified password.

Good luck.

#4

On Mon, 07 Oct 2013 13:16:02 +0000, fuentesbr wrote:

> did not work!

What output did you get?

Jim

–
Jim Henderson
openSUSE Forums Administrator
Forum Use Terms & Conditions at http://tinyurl.com/openSUSE-T-C

#5

Both CREATE USER and INSERT INTO user require GLOBAL privileges. Normally there are four users in mysql.user; logically if mysql.user is empty, there can be no user with GLOBAL privileges able to create a new user.

Reinstall or install MariaDB instead - assuming it is still 5.5.n.

#6

Normally, yes, but not if (as the OP stated) the privileges were skipped
(skip-grant-tables) when loading mysqld for this very purpose.

Good luck.

#7

Shouldn’t


CREATE USER 'root' @ 'localhost' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *. * TO 'root' @ 'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

be


CREATE USER 'root' @ 'localhost' IDENTIFIED BY **PASSWORD** 'root';
GRANT ALL PRIVILEGES ON *. * TO 'root' @ 'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

though I doubt that you want a user root with password root. IIRC I made this mistake some years ago, but not sure. In my mysql history the PASSWORD is in the statement

#8

The ‘PASSWORD’ keyword is optional. See MySQL docs for details.

Good luck.

#9

According to the docs:

This gives all users FULL ACCESS to all tables.
but mysql.user is empty; there isn’t even an anonymous user, as there normally is at install. So, logically, there isn’t a user to receive FULL ACCESS.

Moreover ERROR 1045 refers to access to a database (rather than a table) which suggests that there has to be a existing user and ERROR 1290 is ‘OPTION_PREVENTS_STATEMENT’ which suggests that FULL ACCESS is less than GLOBAL privileges, since it is saying that the ‘CREATE USER’ (and by implication the INSERT INTO …) statements are not permissible under --skip-grant-tables.

You may be able to UPDATE a user’s details but you cannot CREATE a new one.

#10

Hi,

MySQL problem on fresh 13.1 RC2 from CLI

as su:

mail:/home/info # mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)
mail:/home/info #

Came from 12.3 webserver config without any problem, this I don’ t understand…

there is no mysql folder in /var/run/…

system config to use LDAP, Apache2, Postfix MTA, FTP and SAMBA server

Any suggestions much appriciated…

Greets, Fred

#11

Is mysql actually running?

systemctl status mysql.service
#12

On 10/31/2013 06:26 PM, fred dresken wrote:
>
> Hi,
>
> MySQL problem on fresh 13.1 RC2 from CLI
>
> as su:
>
> Code:
> --------------------
> mail:/home/info # mysql
> ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysql/mysql.sock’ (2)
> mail:/home/info #
> --------------------
>
>
> Came from 12.3 webserver config without any problem, this I don’ t
> understand…
>
> there is no mysql folder in /var/run/…
>
> system config to use LDAP, Apache2, Postfix MTA, FTP and SAMBA server
>
> Any suggestions much appriciated…

Have you tried “mkdir /var/run/mysql” as root?