Unable to create mysql database through ansible
Solution 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
Solution 2
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
}}"'
Solution 3
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.
Related videos on Youtube
arun mohan
Updated on September 18, 2022Comments
-
arun mohan over 1 year
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
-
Tux_DEV_NULL over 6 yearsas the error message showed, it cannot find "my.cnf". So check to see if you mysql cnf file in place.
-
-
arun mohan over 6 years@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
-
iamcheko about 4 yearsThis 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.')