Unable to create mysql database through ansible
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
‘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
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.
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
}}"'