Support for db initialization scripts

[fabioportieri]

Hello

it would be nice to have an out-of-the-box support for loading sql scripts during initialization of mariadb instance

afaik you could achieve this simply with configmaps, if scripts loaded is < 1Mb, or by implementing a container that clone a git repository and mounts the script in a volume, but it seems quite cumbersome

thank you

[mmontes11]

Hey there @fabioportieri !

I've been thinking about this but I don't think we should overload more the MariaDB bootstrap process as it is could currently become very heavy by performing tasks like restoring a backup.

Alternatively we could define a new CRD SqlJob which can execute *.sql scripts in a MariaDB impersonating an user:

apiVersion: mariadb.mmontes.io/v1alpha1
kind: SqlJob
metadata:
  name: mariadb-create-table
spec:
     database: mariadb
     asUser: mariadb
     passwordSecretKeyRef:
        name: mariadb
        key: password
      mariaDbRef:
         name: mariadb
      sqlScript: |
           CREATE OR REPLACE TABLE foo (a int);

The user would be able to provide a Configmap key reference instead:

apiVersion: mariadb.mmontes.io/v1alpha1
kind: SqlJob
metadata:
name: mariadb-create-table
spec:
   database: mariadb
   asUser: mariadb
   passwordSecretKeyRef:
      name: mariadb
      key: password
    mariaDbRef:
       name: mariadb
    sqlScriptConfigmapKeyRef:
        name: mariadb-script
        key: 'init.sql'

and also orchestrate multiple SqlJob by providing a dependsOn:

apiVersion: mariadb.mmontes.io/v1alpha1
kind: SqlJob
metadata:
 name: mariadb-insert-table
spec:
    dependsOn:
       - name: mariadb-create-table
    database: mariadb
    asUser: mariadb
    passwordSecretKeyRef:
       name: mariadb
       key: password
     mariaDbRef:
        name: mariadb
     sqlScript: |
          INSERT INTO foo VALUES(1);

Does this cover your needs?

[mmontes11]

Hey @fabioportieri !

This has been implemented, take a look at the updated quickstart guide and examples:

• https://github.com/mmontes11/mariadb-operator#quickstart
• https://github.com/mmontes11/mariadb-operator/tree/main/config/samples/sqljobs

Closing!