Setting mysql using ansible in ubuntu remote machine

Question

I have written one ansible playbook for installing MySQL server in ubuntu remote machine but is not working here is the yaml code

---
- name: setting mysql in ubuntu
  hosts: web01
  become: yes
  tasks:
   - name: install mysql
     apt:
      name: mysql-server
      state: present
   - name: install python
     apt:
      name: python3-pymysql
      state: present
   - name: start & enable server
     service:
      name: mysql
      state: started
      enabled: yes
   - name: user setup in mysql
     mysql_user:
      check_implicit_admin: true
      login_user: root
      login_password: password
      user: root
      password: password
      host: web01
      priv: '*.*.:ALL,GRANT'
   - name: create db
     mysql_db:
      name: db
      state: present

everytime in user setup it is showing:

fatal: [web01]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_******** are correct or /root/.my.cnf has the credentials. Exception message: (1698, "Access denied for user 'root'@'localhost'")"}

the web01 is the ec2 server name in aws I have also configured the inventory file properly and check it using ad-hoc command -m ping

and I have also made .my.cnf in the path /root/.my.cnf in the target machine and I have entered it like:

[client]
user=root
password=password

Answer 1

In the user setup task you are trying to connect to MySQL with the user credentials you want to set up, which doesn't exist at this point.

On Ubuntu MySQL is configured by default to accept connections from root via unix socket without a password.

If you change your task to this you can set up the root user:

    - name: user setup in mysql
      mysql_user:
       user: root
       password: password
       host: web01
       priv: '*.*:ALL,GRANT'
       login_unix_socket: /var/run/mysqld/mysqld.sock

^{(note that you also have a typo in your priv parameter I corrected)}

But technically you don't need to set up this root user at all, since you only need to provide the login_unix_socket parameter. You can set this up via module_defaults, so you don't need to specify it on every task:

- name: setting mysql in ubuntu
  hosts: web01
  become: yes
  module_defaults:
    mysql:
      login_unix_socket: /var/run/mysqld/mysqld.sock
  tasks:

You can just create your application database and the application user for it.