make ansible check if database is present on a remote host

ansible run mssql script
ansible mysql
ansible run sql script
ansible database create
ansible database automation
ansible sql server
ansible postgres
ansible mysql role

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?


    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.

mysql_db – Add or remove MySQL databases from a remote host , The connection timeout when connecting to the MySQL server. 'bobdata' mysql_db: name: bobdata state: present - name: Create new databases with names� Have a self-written letsencrypt role (see the Prometheus: RTFM blog monitoring set up with Ansible – Grafana, Loki, and promtail post). Before running the Let’s Encrypt client to obtain a new certificate – need to check if NGINX is installed on a remote host. Let’s use the package_facts module:


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

Database modules — Ansible Documentation, aerospike_migrations – Check or wait for migrations between nodes mysql_db – Add or remove MySQL databases from a remote host � mysql_info from a database � postgresql_idx – Create or drop indexes from a PostgreSQL database � Requires the mysql and mysqldump binaries on the remote host. This module is not idempotent when state is import, and will import the dump file each time if run more than once. Requires the PyMySQL (Python 2.7 and Python 3.X) or MySQL-python (Python 2.X) package on the remote host.


Because ansible was failing on the grep exit code when it was not found, I found a different way to filter the databases:

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

and then you can do the following:

  when: dbstatus.stdout_lines

because python will treat an empty array as false

postgresql_ping – Check remote PostgreSQL server availability , Simple module to check remote PostgreSQL server availability. If the file exists, the server's certificate will be verified to be signed by one of these authorities. This module uses psycopg2, a Python PostgreSQL database adapter. -m postgresql_ping # In the example below you need to generate certificates previously. Ansible Check Disk Usage of Remote Hosts. There are hundreds upon hundreds of modules that you can use with Adhoc command. To view the entire list of modules with their descriptions, run the command below. # ansible-doc -l To view detailed information about a particular module, run the command. # ansible-doc module_name


Using combination of above answers

- 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"

useful to note you can get the output of dbstatus by adding -vvv when you run the playbook

mysql_info – Gather information about MySQL servers — Ansible , The connection timeout when connecting to the MySQL server. It makes sense if login_user is allowed to connect to a specific database only. X) package on the remote host. 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'� Using Ansible with the Packet host; on the remote host, of the dump file to read from or write to. jackdata state: present # Copy database dump file to remote


mysql_db, (See yum.) Both login_password and login_user are required when you are passing credentials. If none are present, the module will attempt� Step 4:-Go to ansible-oracle-master/roles folder.Check oradb-create role is present for database creation. [root@ansible ansible-oracle-master]# cd roles/ [root@ansible roles]# ls -lrt total 0 drwxr-xr-x. 5 root root 61 Jul 30 11:12 oraswracdb-clone drwxr-xr-x. 3 root root 21 Jul 30 11:12 orasw-meta drwxr-xr-x. 5 root root 49 Jul 30 11:12


mssql_db – Add or remove MSSQL databases from a remote host , Ansible for Network Automation Add or remove MSSQL databases from a remote host. The below requirements are needed on the host that executes this module. aliases: db. state. -. Choices: present ←. absent; import. The database state Create a new database with name 'jackdata' - mssql_db: name: jackdata � When ansible-playbook is executed with --check it will not make any changes on remote systems. Instead, any module instrumented to support ‘check mode’ (which contains most of the primary core modules, but it is not required that all modules do this) will report what changes they would have made rather than making them.


postgresql_db – Add or remove PostgreSQL databases from a , Add or remove PostgreSQL databases from a remote host. If the file exists, the server's certificate will be verified to be signed by one of these Permissions checking for SQL commands is carried out as though the (Added in Ansible 2.4 ) name: Create a new database with name "acme" postgresql_db: name: acme � If I execute the task again, since the directory is already present, nothing will be changed. Changing the Permissions for a Directory in Ansible. In the above task, the directory will be created with the default permission. We can set the permissions using the ‘mode’ parameter. We can give it in two ways.


mysql_user – Adds or removes a user from a MySQL database , The connection timeout when connecting to the MySQL server. Override the host option, making ansible apply changes to all hostnames for a given user. X ) package on the remote host. ALL' state: present - name: Create database user using hashed password with all database privileges mysql_user: name: bob � postgresql_owner – Change an owner of PostgreSQL database object postgresql_pg_hba – Add, remove or modify a rule in a pg_hba file postgresql_ping – Check remote PostgreSQL server availability