Ansible playbook to install and configure MySQL on Ubuntu

Question

I am trying to set up MySQL reasonably secured, on Ubuntu 22.04, using Ansible. This is my playbook (from a post by Lorin Hochstein) See Ansible idempotent MySQL installation Playbook This is my playbook (converted for apt and Ubuntu)

- hosts: carme.hcs
  become: yes
  gather_facts: false
  vars:
    new_mysql_root_password: <redacted>
    mysqlsoftware:
      - python3-pymysql
      - mysql-client
      - mysql-server

  tasks:
    - name: Install MySQL
      action: apt install {{ item }}
      with_items: "{{ mysqlsoftware }}"

    - name: Start the MySQL service
      action: service name=mysql state=started

    # 'localhost' needs to be the last item for idempotency, see
    # http://ansible.cc/docs/modules.html#mysql-user
    - name: update mysql root password for all root accounts
      mysql_user:
        check_implicit_admin: true
        login_user: root
        name: root
        priv: '*.*:ALL,GRANT'
        host: "{{ item }}"
        password: "{{ new_mysql_root_password }}"
      with_items:
        - 127.0.0.1
        - ::1
        - localhost

    - name: copy .my.cnf file with root password credentials
      template: src=./shared/my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600

    - name: delete anonymous MySQL server user for $server_hostname
      action: mysql_user user="" host="{{ server_hostname }}" state="absent"

    - name: delete anonymous MySQL server user for localhost
      action: mysql_user user="" state="absent"

    - name: remove the MySQL test database
      action: mysql_db db=test state=absent`

Steps 1 and 2 work just fine. Step 3 always fails with

TASK [update mysql root password for all root accounts] ********************************************************************************************************* failed: [carme.hcs] (item=127.0.0.1) => {"ansible_loop_var": "item", "changed": false, "item": "127.0.0.1", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"} failed: [carme.hcs] (item=::1) => {"ansible_loop_var": "item", "changed": false, "item": "::1", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"} failed: [carme.hcs] (item=localhost) => {"ansible_loop_var": "item", "changed": false, "item": "localhost", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}

I have checked on carme.hcs, and /root/.my.cnf does not exist. I can log in to mysql with "sudo mysql" but not with "mysql -u root" nor with "mysql -u root -p". Is this not the default set up of a fresh MySQL install, that I wish to change?

I also ran 'select user, host, plugin, authentication_string from user where user = "root";' on carme, and the result is garbled beyond belief. In short, plugin = "mysql_native_password" and authentication_string is blank for root@localhost.

I do not understand what is going wrong. Some enlightenment please!

Answer 1

There are so many things wrong with the playbook I'm embarrassed.

1. I misunderstood about installing pip and what should be installed with apt and what with pip.
2. apt will hang, and needs a -y param.
3. It leaves /root/.my.cnf so root can still log in with no password.
4. When that is fixed, when it is run twice it will fail the second time because it can't log in with no password.
5. 127.0.0.1 and ::1 do not exist in mysql.user table, so they do not need to have their root access removed.

OK, Lets post the updated playbook.

- hosts: carme.hcs
  become: yes
  gather_facts: false
  vars:
    new_mysql_root_password: redacted
    mysqlsoftware:
      - mysql-server
      - mysql-client
  tasks:
    - name: install python, pip etc
      shell: apt-get -y install "{{ item }}"
      with_items:
        - pip 
        - python3-dev 
        - default-libmysqlclient-dev 
        - build-essential

    - name: Install MySQL server
      shell: apt-get -y install mysql-server

    - name: Install MySQL client
      shell: apt-get -y install mysql-client

    - name: pip install mysqlclient
      shell: pip install mysqlclient

    - name: Start the MySQL service
      action: service name=mysql state=started

    - name: copy .my.cnf file with root password credentials
      template: src=/home/ian/Ansible/playbooks/shared/my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600

    - name: update mysql root password for all root accounts
      mysql_user:
        name: root
        host: localhost
        password: "{{ new_mysql_root_password }}"

    - name: delete anonymous MySQL server user for localhost
      action: mysql_user user="" state="absent"

    - name: remove the MySQL test database
      action: mysql_db db=test state=absent

    - name: Remove   /root/.my.cnf
      ansible.builtin.file:
        path: /root/.my.cnf
        state: absent