Let's say you have the following playbook which uses the mysql_db module.

---
- hosts: web
  remote_user: root
  tasks:
    - name: create the all databases .sql backup file
      mysql_db:
        state: dump
        name: all
        target: "/mnt/share/backups/web/sql/{{ ansible_fqdn }}.{{ date }}-all-databases.sql"
...

Let's say the following is being returned when running this playbook.

TASK [create the all databases .sql backup file] 
fatal: [server1.example.com]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (2003, \"Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)\")"}

Notice the error message says "can't connect to MySQL server on 'localhost'". Notice also that the playbook includes the remote_user: root parameter. In this scenario, on each managed node, check to see if you can connect to mySQL or MariaDB as root. Notice in this example that no password was included. This means that root account has been setup to make connections to mySQL or MariaDB without a password.

~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 183632
Server version: 10.5.10-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

If a password must be provided to connect to mySQL or MariaDB, one option is to setup the user account to make connections to mySQL or MariaDB without a password. Or, on each managed node, issue the following command to ensure that the remote_user (root in this example) has been granted access to connect to mySQL or MariaDB on localhost, and to list the hashed password.

~]# mysql -e "select User,Password,Host from mysql.user where User = 'root' \G"
*************************** 1. row ***************************
    User: root
Password: *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF
    Host: localhost
*************************** 2. row ***************************
    User: root
Password: *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF
    Host: 127.0.0.1
*************************** 3. row ***************************
    User: root
Password: *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF
    Host: ::1

Notice that the hashed password in this example is 964A30F03EBE2D0D8EADC172640E4389BC9AF7FF. The select password command can be used to verify the users password matches the hashed password.

~]# mysql -e "select password('itsasecret')"
+-------------------------------------------+
| password('itsasecret')                    |
+-------------------------------------------+
| *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF |
+-------------------------------------------+

If the password is incorrect, you can updated the password.

mysql -e "update mysql.user SET Password=PASSWORD('itsasecret') where User='root'"
mysql -e "update mysql.user SET authentication_string=PASSWORD('itsasecret') where User='root'"

Notice the error message references the /root/.my.cnf file. If the /root/.my.cnf file exists and the password in /root/.my.cnf matches the cleartext password in the above command, then there is no need to include the login_user and login_password options in the playbook.

~]# cat /root/.my.cnf
[client]
password=itsasecret

If issues persist, try including the login_unix_socket option, which should point to the location of the mysql.sock file on each managed node.

---
- hosts: web
  remote_user: root
  tasks:
    - name: create the all databases .sql backup file
      mysql_db:
        state: dump
        name: all
        target: "/mnt/share/backups/web/sql/{{ ansible_fqdn }}.{{ date }}-all-databases.sql"
        login_unix_socket: /var/lib/mysql/mysql.sock
...