Connect to mysql server without sudo
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;
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!
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
You need to change algorithm. Following work for me,
mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
mysql > FLUSH PRIVILEGES;