Ubuntu 18.04 start mysql without root privilege
Solution 1
It should only be the "-u root" user that requires sudo. It's good practice to not use root for most access anyways, so just login as root using sudo and create a newuser:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
Then you should be able to access "mysql -u newuser" without sudo.
Solution 2
I also faced this problem yesterday. To solve this problem follow these steps:
- Connect to MySQL:
sudo mysql -u root
- Check for mysql users in db:
SELECT User,Host FROM mysql.user;
- Drop root user account:
DROP USER 'root'@'localhost';
- Recreate root user:
CREATE USER 'root'@'%' IDENTIFIED BY ''
- Grant all privileges to root user:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
- Set password for root user:
ALTER USER 'root'@'%' IDENTIFIED BY 'yourpassword';
That's it! Now you should be able to connect the MySQL.
Solution 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
I am using Ubuntu 18.04 with mysql version 5.7 in workbench but solved this by following these 10 steps
- Connect to MySQL:
sudo mysql -u root
- Check for mysql users in db:
SELECT User,Host FROM mysql.user;
- Drop root user account:
DROP USER 'root'@'localhost';
SET GLOBAL validate_password_length = 5;
SET GLOBAL validate_password_number_count = 0;
SET GLOBAL validate_password_mixed_case_count = 0;
SET GLOBAL validate_password_special_char_count = 0;
SET GLOBAL validate_password_policy = LOW;
- Recreate root user:
CREATE USER 'root'@'%' IDENTIFIED BY 'admin'
- Grant all privileges to root user:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
David NIWEWE
{ “persona”:{“Student”,”Software Developer”,”Crazy”}, “ego”:{“Student”,”Artist”,”Thinker”,”Human”,”Believer”}, “anima”:”Sophia”, “self”:”Individuating …” }
Updated on June 23, 2022Comments
-
David NIWEWE almost 2 years
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?