'Ubuntu 18.04 start mysql without root privilege

I just installed ubuntu 18.04 and installed LAMP on it, however I had trouble using mysql without the root privilege. when I write:

mysql -u root -p

and enter the password I configured it throws access denied. unless if I write

sudo mysql -u root -p

and enter the password that I'm able to connect. But I don't want the sudo cause it prevents having workbench to connect to mysql, same applies to phpmyadmin too. Any hints on how to fix that?

mysqllinuxphpmyadminworkbenchubuntu-18.04exceljava-io


Solution 1:[1]

I also faced this problem yesterday. To solve this problem follow these steps:

  1. Connect to MySQL: sudo mysql -u root
  2. Check for mysql users in db: SELECT User,Host FROM mysql.user;
  3. Drop root user account: DROP USER 'root'@'localhost';
  4. Recreate root user: CREATE USER 'root'@'%' IDENTIFIED BY ''
  5. Grant all privileges to root user: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
  6. Set password for root user: ALTER USER 'root'@'%' IDENTIFIED BY 'yourpassword';

That's it! Now you should be able to connect the MySQL.

Solution 2:[2]

I am using Ubuntu 18.04 with mysql version 5.7 in workbench but solved this by following these 10 steps

  1. Connect to MySQL: sudo mysql -u root
  2. Check for mysql users in db: SELECT User,Host FROM mysql.user;
  3. Drop root user account: DROP USER 'root'@'localhost';
  4. SET GLOBAL validate_password_length = 5;
  5. SET GLOBAL validate_password_number_count = 0;
  6. SET GLOBAL validate_password_mixed_case_count = 0;
  7. SET GLOBAL validate_password_special_char_count = 0;
  8. SET GLOBAL validate_password_policy = LOW;
  9. Recreate root user: CREATE USER 'root'@'%' IDENTIFIED BY 'admin'
  10. Grant all privileges to root user: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Solution 3:[3]

enter image description here 1. UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';

2.FLUSH PRIVILEGES;

Solution 4:[4]

If you couldn't connect to mysql with any mysql user without sudo, try:

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

or

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

then restart the service

sudo services mysql start

or

sudo /etc/init.d/mysql start

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Taimoor Tariq
Solution 3 Heba abd El monaem
Solution 4 yang

Related Questions