installation - mysql login in problems -

i have had issue multiple times on different vpss have. find different ways around them want solve problem permanently.

i these errors when try log mysql:

(env) lit@digitalocean:~/howlit/init$ mysql error 1045 (28000): access denied user 'lit'@'localhost' (using password: no) (env) lit@digitalocean:~/howlit/init$ mysql -u lit error 1045 (28000): access denied user 'lit'@'localhost' (using password: no) 

then if try root:

(env) lit@digitalocean:~/howlit/init$ mysql -u root error 1698 (28000): access denied user 'root'@'localhost' 

however if do:

sudo mysql -u root 

and enter normal password user lit works.

however cannot create database nor else. i've purged , reinstalled mysql multiple times , run same errors on , over.

what causes these errors? doing wrong in installation?

mysql> show grants; +---------------------------------------------------------------------+ | grants root@localhost                                           | +---------------------------------------------------------------------+ | grant privileges on *.* 'root'@'localhost' grant option | | grant proxy on ''@'' 'root'@'localhost' grant option        | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec)  

try logging in command: mysql -u root -p

or

mysql -u <your user name> -p

enter password set earlier users.

if neglect add -p, mysql assume trying log in without password, , if doesn't match password stored in mysql user table, reject attempt log in.

once logged in, enter: show grants;

now you've shown user grants, issue root user set in way require use sudo. fix log in have sudo, , run following lines.

please carefully. after making sure have backups of mysql data need (this not delete data, it’s play safe), first run: drop user 'root'@'localhost';

then run, without logging out first: create user 'root'@'%' identified '<<<your desired password>>>'; grant privileges on *.* 'root'@'%'; flush privileges;

you should able log in without sudo. non digitalocean machines, prefer limit root db account localhost optimum security, if select sufficient password, should fine either way.

if don't want create user above, can try logging in using: mysql -u root -h localhost -p or mysql -u lit -h localhost -p (if created user lit@localhost in mysql)

since sound interested in debugging root cause (bravo), can run , see other users have been created in mysql. log mysql, , run: use mysql; select * user;

pasting output text editor word wrap disabled make readable.

you should see other user accounts have been created. keep in mind accounts may take priority on account's privileges, , may causing login issues. wouldn't recommend posting output online though passwords hashed out. luck.