Connect to mysql server without sudo

Question

The command:

mysql -u root -p

gives the error:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

But running sudo privileges, works:

sudo mysql -u root -p

Is it possible to get rid of the sudo requirement because it prevents me from opening the database in intellij? I tried the following as in the answer to this question Connect to local MySQL server without sudo:

sudo chmod -R 755 /var/lib/mysql/

which did not help. The above question has a different error thrown

Possible duplicate of [Connect to local MySQL server without sudo](http://stackoverflow.com/questions/24479868/connect-to-local-mysql-server-without-sudo) — , May 15 '16 at 15:24

Riyafa Abdul Hameed · Accepted Answer · 2018-07-12T02:46:45.673

118

Only the root user needs sudo requirement to login to mysql. I resolved this by creating a new user and granting access to the required databases:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

now newuser can login without sudo requirement:

mysql -u newuser -p

edited Jul 12 '18 at 02:46

answered May 15 '16 at 18:17

Riyafa Abdul Hameed

5,468
4
34
40

2

To anyone wondering what the password may be, it's the string after `IDENTIFIED BY`, in this case simply 'password' – AGoranov Jun 09 '19 at 16:08
Just a heads up: when you connect don't put a space after the `-p` option. For example do: `mysql -u newuser -ppassword` (no space!) – Taimoor Jun 29 '20 at 17:13

score 72 · Answer 2 · answered Nov 30 '17 at 07:22

72

You need to change algorithm. Following work for me,

mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
mysql > FLUSH PRIVILEGES;

answered Nov 30 '17 at 07:22

Hemant Thorat

1,718
16
12

It helped me too. Suddenly, after restart I started getting the Access Denied for user root@localhost. This solution help me to connect with mysql – ahsan_cse2004 Jan 30 '20 at 06:57

score 42 · Answer 3 · answered May 18 '19 at 05:07

You can use the same ROOT user, or a NEW_USER and remove the SUDO privileges. Below example shows how to remove connect using ROOT, without SUDO.

Connect to MY-SQL using SUDO

sudo mysql -u root

Delete the current Root User from the User Table

DROP USER 'root'@'localhost';

Create a new ROOT user (You can create a different user if needed)

CREATE USER 'root'@'%' IDENTIFIED BY '';

Grant permissions to new User (ROOT)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Flush privileges, so that the Grant tables get reloaded immediately. (Why do we need to flush privileges?)

FLUSH PRIVILEGES;

Now it's all good. Just in case, check whether a new root user is created.

SELECT User,Host FROM mysql.user;

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | %         |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

Exit mysql. (Press CTRL + Z). Connect to MySQL without SUDO

mysql -u root

Hope this will help!

Worked for me. The only change I made was replacing '%' with 'localhost' — David, Jan 05 '20 at 06:16

Mahmood Shahbazian · Answer 4 · 2020-07-22T06:07:09.357

31

first login to your mysql with sudo.

then use this code to change "plugin" coloumn value from "unix_socket" or "auth_socket" to "mysql_native_password" for root user.

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin IN ('unix_socket', 'auth_socket');

FLUSH PRIVILEGES;

finally restart mysql service. that's it.

if you want more info, check this link

UPDATE:

In new versions of mysql or mariadb you can use :

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password USING PASSWORD('your-password');
FLUSH PRIVILEGES;

edited Jul 22 '20 at 06:07

answered Aug 01 '16 at 21:40

Mahmood Shahbazian

319
3
5

2

plugin value was 'auth_socket' in my case. – Zubin Sep 21 '17 at 17:16
`sudo service mysql restart` to restart. – tejasvi88 Oct 29 '20 at 08:55
This was the only one of all the listed answers that worked for me (MariaDB v15.1). – Husky Nov 18 '20 at 13:35
What if you don't have sudo rights? – Brian Wiley Feb 07 '21 at 02:49

score 7 · Answer 5 · answered Aug 20 '19 at 16:57

I have solved this problem using following commands.

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost';
FLUSH PRIVILEGES;

Here, username = any user name you like.

and password = any password you like.

score 6 · Answer 6 · edited Nov 19 '18 at 08:00

6

You can use the below query:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

This query is enough.

edited Nov 19 '18 at 08:00

Salman Zafar

3,079
4
15
35

answered Jul 19 '18 at 06:56

SREEKANTH KC

69
1
3

score 1 · Answer 7 · edited Jun 20 '20 at 09:12

1

This answer needs to be slightly adapted for mariaDB instead of mysql.

First login as root using sudo:

$ sudo mysql -uroot

Then alter the mariadb root user:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password USING PASSWORD('mypassword');
FLUSH PRIVILEGES;

From now on sudo is not longer needed:

$ mysql -uroot -p

Version used: mysql Ver 15.1 Distrib 10.4.13-MariaDB, for osx10.15 (x86_64) using readline 5.1

edited Jun 20 '20 at 09:12

Community

1
1

answered May 25 '20 at 15:34

user1877106

11
1
2

What if you don't have sudo rights? – Brian Wiley Feb 07 '21 at 02:49

score -1 · Answer 8 · edited May 23 '17 at 12:10

-1

In the comment of the question you answer you referenced, it reads

Ok, just try to analyze all of the directories down in the path of the socket file, they need to have o+rx and the sock file too (it's not a good idea to make it modifiable by others).

You can also try to remove mysql.sock and then restart mysqld, the file should be created by the daemon with proper privileges.

This seemed to work for this question(the one you said you looked at) so it may work for you as well

edited May 23 '17 at 12:10

Community

1
1

answered May 15 '16 at 15:18

How do I do that? The question I referred has a different error thrown – Riyafa Abdul Hameed May 15 '16 at 15:22
Thats no reason to downvote!! : tahe a look in the /etc/mysql/my.cnf file and diable this: #plugin-load-add = auth_socket.so and restart databse, or read this how to start mysql without password https://www.liberiangeek.net/2014/10/reset-root-password-mariadb-centos-7/ – Bernd Buffen May 15 '16 at 20:15

score -5 · Answer 9 · edited Jun 20 '20 at 09:12

-5

The error Message:

"ERROR 1698 (28000): Access denied for user 'root'@'localhost'"

means that the Server not allow the connect for this user and not that mysql cant access the socket.

try this to solve the problem:

Login in your DB

sudo mysql -u root -p

then make these modifications:

MariaDB []>use mysql;
MariaDB [mysql]>update user set plugin=' ' where User='root';
MariaDB [mysql]>flush privileges;
MariaDB [mysql]>exit

try login again without sudo

edited Jun 20 '20 at 09:12

Community

1
1

answered May 15 '16 at 15:38

Bernd Buffen

12,768
2
20
31

3

I tried that and now I can't even login with sudo. Might have to reinstall mysql :( – Riyafa Abdul Hameed May 15 '16 at 16:32

Connect to mysql server without sudo

9 Answers9

Linked