The Final Playbooks for Automating MariaDB VM Creation and Software Deployment

Another step in my blog-based scratch pad for CodexMCP. This time, I'm tackling automated VM provisioning and database deployment. Instead of manually setting up a MariaDB instance, I built an Ansible playbook that spins up a new Proxmox VM, provisions MariaDB, and sets up a dedicated database user—all without manual intervention.

What This Does

1. Clones a Proxmox VM from a template
2. Assigns an IP address and boots it up
3. Configures MariaDB with a dedicated codexmcp user
4. Ensures everything is properly running and accessible

This process eliminates repetitive tasks and ensures a consistent, repeatable deployment for future environments.

Step 1: Proxmox VM Automation

The first part of the automation clones a VM from a template (ID 3000) using Proxmox’s API. It waits for the VM to be fully created before proceeding, ensuring it’s not locked during setup.

- name: Clone and Configure a New Proxmox VM
  hosts: proxmox
  gather_facts: no
  tasks:

    - name: Clone the VM from Template (3000)
      uri:
        url: "https://{{ inventory_hostname }}:8006/api2/json/nodes/mcp/qemu/3000/clone"
        method: POST
        headers:
          Authorization: "PVEAPIToken=root@pam!codexmcp=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
        body_format: form-urlencoded
        body:
          newid: "2022"
          name: "mariadb-core-1"
          full: "1"
          target: "mcp"
          storage: "mcp-zfs1"
        validate_certs: no
      register: clone_response

    - name: Wait for VM to be fully created (Check Lock Status)
      uri:
        url: "https://{{ inventory_hostname }}:8006/api2/json/nodes/mcp/qemu/2022/status/current"
        method: GET
        headers:
          Authorization: "PVEAPIToken=root@pam!codexmcp=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
        validate_certs: no
      register: vm_status
      until: "'lock' not in vm_status.json.data"
      retries: 20
      delay: 5

    - name: Attach Cloud-Init Drive (Required for User & SSH Injection)
      uri:
        url: "https://{{ inventory_hostname }}:8006/api2/json/nodes/mcp/qemu/2022/config"
        method: POST
        headers:
          Authorization: "PVEAPIToken=root@pam!codexmcp=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
        body_format: form-urlencoded
        body:
          ide2: "local-lvm:cloudinit"
        validate_certs: no
      when: "'lock' not in vm_status.json.data"

    - name: Set IP Address and Boot Configuration
      uri:
        url: "https://{{ inventory_hostname }}:8006/api2/json/nodes/mcp/qemu/2022/config"
        method: POST
        headers:
          Authorization: "PVEAPIToken=root@pam!codexmcp=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
        body_format: form-urlencoded
        body:
          ipconfig0: "ip=10.0.1.65/24,gw=10.0.1.1"
          boot: "c"
          bootdisk: "scsi0"
          machine: "q35"
        validate_certs: no
      when: "'lock' not in vm_status.json.data"

    - name: Start the New VM
      uri:
        url: "https://{{ inventory_hostname }}:8006/api2/json/nodes/mcp/qemu/2022/status/start"
        method: POST
        headers:
          Authorization: "PVEAPIToken=root@pam!codexmcp=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
        validate_certs: no
      when: "'lock' not in vm_status.json.data"

What’s Happening Here

• Step 1: The Proxmox API clones the VM template (3000) into a new VM (2022).
• Step 2: It waits until the VM is fully created before moving forward.
• Step 3: Cloud-Init is attached so SSH and user settings can be injected dynamically.
• Step 4: The IP address (10.0.1.65/24) and boot settings are applied.
• Step 5: The VM is started once everything is configured.

Step 2: Deploying & Configuring MariaDB

Once the VM is live, we use Ansible to install MariaDB, create a database, and set up a user.

---
  - hosts: mariadb_core
    become: yes
    gather_facts: yes
  
    tasks:
      - name: Install MariaDB and dependencies
        apt:
          name:
            - python3
            - python3-pymysql
            - mariadb-server
          state: present
          update_cache: yes
  
      - name: Ensure MariaDB is started and enabled at boot
        service:
          name: mariadb
          state: started
          enabled: yes
  
      - name: Create database `codexmcp`
        community.mysql.mysql_db:
          name: codexmcp
          state: present
          login_unix_socket: /var/run/mysqld/mysqld.sock
  
      - name: Create `codexmcp` user with privileges
        community.mysql.mysql_user:
          name: codexmcp
          password: "secure-db-password"
          priv: "codexmcp.*:ALL"
          host: "%"
          plugin: "mysql_native_password"
          state: present
          login_unix_socket: /var/run/mysqld/mysqld.sock
  
      - name: Flush privileges
        community.mysql.mysql_query:
          query: FLUSH PRIVILEGES;
          login_unix_socket: /var/run/mysqld/mysqld.sock
      - name: Allow MariaDB to listen on all interfaces
        lineinfile:
          path: /etc/mysql/mariadb.conf.d/50-server.cnf
          regexp: '^bind-address'
          line: 'bind-address = 0.0.0.0'
          state: present
        notify: Restart MariaDB

    handlers:
      - name: Restart MariaDB
        service:
           name: mariadb
           state: restarted

Breakdown

1. Install MariaDB and required Python modules for Ansible to interact with MySQL.
2. Ensure the service is running so it’s ready to accept connections.
3. Create the codexmcp database.
4. Set up the codexmcp user with ALL privileges on the database.
5. Flush privileges (although MariaDB often doesn’t require this anymore, it’s here for completeness).
6. Set Bind Port to 0.0.0.0 so MariaDB can listen on the private interface for communication from CodexMCP

Why This Matters

This setup is now fully automated—from VM creation to a working database—eliminating manual configurations and making deployments fast, repeatable, and consistent.

No need to manually install MariaDB
User and database are provisioned immediately
Works across multiple VM deployments
Easily integrates with Cloud-Init for SSH access

This is just another step in CodexMCP’s scratch pad. The goal is to continue automating everything, eliminating friction in managing network and database operations. With this framework in place, I can now move on to more advanced integrations like OpenSearch, monitoring, and full-stack automation.

Note:
Remember, this is a scratchpad—things are evolving at an accelerated pace. Soon, we’ll be back to tuning the core and tying everything together, but taking the time now to automate these foundational pieces will save countless headaches down the road. Every step toward full automation brings CodexMCP closer to a seamless, scalable platform that just works.

-Another Problem Solved
--Bryan