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_PASSWORD
that 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 data
directory 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.d
inside the MariaDB container as follows:
$PWD/mdbconfig:/etc/mysql/conf.d
- store configurationWhen using a host mount with SELinux, you need to pass an extra option :z
or :Z
to the end of the volume definition:
z
option indicates that the bind mount content is shared among multiple containers.Z
option 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 mysql
available 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 VPC
plugin 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.yaml
file. 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-password
mariadb-replication-password
mariadb-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.yaml
file.
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 deployed
You 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 mysql
shell
exit;
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 366d
The 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-secret
with 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.