Make an inconspicuous check if the database is present on the remote host

Question

To make sure host A can connect to host B database, I try to use mysql_db on the 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 this error message even if the username / password 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 available 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 configured that MySQL accepts a connection from host A, then you can verify that the database exists

- 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 database B exists

- 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 correct and still get an error, do the following:

/main.yml

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

root.cnf.j2

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

, mysql root . , root .

Answer 2

ansible grep, , :

> mysql -e "SHOW DATABASES LIKE '<database>'" -sN

:

  when: dbstatus.stdout_lines

python false

Answer 3

- name: check if DB exists
  shell: mysql --host={{ db_host }} --user={{ db_username }} --password={{ db_password }} -e 'SHOW DATABASES;' | grep -c {{ db_name }}
  register: dbstatus
  failed_when: dbstatus.rc == 2

- name: Create database
  mysql_db: name={{db_name}} collation=utf8mb4_unicode_ci state=present login_host={{ db_host }} login_user={{ db_root_username }} login_password={{ db_root_password }}
  when: dbstatus.stdout == "0"

- name: Create application user in the database
  mysql_user: name={{ db_username }} password={{ db_password }} host={{ db_connection_host }} append_privs=true priv={{ db_name }}.*:ALL state=present login_host={{ db_host }} login_user={{ db_root_username }} login_password={{ db_root_password }}
  when: dbstatus.stdout == "0"

, dbstatus, -vvv playbook

Answer 4

, 3 :

• " ", , ,
• " ", ;
• mysql, .

mysql login_user login_password.

:

• mysql
• mysql- mysql ( 3306). , .