mySQL InnoDB cluster inside a Kubernetes cluster

Database

InnoDB is a storage engine for the MySQL and MariaDB relational database management systems. It was created by Innobase Oy, a Finnish software company that was acquired by Oracle Corporation in 2005. InnoDB is the default storage engine for both MySQL and MariaDB, and it is widely used in production environments due to its reliability, performance, and advanced features.

Key features

ACID Compliance

InnoDB is designed to ensure ACID (Atomicity, Consistency, Isolation, Durability) compliance, which guarantees data integrity and reliability. It provides transactional support, allowing multiple operations to be grouped together as a single transaction, ensuring that either all the operations are committed or none of them are.

Row-Level Locking

InnoDB uses row-level locking for concurrent access to the data, which allows multiple transactions to read and write to different rows simultaneously without conflicting with each other. This improves performance and concurrency in high-traffic environments.

Foreign Key Constraints

InnoDB supports foreign key constraints, which enforce referential integrity between tables. It ensures that data in related tables remain consistent by automatically enforcing the defined relationships.

Crash Recovery

InnoDB has a robust crash recovery mechanism that ensures data consistency even in the event of a system crash or power failure. It uses a write-ahead logging (WAL) technique to record changes to the database in a transaction log, which can be used for recovery in case of a crash.

MVCC

InnoDB uses Multi-Version Concurrency Control (MVCC) to provide consistent reads and high concurrency. MVCC allows each transaction to see a snapshot of the database at the time it started, ensuring that transactions don't interfere with each other.

Buffer Pool

InnoDB utilizes a buffer pool, which is an area of memory used to cache frequently accessed data and indexes. The buffer pool helps improve performance by reducing disk I/O operations, as data can be retrieved from memory instead of disk.

Automatic Crash Recovery

InnoDB has a built-in mechanism to automatically recover from crashes during database startup. It ensures that the database can be brought back online quickly and efficiently after an unexpected shutdown.

Scalability and Performance

InnoDB is designed to scale well with multi-core processors and high-concurrency workloads. It provides various tuning options and optimizations to improve performance, including configurable buffer pool size, adaptive hash indexes, and I/O optimizations.

Overall, InnoDB is a reliable and feature-rich storage engine that provides transactional support, concurrency control, and data integrity guarantees. It is widely used in production environments where reliability, performance, and data consistency are critical requirements.

Deploying MySQL InnoDB cluster

To deploy MySQL InnoDB in Kubernetes we will use a Helm Chart and the MySQL Operator.

First of all, we have to add the MySQL Operator Helm repository to your Helm configuration by running the following command.

❯ helm repo add mysql-operator https://mysql.github.io/mysql-operator/

Then let's update the Helm repositories to fetch the latest information about available charts.

❯ helm repo update

Now we are ready to install the MySQL Operator using the Helm chart provided by the repository.

helm install mysql-operator mysql-operator/mysql-operator --namespace mysql --create-namespace

Once the operator is deployed we can create a MySQL InnoDB cluster instance using a custom resource definition (CRD) and Helm Chart. Let's create values.yaml file first.

credentials:
  root:
    user: 'myUser'
    password: 'myPassword'
    host: '%'
serverInstances: 2
routerInstances: 2
tls:
  useSelfSigned: true

In the above example, we're creating a MySQL InnoDB cluster instance with two replicas and two routers. We also provide credentials - a username and a password. Now we can apply the YAML file using Helm Chart:

❯ helm install mysql-innodbcluster mysql-operator/mysql-innodbcluster --namespace mysql --create-namespace -f values.yml

Ensure that the MySQL InnoDB cluster resource is created.

❯ kubectl get innodbclusters.mysql.oracle.com mysql-innodbcluster
NAME                  STATUS   ONLINE   INSTANCES   ROUTERS   AGE
mysql-innodbcluster   ONLINE   2        2           2         78d

Check whether associated Pods are running without any errors.

❯ kubectl -n mysql get pods
NAME                                          READY   STATUS    RESTARTS      AGE
mysql-innodbcluster-0                         2/2     Running   1             78d
mysql-innodbcluster-1                         2/2     Running   7 (68d ago)   78d
mysql-innodbcluster-router-7c5f98f9cf-827rq   1/1     Running   0             78d
mysql-innodbcluster-router-7c5f98f9cf-9k98d   1/1     Running   0             78d
mysql-operator-6766d8579c-lbglv               1/1     Running   0             87d

That's it! You have deployed MySQL InnoDB in Kubernetes using the MySQL Operator and Helm Chart. The MySQL Operator simplifies the management and configuration of MySQL instances in Kubernetes by providing a custom resource definition. You can customize the MySQL deployment further by exploring the available configuration options in the MySQL Operator documentation.