[SOLVED] Why can ansible not connect to MySql?

Issue

I want to deploy a MySql database server with these configurations.

This is my playbook to deploy MySql :

- name: "Inclure le fichier : install_utilitaires_mysql.yaml"
  include: install_utilitaires_mysql.yaml
  when: ansible_os_family == "Debian"

- name: "Telechargement d'un package .deb pour ajouter le repo mysql"
  get_url:
    url: https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
    dest: /tmp
  when: ansible_os_family == "Debian"

- name: "Installation du package .deb"
  apt:
    deb: /tmp/mysql-apt-config_0.8.15-1_all.deb
  when: ansible_os_family == "Debian"

- name: "Mise a jour des sources list : apt update"
  apt:
   update_cache: yes
  when: ansible_os_family == "Debian"

- name: "Installation de MySql"
  apt:
    name: mysql-server
    state: latest
  when: ansible_os_family == "Debian"

- name: "Demarrer le service MySql"
  service:
    name: mysql
    state: started
    enabled: yes
  when: ansible_os_family == "Debian"

- name: "Voir la variable mysql_username"
  debug:
    msg: "{{ mysql_username }}"
  when: ansible_os_family == "Debian"

- name: "Création du user : {{ mysql_username }}"
  mysql_user:
    name: "{{ mysql_username }}"
    password: "{{ mysql_password }}"
    priv: '*.*:ALL'
    state: present
  when: ansible_os_family == "Debian"

But when I execute this playbook with the ansible-playbook -i hosts playbook.yaml --vault-password-file password command, I will get this error :

TASK [Création du user : {{ mysql_username }}] ********************************************************************************************************************************************************************
skipping: [192.168.1.41]
[WARNING]: Module did not set no_log for update_password
fatal: [192.168.1.37]: 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: (1698, u\"Access denied for user 'root'@'localhost'\")"}

Why can ansible not connect to MySQL while mysql -u root works perfectly fine?

I found this solution but I don't understand how it works:

- name: Example using login_unix_socket to connect to server
  mysql_user:
    name: root
    password: root
    login_unix_socket: /var/run/mysqld/mysqld.sock
  when: ansible_os_family == "Debian"

First food for thought

I found this code

---
- name: "Inclure le fichier : install_utilitaires_mysql.yaml"
  include: install_utilitaires_mysql.yaml
  when: ansible_os_family == "Debian"

- name: "Telechargement d'un package .deb pour ajouter le repo mysql"
  get_url:
    url: https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
    dest: /tmp
  when: ansible_os_family == "Debian"

- name: "Installation du package .deb"
  apt:
    deb: /tmp/mysql-apt-config_0.8.15-1_all.deb
  when: ansible_os_family == "Debian"

- name: "Mise a jour des sources list : apt update"
  apt:
   update_cache: yes
  when: ansible_os_family == "Debian"

- name: "Installation de MySql"
  apt:
    name: mysql-server
    state: latest
  when: ansible_os_family == "Debian"

- name: "Demarrer le service MySql"
  service:
    name: mysql
    state: started
    enabled: yes
  when: ansible_os_family == "Debian"
  
- name: "Envoi du template .my.cnf dans ~"
  template:
    src: root_cnf.j2
    dest: ~/.my.cnf
    owner: root
    mode: 0600
  when: ansible_os_family == "Debian"

- name: "Redemarrer le service MySql"
  service:
    name: mysql
    state: restarted
    enabled: yes
  when: ansible_os_family == "Debian"

- name: "Création du user : {{ mysql_username }}"
  mysql_user:
    login_user: root
    login_password: root
    name: "{{ mysql_username }}"
    password: "{{ mysql_password }}"
    priv: '*.*:ALL'
    state: present
  when: ansible_os_family == "Debian"

But when I executed this ansible-playbook -i hosts playbook.yaml --vault-password-file password command I get this error:

TASK [Création du user : {{ mysql_username }}] ********************************************************************************************************************************************************************
skipping: [192.168.43.102]
[WARNING]: Module did not set no_log for update_password
fatal: [192.168.43.182]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /********/.my.cnf has the credentials. Exception message: (1698, u\"Access denied for user '********'@'localhost'\")"}

Question : Why does running my playbook result in this error?

Knowing that the root_cnf.j2 jinja2 file contains this configuration :

[client]
user=root
password=root

Solution

As you wrote, you need to use login_unix_socket: /var/run/mysqld/mysqld.sock (replace the path to the socket if necessary) as the password-less login is only available using the socket, not using the network.

See this post on dba.stackexchange.com.

It is basically a security feature, so you do not accidentally expose a password-less access to a database on the internet.

What you want to do is first

- name: Set password for root user
  mysql_user:
    name: "root"
    password: "{{ mysql_password }}"
    priv: '*.*:ALL,GRANT'
    host: 'localhost'
    login_unix_socket: /var/run/mysqld/mysqld.sock
    state: present

which will set a password for root. After that the login for root via the network will be enabled.
Afterwards, you want to do something like this:

- name: Save root password in .my.cnf
  template:
    src: root_cnf.j2
    dest: /root/.my.cnf
    owner: root
    mode: '0600'

With the template being this:

[client]
user=root
password={{ mysql_root_password }}

This will put your password in /root/.my.cnf so you are able to just use mysql on the machine without providing credentials (ansible will pick that up).

Then you can add users with this:

- name: "Create mysql user {{ mysql_username }}"
  mysql_user:
    name: "{{ mysql_username }}"
    password: "{{ mysql_password }}"
    priv: '*.*:ALL'

which is what you want, I guess.

Answered By - toydarian

Answer Checked By - Candace Johnson (WPSolving Volunteer)