Building Dynamic inventory in Ansible

 Hello Guys,

I have recently worked on a project which involved fetching a data from multiple server at the run time and manipulating it and displaying it Grafana dashboard. the most interesting part of it was generating the  inventory at runtime and using it in the same playbook

let us say we have a mysql db with table contain the information of all the servers 

currently showing 3 column which are of our interest  hostname,ip and some var value

building the dynamic inventory the playbook look like below

---

- hosts: localhost

connection: local

tasks:

- set_fact:

db_database: inventorydb

db_host: inventorydbhost

- name: Query Datacenter hosts from DB

mysql_query:

login_host: "{{ db_host }}"

login_db: "{{ db_database }}"

login_user: "{{ db_user }}"

login_password: "{{ db_password }}"

query:

- select distinct(hostname) from inventory ;

single_transaction: yes

register: dc_db_query

- name: Generate hosts file

template:

src: ./templates/hosts.j2

dest: hosts

- meta: refresh_inventory

 The host.j2 file present in the templates look like this

[all]

{% for item in dc_db_query.query_result[0] %}

{{ item.hostname }}

{% endfor %}

This will build the inventory at the run time as well as  the meta flag at the bottom will help us to refresh the inventory to use it on all the host which it will return 

let me know if you want to know on any other topics 

SSL ENCRYPTED MYSQL REPLICATION

Set up the master server

Setting up the master consists of the following steps:

1. Generate SSL certificates.
2. Edit MySQL configuration my.cnf.
3. Restart MySQL server process.
4. Set up database replication privileges (and force SSL).
5. Perform initial database backup to start replication from.
6. Finish up.

Set up the slave server

The steps for the slave server are:

1. Setup the SSL certificates.
2. Edit MySQL server configuration my.cnf.
3. Restart MySQL server.
4. Configure and start slave server.
5. Finish up.

Detailed steps:

we generate the CA certificate:
$ mkdir ~/mysql-tutorial/ && cd ~/mysql-tutorial/
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 1000 \  -key ca-key.pem -out ca-cert.pem

Create the server certificate, remove its passphrase and sign it:
$ openssl req -newkey rsa:2048 -days 1000 \  -nodes -keyout server-key.pem -out server-req.pem

$ openssl rsa -in server-key.pem -out server-key.pem

$ openssl x509 -req -in server-req.pem -days 1000 \  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem


Create the client certificate, remove its passphrase and sign it

$ openssl req -newkey rsa:2048 -days 1000 \  -nodes -keyout client-key.pem -out client-req.pem

$ openssl rsa -in client-key.pem -out client-key.pem

$ openssl x509 -req -in client-req.pem -days 1000 \  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Install the certificates into to /etc/mysql/ directory:
$ sudo cp *.pem /etc/mysql/

Skip the bind address
assign the id to mysql server and enable the bin log And enable SSL by editing and add the following lines to my.cnf :
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Restart  the mysql server:

sudo service mysql restart

mysql -u root -p -h <host-name>

GRANT REPLICATION SLAVE ON *.*
TO '<slave_user>'@’%'
IDENTIFIED BY '<slave_password>'
REQUIRE SSL;
FLUSH PRIVILEGES;
USE <database name>;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

note the log position and log file name
example. binlog  filename mysql-bin.000003  position :1122
Take the mysql dump  
mysqldump -h localhost -u root -p --opt <database> > ~/<database>.sql

UNLOCK TABLES;
quit;

scp ~/<database>.sql  <username>@<ipaddress>:/path/to/paste/

On The Slave

Edit the slave server

Add the id and skip the networking and bind address of the server.
Id of both the server should not be same

STOP SLAVE;
CREATE DATABASE IF NOT EXISTS <database>;
USE <database>;
SOURCE /path/to/<database>.sql;

CHANGE MASTER TO
MASTER_HOST='<master’s ip>',
MASTER_USER='slave_user',
MASTER_PASSWORD='<password>',
MASTER_CONNECT_RETRY=60,
MASTER_LOG_FILE='<binlog file name>',
MASTER_LOG_POS=<position>,
MASTER_SSL=1,
MASTER_SSL_CA='/etc/mysql/ca-cert.pem',
MASTER_SSL_CERT='/etc/mysql/client-cert.pem',
MASTER_SSL_KEY='/etc/mysql/client-key.pem';
START SLAVE;
SHOW SLAVE STATUS \G;