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

Publicist

sudo chmod 777 /var/run/mysqld/ – Tjaden

Only the path was wrong to me: it WORKS on /usr/sbin/mysqld Changing OWNER allows normal users log without sudo. Sudo chown you:you mysqld – Ornamented

Answer 1

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

Publicist

To anyone wondering what the password may be, it's the string after IDENTIFIED BY, in this case simply 'password' – Candiot

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!) – Albertoalberts

@TaimoorAhmad no, just put -p on its own. Then it prompts you for the password, and the password you then type is not displayed on-screen for others to see. – Hendon

Answer 2

You need to change algorithm. Following work for me,

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

Suffruticose

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 – Procession

This worked for me when I was only able to log into mysql as root when I was signed in as root. Once I assigned root a blank password I was able to log out, log back in with the blank password, then use the same command to use my desired password . – Spenser

Answer 3

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!

Waddle

Worked for me. The only change I made was replacing '%' with 'localhost' – Tubule

Brilliant! This should be the accepted answer. Exactly what I was looking for as you explained it all in the well documented steps! – Vanmeter

Answer 4

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;

Trappings

plugin value was 'auth_socket' in my case. – Possessive

sudo service mysql restart to restart. – Contemptuous

This was the only one of all the listed answers that worked for me (MariaDB v15.1). – Marital

Answer 5

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.

Dagley

Answer 6

You can use the below query:

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

This query is enough.

Whence

This answer should be a solution as well. THANKS!!! – President

Answer 7

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

Tush

Answer 8

Login to mysql with sudo: sudo mysql -u root -p

After that Delete current root@localhost account:

~ MariaDB [(none)]> DROP USER 'root'@'localhost';
~ MariaDB [(none)]> CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';
~ MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
~ MariaDB [(none)]> FLUSH PRIVILEGES;

Stinkwood

Answer 9

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

Bertberta

How do I do that? The question I referred has a different error thrown – Publicist

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 liberiangeek.net/2014/10/reset-root-password-mariadb-centos-7 – Etz

Answer 10

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

Etz

I tried that and now I can't even login with sudo. Might have to reinstall mysql :( – Publicist

Congrats on making an answer that not only it fails to solve the problem, but also makes it worse. – Phospholipide