I try to setup a MySQL DB with Ansible, however, I have trouble with changing the initial root password.

    - name: Get temporary root password from install log
      shell: cat /var/log/mysqld.log | grep "temporary password" | grep -oE '[^ ] $'
      register: tmp_root_password


    
    - name: Set new password from temporary password
      shell: 'mysql -e \"ALTER USER root IDENTIFIED BY("{{ mysql_root_password }}");\" --connect-expired-password -uroot -p"{{ tmp_root_password.stdout }}"'

Fails with the following error:

 fatal: [mysqlhost.mydomain]: FAILED! => {"changed": true, "cmd": "mysql -e \\\"ALTER USER root IDENTIFIED BY(\" MyNewPassword\");\\\" --connect-expired-password -uroot -p\"MyTmpPassword\"", "delta": "0:00:00.003081", "end": "2021-11-28 08:40:52.000198", "msg": "non-zero return code", "rc": 1, "start": "2021-11-28 08:40:51.997117", "stderr": "/bin/sh: -c: line 0: syntax error near unexpected token `('\n/bin/sh: -c: line 0: `mysql -e \\\"ALTER USER root IDENTIFIED BY(\" MyNewPassword\");\\\" --connect-expired-password -uroot -p\"MyTmpPassword\"'", "stderr_lines": ["/bin/sh: -c: line 0: syntax error near unexpected token `('", "/bin/sh: -c: line 0: `mysql -e \\\"ALTER USER root IDENTIFIED BY(\" MyNewPassword\");\\\" --connect-expired-password -uroot -p\"MyTmpPassword\"'"], "stdout": "", "stdout_lines": []}

I've tried to set the root password based on the below guide, as well, without any luck.

https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_user_module.html#ansible-collections-community-mysql-mysql-user-module

Thanks!

CodePudding user response：

This is the playbook you could use. This sets the 'debian-sys-main' user as a root user of the database.

---
- name: root | stat to check whether /root/.my.cnf exists
  stat:
    path: /root/.my.cnf
  register: cnf_file

- block:

    - name: root | place temporary cnf file
      template:
        src: temp_cnf.j2
        dest: /etc/my.cnf
        mode: '0644'

    - name: root | start mysql to add the debian-sys-maint user
      systemd:
        name: mysql
        state: started
        enabled: true

    - name: root | get temp root password
      shell: >-
        grep 'temporary password' /var/log/mysqld.log |
        awk '{print $NF}' | tail -n 1
      register: temp_root_pw
      no_log: true

    - name: root | set root password
      shell: >-
        mysqladmin -u root
        --password="{{ temp_root_pw.stdout }}"
        password "{{ mysql_root_password }}"
      no_log: true

    - name: root | set debian-sys-maint user and password
      mysql_user:
        name: debian-sys-maint
        password: "{{ mysql_system_password }}"
        priv: '*.*:ALL,GRANT'
        update_password: always
        state: present
        login_unix_socket: /var/run/mysqld/mysqld.sock
        login_user: root
        login_password: "{{ mysql_root_password }}"
      no_log: true

    - name: root | copy root.cnf
      template:
        src: root.cnf.j2
        dest: /etc/mysql/root.cnf
        mode: '0600'
        owner: root
        group: root

    - name: root | make symlink of file for root db access
      file:
        state: link
        src: /etc/mysql/root.cnf
        path: /root/.my.cnf

    - name: root | delete anonymous connections
      mysql_user:
        name: ""
        host_all: true
        state: absent
      no_log: true

    - name: root | secure root user
      mysql_user:
        name: root
        host: "{{ item }}"
      no_log: true
      loop:
        - ::1
        - 127.0.0.1
        - localhost

    - name: root | ensure test database is removed
      mysql_db:
        name: test
        login_user: root
        state: absent

    - name: root | stop mysql again
      systemd:
        name: mysql
        state: stopped
        enabled: true

    - name: root | remove mysqld log file
      file:
        path: /var/log/mysqld.log
        state: absent

  when: not cnf_file.stat.exists

The temp_cnf.j2:

[client]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

And the root.cnf.j2

# {{ ansible_managed }}

# This file is symlinked to /root/.my.cnf to use passwordless login for the root user

[client]
socket   = {{ mysqld.socket }}
user     = debian-sys-maint
password = {{ percona_system_password }}

[mysql_upgrade]
socket   = {{ mysqld.socket }}
user     = debian-sys-maint
password = {{ percona_system_password }}

Some vars:

mysql_root_password: my_password
mysql_system_password: my_password
mysqld:
  socket: /var/run/mysqld/mysqld.sock 

Should work for CentOS 8, Rocky Linux and Oracle Linux as well.