Access denied for user root localhost using password YES after new installation on Ubuntu

Question

Today I did a login as root into Ubuntu 14 04 1 LTS ll  and then apt-get install mariadb-server  without sudo but as root    With mySQL -h localhost -u root --password  lt PW gt  I got     Access denied for user  root   localhost   using password  YES    With mySQL -u root -p I logged into the DB and did  GRANT ALL PRIVILEGES ON     TO  root   localhost  IDENTIFIED BY   lt PW gt    FLUSH ALL PRIVILEGES    But this did not help  Have you got any idea  I did not find the answer for the similar questions

User · Answer

I had to be logged into Ubuntu as root in order to access Mariadb as root. It may have something to do with that "Harden ..." that it prompts you to do when you first install. So:

$ sudo su
[sudo] password for user: yourubunturootpassword
# mysql -r root -p
Enter password: yourmariadbrootpassword

and you're in.

User · Answer

The new command to flush the privileges is   FLUSH PRIVILEGES  The old command FLUSH ALL PRIVILEGES does not work any more   You will get an error that looks like that   MariaDB   none   gt  FLUSH ALL PRIVILEGES  ERROR 1064  42000   You have an error in your SQL syntax  check the manual that corresponds to your MariaDB server version for the right syntax to use near  ALL PRIVILEGES  at line 1  Hope this helps

User · Answer

In clean Ubuntu 16 04 LTS  MariaDB root login for localhost changed from password style to sudo login style     so  just do   sudo mysql -u root   since we want to login with password  create another user  user   in MariaDB console     you get in MariaDB console with  sudo mysql -u root    use mysql CREATE USER  user   localhost  IDENTIFIED BY  yourpassword    q   then in bash shell prompt    mysql-workbench   and you can login with  user  with  yourpassword  on localhost

User · Answer

Run mysql upgrade    Check that   SHOW GRANTS FOR  root   localhost     says  GRANT ALL PRIVILEGES ON     WITH GRANT OPTION    Check that the table exists  mysql proxies priv    Access denied for user   39 root  39    39 localhost  39  while attempting to grant privileges  How do I grant privileges

User · Answer

from superuser accepted answer   sudo mysql -u root use mysql  update user set plugin    where User  root   flush privileges  exit

User · Answer

TL DR  To access newer versions of mysql mariadb after as the root user  after a new install  you need to be in a root shell  ie sudo mysql -u root  or mysql -u root inside a shell started by su - or sudo -i first     Having just done the same upgrade  on Ubuntu  I had the same issue    What was odd was that   sudo  usr bin mysql secure installation   Would accept my password  and allow me to set it  but  I couldn t log in as root via the mysql client  I had to start mariadb with   sudo mysqld safe --skip-grant-tables   to get access as root  whilst all the other users could still access fine   Looking at the mysql user table I noticed for root the plugin column is set to unix socket whereas all other users it is set to  mysql native password   A quick look at this page  https   mariadb com kb en mariadb unix socket-authentication-plugin  explains that the Unix Socket enables logging in by matching uid of the process running the client with that of the user in the mysql user table  In other words to access mariadb as root you have to be logged in as root   Sure enough restarting my mariadb daemon with authentication required I can login as root with  sudo mysql -u root -p   or  sudo su - mysql -u root -p   Having done this I thought about how to access without having to do the sudo  which is just a matter of running these mysql queries  GRANT ALL PRIVILEGES on     to  root   localhost  IDENTIFIED BY   lt password gt    FLUSH PRIVILEGES     replacing  lt password gt  with your desired mysql root password   This enabled password logins for the root user    Alternatively running the mysql query   UPDATE mysql user SET plugin    mysql native password  WHERE user    root  AND plugin    unix socket   FLUSH PRIVILEGES    Will change the root account to use password login without changing the password  but this may leave you with a mysql mariadb install with no root password on it   After either of these you need to restarting mysql mariadb    sudo service mysql restart   And voila I had access from my personal account via mysql -u root -p  PLEASE NOTE THAT DOING THIS IS REDUCING SECURITY Presumably the MariaDB developers have opted to have root access work like this for a good reason    Thinking about it I m quite happy to have to sudo mysql -u root -p so I m switching back to that  but I thought I d post my solution as I couldn t find one elsewhere

User · Answer

Try the command   sudo mysql secure installation   press enter and assign a new password for root in mysql mariadb   If you get an error like      ERROR 2002  HY000   Can t connect to local MySQL server through socket     var run mysqld mysqld sock    enable the service with   service mysql start   now if you re-enter with   mysql -u root -p   if you follow the problem enter with sudo su and mysql -u root -p now apply permissions to root  GRANT ALL PRIVILEGES ON     TO  root   localhost  IDENTIFIED BY   lt password gt      this fixed my problem in MariaDB    Good luck

User · Answer

System like Ubuntu prefers to use auth socket plugin  It will try to authenticate by comparing your username in DB and process which makes mysql request  it is described in here     The socket plugin checks whether the socket user name  the operating   system user name  matches the MySQL user name specified by the client   program to the server  and permits the connection only if the names   match    Instead you may want to back with the mysql native password  which will require user password to authenticate   About the method to achieve that  I recommend this instead

[mysql] Access denied for user 'root'@'localhost' (using password: YES) after new installation on Ubuntu

Examples related to mysql

Examples related to mariadb