Issue
I have tried to install mysql, and then to run with
mysql -u root -p
but I get
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
I can get in with sudo, but I don't want to run as root. How do I get to run mysql as non-root?
I am using Linux Mint 19.1 32-bit.
What I tried:
sudo apt-get install mysql-client
sudo apt-get install mysql-server
sudo /etc/init.d/mysql restart
sudo mysql_secure_installation
- Set up VALIDATE PASSWORD? No
- Remove anonymous users? No
- Disallow root login remotely? Yes
- Remove test database and access to it? No
- Reload privilege tables now? Yes
Then I try
mysql -u root -p
it asks for the password, I give the same one I gave in mysql_secure_installation...
frank@frank-laptop:~/WebDev$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Gah.
I have reinstalled mysql client and server several times now, doing a complete uninstall between and getting rid of all databases when asked. I don't get asked for a root password during the install. That seems to be normal for an install in Ubuntu and derivatives, hence the mysql_secure_installation to set the password.
I tried following http://www.yolinux.com/TUTORIALS/LinuxTutorialMySQL.html I tried searching on the web for hints, e.g. https://askubuntu.com/questions/766900/mysql-doesnt-ask-for-root-password-when-installing#766908 and others but I have not been able to get mysql to start without sudo. If I do use sudo I can create a database, add tables and add data so the database is installed, I just need more privilege to run it than I think I should need.
Anywhere I looked, the instructions go from installation to starting mysql without anything special between, so I think I must be doing something quite stupid. But I have not been able to figure out what.
Solution
The first time you run mysql -u root -p , you have to run it with sudo in order to create a new user:
DROP USER 'root'@'localhost';
CREATE USER 'XXX'@'%' IDENTIFIED BY 'YYY';
GRANT ALL PRIVILEGES ON *.* TO 'XXX'@'%' WITH GRANT OPTION;
Then you can connect yourself using mysql --user=XXX --password=YYY
I already try but I don't think you can setup your DB without using sudo unfortunately.
Answered By - Vincent Bénet Answer Checked By - Marie Seifert (WPSolving Admin)