Ansible setup mysql root password

Question

anyone of you have an idea to build a yml for mysql updating of root password and granting privileges? I have created my playbook and on the fresh install its working as expected and no issue at all. But when I do vagrant provision again it now fails to set the root password and I'm getting an error. Below are my codes

mysql.yml

---
- name: Install the MySQL packages
  apt: name={{ item }} state=installed update_cache=yes
  with_items:
    - mysql-server
    - mysql-client
    - python-mysqldb
    - libmysqlclient-dev

- name: drop database {{ dbname }}
  mysql_db:
    name: "{{ dbname }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    state: absent
  delegate_to: "{{ dbhost }}"
  run_once: true

- name: create database {{ dbname }}
  mysql_db:
    name: "{{ dbname }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    state: present
  delegate_to: "{{ dbhost }}"
  run_once: true

- name: ensure mysql is running and starts on boot
  service: name=mysql state=started enabled=true

- name: copy .my.cnf file with root password credentials
  template: src=my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600

- name: update mysql root password for all root accounts "{{ dbpass }}"
  mysql_user: name={{ dbuser }} host={{ item }} password="{{ dbpass }}" priv="{{ dbname }}.*:ALL,GRANT"
  with_items:
    - localhost
    - 127.0.0.1

- name: grant privilege on "{{ dbname }}" to "{{ dbuser }}"
  mysql_user:
    name: "{{ item.user }}"
    host: "{{ item.host }}"
    password: "{{ dbpass }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    priv: "{{ dbname }}.*:ALL"
    state: present
  with_items:
    - { user: "{{ dbuser }}" , host: localhost }
    - { user: "{{ dbuser }}" , host: 127.0.0.1 }
  delegate_to: "{{ dbhost }}"
  run_once: true

- name: ensure anonymous users are not in the database
  mysql_user: name='' host={{ item }} state=absent
  with_items:
    - 127.0.0.1
    - localhost

- name: remove the test database
  mysql_db: name=test state=absent

my.cnf.j2

[client]
user=root
password={{ dbpass }}

defaults/main.yml

---
dbhost: localhost
dbname: mydb
dbuser: root
dbpass: root

I'm able to do everything just fine if its fresh install but running it the second time around gives me the error below

score 4 · Accepted Answer · answered Apr 14 '17 at 18:58

4

Seems like you update .my.cnf with new password just before you attempt to change it with the next task.

And you may want to use host_all option when updating password, because with_items run module several times, and there is a possibility of same error: change password on the first item and can't connect on the second item.

answered Apr 14 '17 at 18:58

Konstantin Suvorov

65,183
9
162
193

You may be right with updating the .my.cnf first before updating actual root account. – MadzQuestioning Apr 15 '17 at 09:06

score 3 · Answer 2 · answered Apr 15 '17 at 09:25

Already figure out the correct answer for this. So I'll be adding my answer just a reference for those having the same trouble as me

===========================================================================

---
# Install the needed package of mysql
- name: Install MySQL packages
  apt: pkg={{ item }} state=installed
  with_items:
    - bundler
    - mysql-server
    - mysql-client
    - libmysqlclient-dev
    - python-mysqldb
    - build-essential

# Update the root password immediately. This should come first as ordering
# is very important
- name: Update mysql root password for all root accounts "{{ dbpass }}"
  mysql_user: name=root password="{{ dbpass }}" priv="*.*:ALL,GRANT"

# After we update the root password we are going to use this everytime
# we do an update or create something on mysql
# we will create a copy in /root/.my.cnf as this will be use to check
# the login or root credential. Meaning this should contain the latest
# password of the root (right after we update the root password)
- name: copy .my.cnf file with root password credentials
  template: src=my.cnf.j2 dest=/root/.my.cnf owner=root mode=0600
  notify: Restart the MySQL service

# Remove the unnecessary db for now
- name: Remove the test database
  mysql_db: name=test state=absent

# Make sure no anonymous user in the db
- name: ensure anonymous users are not in the database
  mysql_user: name='' host={{ item }} state=absent
  with_items:
    - 127.0.0.1
    - localhost

# Delete the user if its existing so that we can create the user again
- name: Delete deploy DB user
  mysql_user: name={{ dbuser }} password={{ dbpass }} state=absent
  notify: Restart the MySQL service

# Create our own user aside from the root password
# here our root password and new user created will have the same password
- name: Add deploy DB user
  mysql_user: name={{ dbuser }} password={{ dbpass }} priv=*.*:ALL,GRANT state=present
  notify: Restart the MySQL service

# Delete databases. This should not be included in production.
# this is only on local so its fine.
- name: Drop databases
  mysql_db:
    name: "{{ item }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    state: absent
  with_items:
    - db1
    - db2
    - "{{ dbname }}"
  run_once: true

# Recreate the databases
- name: Create databases
  mysql_db:
    name: "{{ item }}"
    login_user: "{{ dbuser }}"
    login_password: "{{ dbpass }}"
    state: present
  with_items:
    - db1
    - db2
    - "{{ dbname }}"
  run_once: true

# Grant the privilege for the newly created user
- name: grant privilege on "{{ dbname }}" to "{{ dbuser }}"
  mysql_user:
    name: "{{ item.user }}"
    host: "{{ item.host }}"
    password: "{{ dbpass }}"
    priv: "*.*:ALL"
    state: present
  with_items:
    - { user: "{{ dbuser }}" , host: localhost }
    - { user: "{{ dbuser }}" , host: 127.0.0.1 }

How is this different from my answer, that the order is important? — Konstantin Suvorov, Apr 15 '17 at 17:06
@KonstantinSuvorov actually there's no difference. I just added the complete yml file for this. Just incase someone is curious on what the actual structure of the file might look like — MadzQuestioning, Apr 15 '17 at 18:37
If you want to suplement someone else's answer, you can just edit it directly. Stack Overflow is designed as a wiki. Everyone can edit everyone else's posts and answers. — falsePockets, Jun 05 '19 at 04:35

score 1 · Answer 3 · answered Oct 14 '19 at 23:16

I struggled with this for a while. What helped me in the end is

Realizing that on my version of ubuntu (18.04) mysql runs against the socket /var/run/mysqld/mysqld.sock
Finally reading the fine print on the mysql_user ansible module. It describes exactly how to fix this

MySQL server installs with default login_user of ‘root’ and no password. To secure this user as part of an idempotent playbook, you must create at least two tasks: the first must change the root user’s password, without providing any login_user/login_password details. The second must drop a ~/.my.cnf file containing the new root credentials. Subsequent runs of the playbook will then succeed by reading the new credentials from the file.

Combining these two things, this finally worked for me:

`vars/main.yml`

---
mysql_port: 3306
mysql_socket: /var/run/mysqld/mysqld.sock
mysql_superuser: root
mysql_superuser_home: "{% if mysql_superuser == 'root' %}/root{% else %}/home/{{ mysql_superuser }}{% endif %}"
mysql_superuser_password: youllNeverGuessMyPasswordMuahaha

`tasks/main.yml`

---
- name: Install mysql
  apt:
    name: ['mysql-server', 'mysql-client', 'python2.7-mysqldb']
    state: present
    update_cache: yes

# Allows python to create and manipulate mysql config
- name: Ensure pymysql is present
  pip:
    name: pymysql
    state: present

- name: Update mysql password for superuser `{{ mysql_superuser }}`
  mysql_user:
    # Update the superuser to have all grants and a password
    name: "{{ mysql_superuser }}"
    host: localhost
    password: "{{ mysql_superuser_password }}"
    priv: "*.*:ALL,GRANT"
    # Login *as root* to perform this change, even though you might
    # be altering the root user itself
    login_user: root
    login_password: ""
    login_port: "{{ mysql_port }}"
    login_host: localhost
    login_unix_socket: "{{ mysql_socket }}"
    # As a good measure,have ansible check whether an implicit login
    # is possible first
    check_implicit_admin: yes

- name: Create system-wide mysql configuration file
  template:
    src: system_wide_mysql.cnf.j2
    dest: /etc/my.cnf

- name: Create mysql configuration file for `{{ mysql_superuser }}`
  template:
    src: superuser_mysql.cnf.j2
    dest: "{{ mysql_superuser_home }}/.my.cnf"
  notify:
  - Restart Mysql

templates

System-wide `my.cnf`

[mysqld]
datadir=/var/lib/mysql
socket={{ mysql_socket }}
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
port={{ mysql_port }}

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Superuser `.my.cnf`

[client]
user={{ mysql_superuser }}
password={{ mysql_superuser_password }}

Ansible setup mysql root password

3 Answers3

`vars/main.yml`

`tasks/main.yml`

templates

System-wide `my.cnf`

Superuser `.my.cnf`

Linked

Ansible setup mysql root password

3 Answers3

vars/main.yml

tasks/main.yml

templates

System-wide my.cnf

Superuser .my.cnf

Linked

`vars/main.yml`

`tasks/main.yml`

System-wide `my.cnf`

Superuser `.my.cnf`