Mysql Root User Problem

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!

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!

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.

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

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.

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.

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

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

Good luck.

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.

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

Is mysql actually running?

systemctl status mysql.service

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?