This repository contains the Pyrrha solution application database that manages sensor readings over time. It uses MariaDB though you can also use MySQL.
The following steps assume you have Git and Docker installed on your machine.
-
Git clone this repo and change into root directory
git clone https://github.com/Pyrrha-Platform/Pyrrha-Database && cd Pyrrha-Database
-
Set the environment variable
All commands shown below use the environment variable
MDB_PASSWORDthat contains the MariaDB password. You should change it to your own secure password.export MDB_PASSWORD=my-secret-pw -
Build the container
MariaDB is available as an image on DockerHub. The Dockerfile in this repository uses that as the base image. Run the following command to build the Pyrrha mariadb component:
docker build -t mariadb . -
Run the container
Use the following command to download the image and run a container that listens on port 3306. The command also mounts the
datadirectory in the cloned repository to/var/lib/mysql. This makes it easier to access the data files in your host system.docker run --name mariadb -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=${MDB_PASSWORD} -p 3306:3306 -d mariadb
The startup configuration is specified in the file
/etc/mysql/my.cnf. If you want to use a customized MariaDB configuration, you can create your alternative configuration file in a directory on the host machine and then mount that directory location as/etc/mysql/conf.dinside the MariaDB container as follows:$PWD/mdbconfig:/etc/mysql/conf.d- store configuration
When using a host mount with SELinux, you need to pass an extra option
:zor:Zto the end of the volume definition:- The
zoption indicates that the bind mount content is shared among multiple containers. - The
Zoption indicates that the bind mount content is private and unshared.
You can learn more in the official Docker guide. This Stack Overflow answer has more details.
docker run --name mariadb -v $PWD/data:/var/lib/mysql:Z -e MYSQL_ROOT_PASSWORD=${MDB_PASSWORD} -p 3306:3306 -d mariadb
-
Verify the database
You should be able to see the Pyrrha database:
docker exec -t mariadb mysql -uroot -p${MDB_PASSWORD} -e 'show databases;'
Output:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | pyrrha | +--------------------+
You can also list all the tables in the pyrrha database:
docker exec -t mariadb mysql -uroot -p${MDB_PASSWORD} -e 'use pyrrha; show tables;'
Output:
+------------------------------+ | Tables_in_pyrrha | +------------------------------+ | event_types | | events | | events_firefighters_devices | | feedback | | firefighter_sensor_log | | firefighter_status_analytics | | firefighters | | fuel_types | | sensors | | status | | user_types | | users | | vmq_auth_acl | +------------------------------+
-
Use
mysql(OPTIONAL)You are ready to go! You can talk to the database by using localhost:3306. If you have
mysqlavailable on the host machine, use the following command to connect:mysql -u root -p${MDB_PASSWORD} --host localhost --port 3306 --protocol=tcp
-
Sign up for IBM Cloud or log in with your existing account.
-
Create an IBM Kubernetes Cluster (IKS) from the catalog.
-
New clusters automatically provision the
Block Storage for VPCplugin for you.Deprecated: for older clusters, add IBM Cloud Block Storage plug-in from the catalog to your cluster in the default namespace. You need this plugin to use persistance volumes and persistance volume claims on IKS. -
Log into your cluster by following these steps
You can verify that you have the correct context by using this command:
kubectl config current-context
You should see an output like
<cluster_name>/<cluster_ID>. -
Add password to
kubernetes/mariadb-secret.yamlfile. You need to get the base 64 encoded password first by using the following command:echo -n ${MDB_PASSWORD} | base64
There are three passwords in the secret yaml file. You can create different passwords for each one.
mariadb-root-passwordmariadb-replication-passwordmariadb-user-password
-
Add Bitnami Helm repo locally
helm repo add bitnami https://charts.bitnami.com/bitnami
-
Apply the secret file
kubernetes/mariadb-secret-sample.yaml. The pods will not run unless you apply this secret with the correct base 64 encoded credentials.kubectl apply -f kubernetes/mariadb-secret-sample.yaml secret/mariadb created
You can verify that the secret was created successfully.
kubectl get secret NAME TYPE DATA AGE mariadb Opaque 3 2m33s
-
Apply the Helm chart with the
values-production.yamlfile.helm install mariadb bitnami/mariadb -f kubernetes/values-production.yaml NAME: mariadb LAST DEPLOYED: Wed Dec 30 14:43:07 2020 NAMESPACE: staging STATUS: deployed REVISION: 1 TEST SUITE: None NOTES: Please be patient while the chart is being deployedYou should see the primary and secondary pods running after some time. The chart first creates the persistent volume claims first before creatings the pods and services
kubectl get pods NAME READY STATUS RESTARTS AGE mariadb-primary-0 1/1 Running 0 8m18s mariadb-secondary-0 1/1 Running 0 8m18s
-
Copy the SQL data file to the primary pod.
kubectl cp data-seed/pyrrha.sql mariadb-primary-0:/tmp/pyrrha.sql
Check if the file is present in the container
kubectl exec -it mariadb-primary-0 -- ls -lta /tmp pyrrha.sql -
Load the data into the database.
First, set permissions so your can load the SQL without having root privileges:
kubectl exec mariadb-primary-0 -- mysql -uroot -p${MDB_PASSWORD} -e 'SET GLOBAL log_bin_trust_function_creators = 1;'
Next, load the SQL file
kubectl exec -it mariadb-primary-0 -- mysql -uroot -p${MDB_PASSWORD} -e 'source /tmp/pyrrha.sql;'
You just deployed MariaDB On IBM Cloud Kubernetes Services and loaded the pyrrha tables and stored procedures. You can see the tables with the following command:
kubectl exec mariadb-primary-0 -- mysql -uroot -p${MDB_PASSWORD} -e 'use pyrrha; show tables' Tables_in_pyrrha event_types events events_firefighters_devices feedback firefighter_sensor_log firefighter_status_analytics firefighters fuel_types sensors status user_types users
-
Log into MariaDB using your current root password.
mysql -u root -pcurrentpassword --host localhost --port 3306 --protocol tcp
-
Change password for the root user
UPDATE mysql.user SET Password=PASSWORD('Test@123$') WHERE User='root';
-
Flush privileges
FLUSH PRIVILEGES;
-
Exit the
mysqlshellexit;
-
You now need to change the password in the secret used by MariaDB. You need to know the name of the secret.
kubectl get secret | grep mariadb mariadbxx-xxxx-xx Opaque 2 366dThe describe command will show us the content of this seret
kubectl describe secret mariadbxx-xxxx-xx Name: mariadbxx-xxxx-xx ... ... Data ==== mariadb-replication-password: 10 bytes mariadb-root-password: 12 bytes
You can now update this secret with the new password:
kubectl create secret generic mariadbxx-xxxx-xx --from-literal=mariadb-root-password=Test@123$ --dry-run -o yaml \ | kubectl apply -f - -
You can similarly change the other cluster secrets that use this password. The example below updates secret called
rulesdecision-secretwith the new password:kubectl create secret generic rulesdecision-secret --from-literal=MARIADB_PASSWORD=Test@123$ --dry-run -o yaml \ | kubectl apply -f -
-
Remove the Helm chart
helm uninstall mariadb
-
Remove the persistent volume claims:
kubectl delete pvc data-mariadb-primary-0 kubectl delete pvc data-mariadb-secondary-0
-
Remove the secret:
kubectl delete secret mariadb
Please read CONTRIBUTING.md for details on our code of conduct, and the process for submitting Pyrrha pull requests.
This project is licensed under the Apache 2 License - see the LICENSE file for details.