make ansible check if database is present on a remote host

Question

To make sure that host A can connect to the database of the host B, I try to use mysql_db on a remote host

- name: Make sure A can connect to B database
  mysql_db:
    login_user=root
    login_password=password
    login_host=B_address
    login_port=B_port
    name=B_database
    state=present

and I get that error message even if the login/pass is right

msg: unable to connect, check login_user and login_password are correct, 
or alternatively check ~/.my.cnf contains credentials

Am i missing something? can I set login_host with a specific ansible host?

Answer 1

    Did you configure the mysql to accept the connection from Host A because 
by default mysql only accept connection from localhost.

If you have configured that the mysql accept the connection from Host A then you can verify that the database exist

- name: check if DB exists
   shell: mysql -e 'SHOW DATABASES;' | grep {{ B_database }}
   register: dbstatus
   failed_when: dbstatus.rc == 2

Then you can run your task, if the B_database exist

- name: Make sure A can connect to B database
  mysql_db:
    login_user=root
    login_password=password
    login_host=B_address
    login_port=B_port
    name=B_database
    state=present
  when: dbstatus.rc == 0 
  no_log: yes # You can disable this, if you want to print the stdout

If you are sure that the above cases are true and you are still getting error, then please do this:

Add this task inside your task/main.yml

- name: Copy the root credentials as .my.cnf file
   template:
     src: root.cnf.j2
     dest: "~/.my.cnf"
     mode: 0600

and this will be your root.cnf.j2

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

What it will do is, to connect the mysql from the root user without password and perform these task. You can remove it after running all the task or leave it like this because it is under root and have tight permission.

Answer 2

In my situation I only wanted to import an SQL file once, when the database gets created. Here's how to achieve that, the Ansible way:

---
- name: Test playbook
  hosts: localhost
  connection: local
  tasks:
    - name: Database exists
      mysql_db:
        name: some_db
        state: present
      register: database_exists
    - name: Import database schema
      mysql_db:
        name: some_db
        state: import
        target: database.sql.gz
      when: database_exists.changed