How to Connect database within and execute commands using ansible plugin

[ganeshr2]

SUMMARY

use case: Maria DB deployed as a pod in kubernetese, needs to have a database created post-deployment and few tables. When we execute via exec, I am able to pass only one command. but if we need to execute a series of command post opening a connection how do we do this?

ISSUE TYPE

• Bug Report

COMPONENT NAME

K8s_exec

ANSIBLE VERSION

ansible 2.10.4
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.6/site-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.6.8 (default, Nov 16 2020, 16:55:22) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)]

CONFIGURATION

ansible-config dump --only-changed
None

OS / ENVIRONMENT

Centos 7, veth

STEPS TO REPRODUCE

    community.kubernetes.k8s_exec:
      kubeconfig: "/files/kubeconfig"
      namespace: ns1
      pod: mariasqld
      container: sqld
      command: mysql -h 127.0.0.1 -uroot –p
                        connect db1;
                         #create table 

EXPECTED RESULTS

connect and execute the db commands

ACTUAL RESULTS

bad 400 error

[Akasurde]

@ganeshr2 Thanks for reporting this issue. Please provide -vvv output for the run.

[Akasurde]

@ganeshr2 If I understand your requirements correctly, you want to execute several SQL commands to configure the database. I think this can be done using multiple ways.

A few of them as follows -

1. Importing SQL file to MySQL

mysql -u username –-password=your_password database_name < file.sql

Here, file.sql contains your SQL statements.

2. Configuring MySQL after deploying the container with configMaps

You can use configmaps and execute several commands after MySQL pod deployment like

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-deployment-2
  labels:
    app: mysql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
        - name: mysql
          image: mysql:5.7
          ports:
            - containerPort: 3306
          volumeMounts:
            - mountPath: "/var/lib/mysql"
              subPath: "mysql"
              name: mysql-data
            - name: mysql-initdb
              mountPath: /docker-entrypoint-initdb.d
          env:
            - name: MYSQL_ROOT_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: mysql-secrets
                  key: ROOT_PASSWORD
      volumes:
        - name: mysql-data
          persistentVolumeClaim:
            claimName: mysql-data-disk-2
        - name: mysql-initdb
          configMap:
            name: mysql-initdb-config-2
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-initdb-config-2
data:
  init.sql: |
    CREATE DATABASE IF NOT EXISTS mydata;
    USE mydata;
    CREATE TABLE friends (id INT, name VARCHAR(256), age INT, gender VARCHAR(3));
    INSERT INTO friends VALUES (1, 'John Smith', 32, 'm');
    INSERT INTO friends VALUES (2, 'Lilian Worksmith', 29, 'f');
    INSERT INTO friends VALUES (3, 'Michael Rupert', 27, 'm');
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-data-disk-2
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 1Gi

Here, we are using configMap named mysql-initdb-config-2 and storing all SQL commands inside init.sql. Using /docker-entrypoint-initdb.d feature, we can preload the data in MySQL database.

3. Using kubectl connection plugin you can copy sql file and also execute mysql command on the same pod.

podlist - static inventory file

[mysqlpod]
mysql-deployment-2-dc678b74d-6t6m4 ansible_connection=kubectl

---
- hosts: all
  tasks:
    - name: Copy SQL file inside the pod
      copy:
        src: command.sql
        dest: /tmp/command.sql

    - name: Copy creds file
      copy:
        src: client.conf
        dest: /tmp/client.conf

    - name: Execute the commnad
      shell: mysql --defaults-file=/tmp/client.conf -h 127.0.0.1  < /tmp/command.sql

Output looks like this

changed: [mysql-deployment-2-dc678b74d-6t6m4] => {
    "changed": true,
    "cmd": "mysql --defaults-file=/tmp/client.conf -h 127.0.0.1  < /tmp/command.sql",
    "delta": "0:00:00.013893",
    "end": "2021-03-08 08:20:21.938198",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql --defaults-file=/tmp/client.conf -h 127.0.0.1  < /tmp/command.sql",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true,
            "warn": false
        }
    },
    "rc": 0,
    "start": "2021-03-08 08:20:21.924305",
    "stderr": "",
    "stderr_lines": [],
    "stdout": "id\tname\tage\tgender\n1\tJohn Smith\t32\tm\n2\tLilian Worksmith\t29\tf\n3\tMichael Rupert\t27\tm",
    "stdout_lines": [
        "id\tname\tage\tgender",
        "1\tJohn Smith\t32\tm",
        "2\tLilian Worksmith\t29\tf",
        "3\tMichael Rupert\t27\tm"
    ]
}

Let me know if this solves your purpose.

[ganeshr2]

Yes thank you, I choose to use the ansible kubectl plugin to resolve the issue. Appreciate your assistance!

Thank you!

[Akasurde]

For posterity, I wrote a blog about this here - https://medium.com/@AbhijeetKasurde/creating-mysql-pod-with-preloaded-database-2c01c002fdc3