Unable to create mysql database through ansible

‘Fresh’ installation of MySQL 5.7 allows local root logins using socket file (crucial point is the line login_unix_socket: /var/run/mysqld/mysqld.sock in tasks/main.yml)

Despite 'shell' workarounds mentioned in other answers for this and similar questions do their job - they are not idempotent and produce the 'changed' state which is not good.

The following snippets work in completely idempotent way on Ubuntu 18.04+

tasks/main.yml

 # ...deleted... - name: Ensure mysql is running and starts on boot service: name: mysql state: started enabled: yes become: yes - name: Ensure mysql root password is updated for all root accounts mysql_user: name: root host: "{{ item }}" login_unix_socket: /var/run/mysqld/mysqld.sock password: "{{ mysql.root_db_password }}" priv: '*.*:ALL,GRANT' check_implicit_admin: true loop: "{{ mysql.hosts }}" become: yes notify: Restart MySQL - name: Create `/root/.my.cnf` with root password credentials template: src: my.cnf.j2 dest: /root/.my.cnf owner: root mode: 0600 become: yes notify: Restart MySQL 

templates/my.cnf.j2

[client] user=root password={{ mysql.root_db_password }} 

defaults/main.yml

mysql: root_db_password: REDACTED hosts: - "{{ ansible_hostname }}" - 127.0.0.1 - ::1 - localhost 

As stated in the mysql_db module notes and mysql_user notes (same text in both):

Both login_password and login_user are required when you are passing credentials. If none are present, the module will attempt to read the credentials from ~/.my.cnf, and finally fall back to using the MySQL default login of ‘root’ with no password.

So you have several options:

Pass credentials to the task

- name: Creating database mysql_db: name={{ dbname }} state=present login_user={{ DB_USER }} login_password={{ DB_USER_PASS }} - name: Create db User mysql_user: name={{ dbuser }} password={{ password }} priv=*.*:ALL host='localhost' state=present login_user={{ DB_USER }} login_password={{ DB_USER_PASS }} 

Replace DB_USER and DB_USER_PASS with your db user administrator credentials.

But this is far from optimal, because you have to put credentials in every mysql task and your credentials should be in your ansible code. You can use ansible vault to mitigate this but even that there are better options.

Write a ~/.my.cnf file with credentials

Create or edit ~/.my.cnf file in user's home (probably root) with mysql credentials:

[client] user=root password="PASSWORD" 

(You may need to add other connection details).

The third option I'm not sure how it works in CentOS, but mainly implies that root user can connect to mysql without using credentials. Usually it relies on a /root/-.my.cnf file, but in CentOS may differ.

After googling i found a way to reset the temporary password:

- name: Find temporary password shell: "echo `grep 'temporary.*root@localhost' /var/log/mysqld.log | sed 's/.*root@localhost: //'`" register: mysql_root_password_temp tags: register Set the new password using the temporary password - name: Set new password from temporary password shell: 'mysql -e "SET PASSWORD = PASSWORD(''{{ mysql_root_password }}'');" --connect-expired-password -u root -p"{{ mysql_root_password_temp.stdout }}"' 

you need to add the socket for root https://docs.ansible.com/ansible/2.9/modules/mysql_db_module.html#parameter-login_unix_socket

same for mysql_user

- name: Creating database mysql_db: login_unix_socket: /var/run/mysqld/mysqld.sock name: {{ dbname }} state: present - name: Create db User mysql_user: login_unix_socket: /var/run/mysqld/mysqld.sock name: {{ dbuser }} password: {{ password }} priv: "*.*:ALL" host: 'localhost' state: present