Unable to create mysql database through ansible

Question

I have mysql 5.7 installed on my remote syystem.I have to create a new database and a new user access to that database. As it is mysql-community-server 5.7 initially i have to grep password from /var/log/mysqld.log

[root@lamp2 labadmin]# cat /var/log/mysqld.log | grep temporary
2018-01-09T10:57:17.326484Z 1 [Note] A temporary password is generated for 
root@localhost: gg%j,opuE3Sm
2018-01-09T10:57:34.471131Z 0 [Note] InnoDB: Creating shared tablespace for 
temporary tables

So by using ansible how do i give root access as i cant find any module to use this password.Do i want to create a script for that. I have executed the following play book and got error

- hosts: lamp
  remote_user: root
  vars:
    dbname: wordpressdb
    dbuser: wordpressuser
    password: REDhat@123
  tasks:
  - name: installing mysql-python
    yum:
      name: MySQL-python
      state: present
  - name: Creating database
    mysql_db: name={{ dbname }} state=present

  - name: Create db User
    mysql_user: name={{ dbuser }} password={{ password }} priv=*.*:ALL
    host='localhost' state=present

error:

 TASK [Creating database] 
 *******************************************************
 fatal: [52.172.48.12]: FAILED! => {"changed": false, "failed": true, "msg": 
 "unable to find /root/.my.cnf. Exception message: (1045, \"Access denied 
 for user 'root'@'localhost' (using password: NO)\")"}

Help me to find a solution.Iam new to ansible.Also i want to know how to reset root password.

Note: i already copied .my.cnf to target containg username and password for mysql login.but i did it manulally.I want to login by using that temporary password at first.How can i make it possible

Answer 1

‘Fresh’ installation of MySQL 5.7 allows local root logins using socket file (crucial point is the line login_unix_socket: /var/run/mysqld/mysqld.sock in tasks/main.yml)

Despite 'shell' workarounds mentioned in other answers for this and similar questions do their job - they are not idempotent and produce the 'changed' state which is not good.

The following snippets work in completely idempotent way on Ubuntu 18.04+

tasks/main.yml

  # ...deleted...

- name: Ensure mysql is running and starts on boot
  service:
    name: mysql
    state: started
    enabled: yes
  become: yes

- name: Ensure mysql root password is updated for all root accounts
  mysql_user:
    name: root
    host: "{{ item }}"
    login_unix_socket: /var/run/mysqld/mysqld.sock
    password: "{{ mysql.root_db_password }}"
    priv: '*.*:ALL,GRANT'
    check_implicit_admin: true
  loop: "{{ mysql.hosts }}"
  become: yes
  notify: Restart MySQL

- name: Create `/root/.my.cnf`  with root password credentials
  template:
    src:  my.cnf.j2
    dest: /root/.my.cnf
    owner: root
    mode: 0600
  become: yes
  notify: Restart MySQL

templates/my.cnf.j2

[client]
user=root
password={{ mysql.root_db_password }}

defaults/main.yml

mysql:
  root_db_password: REDACTED
  hosts:
    - "{{ ansible_hostname }}"
    - 127.0.0.1
    - ::1
    - localhost

Answer 2

As stated in the mysql_db module notes and mysql_user notes (same text in both):

Both login_password and login_user are required when you are passing credentials. If none are present, the module will attempt to read the credentials from ~/.my.cnf, and finally fall back to using the MySQL default login of ‘root’ with no password.

So you have several options:

Pass credentials to the task

- name: Creating database
  mysql_db: name={{ dbname }} state=present login_user={{ DB_USER }} login_password={{ DB_USER_PASS }} 

- name: Create db User
  mysql_user: name={{ dbuser }} password={{ password }} priv=*.*:ALL
  host='localhost' state=present login_user={{ DB_USER }} login_password={{ DB_USER_PASS }} 

Replace DB_USER and DB_USER_PASS with your db user administrator credentials.

But this is far from optimal, because you have to put credentials in every mysql task and your credentials should be in your ansible code. You can use ansible vault to mitigate this but even that there are better options.

Write a ~/.my.cnf file with credentials

Create or edit ~/.my.cnf file in user's home (probably root) with mysql credentials:

[client]
user=root
password="PASSWORD"

(You may need to add other connection details).

The third option I'm not sure how it works in CentOS, but mainly implies that root user can connect to mysql without using credentials. Usually it relies on a /root/-.my.cnf file, but in CentOS may differ.

Answer 3

After googling i found a way to reset the temporary password:

- name: Find temporary password
  shell: "echo `grep 'temporary.*root@localhost' /var/log/mysqld.log | sed 
's/.*root@localhost: //'`"
  register: mysql_root_password_temp
  tags: register

Set the new password using the temporary password
- name: Set new password from temporary password
  shell: 'mysql -e "SET PASSWORD = PASSWORD(''{{ mysql_root_password }}'');" 
--connect-expired-password -u root -p"{{ mysql_root_password_temp.stdout 
}}"'

Answer 4

you need to add the socket for root https://docs.ansible.com/ansible/2.9/modules/mysql_db_module.html#parameter-login_unix_socket

same for mysql_user

- name: Creating database
  mysql_db:
    login_unix_socket: /var/run/mysqld/mysqld.sock
    name: {{ dbname }}
    state: present

- name: Create db User
  mysql_user: 
    login_unix_socket: /var/run/mysqld/mysqld.sock
    name: {{ dbuser }}
    password: {{ password }}
    priv: "*.*:ALL"
    host: 'localhost'
    state: present