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
    dbname: wordpressdb
    dbuser: wordpressuser
    password: REDhat@123
  - name: installing mysql-python
      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


 TASK [Creating database] 
 fatal: []: 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

  • as the error message showed, it cannot find "my.cnf". So check to see if you mysql cnf file in place. Jan 9, 2018 at 14:12

4 Answers 4


‘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+


  # ...deleted...

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

- name: Ensure mysql root password is updated for all root accounts
    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
    src:  my.cnf.j2
    dest: /root/.my.cnf
    owner: root
    mode: 0600
  become: yes
  notify: Restart MySQL


password={{ mysql.root_db_password }}


  root_db_password: REDACTED
    - "{{ ansible_hostname }}"
    - ::1
    - localhost
  • This didn't work for me on CentOS 7 and with MySQL wsrep 5.7 (galera cluster). I had to modify the task a bit, but got a access denied. Then I provided the login_user and login_password, but that didn't work either. (1862, u'Your password has expired. To log in you must change it using a client that supports expired passwords.')
    – iamcheko
    Feb 23, 2020 at 17:10

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:


(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.

  • @sanzante..thanks for the replay. For login, mysql 5.7 does not uses empty password.We chave to take it from /var/log/mysqld.log.So wheather i can fetch that password i mentioned above.Do i want to create script for that
    – arun mohan
    Jan 10, 2018 at 11:07
  • That's probably a different question. My answer is about passing credentials to mysql_* tasks (I may got your question wrong, though). I guess is best if you open a new question like: How I get the default password set by packaging system when installing MySQL using Ansible or alike.
    – sanzante
    Jan 10, 2018 at 14:56

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 

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
    login_unix_socket: /var/run/mysqld/mysqld.sock
    name: {{ dbname }}
    state: present

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .