You are looking at the documentation of a prior release. To read the documentation of the latest release, please visit here.

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