Access denied for user 'root'@'localhost' (using password: NO)

Install / Update
1

From yesterday trying easy install on my production server but it gives me this error
mysql -u root -p works but when i try to use sudo mysql it gives me error Access denied for user ‘root’@‘localhost’ (using password: NO) before that every thing was perfect and the setup was smooth

TASK [mariadb : Set root Password] *********************************************
task path: /tmp/.bench/playbooks/roles/mariadb/tasks/mysql_secure_installation.yml:14
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: root
<127.0.0.1> EXEC /bin/sh -c ‘echo ~root && sleep 0’
<127.0.0.1> EXEC /bin/sh -c ‘( umask 77 && mkdir -p “echo /root/.ansible/tmp/ansible-tmp-1572767793.4530768-51299877946600” && echo ansible-tmp-1572767793.4530768-51299877946600=“echo /root/.ansible/tmp/ansible-tmp-1572767793.4530768-51299877946600” ) && sleep 0’
Using module file /usr/local/lib/python3.6/dist-packages/ansible/modules/database/mysql/mysql_user.py
<127.0.0.1> PUT /home/ubuntu/.ansible/tmp/ansible-local-18477y2dsvnyo/tmpef_e536j TO /root/.ansible/tmp/ansible-tmp-1572767793.4530768-51299877946600/AnsiballZ_mysql_user.py
<127.0.0.1> EXEC /bin/sh -c ‘chmod u+x /root/.ansible/tmp/ansible-tmp-1572767793.4530768-51299877946600/ /root/.ansible/tmp/ansible-tmp-1572767793.4530768-51299877946600/AnsiballZ_mysql_user.py && sleep 0’
<127.0.0.1> EXEC /bin/sh -c ‘/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1572767793.4530768-51299877946600/AnsiballZ_mysql_user.py && sleep 0’
<127.0.0.1> EXEC /bin/sh -c ‘rm -f -r /root/.ansible/tmp/ansible-tmp-1572767793.4530768-51299877946600/ > /dev/null 2>&1 && sleep 0’
changed: [localhost] => (item=localhost) => {
“ansible_loop_var”: “item”,
“changed”: true,
“invocation”: {
“module_args”: {
“append_privs”: false,
“ca_cert”: null,
“check_implicit_admin”: true,
“client_cert”: null,
“client_key”: null,
“config_file”: “/home/ubuntu/.my.cnf”,
“connect_timeout”: 30,
“encrypted”: false,
“host”: “localhost”,
“host_all”: false,
“login_host”: “localhost”,
“login_password”: “VALUE_SPECIFIED_IN_NO_LOG_PARAMETER”,
“login_port”: 3306,
“login_unix_socket”: null,
“login_user”: null,
“name”: “root”,
“password”: “VALUE_SPECIFIED_IN_NO_LOG_PARAMETER”,
“priv”: null,
“sql_log_bin”: true,
“state”: “present”,
“update_password”: “always”,
“user”: “root”
}
},
“item”: “localhost”,
“msg”: “Password updated (new style)”,
“user”: “root”
}
<127.0.0.1> EXEC /bin/sh -c ‘echo ~root && sleep 0’
<127.0.0.1> EXEC /bin/sh -c ‘( umask 77 && mkdir -p “echo /root/.ansible/tmp/ansible-tmp-1572767793.7680137-255497450268338” && echo ansible-tmp-1572767793.7680137-255497450268338=“echo /root/.ansible/tmp/ansible-tmp-1572767793.7680137-255497450268338” ) && sleep 0’
Using module file /usr/local/lib/python3.6/dist-packages/ansible/modules/database/mysql/mysql_user.py
<127.0.0.1> PUT /home/ubuntu/.ansible/tmp/ansible-local-18477y2dsvnyo/tmpe_vmjy9f TO /root/.ansible/tmp/ansible-tmp-1572767793.7680137-255497450268338/AnsiballZ_mysql_user.py
<127.0.0.1> EXEC /bin/sh -c ‘chmod u+x /root/.ansible/tmp/ansible-tmp-1572767793.7680137-255497450268338/ /root/.ansible/tmp/ansible-tmp-1572767793.7680137-255497450268338/AnsiballZ_mysql_user.py && sleep 0’
<127.0.0.1> EXEC /bin/sh -c ‘/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1572767793.7680137-255497450268338/AnsiballZ_mysql_user.py && sleep 0’
<127.0.0.1> EXEC /bin/sh -c ‘rm -f -r /root/.ansible/tmp/ansible-tmp-1572767793.7680137-255497450268338/ > /dev/null 2>&1 && sleep 0’
The full traceback is:
File “/tmp/ansible_mysql_user_payload_3524584m/ansible_mysql_user_payload.zip/ansible/module_utils/mysql.py”, line 82, in mysql_connect
db_connection = mysql_driver.connect(**config)
File “/usr/lib/python3/dist-packages/MySQLdb/init.py”, line 86, in Connect
return Connection(*args, **kwargs)
File “/usr/lib/python3/dist-packages/MySQLdb/connections.py”, line 204, in init
super(Connection, self).init(*args, **kwargs2)

failed: [localhost] (item=127.0.0.1) => {
“ansible_loop_var”: “item”,
“changed”: false,
“invocation”: {
“module_args”: {
“append_privs”: false,
“ca_cert”: null,
“check_implicit_admin”: true,
“client_cert”: null,
“client_key”: null,
“config_file”: “/home/ubuntu/.my.cnf”,
“connect_timeout”: 30,
“encrypted”: false,
“host”: “127.0.0.1”,
“host_all”: false,
“login_host”: “localhost”,
“login_password”: “VALUE_SPECIFIED_IN_NO_LOG_PARAMETER”,
“login_port”: 3306,
“login_unix_socket”: null,
“login_user”: null,
“name”: “root”,
“password”: “VALUE_SPECIFIED_IN_NO_LOG_PARAMETER”,
“priv”: null,
“sql_log_bin”: true,
“state”: “present”,
“update_password”: “always”,
“user”: “root”
}
},
“item”: “127.0.0.1”,
“msg”: “unable to connect to database: (1045, "Access denied for user ‘root’@‘localhost’ (using password: NO)")”
}
<127.0.0.1> EXEC /bin/sh -c ‘echo ~root && sleep 0’
<127.0.0.1> EXEC /bin/sh -c ‘( umask 77 && mkdir -p “echo /root/.ansible/tmp/ansible-tmp-1572767793.9638236-95442746610021” && echo ansible-tmp-1572767793.9638236-95442746610021=“echo /root/.ansible/tmp/ansible-tmp-1572767793.9638236-95442746610021” ) && sleep 0’
Using module file /usr/local/lib/python3.6/dist-packages/ansible/modules/database/mysql/mysql_user.py
<127.0.0.1> PUT /home/ubuntu/.ansible/tmp/ansible-local-18477y2dsvnyo/tmphev2d4gz TO /root/.ansible/tmp/ansible-tmp-1572767793.9638236-95442746610021/AnsiballZ_mysql_user.py
<127.0.0.1> EXEC /bin/sh -c ‘chmod u+x /root/.ansible/tmp/ansible-tmp-1572767793.9638236-95442746610021/ /root/.ansible/tmp/ansible-tmp-1572767793.9638236-95442746610021/AnsiballZ_mysql_user.py && sleep 0’
<127.0.0.1> EXEC /bin/sh -c ‘/usr/bin/python3 /root/.ansible/tmp/ansible-tmp-1572767793.9638236-95442746610021/AnsiballZ_mysql_user.py && sleep 0’
<127.0.0.1> EXEC /bin/sh -c ‘rm -f -r /root/.ansible/tmp/ansible-tmp-1572767793.9638236-95442746610021/ > /dev/null 2>&1 && sleep 0’
The full traceback is:
File “/tmp/ansible_mysql_user_payload_yf_qbivx/ansible_mysql_user_payload.zip/ansible/module_utils/mysql.py”, line 82, in mysql_connect
db_connection = mysql_driver.connect(**config)
File “/usr/lib/python3/dist-packages/MySQLdb/init.py”, line 86, in Connect
return Connection(*args, **kwargs)
File “/usr/lib/python3/dist-packages/MySQLdb/connections.py”, line 204, in init
super(Connection, self).init(*args, **kwargs2)

failed: [localhost] (item=::1) => {
“ansible_loop_var”: “item”,
“changed”: false,
“invocation”: {
“module_args”: {
“append_privs”: false,
“ca_cert”: null,
“check_implicit_admin”: true,
“client_cert”: null,
“client_key”: null,
“config_file”: “/home/ubuntu/.my.cnf”,
“connect_timeout”: 30,
“encrypted”: false,
“host”: “::1”,
“host_all”: false,
“login_host”: “localhost”,
“login_password”: “VALUE_SPECIFIED_IN_NO_LOG_PARAMETER”,
“login_port”: 3306,
“login_unix_socket”: null,
“login_user”: null,
“name”: “root”,
“password”: “VALUE_SPECIFIED_IN_NO_LOG_PARAMETER”,
“priv”: null,
“sql_log_bin”: true,
“state”: “present”,
“update_password”: “always”,
“user”: “root”
}
},
“item”: “::1”,
“msg”: “unable to connect to database: (1045, "Access denied for user ‘root’@‘localhost’ (using password: NO)")”
}

RUNNING HANDLER [mariadb : restart mysql] **************************************
task path: /tmp/.bench/playbooks/roles/mariadb/handlers/main.yml:2

PLAY RECAP *********************************************************************
localhost : ok=23 changed=14 unreachable=0 failed=1 skipped=14 rescued=0 ignored=0

Traceback (most recent call last):
File “install.py”, line 413, in
install_bench(args)
File “install.py”, line 135, in install_bench
run_playbook(‘site.yml’, sudo=True, extra_vars=extra_vars)
File “install.py”, line 327, in run_playbook
success = subprocess.check_call(args, cwd=os.path.join(cwd, ‘playbooks’))
File “/usr/lib/python2.7/subprocess.py”, line 190, in check_call
raise CalledProcessError(retcode, cmd)
subprocess.CalledProcessError: Command ‘[‘ansible-playbook’, ‘-c’, ‘local’, ‘site.yml’, ‘-vvvv’, ‘-e’, ‘@/tmp/extra_vars.json’, ‘–become’, ‘–become-user=erp’]’ returned non-zero exit status 2

2

Do you have a password for mysql (ie not empty password)?

3

yes i do it connects when i use mysql -u root -p but when i use sudo mysql it gives error

4

There is something wrong with your config.

It should be using the password and not saying ‘NO’ to using the password.
You should revisit the installation instructions and make sure your database details are correctly entered,

(using password: NO)

5

this is an easy installation on fresh vps nothing to do with config

6

‘pip’: “sudo pip install --upgrade setuptools cryptography ansible pip”

with

‘pip’: “sudo pip install --upgrade setuptools cryptography ansible==2.8.5.0 pip”