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)