Can't run mysql commands as root linux user

Question

As linux root user:

root@local:~# mysql

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

I'm confused. Shouldn't the root user be able to run mysql without additional authentication? How do I fix this?

If you run just `mysql` the root user should be without password — Ergest Basha, Nov 30 '21 at 20:22
Of course it asks for a password if I specify a mysql user, but I don't want to provide a password, I want to run mysql commands as the linux root without a password. — Dan, Nov 30 '21 at 20:30
Does [this](https://serverfault.com/a/563717/235281) or [this](https://serverfault.com/a/399274/235281) work for you? — vee, Nov 30 '21 at 20:34
Nope, those answers still require passwords in one way or another — Dan, Nov 30 '21 at 20:47
It confuses a lot of Linux users that there is a Linux root user and a separate MySQL root user. Your MySQL root user does not depend on the root account, after installation. — Dave Stokes, Dec 02 '21 at 15:36

score 0 · Answer 1 · answered Nov 30 '21 at 20:30

0

Your root account in MySql has a password. Try this

mysql -p

and enter the password you, or somebody set.

If that doesn't work try

mysql -h localhost -u root -p

It that doesn't work you'll need to reset your MySql root password. That's the topic of several tutorials on the 'toobz

answered Nov 30 '21 at 20:30

O. Jones

103,626
17
118
172

`mysql -p` requires that the root linux account be able to run `mysql` without providing a password. Which is my problem. – Dan Nov 30 '21 at 20:32
@Dan your problem is that your root user has a password and you want to log in without one. That is not possible – Ergest Basha Nov 30 '21 at 20:34

Bill Karwin · Answer 2 · 2021-11-30T20:55:49.047

0

If the MySQL root user has a password, using the default authentication plugin, then you must provide a password to connect, full stop. You can configure an account with no password, but that's a habit you should avoid.

You can provide a password by any of the following means:

Entering it interactively with the -p option
Entering it in plaintext in the commandline like -p<mypassword>
Storing it in plaintext in your ~/.my.cnf file in the [client] section (see https://dev.mysql.com/doc/refman/8.0/en/option-files.html)
Storing it in an obfuscated form in your ~/.mylogin.cnf file (see https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html).

There's also a way to provide a password through environment variables, but that's discouraged now because it's really not secure.

edited Nov 30 '21 at 20:55

answered Nov 30 '21 at 20:37

Bill Karwin

538,548
86
673
828

What about auth_socket? – Dan Nov 30 '21 at 20:47
That would work if you create the root user to be authenticated using the auth_socket plugin (example: `CREATE USER 'root'@'localhost' IDENTIFIED WITH auth_socket`), but if the root user were created in the conventional way, that is authenticated with a password, then auth_socket would not work. – Bill Karwin Nov 30 '21 at 20:52
I don't understand how the last server I set up had auth_socket set up by default and this new one doesn't. I'm trying to write an installation script to initialize a mysql database and I want to run mysql commands. I either need to use auth_socket (apparently not the default) or be able to provide the root password in the bash script (-p doesn't seem to work, it prompts for a password even if you provide a value). – Dan Nov 30 '21 at 21:01
1

Note that providing a password value on the command-line requires that you have no space between `-p` and the password. That is, `-p password` is wrong, it will interpret the password as the next argument. You must specify it like `-ppassword`. – Bill Karwin Nov 30 '21 at 21:03
I have no idea who set up your last server or what version it was. Perhaps it was MariaDB? MariaDB is not MySQL. They change a lot of features, and one of them is that the root user is created to use auth_socket by default, cf. https://mariadb.com/kb/en/authentication-plugins/ – Bill Karwin Nov 30 '21 at 21:04
I don't know why it isn't a solution for you to use `.my.cnf` or `.mylogin.cnf`. – Bill Karwin Nov 30 '21 at 21:05
I generally try not to keep passwords in plaintext files, seems a little insecure to me. The `-p` comment was helpful though, thank you. Now I just have to decide between that and figuring out how to configure the mysql root user with auth_socket by default on/after installation in a scriptable manner. The MySQL server in both cases was installed with apt on Ubuntu 20 (so MySQL 8). – Dan Nov 30 '21 at 21:09
That's a strange choice, because recent versions of MySQL have started warning that using a password on the command-line is insecure. They recommend to put it in an options file (and be careful about the file permissions on that file). See https://stackoverflow.com/questions/20751352/suppress-warning-messages-using-mysql-from-within-terminal-but-password-written – Bill Karwin Nov 30 '21 at 21:14
I think my preferred way is using auth_socket style authentication then. I've read that it's the default used when installing MySQL but I must have done something to set the password and thus password-based authentication. Gotta figure out how to write my installation script properly so mysql creates the root user with auth_socket authentication. – Dan Nov 30 '21 at 21:19
Also - it's not technically "using a password on the command-line". It's using a password in a bash script where the password is assigned to a variable using `read -sp`. Is that still insecure? – Dan Nov 30 '21 at 21:23
You'll get the same warning from the mysql client, so yes, it's the same. – Bill Karwin Nov 30 '21 at 21:33

score 0 · Accepted Answer · edited Dec 03 '21 at 08:03

0

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

edited Dec 03 '21 at 08:03

E_net4

27,810
13
101
139

answered Dec 02 '21 at 20:07

Dan

744
1
8
23

Can't run mysql commands as root linux user

3 Answers3