Access denied for user 'root'@'localho

问题:

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=<PW> 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 '<PW>';
FLUSH ALL PRIVILEGES;

But this did not help. Have you got any idea? I did not find the answer for the similar questions.

回答1:

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 '<password>';
FLUSH PRIVILEGES;

or

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';
FLUSH PRIVILEGES;

Then restarting mariadb:

sudo service mysql restart

And voila I had access from my personal account vi 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.

回答2:

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

回答3:

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 '<password>';

this fixed my problem in MariaDB.

Good luck

回答4:

I once had a similar problem. In my case it happened because I somehow corrupted my user table of the MySQL Server.

This article helped me: mysql-how-to-fix-Access-denied-for-user-'root'@'localhost'

Basically you have to reset the root user.

回答5:

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.

回答6:

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)]> 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 :)

回答7:

You just need to use sudo to get it working

sudo mysql_secure_installation