Peter Morgan

Peter Morgan

World ! hello.

MySQL/MariaDB Replication Cache with Redis API

2 minute read

msec is a disk cache with a Redis compatible API using MySQL/MariaDB replication to remain consistent. Inserts, updates or deletes in the database are replicated in real time and persisted to a local store.

Architecture

Architecture

msec is a docker container with these major components:

  • Protocol emulator, providing a Redis compatible API
  • Storage engine, responding to read and write requests
  • K/V plugin uses the /data directory for persistent storage, which should be mounted as a Docker volume.
  • Database replica, implementing the binary replication protocol for both MySQL and MariaDB
  • Telemetry, providing a Prometheus compatible scraping endpoint

Replication

Both MySQL and MariaDB support replication where log events are streamed to a replica in real-time. Both databases have similar terminology, but use different implementations of Global Transaction Identifiers (GTID), msec uses the correct dialect when using either database.

Each table is mapped into a local key-value store identified using the primary or composite key fields. Delete, update and insert events are replicated into the local store. The current GTID is also persisted for recovery on restart.

Redis Compatible API

msec understands the Redis protocol providing a compatible API.

The Redis API keys used by msec have the following format:

database.table.key

For example, the following key:

shortishly.grades.234-56-7890

The above key represents the record identified by 234-56-7890, found in the grades table in the shortishly database.

For composite keys a “/” is used to separate the different elements:

shortishly.cities.Tulsa/OK

The above key represents the city Tusla in the state of OK found in the cities table in the shortishly database.

Getting Started

Follow the Docker installation instructions to get Docker running on your system.

Clone the msec repository for the docker compose.yaml with a MySQL my.cnf (or MariaDB my.cnf) setup for replication, and some sample data included:

git clone https://github.com/shortishly/msec.git

Alternatively, with the Github CLI installed use:

gh repo clone shortishly/msec

Change to the newly cloned directory:

cd msec

Start everything up with MySQL 8.1:

./bin/up

To use MariaDB instead use:

MYSQL_IMAGE=mariadb:11.1 ./bin/up

Or Percona 8:

MYSQL_IMAGE=percona:8 ./bin/up

From the sample data, we will be using the grades table in the shortishly database which uses the SSN as the primary key:

"Last name","First name","SSN","Test1","Test2","Test3","Test4","Final","Grade"
"Alfalfa","Aloysius","123-45-6789",40.0,90.0,100.0,83.0,49.0,"D-"
"Alfred","University","123-12-1234",41.0,97.0,96.0,97.0,48.0,"D+"
"Gerty","Gramma","567-89-0123",41.0,80.0,60.0,40.0,44.0,"C"
"Android","Electric","087-65-4321",42.0,23.0,36.0,45.0,47.0,"B-"
"Bumpkin","Fred","456-78-9012",43.0,78.0,88.0,77.0,45.0,"A-"
"Rubble","Betty","234-56-7890",44.0,90.0,80.0,90.0,46.0,"C-"
"Noshow","Cecil","345-67-8901",45.0,11.0,-1.0,4.0,43.0,"F"
"Buff","Bif","632-79-9939",46.0,20.0,30.0,40.0,50.0,"B+"
"Airpump","Andrew","223-45-6789",49.0,1.0,90.0,100.0,83.0,"A"
"Backus","Jim","143-12-1234",48.0,1.0,97.0,96.0,97.0,"A+"
"Carnivore","Art","565-89-0123",44.0,1.0,80.0,60.0,40.0,"D+"
"Dandy","Jim","087-75-4321",47.0,1.0,23.0,36.0,45.0,"C+"
"Elephant","Ima","456-71-9012",45.0,1.0,78.0,88.0,77.0,"B-"
"Franklin","Benny","234-56-2890",50.0,1.0,90.0,80.0,90.0,"B-"
"George","Boy","345-67-3901",40.0,1.0,11.0,-1.0,4.0,"B"
"Heffalump","Harvey","632-79-9439",30.0,1.0,20.0,30.0,40.0,"C"

In this example, using hgetall, we get all the hash members in the shortishly database, from the grades table, identified by 234-56-7890 (Betty Rubble).

redis-cli hgetall shortishly.grades.234-56-7890

 1) "test4"
 2) "90.0"
 3) "test3"
 4) "80.0"
 5) "test2"
 6) "90.0"
 7) "test1"
 8) "44.0"
 9) "ssn"
10) "234-56-7890"
11) "last"
12) "Rubble"
13) "grade"
14) "C-"
15) "first"
16) "Betty"
17) "final"
18) "46.0"

Or using hget, we get just Betty’s grade:

redis-cli hget shortishly.grades.234-56-7890 grade
"C-"

A ‘C-‘ seems harsh, lets give her a ‘C’ instead:

./bin/db-sql \
  --execute="update shortishly.grades set grade='C' where ssn='234-56-7890'"

(./bin/db-sql will use either mysql or mariadb depending on the database that you’ve chosen).

The cache is automatically updated:

redis-cli hget shortishly.grades.234-56-7890 grade
"C"

Any updates to the MySQL/MariaDB database are replicated through to msec in real-time. Unless deleted, a row remains in the disk based storage, updated when required by the database replication process.

You May Also Enjoy