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)