Linux – Unable to create thesql database through ansible

ansiblecentoslinuxMySQL

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

Best Answer

‘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
Related Topic