When automating MySQL 5.7 database setup with Ansible, the temporary root password generated during installation creates authentication hurdles. The standard approach using mysql_db and mysql_user modules fails because:

• MySQL 5.7 generates random root passwords in /var/log/mysqld.log
• Ansible modules default to passwordless root access or ~/.my.cnf
• Temporary passwords contain special characters that need proper escaping

Step 1: Extract Temporary Password

- name: Get temporary MySQL root password
  shell: |
    grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}'
  register: mysql_temp_password
  changed_when: false

Step 2: Create Initial .my.cnf File

- name: Create temporary .my.cnf with generated password
  template:
    src: temp_my.cnf.j2
    dest: /root/.my.cnf
    mode: 0600
  vars:
    temp_password: "{{ mysql_temp_password.stdout }}"

Template file temp_my.cnf.j2:

[client]
user=root
password="{{ temp_password }}"

Step 3: Reset Root Password

- name: Change root password
  mysql_user:
    login_user: root
    login_password: "{{ mysql_temp_password.stdout }}"
    name: root
    host: localhost
    password: "{{ new_mysql_root_password }}"
    check_implicit_admin: yes
  no_log: true

Step 4: Finalize .my.cnf

- name: Update .my.cnf with new password
  template:
    src: permanent_my.cnf.j2
    dest: /root/.my.cnf
    mode: 0600

- hosts: db_servers
  vars:
    new_mysql_root_password: "SecurePass123!"
    db_name: wordpressdb
    db_user: wordpressuser
    db_password: "REDhat@123"
    
  tasks:
    # Step 1: Get temp password
    - name: Extract temporary root password
      shell: |
        grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}'
      register: mysql_temp_pass
      changed_when: false
    
    # Step 2: Create temp .my.cnf
    - name: Create temporary credentials file
      copy:
        content: |
          [client]
          user=root
          password="{{ mysql_temp_pass.stdout }}"
        dest: /root/.my.cnf
        mode: 0600
    
    # Step 3: Reset root password
    - name: Change root password
      mysql_user:
        name: root
        host: localhost
        password: "{{ new_mysql_root_password }}"
        check_implicit_admin: yes
      no_log: true
    
    # Step 4: Create permanent .my.cnf
    - name: Create permanent credentials file
      copy:
        content: |
          [client]
          user=root
          password="{{ new_mysql_root_password }}"
        dest: /root/.my.cnf
        mode: 0600
    
    # Step 5: Create database
    - name: Create application database
      mysql_db:
        name: "{{ db_name }}"
        state: present
    
    # Step 6: Create database user
    - name: Create database user
      mysql_user:
        name: "{{ db_user }}"
        password: "{{ db_password }}"
        priv: "{{ db_name }}.*:ALL"
        host: localhost
        state: present

• Always use no_log: true for password-related tasks
• Store sensitive variables in Ansible Vault
• Consider using become: false with limited MySQL admin accounts
• Implement password rotation policies

If you encounter "Access denied" errors:

1. Verify password contains proper escape characters
2. Check MySQL error logs (/var/log/mysqld.log)
3. Test manual login with the temporary password first
4. Ensure SELinux isn't blocking file creation (/root/.my.cnf)

When working with MySQL 5.7 fresh installations, the system generates a temporary root password stored in /var/log/mysqld.log. This creates authentication hurdles for Ansible automation since standard mysql modules expect proper credentials.

The error Access denied for user 'root'@'localhost' occurs because:

1. Ansible's mysql modules default to checking ~/.my.cnf
2. The temporary password hasn't been properly configured in the connection parameters

Here's a complete playbook solution that handles the temporary password scenario:

---
- hosts: db_servers
  become: yes
  vars:
    temp_mysql_password: "gg%j,opuE3Sm"  # Retrieved from mysqld.log
    new_mysql_password: "SecurePass123!"
    dbname: wordpressdb
    dbuser: wordpressuser
    dbuser_password: "REDhat@123"

  tasks:
    - name: Install MySQL Python bindings
      yum:
        name: MySQL-python
        state: present

    - name: Create .my.cnf with temporary credentials
      copy:
        dest: /root/.my.cnf
        content: |
          [client]
          user=root
          password="{{ temp_mysql_password }}"
        mode: 0600

    - name: Reset root password
      mysql_user:
        login_user: root
        login_password: "{{ temp_mysql_password }}"
        name: root
        password: "{{ new_mysql_password }}"
        host: localhost
        state: present

    - name: Update .my.cnf with permanent credentials
      copy:
        dest: /root/.my.cnf
        content: |
          [client]
          user=root
          password="{{ new_mysql_password }}"
        mode: 0600

    - name: Create application database
      mysql_db:
        name: "{{ dbname }}"
        state: present

    - name: Create database user with privileges
      mysql_user:
        name: "{{ dbuser }}"
        password: "{{ dbuser_password }}"
        priv: "{{ dbname }}.*:ALL"
        host: localhost
        state: present

Password Security: Never hardcode passwords in playbooks. Use Ansible Vault for production:

ansible-vault encrypt_string 'SecurePass123!' --name 'new_mysql_password'

Connection Handling: The playbook implements a three-stage approach:

1. Establishes initial connection with temporary password
2. Resets to permanent password
3. Proceeds with database operations

For environments where mysql modules aren't available:

- name: Create database via shell
  shell: |
    mysql -u root -p"{{ temp_mysql_password }}" --connect-expired-password \
    -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '{{ new_mysql_password }}'; \
    CREATE DATABASE {{ dbname }}; \
    CREATE USER '{{ dbuser }}'@'localhost' IDENTIFIED BY '{{ dbuser_password }}'; \
    GRANT ALL PRIVILEGES ON {{ dbname }}.* TO '{{ dbuser }}'@'localhost';"
  args:
    executable: /bin/bash
  register: mysql_output
  changed_when: "'already exists' not in mysql_output.stderr"

Special considerations for MySQL 5.7:

• Temporary passwords expire immediately after first use
• Password policies may require complex credentials
• Log file locations vary by Linux distribution