Tuesday, August 30, 2022

[SOLVED] Unable to create root user using Ansible Database module in MariaDB 10.3 on CentOS 7 host

Issue

I'm writing a simple playbook for the installation of MariaDB 10.3 using Ansible from Ubuntu 18.04 to my virtual machine running on CentOS 7 and I'm getting the following error which I'm unable to fix after even reading tons of solutions on online forums. Any help would be highly appreciated. Here's the error message

PLAY [myvm] *************************************************************************************************************

TASK [Gathering Facts] **************************************************************************************************
ok: [myvm]

TASK [MariaDB_10.3 official repository] *********************************************************************************
ok: [myvm]

TASK [Installation of mariadb and packages] *****************************************************************************
ok: [myvm]

TASK [Starting and enabling the service] ********************************************************************************
ok: [myvm]

TASK [Setting up root credentials] **************************************************************************************
[WARNING]: Module did not set no_log for update_password
fatal: [myvm]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1045, u\"Access denied for user 'root'@'localhost' (using password: NO)\")"}

PLAY RECAP **************************************************************************************************************
myvm                       : ok=4    changed=0    unreachable=0    failed=1    skipped=0    rescued=0    ignored=0

Here's my complete Ansible playbook

---

- hosts: myvm 
  become: yes
  vars: 
        mysql_root_password: "somerandom"
        wp_user_pass: "anotherrandom"

  tasks:
      - name: MariaDB_10.3 official repository
        yum_repository:
           name: MariaDB
           description: MariaDB_10.3
           baseurl: http://yum.mariadb.org/10.3/centos7-amd64
           gpgkey: https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
           gpgcheck: yes

      - name: Installation of mariadb and packages
        yum: 
           name: 
              - mariadb-server
              - MySQL-python
           state: present

      - name: Starting and enabling the service
        service:
           name: mariadb
           state: started
           enabled: yes

      - name: Setting up root credentials
        mysql_user:
           name: root
           password: "{{ mysql_root_password }}"
           host: localhost
           login_unix_socket: /var/lib/mysql/mysql.sock
           priv: '*.*:ALL,GRANT'
           check_implicit_admin: true 
           state: present

      - name: Creating a new database for wordpress
        mysql_db:
           name: wp_db
           login_user: wp_user
           login_password: "{{ wp_user_pass }}"
           login_host: localhost
           login_port: 3306
           state: present

      - name: Creating a user for wordpress
        mysql_user:
           name: wp_user
           password: "{{ wp_user_pass }}"
           host: localhost
           priv: 'wp_db.*:ALL'
           state: present

      - name: Removing test database
        mysql_db:
           name: test
           login_user: root
           login_password: "{{ mysql_root_password }}"
           state: absent```

Solution

Your error message gives a clue:

"msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf

Try this:

- name: Setting up root credentials
  mysql_user:
    name: root
    password: "{{ mysql_root_password }}"
    login_user: root
    login_password: "{{mysql_root_password}}"
    host: localhost
    login_unix_socket: /var/lib/mysql/mysql.sock
    priv: '*.*:ALL,GRANT'
    check_implicit_admin: true 
    state: present

The URL below has a few good examples, especially for handling the new expires password feature in v5.7 onward.

Especially this part onwards...

- name: Detect and properly set root password
  stat:
    path: /root/.my.cnf
  register: r

- mysql_user:
    name: root
    password: "{{ mysql_root_password }}"
when: r.stat.exists==True

- name: Set new password from temporary password
  shell: 'mysql -e "SET PASSWORD = PASSWORD(''{{ mysql_root_password }}'');" --connect-expired-password -uroot -p"{{ tmp_root_password.stdout }}"'
  when: r.stat.exists==False

# Now that the root password is set to the specified, make it default for the client.
- name: Copy my.cnf
  template:
    src: root_my.cnf.j2
    dest: /root/.my.cnf
    force: yes

See full source here: Handle mysql_secure_installation in Ansible



Answered By - englishPete
Answer Checked By - Katrina (WPSolving Volunteer)
at