Access denied for user 'root'@'localhost'

[netlooker]

After build and provisioning machine I've noticed small issue regarding connection with mysql database for root user.
I wasn't able to login via default root/root either from command line or adminer. After short investigation I found that root@localhost user is using “plugin: auth_socket”. I've changed that via following commands:

sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

After this small task I'm able to login as an root. Below I'm enclosing article which is putting more light on that topic.
https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/

[geerlingguy]

Interesting... I'll take a look—this might need to be filed upstream in the geerlingguy.mysql role repository.

[hawkeyetwolf]

Confirmed the issue and suggested temporary resolution. Thanks @netlooker!

[antonyanimator]

Yep I have the same problem. Thanks for the solution netlooker

sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

[aleksey-tk]

I have the same issue and also can confirm that previous comment fix the problem

[tsmith512]

👍 for the solution provided by @antonyanimator and @netlooker. Got me fixed up. Thank you.

[mixplex]

I had the same issue with the D8, PHP 7 machine. Thank you anonyanimator for the solution and netlooker for the post. Finally got me fixed up too.

[mixplex]

I still have issues with D8 installs, getting caught denying access to root in mysql.

[tsmith512]

@mixplex , If you're having trouble with D8 installs during provisioning, I wonder if that's because the fix from this ticket has to be executed manually. Looks like the MySQL setup is actually pulled in from another repo, but maybe adding a command that would sudo mysql -e 'the command from above' in this file: https://github.com/geerlingguy/drupal-vm/blob/master/provisioning/tasks/install-site.yml at line 10 might be a temporary fix to support auto install?

[geerlingguy]

Note that if you use a mysql_users user specific to your drupal site/mysql_databases database, then automatic installation should work without worrying about the root user account.

I typically don't rely on having access to the root MySQL account (just like I don't use the root Linux account unless it's necessary), and if I need an account to access multiple databases, I'll set that in the user's access permissions.

I still want to fix this upstream, of course :)

[geerlingguy]

Related:

• Add plugin as parameter for mysql_user  ansible/ansible-modules-core#3003
• http://dev.mysql.com/doc/refman/5.7/en/default-privileges.html