New to KubeDB? Please start here.

Initialize MariaDB using Script

This tutorial will show you how to use KubeDB to initialize a MariaDB database with *.sql, *.sh and/or *.sql.gz script. In this tutorial we will use .sql script stored in GitHub repository kubedb/mysql-init-scripts.

Note: The yaml files that are used in this tutorial are stored here folder in GitHub repository kubedb/docs.

Before You Begin

• At first, you need to have a Kubernetes cluster, and the kubectl command-line tool must be configured to communicate with your cluster. If you do not already have a cluster, you can create one by using kind.

• Now, install KubeDB cli on your workstation and KubeDB operator in your cluster following the steps here.

• To keep things isolated, this tutorial uses a separate namespace called demo throughout this tutorial.

  $ kubectl create ns demo
namespace/demo created

Prepare Initialization Scripts

MariaDB supports initialization with .sh, .sql and .sql.gz files. In this tutorial, we will use init.sql script from mysql-init-scripts git repository to create a TABLE kubedb_table in mysql database.

We will use a ConfigMap as script source. You can use any Kubernetes supported volume as script source.

At first, we will create a ConfigMap from init.sql file. Then, we will provide this ConfigMap as script source in init.script of MariaDB crd spec.

Let’s create a ConfigMap with initialization script,

$ kubectl create configmap -n demo md-init-script \
--from-literal=init.sql="$(curl -fsSL https://github.com/kubedb/mysql-init-scripts/raw/master/init.sql)"
configmap/md-init-script created

Create a MariaDB database with Init-Script

Below is the MariaDB object created in this tutorial.

apiVersion: kubedb.com/v1alpha2
kind: MariaDB
metadata:
  name: sample-mariadb
  namespace: demo
spec:
  version: "10.5.8"
  storage:
    storageClassName: "standard"
    accessModes:
    - ReadWriteOnce
    resources:
      requests:
        storage: 1Gi
  init:
    script:
      configMap:
        name: md-init-script
  terminationPolicy: WipeOut

$ kubectl create -f https://github.com/kubedb/docs/raw/v2021.06.23/docs/examples/mysql/Initialization/demo-1.yaml
mysql.kubedb.com/mysql-init-script created

Here,

• spec.init.script specifies a script source used to initialize the database before database server starts. The scripts will be executed alphabatically. In this tutorial, a sample .sql script from the git repository https://github.com/kubedb/mysql-init-scripts.git is used to create a test database. You can use other volume sources instead of ConfigMap. The *.sql, *sql.gz and/or *.sh sripts that are stored inside the root folder will be executed alphabatically. The scripts inside child folders will be skipped.

KubeDB operator watches for MariaDB objects using Kubernetes api. When a MariaDB object is created, KubeDB operator will create a new StatefulSet and a Service with the matching MariaDB object name. KubeDB operator will also create a governing service for StatefulSets with the name kubedb, if one is not already present. No MariaDB specific RBAC roles are required for RBAC enabled clusters.

$ kubectl get mariadb -n demo sample-mariadb -oyaml
apiVersion: kubedb.com/v1alpha2
kind: MariaDB
metadata:
  ...
  name: sample-mariadb
  namespace: demo
  ...
spec:
  authSecret:
    name: sample-mariadb-auth
  init:
    initialized: true
    script:
      configMap:
        name: md-init-script
  ...
  replicas: 1
  storage:
    accessModes:
    - ReadWriteOnce
    resources:
      requests:
        storage: 1Gi
    storageClassName: standard
  storageType: Durable
  terminationPolicy: WipeOut
  version: 10.5.8
status:
  ...
  phase: Ready

KubeDB operator sets the status.phase to Ready once the database is successfully created.

Now, we will connect to this database and check the data inserted by the initlization script.

# Connecting to the database
$ kubectl exec -it -n demo sample-mariadb-0 -- bash
root@sample-mariadb-0:/ mysql -u${MYSQL_ROOT_USERNAME} -p${MYSQL_ROOT_PASSWORD}
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.5.8-MariaDB-1:10.5.8+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

# Showing the inserted `kubedb_table`
Database changed
MariaDB [mysql]> select * from  kubedb_table;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |
+----+-------+
3 rows in set (0.001 sec)

MariaDB [mysql]> quit;
Bye

Cleaning up

To cleanup the Kubernetes resources created by this tutorial, run:

$ kubectl delete mariadb -n demo sample-mariadb
mariadb.kubedb.com "sample-mariadb" deleted
$ kubectl delete ns demo
namespace "demo" deleted